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)

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/

Nested loop join puzzle - unique scan or range scan?

In a 9.2.0.8 database, I puzzled about a nested loop join execution plan which showed index range scan on the unique index of the inner table. After reading the Chapter 11 "Nested loops" of Jonathan Lewis's execllent book "Cost-Based Oracle Fundamentals", things are clear.

There could be two forms of the nested loop join. The classic form shows unique scan on the index of the inner table, whereas the new form shows range scan. Oracle can switch from one to another depending on the number of rows of the driving table. Below is the execution plan (new form) of a query from the sql_trace/tkprof (object names are modified as usual):

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        7     57.61    1585.67     153862     379507          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9     57.62    1585.67     153862     379507          0          81

Rows     Row Source Operation
-------  ---------------------------------------------------
81  TABLE ACCESS BY INDEX ROWID SMT
188718   NESTED LOOPS
94359    TABLE ACCESS FULL SIFD
94358    INDEX RANGE SCAN PK_SMT (object id 227634)

The CPU costing model has to be enabled for Oracle to pick up this new form. We can verify it by the following query

SQL> select pname, pval1
2 from sys.aux_stats$
3 where sname = 'SYSSTATS_MAIN';

PNAME PVAL1
------------------------------ ----------
SREADTIM 12.052
MREADTIM 10.325
CPUSPEED 560
MBRC 7
MAXTHR 115623936
SLAVETHR 31744


To test the classic form, I issued the following command to turn off the CPU costing.

SQL> alter session set "_optimizer_cost_model"=io;

The following was obtained from the sql_trace/tkprof for the classic nested loop execution plan:

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        7     54.98    2268.90     160543     383987          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9     54.98    2268.90     160543     383987          0          81


Rows     Row Source Operation
-------  ---------------------------------------------------
81  NESTED LOOPS
94178   TABLE ACCESS FULL SIFD
81   TABLE ACCESS BY INDEX ROWID SMT
94177    INDEX UNIQUE SCAN PK_SMT (object id 227634)

The run time of the classic form is about 39 min as compared to 28 min of the new form. Noticed the total IIO is 544,530 vs 533,369. The slight difference of the number of rows of table SIFD seen in the tkprof report should be due to execution at different time (last afternoon and this morning for new form and classic form respectively).

Import CBO statistics for particular tables

On some occasions, we found a query having problem and suspect the execution plan has changed due to new stats. We thus want to test if we restore to previous CBO stats will help. However we may only have exported previous CBO statistics at schema level, can we import CBO statistics for the particular tables involved using the stats table obtained at schema level? I did a test and find the anwer is 'Yes'.

Test steps / observations:

1. Current analyze time for the table TAB_OT

col owner format a20
col table_name format a20
select owner, table_name, num_rows, last_analyzed from dba_tables where table_name='TAB_OT';
 
OWNER                TABLE_NAME             NUM_ROWS LAST_ANAL
-------------------- -------------------- ---------- ---------
SCH_RO              TAB_OT           449849200 21-FEB-09

2. DBMS_STATS.IMPORT_TABLE_STATS definition:

PROCEDURE IMPORT_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN
 STATID                         VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT

3. Import table states

begin
   DBMS_STATS.IMPORT_TABLE_STATS (
     'SCH_RO',
     'TAB_OT',
      NULL,
     'SCH_RO_STATS_20090209',
      NULL,
      TRUE,
      'OPS$ORACLE',
      TRUE
);
end;
/ 

Note: SCH_RO_STATS_20090209 is a stats table for the schema SCH_RO

4. Check analyze time again:

 
OWNER             TABLE_NAME          NUM_ROWS LAST_ANAL
------------------ ----------------- ---------- ---------
SCH_RO              TAB_OT           432460407 07-FEB-09

 
OWNER                INDEX_NAME               TABLE_NAME           LAST_ANAL
-------------------- ------------------------ -------------------- ---------
SCH_RO              TAB_OT_PK                 TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX2                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX4                TAB_OT          08-FEB-09
SCH_RO              TAB_OT_IX20               TAB_OT
SCH_RO              TAB_OT_IX5                TAB_OT          08-FEB-09
SCH_RO              TAB_OT_FK8                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX3                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_FK4                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_FK1                TAB_OT          07-FEB-09
 
