Just another example - Writing efficient SQL with analytic function

I have a monitoring job set up to alert me when the buffer gets per execution of a SQL above certain threshold. Today I received one as below:

=================================================
start :  13-MAR-15 02.00.23.885 PM
end   :  13-MAR-15 03.00.48.168 PM
snapid from     107851  to     107853
=================================================

********************************************************
list sql with buffer gets per execution > 100000
********************************************************


!!!! ##########  Expensive SQL found ####### !!!!                               
instance   : 1                                                                  
sql_id     : drdbm833ack3c                                                      
Buffer get : 260358                                                             
Execs      : 1                                                                  
BG/exec    : 260358                                                             
Gets/row   : 700                                                                
SQL TEXT   :                                                                    
SELECT p.rec_id, p.CREATE_DATE,                                                 
p.ordered_zzzyyy_type,p.qualified_zzzyyy_type,p.req_type,p.c_transid,p.status,p.
ERR_CODE,p.ERR_DESC,p.SVC_STATE,p.IS_DOWNGRADED,p.ETF_WAIVE_FLAG  FROM          
BL_XYZ_CHNG_PKG p WHERE p.req_type='R'  AND p.status || ''='IN'  AND            
p.jrs_indicator IN ('1','2','3')  and p.c_transid IN (SELECT MAX(C_TRANSID) FROM
BL_XYZ_CHNG_PKG  GROUP BY rec_id)                                               
 
...

After checking this sql, I've realized this may be a classical example where using analytic function can save resource.

 Original one -- Full table Scan on the same table twice 259k gets

SQL> SELECT p.rec_id,
  2         p.CREATE_DATE,
  3         p.ordered_zzzyyy_type,
  4         p.qualified_zzzyyy_type,
  5         p.req_type ,
  6         p.c_transid,p.status,
  7         p.ERR_CODE,p.ERR_DESC,p.SVC_STATE,p.IS_DOWNGRADED,p.ETF_WAIVE_FLAG
  8    FROM
  9         xyzu.BL_XYZ_CHNG_PKG p
 10  WHERE p.req_type='R'
 11     AND p.status || ''='IN'
 12     AND p.jrs_indicator IN ('1','2','3')
 13     and p.c_transid IN
 14          (SELECT MAX(C_TRANSID) FROM BL_XYZ_CHNG_PKG  GROUP BY rec_id)
 15  ;

242 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3808252951

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |  1613 |   127K|       | 83112   (3)| 00:16:38 |
|*  1 |  HASH JOIN           |                 |  1613 |   127K|       | 83112   (3)| 00:16:38 |
|*  2 |   TABLE ACCESS FULL  | BL_XYZ_CHNG_PKG |  1613 |   107K|       | 25463   (3)| 00:05:06 |
|   3 |   VIEW               | VW_NSO_1        |  6443K|    79M|       | 57578   (2)| 00:11:31 |
|   4 |    HASH GROUP BY     |                 |  6443K|    86M|   148M| 57578   (2)| 00:11:31 |
|   5 |     TABLE ACCESS FULL| BL_XYZ_CHNG_PKG |  6459K|    86M|       | 25154   (2)| 00:05:02 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("P"."C_TRANSID"="$nso_col_1")
   2 - filter("P"."REQ_TYPE"='R' AND "P"."STATUS"||''='IN' AND ("P"."JRS_INDICATOR"='1'
              OR "P"."JRS_INDICATOR"='2' OR "P"."JRS_INDICATOR"='3'))


Statistics
----------------------------------------------------------
         95  recursive calls
          0  db block gets
     259618  consistent gets
     267154  physical reads
          0  redo size
      10348  bytes sent via SQL*Net to client
        261  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        242  rows processed

Rewrite with rank() over partition by  construct - one Full table scan with 129k

SQL> SELECT rec_id,
  2         CREATE_DATE,
  3         ordered_zzzyyy_type,
  4         qualified_zzzyyy_type,
  5         req_type ,
  6         c_transid,status,
  7         ERR_CODE,ERR_DESC,SVC_STATE,IS_DOWNGRADED,ETF_WAIVE_FLAG
  8  from
  9  (
 10     SELECT p.rec_id,
 11            p.CREATE_DATE,
 12            p.ordered_zzzyyy_type,
 13            p.qualified_zzzyyy_type,
 14            p.req_type ,
 15            p.c_transid,p.status,
 16            p.ERR_CODE,p.ERR_DESC,p.SVC_STATE,p.IS_DOWNGRADED,p.ETF_WAIVE_FLAG,
 17            rank() over ( partition by rec_id order by c_transid desc ) rank
 18       FROM
 19            xyzu.BL_XYZ_CHNG_PKG p
 20     WHERE p.req_type='R'
 21        AND p.status || ''='IN'
 22        AND p.jrs_indicator IN ('1','2','3')
 23  )  A
 24  where rank=1;

