| Choosing execution plan with optimizer_index_caching [message #384593] |
Wed, 04 February 2009 06:31  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
I have following seeries of statements
SQL> alter session set optimizer_index_caching = 0 ;
Session altered.
SQL> delete from plan_table where statement_id = 'RBN' ;
6 rows deleted.
SQL> explain plan set statement_id = 'RBN' for
2 select ASSET_CD, B.BRANCH, A.CCISCLASS
3 From SRC_BOI_CCIS_MIS_DTL B, SRC_BOI_CCIS_MIS_HDR A
4 Where A.as_of_date = B.as_of_date
5 And A.brn_cd = B.brn_cd
6 And A.Borr_cd = B.BORR_CD
7 And A.as_of_date = '31-Dec-2008'
8 And B.facilitycd < '500' ;
Explained.
SQL> SELECT id, parent_id,object_name, round(cardinality/1024/1024,0) "ROWS" ,round(cost/1024,0) cos
t,
2 lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
3 decode(id, 0, 'Cost = '||position) "Query Plan"
4 FROM plan_table
5 START WITH id = 0 and statement_id = 'RBN'
6 CONNECT BY prior id = parent_id and statement_id = 'RBN';
ID PARENT_ID OBJECT_NAME ROWS COST Query Plan
--------- --------- ------------------------------ --------- --------- -----------------------------
0 0 4 SELECT STATEMENT Cost = 3896
1 0 0 4 HASH JOIN
2 1 SRC_BOI_CCIS_MIS_HDR 2 0 TABLE ACCESS BY INDEX ROWID SRC_BOI_CCIS_MIS_HDR
3 2 IDX_ASOFDATE_BRNCD_BORRCD_HDR 2 0 INDEX RANGE SCAN IDX_ASOFDATE_BRNCD_BORRCD_HDR
4 1 SRC_BOI_CCIS_MIS_DTL 3 0 TABLE ACCESS BY INDEX ROWID SRC_BOI_CCIS_MIS_DTL
5 4 SRC_BOI_CCIS_MIS_DTL_PK 0 0 INDEX RANGE SCAN SRC_BOI_CCIS_MIS_DTL_PK
6 rows selected.
SQL>
SQL> alter session set optimizer_index_caching = 100;
Session altered.
SQL> delete from plan_table where statement_id = 'RBN' ;
12 rows deleted.
SQL> explain plan set statement_id = 'RBN' for
2 select ASSET_CD, B.BRANCH, A.CCISCLASS
3 From SRC_BOI_CCIS_MIS_DTL B, SRC_BOI_CCIS_MIS_HDR A
4 Where A.as_of_date = B.as_of_date
5 And A.brn_cd = B.brn_cd
6 And A.Borr_cd = B.BORR_CD
7 And A.as_of_date = '31-Dec-2008'
8 And B.facilitycd < '500' ;
Explained.
SQL> SELECT id, parent_id,object_name, round(cardinality/1024/1024,0) "ROWS" ,round(cost/1024,0) cos
t,
2 lpad(' ', 2*(level-1))||operation||' '||options||' '||object_name||' '||
3 decode(id, 0, 'Cost = '||position) "Query Plan"
4 FROM plan_table
5 START WITH id = 0 and statement_id = 'RBN'
6 CONNECT BY prior id = parent_id and statement_id = 'RBN';
ID PARENT_ID OBJECT_NAME ROWS COST Query Plan
--------- --------- ------------------------------ --------- --------- -----------------------------
0 0 4 SELECT STATEMENT Cost = 3896
1 0 0 4 HASH JOIN
2 1 SRC_BOI_CCIS_MIS_HDR 2 0 TABLE ACCESS BY INDEX ROWID SRC_BOI_CCIS_MIS_HDR
3 2 IDX_ASOFDATE_BRNCD_BORRCD_HDR 2 0 INDEX RANGE SCAN IDX_ASOFDATE_BRNCD_BORRCD_HDR
4 1 SRC_BOI_CCIS_MIS_DTL 3 0 TABLE ACCESS BY INDEX ROWID SRC_BOI_CCIS_MIS_DTL
5 4 SRC_BOI_CCIS_MIS_DTL_PK 0 0 INDEX RANGE SCAN SRC_BOI_CCIS_MIS_DTL_PK
6 rows selected.
SQL>
In both the above cases keeping optimizer_index_caching = 0 or 100, it chooses HASH JOIN.
I was trying to choose NESTED LOOPS by setting optimizer_index_caching = 0, but it doesn't. What could be the reason ?
Brayan
|
|
|
|
| Re: Choosing execution plan with optimizer_index_caching [message #384609 is a reply to message #384593] |
Wed, 04 February 2009 08:15  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Because, based on the statistics that the optimizer has to work with, it reckons that it's more efficient to read all the rows and Hash Jin them than it would be to read through one table a row at a time and look at the second table.
Don't forget that Optimizer_Index_Caching just tells the CBO what percentage of index blocks it can expect to find in the Cache. As the Hash Join it's looking at is based on two index range scans of tables, changing O_I_C will affect the cost of a HJ and an NL in a similar fashion.
Also - that parameter will affect the CBO decision for every query in the system. You really shouldn't use it to try to tune a single query.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