9 rows selected.

5. Command to export schema stats
(1) Create stats tab
begin
DBMS_STATS.CREATE_STAT_TABLE (
'STATTABOWNER',
'SCH_RO_STATS_20090302'
);
end;
/


(2) Export stats
begin
DBMS_STATS.EXPORT_SCHEMA_STATS (
'SCH_RO',
'SCH_RO_STATS_20090302' ,
NULL,
'STATABOWNER');
end;
/

Be careful when using SYSDATE in the predicate on 9i databases

I had a task to check a SQL and modify it to be used to generate hourly report. The original SQL given looks like (table and column names modified as usual):

---- literal string for the date  
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= To_Date('01/22/2009 00:00:00','MM/DD/YYYY HH24:MI:SS') 
   AND mas.o_save_date < To_Date('01/23/2009 00:00:00','MM/DD/YYYY HH24:MI:SS')
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;

I run it and the excution plan looks like:

-------------------------------------------------------------------------
| Id  | Operation                      |  Name    |Rows | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     |       |    41 |
|   1 |  SORT GROUP BY                 |          |   4 |   148 |    41 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | O_TR     |   2 |    40 |     9 |
|   3 |    NESTED LOOPS                |          |   4 |   148 |    25 |
|   4 |     TABLE ACCESS BY INDEX ROWID| O_MAS    |   2 |    34 |     7 |
|   5 |      INDEX RANGE SCAN          | O_MAS_IX2|   3 |       |     4 |
|   6 |     INDEX RANGE SCAN           | O_TR_FK3 |  14 |       |     3 |
-------------------------------------------------------------------------

The SQL returns in less than a minute. Then I execuate the following SQL which uses
SYSDATE instead of literal date.

SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= trunc(sysdate)-1 
   AND mas.o_save_date < trunc(sysdate) 
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;

The execution plan changed to hash join and checked v$longops, indicating table scan will takes more than 1 hour, ie:

-------------------------------------------------------------------------------
| Id  | Operation                      |  Name     |Rows  |Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      |      |       |   699K|
|   1 |  SORT GROUP BY                 |           | 2445K|   86M|   243M|   699K|
|   2 |   FILTER                       |           |      |      |       |       |
|   3 |    HASH JOIN                   |           | 2445K|   86M|    30M|   683K|
|   4 |     TABLE ACCESS BY INDEX ROWID| O_MAS     | 1113K|   18M|       | 38909 |
|   5 |      INDEX RANGE SCAN          | O_MAS_IX2 |  401K|      |       |  2976 |
|   6 |     TABLE ACCESS FULL          | O_TR      |   55M| 1053M|       |   617K|
---------------------------------------------------------------------------------

I tried to use hint to force the Oracle to use the first execution plan, the cost is 10M, no surprise that Oracle won't choose this plan.

SELECT 
/*+ use_nl( mas, tr) index(tr O_TR_FK3) */
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= trunc(sysdate)-1 
   AND mas.o_save_date < trunc(sysdate) 
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;


-------------------------------------------------------------------------------------
| Id  | Operation                       |  Name     | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |       |       |       |    10M|
|   1 |  SORT GROUP BY                  |           |  2445K|    86M|   243M|    10M|
|   2 |   FILTER                        |           |       |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID  | O_TR      |     2 |    40 |       |     9 |
|   4 |     NESTED LOOPS                |           |  2445K|    86M|       |    10M|
|   5 |      TABLE ACCESS BY INDEX ROWID| O_MAS     |  1113K|    18M|       | 38909 |
|   6 |       INDEX RANGE SCAN          | O_MAS_IX2 |   401K|       |       |  2976 |
|   7 |      INDEX RANGE SCAN           | O_TR_FK3  |    14 |       |       |     3 |
-------------------------------------------------------------------------------------

For the hourly report, I finally obtained the following SQL:

SELECT  /*+ use_nl( mas, tr) index(mas O_MAS_IX2) index(tr O_TR_FK3) */
       To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count 
 FROM 
      o_mas mas,
      o_tr tr 
WHERE mas.is_pq = 'N'
  AND mas.int_o_id = tr.int_o_id
  AND tr.nt_id IN (201, 209, 217, 222)
  AND mas.o_save_date >= trunc(sysdate,'HH24') - 1/24 
  AND mas.o_save_date <  trunc(sysdate, 'HH24')
GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
/

This SQl returns in seconds. If not using the hint, Oracle will choose hash join. What happend here? I seek answers from Jonathan Lewis's book "Cost-Based Oracle Fundamentals" as I vaguelly remember there is something about SYSDATE. I am right. His book has a section titled "Surprising Sysdate" (p130) that shed light on this issue. Basically, Oracle treat "Sysdate+N" as unknown, and treat it same as bind variable. As a result, optimizer can not calculate the cardinality and selectivity in the same way as the case of literal string. Oracle 10g has identified and addressed this problem.

Copy optimizer table statistics between different schema and databases

This test case is to simulate the situation in which we need to copy a table stats from one schema to anohter schema in a different database.

Summary of Steps:
1. Set up the initial tables and collect stats
2. Export table stats using dbms_stats package in the source database and schema
3. Exp the stats table from source database
4. Imp the stats table to the target database and target schema
5. Import table stats using dbms_stats package in target database

Note: Pay attention to the neccessity of updating c5 in the stats tab to replace source schema name with target schema name.


1. set up the inital tables
Note: Table t is in hr, we want to copy its stats to table t in scott

scott@TEST10G> @1_setup
scott@TEST10G> connect hr/hr;
Connected.
hr@TEST10G>
hr@TEST10G> drop table t;

Table dropped.

hr@TEST10G>
hr@TEST10G> create table t
  2  as
  3  select * from all_objects;

Table created.

hr@TEST10G> exec dbms_stats.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

hr@TEST10G>
hr@TEST10G> grant select on t to scott;

Grant succeeded.

hr@TEST10G>
hr@TEST10G> connect scott/tiger;
Connected.
scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t
  2  as
  3  select * from hr.t;

Table created.

2. Export table stats

hr@TEST10G> conn hr/hr
Connected.
hr@TEST10G>
hr@TEST10G> drop table t_stats;

Table dropped.

hr@TEST10G> exec dbms_stats.create_stat_table(user, 'T_STATS');

PL/SQL procedure successfully completed.

hr@TEST10G> exec dbms_stats.export_table_stats(user, 'T', stattab => 'T_STATS');

PL/SQL procedure successfully completed.

3. Exp the stats table: T_STATS

exp hr/hr tables=t_stats file=t_stats.dmp log=3_exp_t_stats.log

$ 3_exp_t_stats.sh

Export: Release 10.2.0.1.0 - Production on Mon Nov 10 18:51:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        T_STATS         14 rows exported
Export terminated successfully without warnings.

4. Imp the stats table: T_STATS
imp system/oracle fromuser=hr touser=scott tables=t_stats file=t_stats.dmp log=4_imp_t_stats.log

Import: Release 10.2.0.1.0 - Production on Mon Nov 10 18:52:22 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing HR's objects into SCOTT
. . importing table "T_STATS" 14 rows imported
Import terminated successfully without warnings.

5. Import table stats
Note: to update c5 in stats table is necessary !

system@TEST10G> @5_imp_tab_stats.sql
system@TEST10G> connect scott/tiger
Connected.
scott@TEST10G>
scott@TEST10G> -- must update column c5
scott@TEST10G>
scott@TEST10G> Prompt c5 stores schema name
c5 stores schema name
scott@TEST10G> select c5 from t_stats;

C5
------------------------------
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR

14 rows selected.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> --  see what will happen if we don't update c5
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER             TABLE_NAME      NUM_ROWS LAST_ANALYZED
----------------- ------------- ---------- -------------------
HR                T                  40733 2008-11-10 18:59:31
SCOTT             T

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> -- update c5
scott@TEST10G>
scott@TEST10G> update t_stats set c5='SCOTT';

14 rows updated.

scott@TEST10G>
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER          TABLE_NAME       NUM_ROWS LAST_ANALYZED
-------------- -------------- ---------- -------------------
HR             T                   40733 2008-11-10 18:59:31
SCOTT          T                   40733 2008-11-10 18:59:31

Locking and unlocking Oracle optimizer statistics

