Home » RDBMS Server » Performance Tuning » Choosing execution plan with optimizer_index_caching (9.2.0.7)
Choosing execution plan with optimizer_index_caching [message #384593] Wed, 04 February 2009 06:31 Go to next message
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 Go to previous messageGo to next message
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.
Re: Choosing execution plan with optimizer_index_caching [message #384611 is a reply to message #384609] Wed, 04 February 2009 08:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also - '31-Dec-2008' isn't a date, it's a string.
to_date('31-Dec-2008','dd-mon-yyyy') is a date.
Previous Topic: Oracle.exe using above 95% cpu usage
Next Topic: Tuning on Window Server
Goto Forum:
  


Current Time: Tue Dec 06 14:18:48 CST 2016

Total time taken to generate the page: 0.05228 seconds