242 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3870177004

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |  1613 |   546K| 25464   (3)| 00:05:06 |
|*  1 |  VIEW                    |                 |  1613 |   546K| 25464   (3)| 00:05:06 |
|*  2 |   WINDOW SORT PUSHED RANK|                 |  1613 |   107K| 25464   (3)| 00:05:06 |
|*  3 |    TABLE ACCESS FULL     | BL_XYZ_CHNG_PKG |  1613 |   107K| 25463   (3)| 00:05:06 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RANK"=1)
   2 - filter(RANK() OVER ( PARTITION BY "REC_ID" ORDER BY
              INTERNAL_FUNCTION("C_TRANSID") DESC )<=1)
   3 - filter("P"."REQ_TYPE"='R' AND "P"."STATUS"||''='IN' AND
              ("P"."JRS_INDICATOR"='1' OR "P"."JRS_INDICATOR"='2' OR "P"."JRS_INDICATOR"='3'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     129827  consistent gets
     127794  physical reads
          0  redo size
      10184  bytes sent via SQL*Net to client
        261  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        242  rows processed

Using analytic function can improve query performance greatly

Let's say we have a table: t (x,y,z), how to output the rows of the table with max value of z based on y?

For example if we have (1,1,2) (1,1,3),(2,1,1), we should get (1,1,3).

Below is a test case that demonstrats two approaches: Approach 1 uses subquery resulting in 38 consistent gets; Approach 2 uses analytic approach resulting in 7 consistent gets

SQL>@test
SQL>set echo on
SQL>drop table t;

Table dropped.

SQL>create table t(x number, y number, z number);

Table created.

SQL>
SQL>insert into t values(1,1,2);

1 row created.

SQL>insert into t values(1,1,3);

1 row created.

SQL>insert into t values(2,1,1);

1 row created.

SQL>
SQL>
SQL>set autotrace on
SQL>-- approach 1
SQL>
SQL>select x, y, z
  2  from t
  3  where
  4   exists ( select 1 from
  5            ( select y, max(z) mz
  6              from t
  7               group by y
  8             ) zz
  9             where t.y=zz.y
 10               and t.z=zz.mz
 11          )
 12  ;

         X          Y          Z
---------- ---------- ----------
         1          1          3

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3359768323

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   117 |     5  (20)| 00:00:01 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | T    |     3 |   117 |     2   (0)| 00:00:01 |
|*  3 |   FILTER             |      |       |       |            |          |
|   4 |    HASH GROUP BY     |      |     1 |    26 |     3  (34)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T    |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE "Y"=:B1 GROUP
              BY "Y",:B2 HAVING MAX("Z")=:B3))
   3 - filter(MAX("Z")=:B1)
   5 - filter("Y"=:B1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         78  recursive calls
          0  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>-- approach 2
SQL>
SQL>
SQL>select x,y,z from
  2  (
  3     select x,y, z, max(z) over (partition by y) mz
  4     from t
  5  )
  6  where z=mz
  7  ;

         X          Y          Z
---------- ---------- ----------
         1          1          3

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2206009079

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |   156 |     3  (34)| 00:00:01 |
|*  1 |  VIEW               |      |     3 |   156 |     3  (34)| 00:00:01 |
|   2 |   WINDOW SORT       |      |     3 |   117 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |     3 |   117 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Z"="MZ")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        272  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Approach 1 is essetially used in a query in one of our production databases that causes performance issue. I suggested we re-write the query using approach 2. My test results of the production-like query show that bad query needs 2M consistent gets to get 6382 rows out while the re-written query needs only about 6k.

Bitmap plans can estimate wrong cardinality

There is a problem query involving 3-table join in one of our prodcutin databases. The difference between the good plan and bad plan is the join order in a hash join execution plan. The reason for Oracle to pick up the bad join order is that Oracle CBO chooses the bitmap plan and this plan gives very wrong cardinality. What I did is to set "_b_tree_bitmap_plans"=false at system level, which allowing the CBO to generate the good plan. Then I created a sql plan baseline for that query from the good plan ( using sqlt/utl/coe_load_sql_baseline.sql script). After that, considering that there may be some queries that benfit from the bitmap access path, I changed back "_b_tree_bitmap_plans" to be true.

Below tests demonstrated how wrong the cardinaltiy estimeated by the bitmap plan could be.

• in the bitmap plan,  the number of rows estimated is 578K

SQL> select * from BOD_OTHR_SERIAL_ACTIVITY_I8400 I8400
  2  where
  3  (I8400.DVTQ_ACCT_NUM = 'A145779917' OR (
  4      I8400.UJTJPM_CUSTOMER_ID = '' AND I8400.UJTJPM_ACCOUNT_ID = ''
  5  ))
  6  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 972526864

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                                |   578K|   170M|   161K (11)| 00:05:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | BOD_OTHR_SERIAL_ACTIVITY_I8400 |   578K|   170M|   161K (11)| 00:05:17 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                                |       |       |            |          |
|   3 |    BITMAP OR                     |                                |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                                |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IX_I8400_04                    |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                                |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IX_I8400_07                    |       |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("I8400"."UJTJPM_CUSTOMER_ID"='' AND "I8400"."UJTJPM_ACCOUNT_ID"='')
   7 - access("I8400"."DVTQ_ACCT_NUM"='A145779917')

• -- disable the bitmap plan

SQL> alter session set "_b_tree_bitmap_plans"=false;

Session altered.

• -- Now the estimated rows is 2

SQL> select * from BOD_OTHR_SERIAL_ACTIVITY_I8400 I8400
  2  where
  3  (I8400.DVTQ_ACCT_NUM = 'A145779917' OR (
  4      I8400.UJTJPM_CUSTOMER_ID = '' AND I8400.UJTJPM_ACCOUNT_ID = ''
  5  ))
  6  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 7978316

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |     2 |   618 |     9   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                                |       |       |            |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| BOD_OTHR_SERIAL_ACTIVITY_I8400 |     1 |   309 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_I8400_07                    |     1 |       |     4  (25)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| BOD_OTHR_SERIAL_ACTIVITY_I8400 |     1 |   309 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IX_I8400_04                    |     1 |       |     2  (50)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("I8400"."DVTQ_ACCT_NUM"='A145779917')
   4 - filter(LNNVL("I8400"."DVTQ_ACCT_NUM"='A145779917'))
   5 - access("I8400"."UJTJPM_CUSTOMER_ID"='' AND "I8400"."UJTJPM_ACCOUNT_ID"='')

•  the acutall number of row is 1:

SQL> select count(*)  from BOD_OTHR_SERIAL_ACTIVITY_I8400 I8400
  2      where
  3      (I8400.CUST_ACCT_NUM = 'A145779917' OR (
  4          I8400.UJTJPM_CUSTOMER_ID = '' AND I8400.UJTJPM_ACCOUNT_ID = ''
  5      ))
  6      ;

  COUNT(*)
----------
         1

1 row selected.

SQL Tuning

Things I think important to know for sql tuning:

• Be able to read execution plan

         - right-most first, from top to bottom
         - visual approach to assist understanding   
         - pay attention to the Predicate Information section

• Obtaining the execution plan

         - AUTOTRACE 
         - dbms_xplan.display_curosr

• Cardinality

         - the predicted number of rows generated by an operation
         - cardinality = selectivity * ( number of input rows)

• COST

         - represent the optimizer's best estimate of the time it will take to execute the statement; CBO always chooses the execution plan with minimum cost.

• Tuning by cardinality feedback

         - using dbms_xplan.display_cursor to obtain E-row vs A-row ( set statistical_level=all;  or using /* gather_plan_statistics */ )

• AWR, ADDM, ASH and wait interface - able to identify the offensive SQLs
• bind variable peeking and plan instability
• 10046 trace & tkprof

         - a rule of thumb to check efficiency: query+ current/rows < 20

• 10053 trace
• join method and join order
• Able to check table indexes and table column statistics ( HISTOGRAM)
• Using top-n query to show the data distribution

         ie.  select * from ( select col_of_insterest,count(*) from table_A group by col_of_interest order by 2 desc) where rownun <=50

• Talk to lead developers

         - tune questions not just tune queries

• Fix a bad query or method of improvement

        - index re-design,
        - hints,
        - gather stats,
        - explore parallelism,
        - plan stability: sql profile( sqlt coe* script) and sql plan baseline (sqlt coe* script)

Avoid Merge Join Cartesian in a SQL Tunning Exercise

Encountered a query that caused CPU utilization high. In a 15 min AWR, this query executes 78 times with total Buffer Gets 949M, which contributes 78.8% of the total. I filled in some bind variable values by checking v$sql_bind_capture view. And I executed the sql from sqlplus with gather_plan_statistics hint. Below is the execution plan witn E-Rows and A-Rows info :

Plan hash value: 4161037915

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|

-------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------ 
|   1 |  SORT ORDER BY                             |                        |      1 |      1 |    113K|00:02:27.54 |    4242K|  12873 |   8580 |    76M|  3013K|   67M (0)|         | 
|   2 |   HASH UNIQUE                              |                        |      1 |      1 |    113K|00:02:23.74 |    4242K|  12873 |   8580 |    72M|  7323K| 9538K (1)|   73728 | 
|*  3 |    FILTER                                  |                        |      1 |        |    114K|00:03:25.41 |    4242K|   4293 |      0 |       |       |          |         | 
|   4 |     NESTED LOOPS                           |                        |      1 |      1 |    114K|00:00:15.77 |    4239K|   4291 |      0 |       |       |          |         | 
|   5 |      MERGE JOIN CARTESIAN                  |                        |      1 |      1 |    199K|00:00:01.71 |    6001 |    123 |      0 |       |       |          |         | 
|   6 |       MERGE JOIN CARTESIAN                 |                        |      1 |      1 |   7112 |00:00:00.44 |    5998 |    123 |      0 |       |       |          |         | 
|*  7 |        HASH JOIN OUTER                     |                        |      1 |      1 |    889 |00:00:00.15 |    5995 |    123 |      0 |   985K|   927K| 1229K (0)|         |
|   8 |         NESTED LOOPS                       |                        |      1 |      1 |    889 |00:00:00.11 |    5979 |    123 |      0 |       |       |          |         | 
|   9 |          NESTED LOOPS                      |                        |      1 |      1 |    889 |00:00:00.10 |    5088 |    123 |      0 |       |       |          |         | 
|* 10 |           HASH JOIN OUTER                  |                        |      1 |      1 |    889 |00:00:00.09 |    4197 |    123 |      0 |   928K|   928K| 1265K (0)|         | 
|  11 |            NESTED LOOPS                    |                        |      1 |      1 |    865 |00:00:00.10 |    4193 |    123 |      0 |       |       |          |         |
|  12 |             NESTED LOOPS                   |                        |      1 |      1 |   1839 |00:00:00.07 |     504 |    123 |      0 |       |       |          |         | 
|  13 |              MERGE JOIN CARTESIAN          |                        |      1 |      1 |      1 |00:00:00.01 |      12 |      0 |      0 |       |       |          |         | 
|  14 |               NESTED LOOPS                 |                        |      1 |      1 |      1 |00:00:00.01 |       8 |      0 |      0 |       |       |          |         | 
|  15 |                NESTED LOOPS                |                        |      1 |      1 |      1 |00:00:00.01 |       5 |      0 |      0 |       |       |          |         | 
|  16 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFDS               |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 17 |                  INDEX UNIQUE SCAN         | PK_OZBJZFDS            |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  18 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFD_CATEGORY       |      1 |     26 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|* 19 |                  INDEX UNIQUE SCAN         | PK_OZBJZFD_CATEGORY    |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |      0 |       |       |          |         | 
|* 20 |                TABLE ACCESS BY INDEX ROWID | OZBJZFD_MARKETS        |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 21 |                 INDEX RANGE SCAN           | PK_OZBJZFD_MARKETS     |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  22 |               BUFFER SORT                  |                        |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|* 23 |                TABLE ACCESS BY INDEX ROWID | MARKETS                |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|* 24 |                 INDEX RANGE SCAN           | PK_MARKETS             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |      1 |      1 |   1839 |00:00:00.07 |     492 |    123 |      0 |       |       |          |         | 
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |      1 |      1 |   1894 |00:00:00.01 |      16 |     13 |      0 |       |       |          |         |
|* 27 |             TABLE ACCESS BY INDEX ROWID    | OQNCVDSS               |   1839 |      1 |    865 |00:00:00.03 |    3689 |      0 |      0 |       |       |          |         |
|* 28 |              INDEX UNIQUE SCAN             | PK_OQNCVDSS            |   1839 |      1 |   1839 |00:00:00.01 |    1841 |      0 |      0 |       |       |          |         | 
|  29 |            VIEW                            |                        |      1 |     29 |     29 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|  30 |             SORT UNIQUE                    |                        |      1 |     29 |     29 |00:00:00.01 |       4 |      0 |      0 |  4096 |  4096 | 4096  (0)|         | 
|  31 |              UNION-ALL                     |                        |      1 |        |     29 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|  32 |               INDEX FULL SCAN              | PK_SOURCE_TARGET_RULES |      1 |     20 |     20 |00:00:00.01 |       1 |      0 |      0 |       |       |          |         | 
|  33 |               TABLE ACCESS FULL            | CARRYOVER_ISOC_MAPPING |      1 |      9 |      9 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         |
|  34 |           TABLE ACCESS BY INDEX ROWID      | SPEED_CODES            |    889 |      1 |    889 |00:00:00.01 |     891 |      0 |      0 |       |       |          |         |
|* 35 |            INDEX UNIQUE SCAN               | PK_SPEED_CODES         |    889 |      1 |    889 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  36 |          TABLE ACCESS BY INDEX ROWID       | OQNCVDS_TYPES          |    889 |      1 |    889 |00:00:00.01 |     891 |      0 |      0 |       |       |          |         | 
|* 37 |           INDEX UNIQUE SCAN                | PK_OQNCVDS_TYPES       |    889 |      1 |    889 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  38 |         INDEX FAST FULL SCAN               | PK_OFFER_PROD          |      1 |   1255 |   1255 |00:00:00.01 |      16 |      0 |      0 |       |       |          |         | 
|  39 |        BUFFER SORT                         |                        |    889 |      8 |   7112 |00:00:00.01 |       3 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|  40 |         TABLE ACCESS FULL                  | BILLING_FREQ           |      1 |      8 |      8 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|  41 |       BUFFER SORT                          |                        |   7112 |     28 |    199K|00:00:00.20 |       3 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|  42 |        TABLE ACCESS FULL                   | UNIT_TYPES             |      1 |     28 |     28 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 43 |      TABLE ACCESS BY INDEX ROWID           | UHCDN_RATES            |    199K|      1 |    114K|00:02:16.61 |    4233K|   4168 |      0 |       |       |          |         | 
|* 44 |       INDEX RANGE SCAN                     | PK_UHCDN_RATES         |    199K|     36 |   4879K|00:01:48.28 |     727K|    911 |      0 |       |       |          |         | 
|* 45 |     INDEX RANGE SCAN                       | PK_OZBJZFD_OQNCVDSS    |    976 |      1 |    933 |00:00:00.02 |    2928 |      2 |      0 |       |       |          |         | 
|* 46 |      INDEX UNIQUE SCAN                     | PK_OQNCVDSS            |      9 |      1 |      4 |00:00:00.01 |      18 |      0 |      0 |       |       |          |         |

-------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------

The AUTOTRACE statistics is as follows:

Statistics
----------------------------------------------------------
         72  recursive calls
          0  db block gets
    4242325  consistent gets
      13434  physical reads
          0  redo size
    9673175  bytes sent via SQL*Net to client
      13494  bytes received via SQL*Net from client
       1141  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
     113976  rows processed

By comparing the E-rows and A-rows from the execution, it is easy to identify that the problem starts from the operation id 25 and 26, where E-rows=1 and A-rows=1839 and 1894.

|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |      1 |      1 |   1839 |00:00:00.07 |        ... 
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |      1 |      1 |   1894 |00:00:00.01 |        ...                   

With E-rows=1, Oracle CBO decides to use "MERGE JOIN CARTESIAN". Notice at the end, CBO estimate only 1 row whereas actual number of rows is 113K. So the key to tune this query is to avoid the Cartesian join at operation id 5 and 6. I modified the query by adding the following hints and of course make sure the tables order is correct in the FROM clause:

/*+ ordered use_hash(E), use_hash(F) */

Here is the execution plan  of the modified query:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                        |     1 |  2841 |    74   (9)| 00:00:01 |
|   1 |  SORT ORDER BY                             |                        |     1 |  2841 |    74   (9)| 00:00:01 |
|   2 |   HASH UNIQUE                              |                        |     1 |  2841 |    73   (7)| 00:00:01 |
|*  3 |    FILTER                                  |                        |       |       |         |     |
|*  4 |     HASH JOIN OUTER                        |                        |     1 |  2841 |    72   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                          |                        |     1 |  2835 |    68   (5)| 00:00:01 |
|   6 |       NESTED LOOPS                         |                        |     1 |  2828 |    67   (5)| 00:00:01 |
|*  7 |        HASH JOIN                           |                        |     1 |  2813 |    66   (5)| 00:00:01 |
|   8 |         NESTED LOOPS                       |                        |     1 |  2801 |    64   (5)| 00:00:01 |
|   9 |          NESTED LOOPS                      |                        |     1 |  2781 |    63   (5)| 00:00:01 |
|* 10 |           HASH JOIN OUTER                  |                        |     1 |  2682 |    17  (18)| 00:00:01 |
|  11 |            NESTED LOOPS                    |                        |     1 |   578 |    11   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                   |                        |     1 |   326 |    10   (0)| 00:00:01 |
|  13 |              MERGE JOIN CARTESIAN          |                        |     1 |   263 |     7   (0)| 00:00:01 |
|  14 |               NESTED LOOPS                 |                        |     1 |   153 |     5   (0)| 00:00:01 |
|  15 |                NESTED LOOPS                |                        |     1 |   125 |     3   (0)| 00:00:01 |
|  16 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFDS               |     1 |   120 |     2   (0)| 00:00:01 |
|* 17 |                  INDEX UNIQUE SCAN         | PK_OZBJZFDS            |     1 |       |     1   (0)| 00:00:01 |
|  18 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFD_CATEGORY       |     1 |     5 |     1   (0)| 00:00:01 |
|* 19 |                  INDEX UNIQUE SCAN         | PK_OZBJZFD_CATEGORY    |     1 |       |     0   (0)| 00:00:01 |
|* 20 |                TABLE ACCESS BY INDEX ROWID | OZBJZFD_MARKETS        |     1 |    28 |     2   (0)| 00:00:01 |
|* 21 |                 INDEX RANGE SCAN           | PK_OZBJZFD_MARKETS     |     1 |       |     1   (0)| 00:00:01 |
|  22 |               BUFFER SORT                  |                        |     1 |   110 |     5   (0)| 00:00:01 |
|* 23 |                TABLE ACCESS BY INDEX ROWID | MARKETS                |     1 |   110 |     2   (0)| 00:00:01 |
|* 24 |                 INDEX RANGE SCAN           | PK_MARKETS             |     1 |       |     1   (0)| 00:00:01 |
|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |     1 |    63 |     3   (0)| 00:00:01 |
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |     1 |       |     2   (0)| 00:00:01 |
|* 27 |             TABLE ACCESS BY INDEX ROWID    | OQNCVDSS               |     1 |   252 |     1   (0)| 00:00:01 |
|* 28 |              INDEX UNIQUE SCAN             | PK_OQNCVDSS            |     1 |       |     0   (0)| 00:00:01 |
|  29 |            VIEW                            |                        |    29 | 61016 |     5  (40)| 00:00:01 |
|  30 |             SORT UNIQUE                    |                        |    29 |  1138 |     5  (80)| 00:00:01 |
|  31 |              UNION-ALL                     |                        |       |       |         |     |
|  32 |               INDEX FULL SCAN              | PK_SOURCE_TARGET_RULES |    20 |   760 |     1   (0)| 00:00:01 |
|  33 |               TABLE ACCESS FULL            | CARRYOVER_ISOC_MAPPING |     9 |   378 |     2   (0)| 00:00:01 |
|* 34 |           TABLE ACCESS BY INDEX ROWID      | UHCDN_RATES            |    21 |  2079 |    46   (0)| 00:00:01 |
|* 35 |            INDEX RANGE SCAN                | PK_UHCDN_RATES         |    72 |       |     2   (0)| 00:00:01 |
|  36 |          TABLE ACCESS BY INDEX ROWID       | OQNCVDS_TYPES          |     1 |    20 |     1   (0)| 00:00:01 |
|* 37 |           INDEX UNIQUE SCAN                | PK_OQNCVDS_TYPES       |     1 |       |     0   (0)| 00:00:01 |
|  38 |         TABLE ACCESS FULL                  | BILLING_FREQ           |     8 |    96 |     2   (0)| 00:00:01 |
|  39 |        TABLE ACCESS BY INDEX ROWID         | UNIT_TYPES             |     1 |    15 |     1   (0)| 00:00:01 |
|* 40 |         INDEX UNIQUE SCAN                  | PK_UNIT_TYPES          |     1 |       |     0   (0)| 00:00:01 |
|  41 |       TABLE ACCESS BY INDEX ROWID          | SPEED_CODES            |     1 |     7 |     1   (0)| 00:00:01 |
|* 42 |        INDEX UNIQUE SCAN                   | PK_SPEED_CODES         |     1 |       |     0   (0)| 00:00:01 |
|  43 |      INDEX FAST FULL SCAN                  | PK_OFFER_PROD          |  1255 |  7530 |     3   (0)| 00:00:01 |
|* 44 |     INDEX RANGE SCAN                       | PK_OZBJZFD_OQNCVDSS    |     1 |    12 |     3   (0)| 00:00:01 |
|* 45 |      INDEX UNIQUE SCAN                     | PK_OQNCVDSS            |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

AUTOTRACE statistics of the modified query is also shown below:

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     479315  consistent gets
       2097  physical reads
          0  redo size
    9673175  bytes sent via SQL*Net to client
      13535  bytes received via SQL*Net from client
       1141  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     113976  rows processed

It can be seen that after tunning the "consistent gets" drop to 479,315 from 4,242,325. In the  production database, I created a SQL Profile to enforce the better plan.

Tuning a Hierarchical Query

Encountered an expensive production sql today, basically it is in the following structure:

select BM.*, V.*  
FROM 
   BMXYZ BM,
   VXYZ V,
   BBXYZ BB
WHERE 
  V.BO_ID=BM.BO_ID
 AND BM.VOL_PARENT_BO_ID IN(SELECT B.VOL_PARENT_BO_ID
                            FROM BMXYZ B
                        START WITH BB.BO_ID=B.VOL_PARENT_BO_ID
                CONNECT BY PRIOR B.BO_ID = B.VOL_PARENT_BO_ID )
AND BB.USER_ID='xyzuvw'
AND V.CONTENT_VENDOR_ID='3000000'
;

At the first glance, it seems there are no join conditions involving BB. Finally I was able to understand what the sql tries to do :

(1) obtain a set of BO_ID's from table BB
(2) for each BO_ID in the set, find all child rows of it from the table BM
(3) finally row source from (2) join table V.

After rewriting it as follows, the query run much faster with only hundreds gets:

select BM.*, V.*  
FROM 
   BMXYZ BM,
   VXYZ V
WHERE 
  V.BO_ID=BM.BO_ID
 AND BM.VOL_PARENT_BO_ID IN(SELECT B.VOL_PARENT_BO_ID
                            FROM BMXYZ B, (select bo_id from BBXYZ  where user_id='xyzuvw') BB
                        START WITH BB.BO_ID=B.VOL_PARENT_BO_ID
                CONNECT BY PRIOR B.BO_ID = B.VOL_PARENT_BO_ID )
AND V.CONTENT_VENDOR_ID='3000000'
;

Create a SQL Plan Baseline based on the good execution plan in AWR

We can create SQL plan baseline for a SQL to ensure the SQL execute with a good execution plan in 11g. The desired good execution plan sometimes can be obtained from AWR.


Today, a colleague sent team a note about the steps he adopted to create a SQL plan baseline for fixing a problem reporting query as follows:

1) -- create a SQL tuning set