Last Saturdy night, I supported a Change Request which involved copying the statistics from a populated partition in the partitioned table to partitions currently showing zero rows. Such change would be made for about 24 partitioned tables . Script to implement this change was provided by the requestor. However, when I run the script, just for the first partitioned table in the script, I recived the following error:

declare
*
ERROR at line 1:
ORA-00081: address range [0x60000000000D5E80, 0x60000000000D5E84) is not
readable
ORA-00600: internal error code, arguments: [4832], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 6518
ORA-06512: at "SYS.DBMS_STATS", line 7254
ORA-06512: at line 30

Note: dababase version: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit

I have no idea at all about this. Later, the requester suggested to unlock the statistics before running the script. After unlocking the schema statistics, the script went through. I don't know lock and unlock schema or table statistics before, so I decided to explore this feature a little bit.

First of all, in the Oracle documentation:
Statistics for a table or schema can be locked. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked. These locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.

The DBMS_STATS package provides two procedures for locking and two procedures for unlocking statistics:

LOCK_SCHEMA_STATS

LOCK_TABLE_STATS

UNLOCK_SCHEMA_STATS

UNLOCK_TABLE_STATS
From another Oracle do:

The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

I build up a test case trying to simulate the situaltion. However, I got more descriptive error message instead of the one presented above: (my test db is 32-bit 10g)

denis@TEST10G> @lock_stats2
denis@TEST10G> set echo on
denis@TEST10G> rem lock_stats2.sql
denis@TEST10G> rem  -- simulate copy stats from one partition to other partitions that have zero rows
denis@TEST10G> rem
denis@TEST10G> 
denis@TEST10G> drop table t purge;

Table dropped.

denis@TEST10G> drop table my_stat_tab purge;

Table dropped.

denis@TEST10G> 
denis@TEST10G> create table t nologging
  2  partition by range(id)
  3  (
  4    partition  p1 values less than (100),
  5    partition  p2 values less than (200),
  6    partition  p3 values less than (300),
  7    partition  p4 values less than (400),
  8    partition  p5 values less than (500),
  9    partition  p_maxval values less than (MAXVALUE)
 10  )
 11  as
 12  select
 13    rownum id, a.*
 14  from dba_objects a
 15  where rownum < 1000;

Table created.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> Prompt >>> only analyze one partition
>>> only analyze  one partition
denis@TEST10G> exec dbms_stats.gather_table_stats(user, 'T', 'P1');

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     99
P2
P3
P4
P5
P_MAXVAL

6 rows selected.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> Prompt >>> in the case of table stats is locked
>>> in the case of table stats is locked
denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> exec dbms_stats.create_stat_table(user,'my_stat_tab')

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> begin
  2    for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
  3          and nvl(num_rows,0) <= 75) loop
  4    begin
  5       dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  6       execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
  7       dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  8    end;
  9    end loop;
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 1607
ORA-06512: at "SYS.DBMS_STATS", line 2117
ORA-06512: at "SYS.DBMS_STATS", line 7249
ORA-06512: at line 7


denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     99
P2
P3
P4
P5
P_MAXVAL

6 rows selected.

denis@TEST10G> 
denis@TEST10G> Prompt >>> in the case of table stats is unlocked
>>> in the case of table stats is unlocked
denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> begin
  2    for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
  3          and nvl(num_rows,0) <= 75) loop
  4    begin
  5       dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  6       execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
  7       dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  8    end;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     99
P2                                     99
P3                                     99
P4                                     99
P5                                     99
P_MAXVAL                               99

6 rows selected.

denis@TEST10G> spool off

The following test shows that we can query the stattype_locked column of user_tab_statistics to know if the table stats is locked or not:

denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;

TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T                                                             ALL
MY_STAT_TAB
T                              P_MAXVAL                       ALL
T                              P5                             ALL
T                              P4                             ALL
T                              P3                             ALL
T                              P2                             ALL
T                              P1                             ALL

8 rows selected.

denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;

TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T
MY_STAT_TAB
T                              P_MAXVAL
T                              P5
T                              P4
T                              P3
T                              P2
T                              P1

8 rows selected.

Speed up the optimizer analyze job in a replicated environment

