by Emmanuel Humblot
Hello,
This is my contribution. The number of Buffer Gets I reach is 12.
This is achieved by minimizing the number of query blocks (2) while avoiding outer joins.
Tables here are very small so TABLE ACCESS FULL is not a problem and is even often preferable.
Regards,
Emmanuel Humblot
SET LINES 300 SET PAGES 50 SELECT /*+ GATHER_PLAN_STATISTICS */ L.LOCATION_ID ,L.CITY FROM EMPLOYEES E ,JOBS J ,LOCATIONS L ,DEPARTMENTS D WHERE J.JOB_ID = E.JOB_ID AND ( (E.FIRST_NAME = 'Steven' AND E.LAST_NAME = 'King') OR J.JOB_TITLE = 'President') AND D.LOCATION_ID = L.LOCATION_ID AND E.DEPARTMENT_ID = D.DEPARTMENT_ID UNION SELECT /*+ GATHER_PLAN_STATISTICS */ L.LOCATION_ID ,L.CITY FROM EMPLOYEES E ,JOBS J ,JOB_HISTORY JH ,LOCATIONS L ,DEPARTMENTS D WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID AND JH.JOB_ID = J.JOB_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND J.JOB_TITLE = 'President'; SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL IOSTATS LAST'));
LOCATION_ID CITY
----------- ------------------------------
1700 Seattle
1 row selected.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID du03stsu10th9, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */
l.location_id, l.city FROM employees e,
jobs j,locations l,departments d WHERE j.job_id = e.job_id AND (
(e.first_name = 'Steven' AND e.last_name = 'King') OR j.job_title =
'President' ) and d.location_id = l.location_id AND e.department_id =
d.department_id union SELECT /*+ GATHER_PLAN_STATISTICS */
l.location_id, l.city FROM employees
e,jobs j,job_history jh,locations l,departments d WHERE
jh.employee_id=e.employee_id AND jh.job_id = j.job_id and
d.location_id = l.location_id AND e.department_id = d.department_id AND
j.job_title = 'President'
Plan hash value: 623879073
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 18 (100)| | 1 |00:00:00.01 | 12 |
| 1 | SORT UNIQUE | | 1 | 7 | 528 | 18 (56)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 12 |
|* 3 | HASH JOIN | | 1 | 6 | 462 | 10 (20)| 00:00:01 | 1 |00:00:00.01 | 9 |
|* 4 | HASH JOIN | | 1 | 6 | 390 | 7 (15)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 5 | HASH JOIN | 1 | 6 | 348 | 5 (20)| 00:00:01 | 1 |00:00:00.01 | 5 |
| 6 | TABLE ACCESS FULL | JOBS | 1 | 19 | 513 | 2 (0)| 00:00:01 | 19 |00:00:00.01 | 2 |
| 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 3317 | 2 (0)| 00:00:01 | 107 |00:00:00.01 | 3 |
| 8 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 189 | 2 (0)| 00:00:01 | 27 |00:00:00.01 | 2 |
| 9 | TABLE ACCESS FULL | LOCATIONS | 1 | 23 | 276 | 2 (0)| 00:00:01 | 23 |00:00:00.01 | 2 |
| 10 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 3 |
| 11 | NESTED LOOPS | | 1 | 1 | 66 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 12 | NESTED LOOPS | | 1 | 1 | 54 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 13 | NESTED LOOPS | | 1 | 1 | 47 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 14 | NESTED LOOPS | | 1 | 1 | 40 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
|* 15| TABLE ACCESS FULL | JOBS | 1 | 1 | 27 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 16 | TABLE ACCESS BY INDEX ROWID | JOB_HISTORY | 1 | 1 | 13 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 17| INDEX RANGE SCAN | JHIST_JOB_IX | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
| 18 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 0 | 1 | 7 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 19| INDEX UNIQUE SCAN | EMP_EMP_ID_PKX | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 |
| 20 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 0 | 1 | 7 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 21| INDEX UNIQUE SCAN | DEPT_ID_PKX | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 |
|* 22| INDEX UNIQUE SCAN | LOC_ID_PKX | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 |
| 23 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 0 | 1 | 12 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - access("J"."JOB_ID"="E"."JOB_ID")
filter((("E"."FIRST_NAME"='Steven' AND "E"."LAST_NAME"='King') OR "J"."JOB_TITLE"='President'))
15 - filter("J"."JOB_TITLE"='President')
17 - access("JH"."JOB_ID"="J"."JOB_ID")
19 - access("JH"."EMPLOYEE_ID"="E"."EMPLOYEE_ID")
21 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
22 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
Brendan Furey
cmartin2
eaviles94
emmanuelhumblot
jimmybrock
Kim Berg Hansen
nocoug
Rafu
vbarun
Northern California Oracle Users Group 
Nice, Emmanuel.
At first I was going to object slightly, since your code assumes that every employee has a job, and I thought it would be wrong if there was an employee named Steven King without a job. But then I looked at the schema and see that employees.job_id is not null and has foreign key to jobs, so that is OK. Good lesson here that one should look at the tables and constraints and use that knowledge for better code 😉
Even so I cannot reproduce your 12 buffers on my 12.1.0.1.0 instance? I have gathered compute statistics on the HR schema, but running your code never gets below 21 buffers for me – it insists on indexes and nested loops. I’ll try if I can hint it to get it to show same results as you.
LikeLike
[…] Humblot posted an entry that used as few as 12 buffer gets – very good. His entry also reminded me to remember to […]
LikeLike
Hello Kim Berg,
Thank you for your feedback. Have you checked that your parameters optimizer_index_caching and optimizer_index_cost_adj are set to the default value ? If not it can affect the behaviour of the optimizer, making it more or less prone to using indexes than by default.
To test a query with proper values, you don’t have to change the parameter values neither at the instance nor at the session level.
All you have to do is to include the following hints in the query, which results in resetting the parameters at the default value just for the execution of the query :
OPT_PARAM(‘OPTIMIZER_INDEX_CACHING’,0)
OPT_PARAM(‘OPTIMIZER_INDEX_COST_ADJ’,100)
Best regards,
Emmanuel
LikeLike