exec DBMS_SQLTUNE.CREATE_SQLSET('test');

2) -- load the SQL tuning set with the good execution plan from AWR

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(70837, 71044,
'sql_id=' || CHR(39)||'8w0vxcj017b0m'||CHR(39)'  and plan_hash_value=2753253816',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('test', baseline_ref_cursor);
end;
/



3) -- Verify the good executon plan

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'test','8w0vxcj017b0m'));


4) -- Verify the sql text in the sql tuing set

select *
from dba_sqlset_statements
where sqlset_name = 'test'
order by sql_id;


5) -- create the sql plan baseline from sql tuning set



set serveroutput on
declare
my_integer pls_integer;

begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'test',
sqlset_owner => 'Vxxxxx',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/



6) -- verify

select * from dba_sql_plan_baselines



update Aug. 26, 2013 -

In the latest sqlt tool utl directory, there is a script called  coe_load_sql_baseline.sql
, which can be used to create sql plan baseline for  a sql based on sqlid and plan hash value conveniently.

Ref: SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results (Doc ID 215187.1)

Why 11g chooses the access path of index scan with inlist as filter?

In a previous post ( http://oracle-study-notes.blogspot.com/2012/07/using-sql-plan-baseline-to-ensure-10g.html), I described that a SQL performed badly after upgrade from 10g to 11g due to the change of execution plan and I used "SQL Plan Baseline" feature to force the sql executing with the good plan. The bad plan uses "inlist as filter" in an index scan access path. In this post I will try to understand why the execution plan changed from 10g to 11g. To simplify the investigation, I only explored a single table access query shown as follows:

select fds_seq,order_id 
from  fds
where
     fds.status IN ( 'LD', 'RT' )  
and  fds.thread_id = '1'  
;

In this query, the table fds is created from the production table by CTAS and it has only one index fds_ix1 on (thread_id,status). After upgrade to 11g, we used the same statistics gathering script as in 10g. I collected the stats for table fds using the same options as those in the 11g or 10g database:

exec dbms_stats.gather_table_stats(user,'FDS',method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent =>10, cascade => TRUE,degree => 4) ;

The column statistics of this table is shown below:

COLUMN_NAME                      DISTINCT DENSITY      NULLS BKTS LO               HI
------------------------------ ---------- ------- ---------- ---- ---------------- ----------------
FDS_SEQ                         101188250   0.000          0    1          2756031        109967122
ORDER_ID                         19009758   0.000   49654490    1 CADX010101010    RMOG293464421,CM
RETRY_COUNT                            48   0.021  100892510    1 1                9
STATUS                                 14   0.071          0    1 DD               XX
THREAD_ID                           46932   0.000          0    1 0                bnscqpa4+9999
TIMESTAMP                        22878788   0.000          0    1 27-DEC-2004      23-JUL-2012

In the 11g (11.2.0.3) database, the execution plan of the query and execution statistics are obtained from AUTOTRACE:

Execution Plan
----------------------------------------------------------
Plan hash value: 137039772

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |  4312 |   101K|    63   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FDS     |  4312 |   101K|    63   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FDS_IX1 |  2224 |       |    10   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FDS"."THREAD_ID"='1')
       filter("FDS"."STATUS"='LD' OR "FDS"."STATUS"='RT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     153373  consistent gets
     153361  physical reads
          0  redo size
      19755  bytes sent via SQL*Net to client
        294  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        696  rows processed

By looking at the the Predicate Information section, we can know that Oracle chooses "inlist as filiter" access path. I also created the same FDS table in a 10g (10.2.0.4) database and analyzed it with same option. The columns stats shown as follows:

COLUMN_NAME                      DISTINCT DENSITY      NULLS BKTS LO               HI
------------------------------ ---------- ------- ---------- ---- ---------------- ----------------
FDS_SEQ                         101182940   0.000          0    1          2756031        109967120
ORDER_ID                         19024012   0.000   49606920    1 CADX010101010    RMOG292175979,CM
RETRY_COUNT                            47   0.021  100889040    1 1                9
STATUS                                 14   0.071          0    1 DD               XX
THREAD_ID                           46926   0.000          0    1 0                bnscqpa4+9999
TIMESTAMP                        22875629   0.000          0    1 27-DEC-2004      23-JUL-2012

It can be seen that the column statistics of the table is almost identifical in 10g and 11g. However, the AUTOTRACE report in the 10g database shows the different execution plan and much less gets-per-execution value (132 vs 153373)

Execution Plan
----------------------------------------------------------
Plan hash value: 3825939584

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   308 |  7392 |    13   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FDS     |   308 |  7392 |    13   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FDS_IX1 |   315 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("FDS"."THREAD_ID"='1' AND ("FDS"."STATUS"='LD' OR
              "FDS"."STATUS"='RT'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        132  consistent gets
        105  physical reads
          0  redo size
       8924  bytes sent via SQL*Net to client
        289  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        696  rows processed

I did 10053 traces for the query in 10g and 11g database respectivly. The 10053 trace excerpts are shown in Appendix. It can be seen that Oracle CBO evaluated both execution plans in 10g and 11g. The following table gives the cost of each plan in 10g and 11g as seen in the trace

------------------------------------------------------
Plan        Index scan with           Index scan with  
            inlist as filter          INLIST ITERATOR
------------------------------------------------------
10.2.0.4           18.00                      13.03 
11.2.0.3           63.27                     122.30 
-------------------------------------------------------

Based on the cost value, it is easy to understand that 10g and 11g choose different plans as their "best" plan. By comparing the 10053 trace, I think one key difference that leads to the different cost values lies in the index selectivity of the two-column index FDS_IX1. Index selectivity: FDS_IX1 on (thread_id, status )

------------------------------------------------------------------------------
Plan        Index  selectivity           #DK of        NDV of            NDV of
                                        Index         thread_id         status
-------------------------------------------------------------------------------
10.2.0.4         3.0443e-06              46926          46926             14 
11.2.0.3           0.000043              46932          46932             14
------------------------------------------------------------------------------

In 10g: index selectivity = (1/46926 ) * (1/14) *2 = 3.0443e-06 In 11g: index selectivity = (1/46932 ) * 2 = 0.0000043 It shows that Oracle uses different the formula to calculate the two-column index selectivity. In 10g the selectivity is based on 1/num_distinct for the two columns; while in 11g 1/distinct_keys for the index. I believe this difference on index selectivity explains the execution plan changes from 10g to 11g in this particular case, especially we use same stats gathering option in  11g as in 10g ( i.e. no histogram)

Appendix - 10053 trace:

-- script 
alter session set events '10053 trace name context forever';

explain plan for
select fds_seq,order_id 
from  fds
where
     fds.status IN ( 'LD', 'RT' )  
and  fds.thread_id = '1'  
;

alter session set events '10053 trace name context off';


---- from  a 10g database -----------

*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 765 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FDS  Alias: FDS
    #Rows: 101182940  #Blks:  499443  AvgRowLen:  31.00
Index Stats::
  Index: FDS_IX1  Col#: 5 2
    LVLS: 3  #LB: 285370  #DK: 46926  LB/K: 27.00  DB/K: 223.00  CLUF: 2337470.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): STATUS(CHARACTER)
    AvgLen: 3.00 NDV: 14 Nulls: 0 Density: 0.071429
  Column (#5): THREAD_ID(VARCHAR2)
    AvgLen: 6.00 NDV: 46926 Nulls: 0 Density: 2.1310e-05
  Table: FDS  Alias: FDS
    Card: Original: 101182940  Rounded: 308  Computed: 308.03  Non Adjusted: 308.03
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  112728.85  Resp: 112728.85  Degree: 0
      Cost_io: 109255.00  Cost_cpu: 31889016166
      Resp_io: 109255.00  Resp_cpu: 31889016166
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 13.00  resc_cpu: 249823
    ix_sel: 3.0443e-06  ix_sel_with_filters: 3.0443e-06
    Cost: 13.03  Resp: 13.03  Degree: 1
  Considering index with inlist as filter
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 18.00  resc_cpu: 794233
    ix_sel: 2.1310e-05  ix_sel_with_filters: 3.0443e-06
    Cost: 18.09  Resp: 18.09  Degree: 1
  Rejected inlist as filter
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange  Index: FDS_IX1
         Cost: 13.03  Degree: 1  Resp: 13.03  Card: 308.03  Bytes: 0
***************************************



--- from a 11g database ----------------

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 769 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM:  10 milliseconds (default is 10)
  MBRC:       NO VALUE blocks (default is 16)
 
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FDS  Alias: FDS
    #Rows: 101188250  #Blks:  500926  AvgRowLen:  32.00  ChainCnt:  0.00
Index Stats::
  Index: FDS_IX1  Col#: 5 2
    LVLS: 3  #LB: 290350  #DK: 46932  LB/K: 27.00  DB/K: 228.00  CLUF: 2450470.00
Access path analysis for FDS
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for FDS[FDS]
  Column (#2): STATUS(
    AvgLen: 3 NDV: 14 Nulls: 0 Density: 0.071429
  Column (#5): THREAD_ID(
    AvgLen: 6 NDV: 46932 Nulls: 0 Density: 0.000021
  ColGroup (#1, Index) FDS_IX1
    Col#: 2 5    CorStregth: 14.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Table: FDS  Alias: FDS
    Card: Original: 101188250.000000  Rounded: 4312  Computed: 4312.12  Non Adjusted: 4312.12
  Access Path: TableScan
    Cost:  113037.39  Resp: 113037.39  Degree: 0
      Cost_io: 109579.00  Cost_cpu: 31901063983
      Resp_io: 109579.00  Resp_cpu: 31901063983
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 122.00  resc_cpu: 2803250
    ix_sel: 0.000043  ix_sel_with_filters: 0.000043
    Cost: 122.30  Resp: 122.30  Degree: 1
  Considering index with inlist as filter
 
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Access Path: index (RangeScan)
    Index: FDS_IX1
    resc_io: 63.00  resc_cpu: 2477875
    ix_sel: 0.000021  ix_sel_with_filters: 0.000021

 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 0.00
 ***** End Logdef Adjustment ******
    Cost: 63.27  Resp: 63.27  Degree: 1
  Accepted inlist as filter
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange
  Index: FDS_IX1
         Cost: 63.27  Degree: 1  Resp: 63.27  Card: 4312.12  Bytes: 0
 
***************************************

Notes:
(1) In 11g, by fooling CBO through setting column stats manually, I am able to get the 10g access path with much better gets per execution:

Execution Plan
----------------------------------------------------------
Plan hash value: 3825939584

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  4040 | 96960 |   119   (0)| 00:00:02 |
|   1 |  INLIST ITERATOR             |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FDS     |  4040 | 96960 |   119   (0)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | FDS_IX1 |  4229 |       |    15   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("FDS"."THREAD_ID"='1' AND ("FDS"."STATUS"='LD' OR
              "FDS"."STATUS"='RT'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        132  consistent gets
        104  physical reads
          0  redo size
      19755  bytes sent via SQL*Net to client
        294  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        696  rows processed

(2)If we re-write the original query with UNION ALL, we also obtain a much better execution plan:

SQL> select fds_seq,order_id
  2  from  fds
  3  where
  4       fds.status IN ( 'LD' )
  5  and  fds.thread_id = '1'
  6  union all
  7  select fds_seq,order_id
  8  from fds
  9  where
 10       fds.status IN ('RT' )
 11  and  fds.thread_id = '1'
 12  ;

696 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3716244554

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  4040 | 96960 |   122  (50)| 00:00:02 |
|   1 |  UNION-ALL                   |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FDS     |  2020 | 48480 |    61   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FDS_IX1 |  2115 |       |     9   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| FDS     |  2020 | 48480 |    61   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | FDS_IX1 |  2115 |       |     9   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("FDS"."THREAD_ID"='1' AND "FDS"."STATUS"='LD')
   5 - access("FDS"."THREAD_ID"='1' AND "FDS"."STATUS"='RT')


Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
        156  consistent gets
        108  physical reads
          0  redo size
       8851  bytes sent via SQL*Net to client
        294  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        696  rows processed

(3) In the 11g database, under some circumstances when gathering table stats, there could probably be histograms on the columns and Oracle could generate optimal execution plan in this case. (I did observe in one case when played around stats gathering on the FDS table)

Using SQL Plan Baseline to ensure a 10g execution plan after upgrade to 11g

Recently I have got a chance to use the SQL Plan Management feature - SQL Plan Baseline to fix a regressed sql after upgrade to 11g from 10g. The problem sql looks like:

SELECT 
       fc.gjmfmpbe_type, 
       fc.gjmfmpbe_config_seq, 
       fi.gjmfmpbe_info_seq, 
       fd.gjmfmpbe_detail_seq, 
       fi.physical_gjmf_date, 
       fi.gjmf_name, 
       fi.mpbe_timestamp, 
       fd.gjmf_detail, 
       fc.mog_prefix, 
       fd.gjmf_detail_enc, 
       fds.retry_count, 
       fc.retry_config 
FROM   
       gjmfmpbe_config fc, 
       gjmfmpbe_detail_status fds,
       gjmfmpbe_detail fd, 
       gjmfmpbe_info fi 
WHERE  ( fi.gjmfmpbe_info_seq = fd.gjmfmpbe_info_seq ) 
       AND ( fds.gjmfmpbe_detail_seq = fd.gjmfmpbe_detail_seq ) 
       AND ( fi.gjmfmpbe_config_seq = fc.gjmfmpbe_config_seq ) 
       AND ( fds.status IN ( 'LD', 'RT' ) ) 
       AND ( fc.gjmf_directory = 'BillCycle' ) 
       AND ( fds.thread_id = '1' ) 
ORDER  BY fd.gjmfmpbe_detail_seq;  

The execution plans of this sql in 10g and 11g are shown below, respectively:

10 g execution plan
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |       |       |  1477 (100)|          |
|   1 |  SORT ORDER BY                    |                            |    13 |  4641 |  1477   (1)| 00:00:18 |
|   2 |   NESTED LOOPS                    |                            |    13 |  4641 |  1476   (1)| 00:00:18 |
|   3 |    NESTED LOOPS                   |                            |   486 |   116K|   990   (1)| 00:00:12 |
|   4 |     MERGE JOIN CARTESIAN          |                            |   486 | 25758 |    17   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID  | GJMFMPBE_CONFIG            |     1 |    34 |     2   (0)| 00:00:01 |
|   6 |       INDEX RANGE SCAN            | GJMFMPBE_CONFIG_FILEDIR    |     1 |       |     1   (0)| 00:00:01 |
|   7 |      BUFFER SORT                  |                            |   486 |  9234 |    15   (0)| 00:00:01 |
|   8 |       INLIST ITERATOR             |                            |       |       |            |          |
|   9 |        TABLE ACCESS BY INDEX ROWID| GJMFMPBE_DETAIL_STATUS     |   486 |  9234 |    15   (0)| 00:00:01 |
|  10 |         INDEX RANGE SCAN          | GJMFMPBE_DETAIL_STATUS_IX1 |   486 |       |     6   (0)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID   | GJMFMPBE_DETAIL            |     1 |   193 |     2   (0)| 00:00:01 |
|  12 |      INDEX UNIQUE SCAN            | PK_GJMFMPBE_DETAIL         |     1 |       |     1   (0)| 00:00:01 |
|  13 |    TABLE ACCESS BY INDEX ROWID    | GJMFMPBE_INFO              |     1 |   111 |     1   (0)| 00:00:01 |
|  14 |     INDEX UNIQUE SCAN             | PK_GJMFMPBE_INFO           |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------------------------------


11 g execution plan 

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |       |       | 11130 (100)|       |
|   1 |  SORT ORDER BY                   |                            |   108 | 37692 | 11130   (1)| 00:02:14 |
|*  2 |   HASH JOIN                      |                            |   108 | 37692 | 11129   (1)| 00:02:14 |
|   3 |    TABLE ACCESS BY INDEX ROWID   | GJMFMPBE_CONFIG            |     1 |    25 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN             | GJMFMPBE_CONFIG_FILEDIR    |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    HASH JOIN                     |                            |  4333 |  1370K| 11127   (1)| 00:02:14 |
|   6 |     NESTED LOOPS                 |                            |       |       |            |       |
|   7 |      NESTED LOOPS                |                            |  4333 |   901K|  8733   (1)| 00:01:45 |
|   8 |       TABLE ACCESS BY INDEX ROWID| GJMFMPBE_DETAIL_STATUS     |  4333 | 77994 |    59   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | GJMFMPBE_DETAIL_STATUS_IX1 |  2183 |       |    18   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN          | PK_GJMFMPBE_DETAIL         |     1 |       |     1   (0)| 00:00:01 |
|  11 |      TABLE ACCESS BY INDEX ROWID | GJMFMPBE_DETAIL            |     1 |   195 |     2   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL            | GJMFMPBE_INFO              |   466K|    49M|  2388   (1)| 00:00:29 |
---------------------------------------------------------------------------------------------------------------

The difference lies in the join order. In 10g join order: fc -> fds -> fd -> fi, and in 11g join order: fc -> (fds -> fd -> fi). In 11g the buffer gets per execution is around 427K as shown with below query:

select sql_id, child_number, buffer_gets/executions, executions, sql_plan_baseline from v$sql where sql_id='cz1myj2gx5xwv';

SQL_ID        CHILD_NUMBER BUFFER_GETS/EXECUTIONS EXECUTIONS SQL_PLAN_BASELINE
------------- ------------ ---------------------- ---------- ------------------------------
cz1myj2gx5xwv            0             427447.751        233

Before upgrade, we backed up 10g execution plan in a sql tuning set. so I was able to obtain the buffer gets per execution in 10g by:

SELECT sql_id, plan_hash_value, executions, buffer_gets/executions from
   table(dbms_sqltune.select_sqlset(
       'SPM_STS'
      ,'sql_id=''cz1myj2gx5xwv'''
      , SQLSET_OWNER=>'OPS$ORACLE'
 )
   );  
  


SQL_ID        PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS/EXECUTIONS
------------- --------------- ---------- ----------------------
cz1myj2gx5xwv      1052698328       2742             31115.0627

Now come to the fix. I executed the following procedure to load the 10 plan stored in a SQL tuning set (STS) into SQL plan baselines for this problem sql

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    SQLSET_NAME => 'SPM_STS'
   ,SQLSET_OWNER => 'OPS$ORACLE'
   ,BASIC_FILTER =>  'sql_id=''cz1myj2gx5xwv'''
 );
END;
/

To verify the problem is fixed, I checked the execution stats shortly after:

select sql_id, child_number, buffer_gets/executions,round(elapsed_time/1000000/executions) ela_secs_exe, executions, sql_plan_baseline from v$sql where sql_id='cz1myj2gx5xwv';

SQL_ID        CHILD_NUMBER BUFFER_GETS/EXECUTIONS ELA_SECS_EXE EXECUTIONS SQL_PLAN_BASELINE
------------- ------------ ---------------------- ------------ ---------- ------------------------------
cz1myj2gx5xwv            0              433577.21         2414        233
cz1myj2gx5xwv            2                  11936            4         14 SQL_PLAN_4090fd80m1dzb80df0a95

WOW! 2414 seconds vs 4 seconds ??? So why the 11g chose the bad execution plan in the first place? I will investigate further if possible.


 ----------------- update Jul. 10, 2012 -----------------

The difference between 11g and 10g plan is not about join order. It is that in 11g Oracle CBO accepted "inlist as filter" access path. I did 10053 trace for the following sql:

select * from  ttq_bbbb.gjmfmpbc_detail_status fds where fds.thread_id= '1' and fds.status in ('LD', 'RT');

The execution plan looks like:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |  4332 |   135K|    59   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| GJMFMPBC_DETAIL_STATUS     |  4332 |   135K|    59   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | GJMFMPBC_DETAIL_STATUS_IX1 |  2167 |       |    18   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FDS"."THREAD_ID"='1')
       filter("FDS"."STATUS"='LD' OR "FDS"."STATUS"='RT')

Note: the index GJMFMPBC_DETAIL_STATUS_IX1 is on GJMFMPBC_DETAIL_STATUS ( thread_id, status). In the trace, it can be seen:

Access Path: index (RangeScan)
    Index: GJMFMPBC_DETAIL_STATUS_IX1
    resc_io: 115.00  resc_cpu: 2790630
    ix_sel: 0.000043  ix_sel_with_filters: 0.000043
    Cost: 115.30  Resp: 115.30  Degree: 1
  Considering index with inlist as filter
 
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.0000
  Access Path: index (RangeScan)
    Index: GJMFMPBC_DETAIL_STATUS_IX1
    resc_io: 59.00  resc_cpu: 2439947
    ix_sel: 0.000021  ix_sel_with_filters: 0.000021
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 0.00
 ***** End Logdef Adjustment ******
    Cost: 59.26  Resp: 59.26  Degree: 1
  Accepted inlist as filter
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange
  Index: GJMFMPBC_DETAIL_STATUS_IX1
         Cost: 59.26  Degree: 1  Resp: 59.26  Card: 4331.78  Bytes: 0

Implicit conversion of a character string to Oracle DATE type is dangerous

Typically TO_DATE function is used by taking a char as the first argument and 'fmt' as the second argument. According to Oracle doc:

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a datetime model format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is J, for Julian, then char must be an integer.

It should be pointed out that omitting the fmt could be a very bad practise. If the character string does not match the default format, Oracle does not necessarily throw error in some cases. For example, I have executed the following commands in the SQL* Plus:

SQL>alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';

Session altered.


SQL>select count(*) from  dba_objects where created between '1-Nov-2011' and '14-Nov-2011';

  COUNT(*)
----------
         0

1 row selected.

SQL>select to_date('1-Nov-2011') from dual;

TO_DATE('1-NOV-2011
-------------------
0001-11-20 11:00:00

1 row selected.

While the correct results should be as follows:

SQL>alter session set nls_date_format='dd-Mon-yyyy HH24:mi:ss';

Session altered.

SQL>select count(*) from  dba_objects where created between '1-Nov-2011' and '14-Nov-2011';

  COUNT(*)
----------
       171

1 row selected.

SQL>select to_date('1-Nov-2011') from dual;

TO_DATE('1-NOV-2011'
--------------------
01-Nov-2011 00:00:00

1 row selected.

I have learned this in a pretty hard way during a sql tuning effort. Many hours have been spent before I realized that the weired results were due to this implicit conversion "bug". ( See http://www.freelists.org/post/oracle-l/Huge-difference-between-sqlplus-and-sqldeveloper-sorting-in-memory-vs-disk,7)

Tuning an inefficient SQL with semi-join

Found a sql look like the following in a batch job:

SELECT    
 distinct
 C.INT_PSEFS_ID
,C.ACCOUNT_ID
,C.PSEFS_ID
,C.IS_PQ
,C.psefs_save_date
FROM     
   MYSCH.PSEFS_MSTR A
  ,MYSCH.PSEFS_DTLS B
  ,MYSCH.PSEFS_MSTR C
WHERE    
    A.INT_PSEFS_ID = B.INT_PSEFS_ID
  AND C.PSEFS_ID LIKE SUBSTR (A.PSEFS_ID, 1, 13)|| '%'
  AND A.IS_VALID = 'N' 
  AND B.PSEFS_STATUS_ID IN (4, 6)
  AND A.PSEFS_COMP_DATE  <= SYSDATE - 45
;

The execution plan is as follows:

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |    66T|  4861T|       |  3064G  (2)|999:59:59 |
|   1 |  HASH UNIQUE                 |                  |    66T|  4861T|    11P|  3064G  (2)|999:59:59 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PSEFS_MSTR       |  3287K|   122M|       |   149K  (1)| 00:29:49 |
|   3 |    NESTED LOOPS              |                  |    66T|  4861T|       |  3027G  (1)|999:59:59 |
|*  4 |     HASH JOIN                |                  |    20M|   794M|   550M|   839K  (3)| 02:47:49 |
|*  5 |      TABLE ACCESS FULL       | PSEFS_DTLS       |    26M|   250M|       |   612K  (2)| 02:02:29 |
|*  6 |      TABLE ACCESS FULL       | PSEFS_MSTR       |    20M|   600M|       |   156K  (6)| 00:31:17 |
|*  7 |     INDEX RANGE SCAN         | PSEFS_MSTR_IX5   |   591K|       |       |  5154   (1)| 00:01:02 |
---------------------------------------------------------------------------------------------------------

INT_PSEFS_ID is a PK of A, and a FK of B, so A join B on INT_PSEFS_ID will generate a result set (refer to as R1) with duplicated records of A; Then we selected out row of C (same table as A) by join it with R1t based on an unusual join condition with LIKE operator. To see the problem by way of example, supposing we have a row in C with PSRFS_ID='ABCDEFGHIJKLM_2'; In R1, we could have several rows with PSRFS_ID in('ABCDEFGHIJKLM_3', 'ABCDEFGHIJKLM_4', 'ABCDEFGHIJKLM_4', 'ABCDEFGHIJKLM'), due to the LIKE, the row in C will match 4 rows in R1, thus generate 4 duplicated rows in the final result set. I rewrote the SQL to use sub-queries to enable the more efficient semi-joins intead of normal joins

select 
C.INT_PSEFS_ID
, C.ACCOUNT_ID
, C.PSEFS_ID,IS_PQ
, C.psefs_save_date
from MYSCH.PSEFS_MSTR C
where
   SUBSTR(C.PSEFS_ID,1,13) in
   (
   select  SUBSTR(A.PSEFS_ID,1,13)
   from MYSCH.PSEFS_MSTR  A
    where  A.IS_VALID = 'N'
     and A.PSEFS_COMP_DATE  <= SYSDATE - 45 
     and exists ( select 1 from MYSCH.PSEFS_DTLS B
     where A.int_psefs_id = B.int_psefs_id
       and B.PSEFS_STATUS_ID IN (4, 6) )
   ) ;

Now the execution plan looks like:

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |    26M|  1182M|       |  1174K  (3)| 03:54:49 |
|*  1 |  HASH JOIN RIGHT SEMI  |               |    26M|  1182M|   387M|  1174K  (3)| 03:54:49 |
|   2 |   VIEW                 | VW_NSO_1      |    20M|   155M|       |   839K  (3)| 02:47:49 |
|*  3 |    HASH JOIN RIGHT SEMI|               |    20M|   794M|   550M|   839K  (3)| 02:47:49 |
|*  4 |     TABLE ACCESS FULL  | PSEFS_DTLS    |    26M|   250M|       |   612K  (2)| 02:02:29 |
|*  5 |     TABLE ACCESS FULL  | PSEFS_MSTR    |    20M|   600M|       |   156K  (6)| 00:31:17 |
|   6 |   TABLE ACCESS FULL    | PSEFS_MSTR    |    65M|  2445M|       |   153K  (4)| 00:30:45 |
------------------------------------------------------------------------------------------------

Fig 1. Good query visual representation

           




Fig 2. Bad query visual representaion



Note: though this sql is actually rather simple, the visual approach advocated by Jonathan Lewis in this article really helps me to do the analysis

Composite index selectivity

I did tests with 10053 trace today and found that Oracle CBO behavior changes regarding compute selectivity of 3-column composite index from 9.2.0.8 to 10.2.0.4. (note: no histogram on any columns)

In 9i: selectivty of index(col1, col2, col3)= density (co11) x denisty (col2) x denisity(col3);

In 10g and 11g selectivity of index(col1, col2, col3) = 1/DISTINCT_KEYS where DISTINCT_KEYS is from index stats obtainable through dba_indexes.


Here is the test case which mimics the production able and the problem sql.

----- begin of the test case ----

drop table test;
create table test
as
select level id,
       trunc(mod(level,80)) id1,
       20*trunc(mod(level, 40)) id2,
       1    id3,
       trunc(mod(level,50)) id4
from dual
connect by level <=1000000;


create index test_ix1 on test(id1, id2, id3);

exec dbms_stats.gather_table_stats(user,'test', method_opt=>'FOR ALL COLUMNS size 1', cascade=>true);


alter session set tracefile_identifier = test;
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

explain plan for
select id
from test
where id1 = 20
and id3=1
and id2 = 400;

ALTER SESSION SET EVENTS '10053 trace name context off';
Exit
------ end of test case  -----------------------

#### 9.2.0.8  10053 trace excerpt


***************************************
SINGLE TABLE ACCESS PATH
Column:        ID1  Col#: 2      Table: TEST   Alias: TEST
    NDV: 80        NULLS: 0         DENS: 1.2500e-02 LO:  0  HI: 79
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        ID3  Col#: 4      Table: TEST   Alias: TEST
    NDV: 1         NULLS: 0         DENS: 1.0000e+00 LO:  1  HI: 1
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        ID2  Col#: 3      Table: TEST   Alias: TEST
    NDV: 40        NULLS: 0         DENS: 2.5000e-02 LO:  0  HI: 780
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: TEST     ORIG CDN: 1000000  ROUNDED CDN: 313  CMPTD CDN: 313
  Access path: tsc  Resc:  857  Resp:  847
  Access path: index (equal)
      Index: TEST_IX1
  TABLE: TEST
      RSC_CPU: 711380   RSC_IO: 82
  IX_SEL:  0.0000e+00  TB_SEL:  3.1250e-04
  BEST_CST: 83.00  PATH: 4  Degree:  1


#### 10.2.0.4  10053 trace excerpt

***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): ID1(NUMBER)
    AvgLen: 3.00 NDV: 81 Nulls: 0 Density: 0.012346 Min: 0 Max: 79
  Column (#4): ID3(NUMBER)
    AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 1 Min: 1 Max: 1
  Column (#3): ID2(NUMBER)
    AvgLen: 4.00 NDV: 40 Nulls: 0 Density: 0.025 Min: 0 Max: 780
  Table: TEST  Alias: TEST
    Card: Original: 1003092  Rounded: 310  Computed: 309.60  Non Adjusted: 309.60
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  730.88  Resp: 730.88  Degree: 0
      Cost_io: 700.00  Cost_cpu: 284170229
      Resp_io: 700.00  Resp_cpu: 284170229
  Access Path: index (AllEqRange)
    Index: TEST_IX1
    resc_io: 3173.00  resc_cpu: 27721329
    ix_sel: 0.0125  ix_sel_with_filters: 0.0125
    Cost: 3176.01  Resp: 3176.01  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 730.88  Degree: 1  Resp: 730.88  Card: 309.60  Bytes: 0

Note:
select index_name, distinct_keys from user_indexes where index_name='TEST_IX1';

INDEX_NAME                     DISTINCT_KEYS
------------------------------ -------------
TEST_IX1                                  80


#### 11.2.0.1  10053 trace excerpt


***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TEST[TEST]
  ColGroup (#1, Index) TEST_IX1
    Col#: 2 3 4    CorStregth: 40.00
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0125
  Table: TEST  Alias: TEST
    Card: Original: 1000000.000000  Rounded: 12500  Computed: 12500.00  Non Adjusted: 12500.00
  Access Path: TableScan
    Cost:  629.80  Resp: 629.80  Degree: 0
      Cost_io: 617.00  Cost_cpu: 283372261
      Resp_io: 617.00  Resp_cpu: 283372261
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0125
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0125
  Access Path: index (AllEqRange)
    Index: TEST_IX1
    resc_io: 3173.00  resc_cpu: 27721329
    ix_sel: 0.012500  ix_sel_with_filters: 0.012500
    Cost: 3174.25  Resp: 3174.25  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 629.80  Degree: 1  Resp: 629.80  Card: 12500.00  Bytes: 0

Notice the ix_sel line in the above 10053 trace excerpt; for 10g and 11g it equal to 1/80. for 9i seems computed through multiplying selectivity of eeach column.

Update:
Came accross Jonathan Lewis's comment, and came to know this CBO behavior changes acctually from 10.1.0.4 ie. 10.1.0.4 is still similar to 9i behavior.


Below is his comment: (http://kr.forums.oracle.com/forums/thread.jspa?messageID=3153224)

--------
For a single-table query with the predicate
(full list of index columns) = (set of values)

The selectivities vary across version as follows:

11.1.0.6: Table selectivity and index selectivity given by distinct_keys in index
10.2.0.1: Table selectivity given by product of column selectivities, index selectivity given by distinct_keys in index
10.1.0.4: Table selectivity and index selectivity given by product of column selectivities

------------

JL's original post about this behavior: http://jonathanlewis.wordpress.com/2008/03/11/everything-changes/

Tuning by cardinality feedback and understanding of join cardinality and transitive closure

What comes out of my recent tuning experience of a 13-table join query is my deeper understanding of "Tuning by Cardinality Feedback", join cardinality and transitive closure. The problem for that 13-table join SQL is that Oracle predictes the join cardinality to be 1 always so it chooses nested loop join, whereas the hash join is able to bring the consistent gets from 400K to 6K.

First of all, let's see where the cardinality estimation is wrong. Below are the estimated cardinality from each operations in the first 4-table join: PACKAGE_MARKETS -> MARKETS -> PACKAGE_CATEGORY -> PACKAGE_PRODUCTS

10    9       NESTED LOOPS (Cost=10 Card=1 Bytes=522)
  11   10         NESTED LOOPS (Cost=9 Card=1 Bytes=325)
  12   11           NESTED LOOPS (Cost=4 Card=1 Bytes=229)
  13   12             NESTED LOOPS (Cost=3 Card=1 Bytes=224)
  14   13               NESTED LOOPS (Cost=2 Card=1 Bytes=104)
  15   14                 INDEX (UNIQUE SCAN) OF 'PK_PACKAGE_MARKETS' (UNIQUE) (Cost=1 Card=1 Bytes=12)
  16   14                 TABLE ACCESS (BY INDEX ROWID) OF 'MARKETS' (Cost=1 Card=1 Bytes=92)
  17   16                   INDEX (UNIQUE SCAN) OF 'PK_MARKETS' (UNIQUE)
  18   13               TABLE ACCESS (BY INDEX ROWID) OF 'PACKAGES' (Cost=1 Card=1 Bytes=120)
  19   18                 INDEX (UNIQUE SCAN) OF 'PK_PACKAGES' (UNIQUE)
  20   12             TABLE ACCESS (BY INDEX ROWID) OF 'PACKAGE_CATEGORY' (Cost=1 Card=1 Bytes=5)
  21   20               INDEX (UNIQUE SCAN) OF 'PK_PACKAGE_CATEGORY' (UNIQUE)
  22   11           TABLE ACCESS (BY INDEX ROWID) OF 'PACKAGE_PRODUCTS' (Cost=5 Card=1 Bytes=96)
  23   22             INDEX (RANGE SCAN) OF 'PK_PACKAGE_PRODUCTS' (UNIQUE) (Cost=2 Card=6)
  24   10         TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' (Cost=1 Card=1 Bytes=197

Below are the real cardinalities obtained from SQL_TRACE/TKPROF. we can see the real cardinality coming out of PACKAGE_PRODUCTS table is 2725 and the estimated one is 1 as shown above. This is where the whole execution plan becomes "wrong".

2725         NESTED LOOPS
      1          NESTED LOOPS
      1           NESTED LOOPS
      1            NESTED LOOPS
      1             INDEX UNIQUE SCAN PK_PACKAGE_MARKETS (object id 423961)
      1             TABLE ACCESS BY INDEX ROWID MARKETS
      1              INDEX UNIQUE SCAN PK_MARKETS (object id 423924)
      1            TABLE ACCESS BY INDEX ROWID PACKAGES
      1             INDEX UNIQUE SCAN PK_PACKAGES (object id 423954)
      1           TABLE ACCESS BY INDEX ROWID PACKAGE_CATEGORY
      1            INDEX UNIQUE SCAN PK_PACKAGE_CATEGORY (object id 423956)
   2725          TABLE ACCESS BY INDEX ROWID PACKAGE_PRODUCTS
   2811           INDEX RANGE SCAN PK_PACKAGE_PRODUCTS (object id 423963)

What I did is to add hints to force Oracle choose hash join instead of nested loop join for the remaining 9 tables.

This execersie also arouse my insterest to understand how join cardinality is calculated, thus I re-read Chapter 10 of Jonanth Lewis's Cost-Based Oracle Fundamentals. I found what he says is true ( as always :-)). Below is a test case output for a two table join SQL that involves "transitive closure" - there is a filter on the join column:

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL>
SQL>-- from the count we know the acutual join cardinality should be 86
SQL>select count(*) from packages where package_id='Y9995';

  COUNT(*)
----------
         1

SQL>select count(*) from package_products where package_id='Y9995';

  COUNT(*)
----------
        86

SQL>
SQL>-- JL mentioned "query_reworte_enabled" has impact on join predicate removal
SQL>-- prior to 10g in hist book "Cost-Based Oracle Fundamentals"
SQL>-- I confirmed it is true in our 9i db. I also tested with rewrite/nowrite hints
SQL>-- instead of session level "query_rewrite_enabled parameter". No impact
SQL>-- from the hints.
SQL>
SQL>
SQL>show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
SQL>alter session set query_rewrite_enabled=true;

Session altered.

SQL>
SQL>
SQL>-- query_rewrite_enabled=true
SQL>explain plan for
  2  select  *
  3    from  packages a,   -- NROWS 2656
  4        package_products b   -- NROWS 284247
  5   where a.package_id   -- NDV  2656 PK
  6       = b.package_id   -- NDV  2610 NOT NULL
  7   and a.package_id = 'Y9995'
  8  ;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   240 |     5 |
|   1 |  NESTED LOOPS                |                      |     1 |   240 |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PACKAGES             |     1 |   144 |     2 |
|*  3 |    INDEX UNIQUE SCAN         | PK_PACKAGES          |     1 |       |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| PACKAGE_PRODUCTS     |     1 |    96 |     3 |
|*  5 |    INDEX RANGE SCAN          | PK_PACKAGE_PRODUCTS  |     1 |       |     2 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."PACKAGE_ID"='Y9995')
   5 - access("B"."PACKAGE_ID"='Y9995')
       filter("A"."PACKAGE_ID"="B"."PACKAGE_ID")

Note: cpu costing is off

20 rows selected.

SQL>
SQL>alter session set query_rewrite_enabled=false;

Session altered.

SQL>
SQL>-- query_rewrite_enabled=false
SQL>explain plan for
  2  select  *
  3    from  packages a,   -- NROWS 2656
  4        package_products b   -- NROWS 284247
  5   where a.package_id   -- NDV  2656 PK
  6       = b.package_id   -- NDV  2610 NOT NULL
  7   and a.package_id = 'Y9995'
  8  ;

Explained.

SQL>
SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |   109 | 26160 |    52 |
|   1 |  NESTED LOOPS                |                      |   109 | 26160 |    52 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PACKAGES             |     1 |   144 |     2 |
|*  3 |    INDEX UNIQUE SCAN         | PK_PACKAGES          |     1 |       |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| PACKAGE_PRODUCTS     |   109 | 10464 |    50 |
|*  5 |    INDEX RANGE SCAN          | PK_PACKAGE_PRODUCTS  |   109 |       |     2 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."PACKAGE_ID"='Y9995')
   5 - access("B"."PACKAGE_ID"='Y9995')

Note: cpu costing is off

19 rows selected.

The test case showed two scenarios with same SQL and apparently obtained same execution plan, but
1. with query_rewrite_enabled=true, the estimated cardinality is 1
2. with query_rewrite_enabled=false, the estimated cardinality is 109
3. There are differences in the "Predicate Information" section of the execution plan. With query_rewrite_enabled=false, there is no join predicate

So how those cardinilities are calculated by Oracle? If we have two table join in the following form:

select *
  from t1, t2
 where t1.c1 = t2.c2
   and filter_predicates_of_t1
   and filter_predicates_of_t2;

The basic join cardinality formula is as follows (from "Cost-Based Oracle Fundamentals" p266):

Join Selectivity =
  ((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) *
  ((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2)) /
  greater(num_distinct(t1.c1), num_distinct(t2.c2))

Join Cardinality =
   join selectivity *
   filtered cardinality(t1) * filtered cardinality(t2)

In our scenario 1:

Join Selectivity = 
  (( 2656 -0) / 2656 ) *
  (( 284247 - 0) / 284247) /
  greater(2656, 2619)
  = 1/2656

Join Cardinaltiy =
   (1/2656) *
   (2656/2656) * ( 284247 /2610) 
   = 0.004 ( rounded to 1)

In our senario 2, notice we don't have join predicate actually, Oracle eliminated the Join Selectivity part:

Join Cardinlity =
   (2656/2656) * ( 284247 /2610)
   = 109

My production 9i database has set query_rewrite_enabled=true. So if I wrote the SQL without the join condition as follows::

select  *
 from  packages a,   -- NROWS 2656
       package_products b   -- NROWS 284247
where
      b.package_id = 'Y9995'
  and a.package_id = 'Y9995'
;

I can get the correct cardinality as in the scenaro 2.

BTW, for example, if a=b, and a=5; then b=5, this is called "transitive closure".

Execution Plan of a Subquery inside CASE statement

I found that in our 9.2.0.8 database the execution plan of a subquery inside CASE statement can not be seen in the SQL_TRACE/TKPROF report or from the excution plan extracted from the shared pool. However, it can be seen from Autotrace explain plan. I constructed a test case to demonstrate this as follows:

rem script: xplan_diff.sql
rem   execution plan for the subquery in the CASE statement not shown in SQL_TRACE/TKPROF in 9i
rem
rem

spool xplan_diff.log
set echo on

drop table t;
drop table t2;

create table t 
as 
select rownum id,
       'GOOD' val
from all_objects
where rownum <=20;
 
update t set val='BAD' where mod(id,2) = 0;
commit;

create table t2
as
select rownum id,
       'TEST' val
from all_objects
where rownum <=20; 


alter session set tracefile_identifier = test;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
set autotrace traceonly
select id,
       case when  val = 'GOOD' then 
  (select t2.val 
  from t, t2
  where t.id = t2.id
    and rownum = 1
         )
       else 'NO TEST'
       END  as IS_TEST
from t;
set autotrace off
alter session set events '10046 trace name context forever, level 12';
spool off
exit;


set doc off
doc

---- 9.2.0.8 Autotrace xplan  ---------------------------

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T2'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T'
   7    0   TABLE ACCESS (FULL) OF 'T'


----- 9.2.0.8  tkprof  -----------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
     20  TABLE ACCESS FULL T
 
 

------ 10.2.0.4  Autotrace  xplan -------------------------------------------
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    20 |   380 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY      |      |       |       |            |          |
|*  2 |   HASH JOIN         |      |    20 |   640 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |    20 |   260 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |    20 |   380 |     2   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL  | T    |    20 |   380 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   2 - access("T"."ID"="T2"."ID")

------ 10.2.0.4  tkprof ------------
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=6 pr=0 pw=0 time=1435 us)
      1   HASH JOIN  (cr=6 pr=0 pw=0 time=1417 us)
     20    TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=109 us)
      1    TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=64 us)
     20  TABLE ACCESS FULL T (cr=4 pr=0 pw=0 time=72 us)

#

Note: in 10.2.0.4 no such problem.


I was puzzled the other day about a similar sql to that in the above test case in our 9i  production database, which has two cursors with very different  sql_plan_hash_value and gets_per_exections when I checked it from v$sql. But when I pulled the execution plan out from v$sql_plan, the execution plan from both cursors are exactly same. Now I understand the subquery execution plan parts are missing. The different cost is due to the join order swapped in the subquery.

Demonstrate a function-based index is unable to be used due to cursor_sharing setting

rem  Demonstrate a function-based index is unable to be used
rem  due to cursor_sharing setting
rem
rem  The function is substr(col, 1,6), when cursor_sharing = 
rem  FORCE or SIMILAR, the literals, i,e, 1 and 6 are replaced 
rem  by system-generated variables. This may casued mis-match 
rem  against the index definition
rem
rem  When we use autotrace to check the exection plan,
rem  we do see the index is used; however, at run time, this is 
rem  not the case as shown by 10046 trace. 
rem
rem  Test env: 9.2.0.8 

set echo on
spool fun_idx

drop table t;
create table t as
select rownum id,
       object_name name,
       rpad('x',8) val
from all_objects 
where rownum <=2000;

create index t_idx on t(substr(name, 1,6));
exec dbms_stats.gather_table_stats(user,'t', cascade=>true);

select * from v$version;

alter session set tracefile_identifier=fun;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

alter session set cursor_sharing=force;
select /* force */ * 
from t 
where substr(name,1,6)='ABCDEF';

alter session set cursor_sharing=similar;
select /* similar */ * 
from t 
where substr(name,1,6)='ABCDEF';

alter session set cursor_sharing=exact;
select /* exact */ * 
from t 
where substr(name,1,6)='ABCDEF';

exit;


set doc off
doc

--- force  ----

select /* force */ *
from t
where substr(name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         13          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         13          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T

--- similar ----
 
select /* similar */ *
from t
where substr(name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         13          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0         13          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T

--- exact ---

select /* exact */ *
from t
where substr(name,1,6)='ABCDEF'
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID T
      0   INDEX RANGE SCAN T_IDX (object id 224130)
# 

May 11, 2010 updated: Original post ended at the previous line, all below is added today:

I was pinged by one of my colleagues the other day saying "Jonathan Lewis referred to your blog". I was surprized to hear that. Then I found out that Coskan first included this post in his blog , then Jonathan mentioned it in one of his blog posts (footnote section), which describes the same issue but of course includes deeper technical insights. I regarded Jonathan as my mentor secretly :-). First of all, I learned a lot from his blog and website, and his book "Cost-based Oracle Fundenmental" as well. In my LinkedIn profile "Reading List by Amazon" section, I commented "I read it through when I was on bench in Fall 2007. Even 20% of understanding of it helped me gain some respect from app team and peer DBAs by showing I was able to tune sqls . I will read it again and again utill fully digest it hopefully one day. " Secondly, I once listened to a podcast of his interview with DBAZine, in which he emphasized how the approach of building test cases can help one become better DBA or troubleshooter. I kept this in mind and have been trying practise this tip always. The test case I showed  in this post is a proof :-). The style of this test case is actually somewhat mimicing the code examples of the above mentioned book. Especially the "set doc off" syntax, which I did not know until I read his book.

This test case was resulted from my trouble-shooting experience for a real production issue at the posting date. A production database  server CPU utilization reached 100% from time to time from the morning and some jobs were slower than normal. Top SQL during the problem period was an update statement, supposedly using a function-based index. I was called by a fellow DBA to check why full table scan was actually used instead. From the shared pool, we knew it has been executed by FTS .

My favorite tool to check the execution plan at the first round is Autotrace :

** Autotrace showing index scan but consistent gets = 148626

myusrid@MYPRDDB1> set autotrace traceonly
myusrid@MYPRDDB1> select * from
2 XYZ_UVW.ABC_ABCDEFG_ABCDEF_ABCDEF
3 WHERE substr(DEPENDANT_ORDER,1,13) = '123456789012'
4 ;

o rows selected

ecution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=18 Bytes=1494)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ABC_ABCDEFG_ABCDEF_ABCDEF' (Cost=4 Card=18 Bytes=1494)
2 1 INDEX (RANGE SCAN) OF 'ABC_ABCDEFG_ABCDEF_ABCDE_IDX5N' (NON-UNIQUE) (Cost=Card=1)


statistics
---------------------------------------------------------
0 recursive calls
0 db block gets
148626 consistent gets
135926 physical reads
0 redo size
490 bytes sent via SQL*Net to client
229 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


I knew parse-time execution plan could be different from run-time. So from Autotrace, I confirmed index scan was considered favarable, however from the "consistent gets", I knew at run time Oracle did FTS as it matched stackpack "Gets per Execution":


** From statspack during problem period, Gets Per Exec = 149314

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
98,995,283 663 149,314.2 49.0 ######## 10769.86 3803649956
Module: JDBC Thin Client
UPDATE ABC_ABCDEFG_ABCDEF_ABCDEF SET DEPENDANT_ORDER =:1 WHERE S
UBSTR(DEPENDANT_ORDER,:"SYS_B_0",:"SYS_B_1") =:2

It took me quite a while to realize what happened was due to CURSOR_SHARING setting:

** Autotrace showing index scan and consistent gets = 3 after seting cursor_sharing=exact

myusrid@MYPRDDB1> alter session set cursor_sharing=exact;

Session altered.

myusrid@MYPRDDB1> select * from
2 XYZ_UVW.ABC_ABCDEFG_ABCDEF_ABCDEF
3 WHERE substr(DEPENDANT_ORDER,1,13) = '123456789012'
4 ;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=18 Bytes=1494)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ABC_ABCDEFG_ABCDEF_ABCDEF' (Cost=4 Card=18 Bytes=1494)
2 1 INDEX (RANGE SCAN) OF 'ABC_ABCDEFG_ABCDEF_ABCDE_IDX5N' (NON-UNIQUE) (Cost=3 Card=1)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
490 bytes sent via SQL*Net to client
229 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


I took a brave step to solve the prodution issue as during the middle of the day code change was not possible and also it was subjected to some change management processes in my working envrionment. I told the development team, I was going to change CURSOR_SHARING from FORCE to EXACT in the production database as an emergent performance stablizing measure ( as this change should be subjected to change management process too), and if we saw problems after the change, I would revert it back and they still needed to plan code change to fix that update statment (Dev think they should have used bind variables appropriately, but no one could really gurantee this change would not cause other more serious problems). So I changed it to EXACT, so far so good.