In an application, the production database is replicated to a reporting database and a DR database through SharePlex replication technology. The analyze jobs for the optimizer stats run on three instances bi-weekly. The problem is that the analyze job takes as long as 24 hours in the reporting database, which impact other jobs seriously. I have proposed a new procedure in order to reduce the time needed for the analyze job and potentially allow collectng more accurate optimizer stats within the time window available. The basic idea is to distribute the job among three instances and then combine the results, kind of divide-and-conquer.

Below I describe the steps. And overall time to obtain the stats was about 7-8 hours from the first time implementation, which involvs some manual steps. I think it is possible to automate the whole procedure if needed.


Steps:


1. Backup the current stats in PROD, RPT and DR instances:

1.1 create_stat_table

begin
DBMS_STATS.CREATE_STAT_TABLE (
'DB_ADMIN',
'MYSCHMEA_STATS_052508'
);
end;
/

1.2 export_schema_stats

begin
DBMS_STATS.EXPORT_SCHEMA_STATS (
'MYSCHEMA',
'MYSCHEMA_STATS_052508' ,
NULL,
'DB_ADMIN');
end;
/

2. Analyze group A table stats in PRD

Note: all tables that under MYSCHEMA are divided into two groups: group A and group B

It took about 4.5 hrs

3. Analyze group B table stats in DR

It took about 4.5 hrs

4. Analyze additional index stats in rpt

Note: Some indexes only exist in the reporting database

It took about 5.5 hrs

5. Sync up stats among three database

5.1 In PRD: export group A table/index stats

a. create stats table

begin
DBMS_STATS.CREATE_STAT_TABLE (
'DB_ADMIN',
'TAB_A_STATS',
NULL);
end;
/

b. run script: exp_tab_a_stats.sql



5.2 In DR: export group B table/index stats

begin
DBMS_STATS.CREATE_STAT_TABLE (
'DB_ADMIN',
'TAB_B_STATS',
NULL);
end;
/

script: exp_tab_b_stats.sql

5.3 In PRD: import group B table/index stats
a.
create table db_admin.tab_b_stats as
select * from db_admin.tab_b_stats@DB_DR;

b.
run script: imp_tab_b_stats.sql

Verify:

select table_name, last_analyzed from dba_tables where owner='MYSCHEMA' and last_analyzed < sysdate -1;

select index_name, last_analyzed from dba_indexes where owner='MYSCHEMA' and last_analyzed < sysdate -1;

5.4 RRT: import group A table/index stats


create table db_admin.tab_b_stats
as select * from db_admin.tab_b_stats@DB_DR;

create table db_admin.tab_a_stats
as select * from db_admin.tab_a_stats@DB_PRD;

script: imp_tab_a_stats.sql

5.5 Rpt: import group B table/index stats
script: imp_tab_b_stats.sql

5.6 Dr: import group A table/index stats
create table db_admin.tab_a_stats
as select * from db_admin.tab_a_stats@DB_PRD_lnk;

script: imp_tab_a_stats.sql


Note: to generate the imp/exp table stats scripts, I used the AWK script: for example:

# exp_stats.awk
{
   print "begin"
   print "  dbms_stats.export_table_stats("
   print "       'MYSCHEMA', "
   print "       '" $1 "',"
   print "       stattab => 'TAB_B_STATS' ,"
   print "       statown => 'DB_ADMIN' ,"
   print "       cascade => TRUE, "
   print "  );"
   print "end;"
   print "/"
   print " "
}


# imp_stats.awk
{
   print "begin"
   print "  dbms_stats.import_table_stats("
   print "       'MYSCHEMA', "
   print "       '" $1 "',"
   print "       stattab => 'TAB_B_STATS' ,"
   print "       statown => 'DB_ADMIN' ,"
   print "       cascade => TRUE "
   print "  );"
   print "end;"
   print "/"
   print " "
}

CBO is unable to pick up good execution plan

The problem ocurred in an Oracle 9.2.0.8 database. Many queries suddently run much longer from one day. What kind of changes have been made to the database is not obvious. Checking their execution plans, typically seeing the CBO choose hash join and full table scan for some large tables. By trial and error, the resolution to this problem is to disable hash join at the session level and add index hints. Typically by doing so, the queries can be executed in a few minutes instead of few hours. The execution plans before the problem occurrs have not been kown. It is evident that by collecting optimizer statistics more accuratly can help the CBO choose better plan. It has also been tested that setting the optimizer_index_cost_adj parameter to 10 or 20 can make the CBO choose better plans for some queries. It seems that CBO under-estimate the cost of full table scan.

Below is an example. The query is a relative simple one, which involves join of two large tables:

 select 'Pots,',count(distinct(od.master_order_id))
   from tableOD od,
        tableSD sd
  where
        sd.MASTER_ORDER_ID=od.MASTER_ORDER_ID
    and sd.SUB_ORDER_TYPE in (1,3,4)
    and sd.product_category in (2,3,17)
    and sd.SUPP_ORDER_ID=0
    and sd.service_type='Install'
    and od.RECEIVED_DATE between To_Date('05/20/08 00:00:00','MM/DD/YY HH24:MI:SS')
    and    To_Date('05/20/08 23:59:59','MM/DD/YY HH24:MI:SS')
    and (sd.SSPUNITY in ('N') or sd.SSPUNITY is null )
     and (sd.DRY_LOOP_IND in ('N') or  sd.DRY_LOOP_IND is null )
    and (sd.HARMONY_DATA_RECEIVED = 'N' or sd.HARMONY_DATA_RECEIVED is null )
   group by trunc(od.RECEIVED_DATE),
               sd.SSPUNITY,
               sd.HARMONY_DATA_RECEIVED,
               sd.DRY_LOOP_IND;

The execution plan obtained from v$sql_plan is shown as follows:

##### bad plan: if no index hint and not disable hash join

----------------------------------------------------------------------------------
 Id   Operation                        Name             Rows   Bytes  Cost (%CPU)
----------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                        
   1   SORT GROUP BY                                     1042  63562    504K  (5)
   2    FILTER                                                                
   3     HASH JOIN                                       1042  63562    504K  (5)
   4      TABLE ACCESS FULL           TABLESD            1042  39596    443K  (6)
   5      TABLE ACCESS BY INDEX ROWID TABLEOD             103K  2314K 61262   (1)
   6       INDEX RANGE SCAN           ORD_DTL_RECV_DATE   185K          581   (1)
----------------------------------------------------------------------------------

The better execution plan is obtained by:
1. alter session set hash_join_enabled=false;
2. add: select /*+ INDEX(od ORD_DTL_RECV_DATE) INDEX(sd SSD_MASTER_ORDER_ID_NUK) */

Note we won't get the better plan if only adopting one of the above conditions.


##### good plan

-------------------------------------------------------------------------------------
 Id   Operation                         Name                  Rows   Bytes  Cost (%CPU)
----------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                                 
   1   SORT GROUP BY                                           1042  63562    576K  (1)
   2    FILTER                                                                         
   3     TABLE ACCESS BY INDEX ROWID   TABLESD                   1     38      6  (17)
   4      NESTED LOOPS                                         1042  63562    576K  (1)
   5       TABLE ACCESS BY INDEX ROWID TABLEOD                 103K  2314K 61262   (1)
   6        INDEX RANGE SCAN           ORD_DTL_RECV_DATE       185K          581   (1)
   7       INDEX RANGE SCAN            SSD_MASTER_ORDER_ID_NUK   2             4  (25)
-------------------------------------------------------------------------------

I also checked the execution plan after collecting more accurate statitics on the table
TABLESD:

exec dbms_stats.gather_table_stats('SSP_FLOW',
'TABLESD',
method_opt => 'FOR COLUMNS MASTER_ORDER_ID SIZE 1',
estimate_percent => 100, cascade =>true);

Note: this analyze job took more than 8 hours


The plan is still not optimal in this case:

--------------------------------------------------------------------------------
 Id   Operation                        Name     Rows   Bytes  Cost (%CPU)
--------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                
   1   SORT GROUP BY                             1042  63562    420K  (6)
   2    FILTER                                                        
   3     NESTED LOOPS                            1042  63562    420K  (6)
   4      TABLE ACCESS FULL           TABLESD    1042  39596    416K  (6)
   5      TABLE ACCESS BY INDEX ROWID TABLEOD       1     23      5  (20)
   6       INDEX RANGE SCAN           TEMP_1        1             4  (25)
--------------------------------------------------------------------------------