Cost Based Optimization ! Query Performance Issue after creating a new Index…

Creating an Index to optimize performance of a Query is usually an adopted strategy by most of the Database Administrators or Application Vendors. While it may optimize the performance of the query for which it has been created, but can have an impact on other queries, as these may start picking up the newly created index and this new index may not be optimal for these queries. This is especially true where Indexing Strategy is not up to the mark. My first AIOUG Session at Hyderabad, I presented something on Proactive and Reactive Tuning. If Optimization is ignored during Application Development Life cycle, then these efforts are done in an reactive mode and this is when the database administrators or developers end up creating unwanted or more number of Indexes. With these redundant indexes in place, there is always a threat of performance impact after creating a new index and this is what transpired at one of my customer place.

Recently, the application team at one of my customer site introduced a new Batch Process that runs 2-3 times a day. After Implementation, they found the performance of this process not up-to-the mark and therefore created a new Index to optimize one of the resource intensive query of that process. After creating this new Index, the performance of the batch process improved, but few other queries started using this newly created index, with the sessions waiting on “read by other session”. Now, the customer was in a big dilemma. If they drop the new index, the batch process is impacted and if it is created, 2-3 other queries are impacted. When this was escalated to the Application Vendor, they washed off their hands stating this to be an Oracle Issue. In my opinion, this seems to be an application issue and as you read further you will come to know the reason for this, but the issue can also be attributed to Oracle Optimizer. Why the Optimizer chose a newly created index, when the performance of the query was optimal with the existing index ? Let us walk through a detailed issue and the analysis.

After Implementing a new Batch process, Query No.1 below was doing a Full Table Scan of a 80 GB table. To optimize this query and improve the completion time of the Batch process, an Index BLINE_INDX was created. There was already an Index N_T_ADVICE_DTL_IDX3 on the CASEID column as a leading column, but the optimizer chose not to use this Index, may be since this column has been used as a Range Predicate. Instead of Investigation the reason for not using this Index, the Application Vendor reverted with a new index BLINE_INDX and therefore this index was created. Once this new Index was created, the execution plan of Query No.2, which was optimally using N_T_ADVICE_DTL_IDX3 ceased to use this index and started using the New Index BLINE_INDX. While there were several more indexes on this table, I have considered only two indexes that are relevant for this posting.

Query No.1 (BLINE PROCESS)
--------------------------

SELECT ROWID,CASEID,TO_CHAR(ADVICEDATE,'YYYYMMDD'),TXNADVICEID,CHARGEID,TXNID,
TXNTYPE,ADVICETYPE,ADVICEAMT,ORIGNALAMT,STATUS,DESCRIPTION,REMARKS,BPTYPE 
FROM 	flea.N_T_ADVICE_DTL 
WHERE 	caseid BETWEEN :1 AND :2 
AND 	ADVICEDATE BETWEEN :3 AND :4 
AND 	status='A' 
ORDER BY CASEID, ADVICEDATE,  TXNADVICEID;

Query No.2
----------

SELECT SUM(txnadjustedamt) 
FROM	flea.n_t_advice_dtl 
WHERE	caseid = :b1 
AND	chargeid =93 
AND	status ='A';

Index Details
-------------

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
BLINE_INDX                     CASEID                                       1 <== Local
                               ADVICEDATE                                   2
                               STATUS                                       3

N_T_ADVICE_DTL_IDX3            CASEID                                       1 <== Local
                               CHARGEID                                     2

My services in this issue was sought when the Customer wanted the analysis on why Query 2 started picking up a new Index, while, both the columns of IDX3 index are present in Query 2 and therefore should be an obvious choice for the optimizer. This seems to be true and therefore, I started my analysis on this issue with a query to v$sql_bind_capture to get the actual production runtime value passed to the bind :1 by the application user. I executed this query and passed the bind value to a bind variable b1. The runtime plan of the query shows BLINE_INDX index being used and this matches the production issue.

SQL>@bind_capture
new   2: where sql_id='b7nkg6m692jgq'

NAME                             POSITION DATATYPE_STRING      VALUE_STRING
------------------------------ ---------- -------------------- ------------------------------
:1                                      1 NUMBER               2568494

SQL>variable b1 number;
SQL>exec :b1:=2568494;

SELECT /*+ VIVEK_2568494 */ SUM(txnadjustedamt) 
FROM	flea.n_t_advice_dtl 
WHERE	caseid = :b1 
AND	chargeid =93 
AND	status ='A';

Plan Table
============
------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                            | Name               | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                     |                    |       |       |     4 |           |       |       |
| 1   |  SORT AGGREGATE                      |                    |     1 |    16 |       |           |       |       |
| 2   |   PARTITION RANGE SINGLE             |                    |     1 |    16 |     4 |  00:00:01 | KEY   | KEY   |
| 3   |    TABLE ACCESS BY LOCAL INDEX ROWID | N_T_ADVICE_DTL     |     1 |    16 |     4 |  00:00:01 | KEY   | KEY   |
| 4   |     INDEX RANGE SCAN                 | BLINE_INDX         |    47 |       |     1 |  00:00:01 | KEY   | KEY   |
------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("CHARGEID"=:SYS_B_0)
4 - access("CASEID"=:B1 AND "STATUS"=:SYS_B_1)
4 - filter("STATUS"=:SYS_B_1)

This being a partitioned table, the value 2568494 falls under the high_value of Partition 3 of the table. Therefore, the statistics for this partition was referred for the calculation and analysis. Shown below are the Optimizer Statistics for Partition 3 and the calculation that optimizer does to derive a cost of an Index Scan.


## Calculation for Partition 3
-------------------------------
TABLE_OWNER  PARTITION_NAME           HIGH_VALUE      NUM_ROWS     BLOCKS PARTITION_POSITION
------------ ------------------------ ------------- ---------- ---------- ------------------
FLEA         N_T_ADVICE_DTL_FO_P3     3000000         90588615    2019809                  3

Partition Column Stats
-----------------------
## Stats for Partition 3 based on Bind Capture : 2568494

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
--------------- ------------ ---------- ---------- ---------------
CASEID                954287          0 1.0479E-06 NONE
CHARGEID                 105          0  .00952381 NONE
STATUS                     2          0         .5 NONE

Partition Index Stats
---------------------
INDEX_NAME                PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   AVDPK      ALBKP
------------------------- ------------------------------ ---------- ----------- ------------- ----------------- ------- ----------
BLINE_INDX                N_T_ADVICE_DTL_FO_P3                    3      335600       8818906          46069060       5          1

N_T_ADVICE_DTL_IDX3       N_T_ADVICE_DTL_FO_P3                    3      293115       1638635          48089155      29          1

Once the data was collected, it was time to compute the cost of both the Indexes. The formula for computing the cost of an Index Scan and then Table Access by Index step is :

Cost of Index Scan (A) = BLEVEL + CEIL(LEAF_BLOCKS * INDEX SELECTIVITY)
Cost of Table Scan via Index (B) = CEIL(CLUSTERING_FACOR * INDEX SELECTIVITY WITH FILTERS)
Total Cost = A + B

Analysis & Cost Calculation

In case of BLINE_INDX Index, for the selectivity, only CASEID column is to be considered. Revisit the Execution Plan above and check for the Predicate Information. While CASEID and STATUS columns exists in this Index, Access_Predicate Information shows that only CASEID column is used for Index Scan and further STATUS column is used as a Filter_predicate. Therefore, Num_Distinct of CASEID has to be used to derive Index Selectivity and product of Num_Distinct of CASEID and STATUS has to be used to compute the Index Selectivity with Filters.

Similary, for N_T_ADVICE_DTL_IDX3, since both the columns present in the Index are used in the Query, the Selectivity (Index and with Filters) will have to be computed on the product of the two columns present i.e.CASEID and CHARGEID.

One point also to be noted here is that the parameter OPTIMIZER_INDEX_COST_ADJ has been set to 15 and this is also to be taken into consideration when computing the Final Cost.

Calculation For bline Index
---------------------------
select 3+ceil(335600*1/954287), ceil(46069060*1/954287*1/2) from dual;

Index Cost : 4
Table Access : 4+25 = 29

OICA = 29*15/100 = 4.35

Rounded Up to 4
----------------

For IDX3 Index
--------------

select 3+ceil(293115*1/954287*1/105), ceil(48089155*1/954287*1/105) from dual;

Index Cost : 4
Table Access : 4+1 = 5

OICA : 5*15/100 = 0.75

Rounded Up to 1
---------------

Based on this calculation, the cost of IDX3 seems to be cheaper and should have been an obvious choice. Why is Optimizer computing BLINE_INDX to be cheaper than IDX3 ? There’s another better place to compare our analysis with the Optimizer Computation and this is 10053 trace file. For this, the query needs to be hard parsed and therefore, with a minor change in the query, it was reexecuted for the same bind value and a trace file was generated.

Once the 10053 trace was generated, it was easier to get into the rationale behind Optimizer Choice of BLINE_INDX index for this query. Pasted below is the relevant content of the 10053 trace file. I have also underlined the relevant value that will be used for analysis and comparison.

Table Stats::
  Table: N_T_ADVICE_DTL  Alias: N_T_ADVICE_DTL  Partition [2]
    #Rows: 90588615  #Blks:  2019809  AvgRowLen:  164.00
Index Stats::
  Index: BLINE_INDX  Col#: 28 27 17  PARTITION [2]
    LVLS: 3  #LB: 335600  #DK: 8818906  LB/K: 1.00  DB/K: 5.00  CLUF: 46069060.00
  Index: N_T_ADVICE_DTL_IDX3  Col#: 28 3  PARTITION [2]
    LVLS: 3  #LB: 293115  #DK: 1638635  LB/K: 1.00  DB/K: 29.00  CLUF: 48089155.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#28): CASEID(NUMBER)  Part#: 2
    AvgLen: 6.00 NDV: 954287 Nulls: 0 Density: 1.0479e-06 Min: 2000000 Max: 2999998
  Column (#3): CHARGEID(NUMBER)  Part#: 2
    AvgLen: 4.00 NDV: 105 Nulls: 0 Density: 0.0095238 Min: 2 Max: 1000018
  Column (#17): STATUS(VARCHAR2)  Part#: 2
    AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
  Table: N_T_ADVICE_DTL  Alias: N_T_ADVICE_DTL
    Card: Original: 90588615  Rounded: 1  Computed: 0.45  Non Adjusted: 0.45
  Access Path: TableScan
    Cost:  441312.32  Resp: 441312.32  Degree: 0
      Cost_io: 425225.00  Cost_cpu: 81419528497
      Resp_io: 425225.00  Resp_cpu: 81419528497
kkofmx: index filter:"N_T_ADVICE_DTL"."STATUS"=:B1
kkofmx: index filter:"N_T_ADVICE_DTL"."CHARGEID"=:B1 AND "N_T_ADVICE_DTL"."STATUS"=:B2
kkofmx: index filter:"N_T_ADVICE_DTL"."CASEID"=:B1 AND "N_T_ADVICE_DTL"."CHARGEID"=:B2 AND "N_T_ADVICE_DTL"."STATUS"=:B3
  Access Path: index (RangeScan)
    Index: BLINE_INDX
    resc_io: 29.00  resc_cpu: 195479
    ix_sel: 1.0703e-06  ix_sel_with_filters: 5.3516e-07
    Cost: 4.36  Resp: 4.36  Degree: 1
  Access Path: index (AllEqRange)
    Index: N_T_ADVICE_DTL_IDX3
    resc_io: 34.00  resc_cpu: 216124
    ix_sel: 6.1026e-07  ix_sel_with_filters: 6.1026e-07
    Cost: 5.11  Resp: 5.11  Degree: 1
  Best:: AccessPath: IndexRange  Index: BLINE_INDX
         Cost: 4.36  Degree: 1  Resp: 4.36  Card: 0.45  Bytes: 0

The Cost of BLINE_INDX (resc_io) matches our calculation. RESC_IO for BLINE_INDX is 29 and with OICA set to 15, the final cost is 4.36, rounded off to 4. Surprisingly, the Cost of IDX3 (resc_io), as per Optimizer is 34, as against our calculation of 5. Optimizer always chose a least costed plan and in this case, 29 against 34 and 4.36 agains 5.11 is cheaper, therefore Optimizer opted for BLINE_INDX Index. Now, the action plan was to check for the Calculation made by the Optimizer that derives the cost of IDX3 index to be 34. BLEVEL and CLUSTERING_FACTOR of the IDX3 index is unchanged and therefore, I doubted on the Selectivity part. The ix_sel (index_selectivity) and ix_sel_with_filters (Index Selectivity with Filters) for IDX3 index is 6.1026e-07. The Selectivity, as derived by me, was 1/NDV*1/NDV (for two columns). I decided to do some reverse engineering and therefore, calculated the value of 1/6.1026e-07 which is 1638645.82. Then I looked for a value closer to 1638645.92 and observed that the DISTINCT_KEYS of IDX3 is 1638635, which is very much close. Now, if you check the Index and Index with Filters Selectivity based on 1/1638635, then it exactly matches the ix_sel and ix_sel_with_filters value of 10053 trace. Further, note the line Access Path: index (AllEqRange) above Index: N_T_ADVICE_DTL_IDX3 in the trace file. AllEqRange means, all the columns of an Index has been used as an Equality predicate and therefore, instead of using individual selectivity of each columns, optimizer has considered the computed DISTINCT_KEYS of an Index and this has flawed the Optimizer Computation.

By this time, it was clear and evident that the flaw in this calculation is due to AllEqRange, it was time to implement a change that would resolve the issue. There were two solutions for this issue. These are :

  1. Modify the Clustering Factor for the problematic partitions.
  2. Add a new Column to IDX3 Index that is not used in the Problematic Query (query no.2)

I computed the cost for each of the partitions and observed that the clustering_factor for almost 70% of the index partitions will have to be modified and this means, re-calculating this everytime the stats are computed. Locking of Stats after modifying the Clustering_factor would have helped but then, Optimizer Behaviour Change and the risk of performance impact with other queries was on top of the mind and therefore, this option was not implemented. I tested this for one partition, where after modifying the Clustering_factor, the optimizer started picking up IDX3 index.

The other option was to modify the index by way of adding a new column. Again, in order to eliminate any potential risk after adding a third column, the best option implemented thought of and implemented was adding a column with single distinct value. Therefore, a column CURRENCYID with a single distinct value was appended to this index and was tested on UAT. Before making this change on the UAT, first we made sure that the issue is reproduced. Once we were sure that the similar issue exists on the UAT enviroment as well, we dropped and recreated the index on (CASEID, CHARGEID, CURRENCYID) and the change had a dramatic impact. With this change, the AllEqRange changed to RangeScan and the optimizer computed the cost based on Individual Selectivities of the two columns rather than DISTINCT_KEYS from dba_indexes. Pasted below is the relevant portion of 10053 trace generated on UAT post-index change.

Table Stats::
  Table: N_T_ADVICE_DTL  Alias: N_T_ADVICE_DTL Partition [2]
    #Rows: 25494530  #Blks:  10874  AvgRowLen:  165.00
Index Stats::
  Index: BLINE_INDX  Col#: 28 27 17  PARTITION [2]
    LVLS: 2  #LB: 92720  #DK: 8843108  LB/K: 1.00  DB/K: 1.00  CLUF: 11966280.00
  Index: N_T_ADVICE_DTL_IDX3  Col#: 28 3 18 PARTITION [2]
    LVLS: 2  #LB: 86050  #DK: 2764430  LB/K: 1.00  DB/K: 4.00  CLUF: 12732735.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#28): CASEID(NUMBER)
    AvgLen: 6.00 NDV: 406027 Nulls: 240 Density: 2.4629e-06 Min: 0 Max: 51734139
  Column (#3): CHARGEID(NUMBER)
    AvgLen: 4.00 NDV: 165 Nulls: 0 Density: 0.0060606 Min: 2 Max: 10000048
  Column (#17): STATUS(VARCHAR2)
    AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 0.33333
  Table: N_T_ADVICE_DTL  Alias: N_T_ADVICE_DTL     
    Card: Original: 25494530  Rounded: 1  Computed: 0.13  Non Adjusted: 0.13
  Access Path: TableScan
    Cost:  2544.46  Resp: 2544.46  Degree: 0
      Cost_io: 2380.00  Cost_cpu: 414356148
      Resp_io: 2380.00  Resp_cpu: 414356148
kkofmx: index filter:"N_T_ADVICE_DTL"."STATUS"=:B1
kkofmx: index filter:"N_T_ADVICE_DTL"."CHARGEID"=:B1 AND "N_T_ADVICE_DTL"."STATUS"=:B2
kkofmx: index filter:"N_T_ADVICE_DTL"."CASEID"=:B1 AND "N_T_ADVICE_DTL"."CHARGEID"=:B2 AND "N_T_ADVICE_DTL"."STATUS"=:B3
  Access Path: index (RangeScan)
    Index: BLINE_INDX
    resc_io: 14.00  resc_cpu: 127837
    ix_sel: 2.5335e-06  ix_sel_with_filters: 8.4450e-07
    Cost: 2.11  Resp: 2.11  Degree: 1
  Access Path: index (RangeScan)
    Index: N_T_ADVICE_DTL_IDX3
    resc_io: 4.00  resc_cpu: 29395
    ix_sel: 1.4926e-08  ix_sel_with_filters: 1.4926e-08
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: N_T_ADVICE_DTL_IDX3
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 0.13  Bytes: 0

 
============
Plan Table
============
-----------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                            | Name                    | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
-----------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                     |                         |       |       |     1 |           |       |       |
| 1   |  SORT AGGREGATE                      |                         |     1 |    15 |       |           |       |       |
| 2   |   PARTITION RANGE SINGLE             |                         |     1 |    15 |     1 |  00:00:01 | KEY   | KEY   |
| 3   |    TABLE ACCESS BY LOCAL INDEX ROWID | N_T_ADVICE_DTL          |     1 |    15 |     1 |  00:00:01 | KEY   | KEY   |
| 4   |     INDEX RANGE SCAN                 | N_T_ADVICE_DTL_IDX3     |     1 |       |     1 |  00:00:01 | KEY   | KEY   |
-----------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("STATUS"=:SYS_B_1)
4 - access("CASEID"=:B1 AND "CHARGEID"=:SYS_B_0)

The change is still pending for Implementation on production and will be done post month end. With the analysis, it seems that the issue should resolve post this change. If you have come across any such issues, please do share the findings. For the other 2 queries, I do have an interesting fact about Cost Based Optimizer, but unfortunately, have not collected artifacts and data to post on the blog. While the change in BLINE_INDX Index suggested by me has resolved these issues as well, for posting purpose, I am doing further study on this and will share the findings as and when I am ready with it.

Benefits of SQL Baseline ! Yet another performance issue post 10g Upgrade

First, let me apologies for a brief pause from blogging. I was out of action for most of these days and as I always maintain, I write only if I am active and have something to share with Oracle Community.

Few years back, I had suggested a customer to maintain SQL Baseline that collects the execution plan and other details like SQL_TEXT etc. While the customer had scheduled a job that periodically collects these informations into some tables, these collected information were never used, infact, a need never arouse to get into the collected information. Finally, post upgrade of this database to 10g, a severe performance degradation was reported and the information collected using SQL Baseline came handy. Usually, before upgrading a database to 10g, I strongly recommend performance testing, but in this case, the customer went ahead with the production upgrade with minimal testing.

Another challenge in this Upgrade was the Stats gathering policy. During the pre-upgrade discussion, the Customer mentioned :

  1. Stats for very few tables are generated periodically,
  2. Stats for most of the tables are stale and if gathered, impacts the performance,
  3. Stats for many of the tables are not collected at all.

Performance issues, with very minimal testing and Optimizer Stats issues, was inevitable and customer was well aware of this fact. However, post-upgrade the impact was unexpectedly very severe and the database was almost unworkable. As a result, and as expected in a firefighting mode, various optimization techniques were ineffectively implemented. At this point of time, our investigation started.

As mentioned earlier, the challenge here was unavailability of test results from 9i instance. There were significant waits on CBC Latches and "read by other session" and one common table used in these impacted queries (almost in 90% of the queries) was TH_DBF. During my investigation, I concentrated on one top query that was executed multiple times and was waiting on CBC latch for most of the time. The text of the query is as mentioned below, alongwith the execution plan from 10g.

select T1.TIMESTAMP, T1.M_IDENTITY, T1.M_TRN_GRP, T1.M_TRN_TYPO, T1.M_BINTERNAL, T1.M_BENTITY, 
T1.M_SINTERNAL, T1.M_SENTITY, T1.M_TRN_STATUS, T1.M_TRN_EXP, T1.M_VAL_STATUS, 
T1.M_MOP_LAST,T2.TIMESTAMP, T2.M_IDENTITY, T2.M_NB, T2.M_LINE, T2.M_SRC_PFOLIO, T2.M_DST_PFOLIO, 
T2.M_CNTRP, T2.M_FEE, T2.M_CUR, T2.M_CODE, T2.M_AUTF, T2.M_AUTS, T2.M_TYPE, 
T2.M_VALUE_TYPE, T2.M_VALUE_DATE, T2.M_AVP 
from 	TH_DBF T1,
	TB_DBF T2  
where 	(T1.M_NB   =  T2.M_NB ) 
and 	((((((((T1.M_BINTERNAL like 'N%') or (T1.M_SINTERNAL like 'N%')) 
and 	(T1.M_TRN_EXP>=to_date('20100105','YYYYMMDD'))) 
and 	(((T1.M_VAL_STATUS'MVAL') and (T1.M_VAL_STATUS'MCON')) 
and 	(T1.M_VAL_STATUS'MCAP'))) and (T1.M_MOP_LAST3)) 
and 	(T1.M_TRN_STATUS'DEAD')) and (T1.M_TRN_GRP like 'FXD%')) 
or 	((((T1.M_BENTITY'INDIA MUM') and (T1.M_SENTITY'INDIA MUM')) 
and 	(instr(T1.M_TRN_TYPO, 'PAYCUR') >0 )) 
and 	(T1.M_TRN_EXP>to_date('20100105','YYYYMMDD'))));

Plan hash value: 2180463251

-----------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes |TempSpc| Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |       |       |       | 52386 |
|*  1 |  HASH JOIN         |                |   367K|    50M|  9512K| 52386 |
|*  2 |   TABLE ACCESS FULL| TH_DBF         | 91836 |  8430K|       | 46682 |
|   3 |   TABLE ACCESS FULL| TB_DBF         |  2541K|   121M|       |  3161 |
-----------------------------------------------------------------------------

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

   1 - access("T1"."M_NB"="T2"."M_NB")
   2 - filter(((("T1"."M_BINTERNAL" LIKE :SYS_B_00 OR
              "T1"."M_SINTERNAL" LIKE :SYS_B_01) AND "T1"."M_TRN_EXP">=:SYS_B_02 AND
              "T1"."M_VAL_STATUS":SYS_B_03 AND "T1"."M_VAL_STATUS":SYS_B_04 AND
              "T1"."M_VAL_STATUS":SYS_B_05 AND "T1"."M_MOP_LAST":SYS_B_06 AND
              "T1"."M_TRN_STATUS":SYS_B_07 AND "T1"."M_TRN_GRP" LIKE :SYS_B_08) OR
              ("T1"."M_BENTITY":SYS_B_09 AND "T1"."M_SENTITY":SYS_B_10 AND
              INSTR("T1"."M_TRN_TYPO",:SYS_B_11)>:SYS_B_12 AND
              "T1"."M_TRN_EXP">:SYS_B_13)))

SQL Baseline

Fortunately, the customer had maintained SQL Baseline for all the queries and the good plan from 9i was found in these baseline tables. In 9i, thanks to Thomas Kyte, following tables are critical and should be maintained on a regular basis :

create table sqlarea_baseline as select hash_value, sql_text, executions, buffer_gets from v$sqlarea;
create index sqlarea_baseline_idx on sqlarea_baseline(hash_value);

insert into sqlarea_baseline
select hash_value, sql_text, executions, buffer_gets from v$sqlarea a
where not exists
(select 'x' from sqlarea_baseline b where b.hash_value = a.hash_value);

create table sqlplan_baseline as select * from v$sql_plan;
create index sqlplan_baseline_idx on sqlplan_baseline(hash_value);

insert into sqlplan_baseline
select * from v$sql_plan a
where not exists
(select 'x' from sqlplan_baseline b where b.hash_value = a.hash_value);

The data collected using above scripts came handy and we could get the plan of this SQL from 9i. The plan from 9i is pasted below :

9i Plan
-------
--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |
Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 4101123407 ----|       |      |  48785 |
| NESTED LOOPS                   |                     |    28K|    3M|  48785 |
|  TABLE ACCESS FULL             |TH_DBF               |     7K|  643K|  46682 |
|  INDEX RANGE SCAN              |TB_ND1               |     4 |      |      2 |
--------------------------------------------------------------------------------

In both the cases, the optimizer has opted for a Full Table Scan of T_DBF table. The difference is Nested Loop in 9i against Hash Join and FTS of the second table in 10g. Now the investigation was restricted to check for the change that has triggered this HASH Join. If you notice the number of rows, as calculated by the optimizer, returned by the FULL Table Scan of TH_DBF, there is a vast difference in the calculated cardinality by the two versions. Optimizer from 9i computes 7K rows, whereas, it is 91K in 10g. The Optimizer choice of Hash Join is well justified, if the rows returned by the initial step is huge. Just to check whether the plan changes by passing 7k as a cardinality of the Driving Step, I executed the query with CARDINALITY(t1,7000) hint and as expected, the plan from 9i was restored i.e.changed from Hash Join to Nested Loop, also, the response time of the query improved as well.

Now, since the problem was clear and known, detailed investigation in the optimizer calculation was required. Modifying the query with CARDINALITY hint was not possible, as it required change at the application level and could have taken time. Modifying the statistics was also not feasible as this table is the heart of the application and modifying the stats may impact other queries. Therefore, the only choice was to investigate and fix the issue.

INVESTIGATION

With the analysis mentioned above, it was evident that Optimizer is miscomputing the expected Cardinality of the row source generated by the driving step. Therefore, the columns of TH_DBF table used in the query were looked upon and one such column is M_TRN_EXP. This is a date column. Whenever this query is executed, the value passed, at runtime, to this column is SYSDATE. As a result, the number of rows returned by this query is less than 5000 rows. This was validated after executing the query with CARDINALITY hint. With 5000 rows, and 91k prediction by the optimizer, I suspected something amiss with the Column level statistics and therefore, I got into these details.

Using dbms_stats.convert_raw_value function, I could convert the raw value data stored in Low_Value and High_Value of dba_tab_columns. Surprisingly, the result after converting these is as under (the date is in YYYYMMDD format) :

COLUMN_NAME                    NUM_DISTINCT GET_DATE GET_DATE
------------------------------ ------------ -------- --------
M_TRN_EXP                              4355 19810101 20660901

With M_TRN_EXP>sysdate, the optimizer assumes that it has to fetch the data of 56 years out of 85 Years, which is nearly 65%, and this calculation is enough to misguide the optimizer computation. I have always maintained, that Optimizer is a piece of Code and it has to come out with a result based on the input given by us, in the form of statistics. There were only 4 rows with dates pertaining to 1966 and optimizer was not aware of this fact.

SOLUTION

Now, it was quite evident that this is purely an application issue and the Data entered into the application table. The customer requested for a immediate fix to this issue and the solutions suggested were :

  1. Rectifying the data : Not possible immediately
  2. Modifying Column Level Statistics (High Value)
  3. Generating Histograms on this column

Just to make sure that the Column Level Statistics, due to future date data entered, is a problem, we modified the statistics for M_TRN_EXP column and changed the High_Value to 20111231 and the query plan changed for good (similar to 9i). Therefore, a better approach discussed was to generate histogram on this column. Once the histogram was generated, the query response time improved and the plan from 9i was restored. Surprisingly, with this change, the overall performance of the database improved drastically, as most of the queries executed are on this table and all the queries have similar logic (M_TRN_EXP).

Predicting Performance Impact after adding a New Column to an existing Index

This blog featured on my previous blog URL (still unaccessible) and was published on 7th September 2008. Thought of reposting this, as a similar performance issue was recently reported by one of my customer, where a query had to be fixed by way of an Index Hint. This blog would also help you understanding the way optimizer_index_cost_adj is to be calculated for optimizing a query performance. This calculation was requested by one of a regular reader of my blogs.

Modifying an existing index to optimize multiple queries is a common practice, as this eliminates the overheads of maintaning muliple indexes, by way of INSERTS / UPDATES / DELETES. This modification of an existing index includes, re-arranging column ordering, adding a new column etc. This blog focuses on an issue that may trigger and impact the performance of your production system due to addition of a new column in an existing index.

The scenario that I am going to discuss in this blog is, “Assuming one day you are at a customer site investigating a performance issue or doing a proactive health check of a production system. During investigation, you come across two queries that are almost similar and use same Index. The only difference between the two queries is an additional Filter Predicate.” The two queries may look like :

select * from emp where sal=:b2;
select * from emp where sal=:b2 and deptno=:b1;

Both the queries use an Index on SAL column, which is a Single Column Index. We all know that the logical I/O’s of a query can be reduced if a query can make best use of an Index and scans the table, only when required. Which means, a composite index on (SAL, DEPTNO) would be efficient and should reduce the Logical I/O’s for the second query, without impacting the first query. Therefore, instead of creating a new composite index, the suggestion is put forward to modify an existing index on SAL and add DEPTNO as a second column. This will ensure that both the queries use the same index. After the alteration of this Index on production, won’t you be stunned if the customer comes back and says that the performance is still not OK, and infact, is worst than it was a day before ? This is because, while the alteration improved the performance of the second query, it impacted the performance of the first with the change in optimizer plan as it calculates the cost of an Altered Index to be costlier than Full Table Scan. Let us walk through a test case to understand the issue and the rationale behind optimizer estimation.

TEST CASE

drop table vivek_test;

exec dbms_random.seed(0);

create table vivek_test as
with test as
(select * from all_objects where rownum between 1 and 10000)
select	trunc((rownum-1)/1000) object_id,
	round(dbms_random.value(1,100),0) bad_clustered,
	a.object_name,
	a.temporary,
	a.created
from	test a,
	test b
where  rownum between 1 and 100000; 

create index vivek_test_idx on vivek_test(object_id);

exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',cascade=>true);

Below are the statistics for the table and its index (in my test database) :

select owner, table_name, num_rows, blocks from dba_tables where table_name='VIVEK_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ------------------------------ ---------- ----------
VIVEK                          VIVEK_TEST                          99889        420

select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
from	dba_indexes
where	table_name='VIVEK_TEST';

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_IDX                     100000          1         195           100               420

Now, I will run two queries (assume these to be application queries) against the table and both of these queries use the Index created on Object_ID. The queries and their Runtime Execution Plans are pasted below :

variable b1 number;
variable b2 number;
exec :b1:=0;
exec :b2:=57;
select /*+ QUERY1 */ * from vivek_test where object_id=:b1;

SQL_ID  99ss8hxuax3q2, child number 0
-------------------------------------
select /*+ QUERY1 */ * from vivek_test where object_id=:b1

Plan hash value: 3696163677

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |     7 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |   981 | 24525 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | VIVEK_TEST_IDX |   990 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=:B1)

select /*+ QUERY2 */ * from vivek_test where object_id=:b1 and bad_clustered=:b2;

Plan hash value: 3696163677

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    10 |   250 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | VIVEK_TEST_IDX |   990 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - filter("BAD_CLUSTERED"=:B2)
   2 - access("OBJECT_ID"=:B1)

Based on the queries executed against this table, it is decided to drop the exisiting index on OBJECT_ID and recreate it on OBJECT_ID, BAD_CLUSTERED. This is to optimize the second query (eliminate unwanted logical I/O’s) and with an assumption that this change will not impact the first query, with QUERY1 hint.

drop index vivek_test_idx;
create index vivek_test_idx on vivek_test(object_id, bad_clustered);
exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',cascade=>true);
SQL> select owner, table_name, num_rows, blocks from dba_tables where table_name='VIVEK_TEST';

OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ------------------------------ ---------- ----------
VIVEK                          VIVEK_TEST                         102477        420

Elapsed: 00:00:00.11
SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
  2  from dba_indexes 
  3  where table_name='VIVEK_TEST';

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_IDX                     100000          1         237          9998             41509

On a production system, these changes are normally done during the downtime and the system is released for the users. The impact is usually visible when the users start executing these queries concurrently. Let us re-execute the queries with the change in the index implemented. We will execute the second query first (with QUERY2 hint) and then the first query and compare the runtime execution plan.

variable b1 number;
variable b2 number;
exec :b1:=0;
exec :b2:=57;

select /*+ QUERY2 */ * from vivek_test where object_id=:b1 and bad_clustered=:b2;

Plan hash value: 3696163677

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    10 |   250 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | VIVEK_TEST_IDX |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=:B1 AND "BAD_CLUSTERED"=:B2)

select /*+ QUERY1 */ * from vivek_test where object_id=:b1;

Plan hash value: 3275957777

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |    97 (100)|          |
|*  1 |  TABLE ACCESS FULL| VIVEK_TEST |  1015 | 25375 |    97   (4)| 00:00:02 |
--------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=:B1)

While the cost of the second query, and the logical I/O’s as well, has gone down, the change has impacted the first query, that was working absolutely fine before the change. This can lead to a greater impact when these are run concurrently. To check for the impact, I simulated concurrency by way of spawning 20 sessions, each running the impacted query. With these 20 concurrent sessions, my 2 CPU laptop choked up with 100% utilization and 95% waits on CBC Latches. The code to simulate the concurrency is pasted below with an explanation :

  1. Create a Staging table and populate the object id’s in random order to be queried.
  2. The staging table holds 20000 object id’s with 10 parallel streams (PSTREAMS).
  3. The procedure will be scheduled from dbms_job, with pstreams as an Input parameter (10 Streams).
drop table staging;

create table staging as
select object_id, mod(rownum,10) pstreams  
from vivek_test where rownum between 1 and 20000
order by dbms_random.random;

create or replace procedure process_job(ps in number) as
l_cnt	number;
begin
for i in (select object_id from staging where pstreams=ps)
loop
select count(distinct temporary) into l_cnt from vivek_test
where	object_id=i.object_id;
end loop;
end;
/


variable jobno number;
variable hrs number;
variable mns number;
exec :hrs:=14;
exec :mns:=35;

begin
for i in 1..10
loop
BEGIN
     DBMS_JOB.SUBMIT(:jobno,'process_job('||i||');', 
         trunc(sysdate)+:hrs/24+:mns/(24*60));
     COMMIT;
END;
end loop;
end;
/

What could have triggered the change in plan ?

Before getting into the cause of this change, let us execute the query with an Index Hint. This will help us investigate the issue.

select /*+ INDEX(VIVEK_TEST,VIVEK_TEST_IDX) */ * from vivek_test where object_id=:b1;

Plan hash value: 3696163677

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |   420 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |  1015 | 25375 |   420   (0)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | VIVEK_TEST_IDX |  1015 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=:B1)

With an Index Hint, the cost of step TABLE ACCESS BY INDEX ROWID step is 420 as against 97 for the unhinted query (TABLE SCAN FULL). Optimizer, while generating an optimal execution plan, compares cost of each available access path and selects the one with least cost. In this case, the cost of a TABLE SCAN FULL was assumed to be cheaper than the cost of an INDEX SCAN. The Index can be considered costlier due to two reasons : by way of Cost and / or, due to huge number of Logical I/O’s. TABLE ACCESS BY INDEX ROWID is always followed by INDEX RANGE SCAN, and as can be seen from the plan, the cost of an INDEX SCAN is mere 4, while the cost of TABLE ACCESS step is 420. While the former is calculated from BLEVEL and LEAF_BLOCKS, the later is calculated based on CLUSTERING_FACTOR (CF).

Compare the CF of the Index, before and after change. Before the change, CF was 420 (near to the num_blocks of the table), after the change it changed drastically to 41509. This was due to the bad clustering of the additional column bad_clustered and this change was enough to blow up the cost. The cost calculation before and after the change is as under :

BEFORE THE CHANGE
Index Cost = BLEVEL + CEIL(LEAF_BLOCKS * SELECTIVITY)
Table Scan = CEIL(CF * SELECTIVITY)
Total Cost of TABLE ACCESS BY INDEX ROWID = Index Cost + Table Scan

While BLEVEL is considered for Index Cost Calculation, optimizer ignores this if the BLEVEL of an Index is 1 and all the columns of an Index are used as an EQUALITY Predicate in the query. Therefore, in our case too, since the columns are used as an Equality predicate and the blevel is 1, this value will be ignored.

SQL> select ceil(195*1/100), ceil(420*1/100) from dual;

CEIL(195*1/100) CEIL(420*1/100)
--------------- ---------------
              2               5

The cost of the first query, before the change is 7. Now, after the change :

SQL> select 1+ceil(237*1/100), ceil(41509*1/100) from dual;

1+CEIL(237*1/100) CEIL(41509*1/100)
----------------- -----------------
                4               416

With Index Hint, the cost is 420 and this is costlier than 97. There are two solutions for this issue. These are :

  1. Chnage the value for OPTIMIZER_INDEX_COST_ADJ (OICA) and set this globally.
  2. Fix the Query with an Index Hint.

The first change (i.e.OICA), would require application testing as setting this globally can impact other queries. The second change was a feasible solution and could be applied without impacting other queries. Let us visit the formula to calculate the value of OICA.

OPTIMIZER_INDEX_COST_ADJ

This is calculated as COST OF A FTS divided by TABLE ACCESS BY INDEX ROWID. In our case, the cost of FTS is 97 and Table Access by Index Rowid is 420. Therefore 97/420*100 is 23.09. This means, if the value of OICA is set to less than 23, the query should use an Index, without modification.

SQL> alter session set optimizer_index_cost_adj=22;

Session altered.
select /*+ OICA_22 */ * from vivek_test where object_id=:b1

Plan hash value: 3696163677

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |    92 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |  1015 | 25375 |    92   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | VIVEK_TEST_IDX |  1015 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=:B1)

As mentioned earlier, a better option would be to hint the query. Once the query is modified with an Index Hint, I spawned the Concurrency jobs and could see that gain, with less than 5% waits on CBC latches, as against, more than 95% and 100% CPU Utilization.

How do we predict the impact of this change before implementing it ?

Before making these changes, the impact can be predicated. This prediction should help estimate the cost change, before and after. Following query can be modified and run (for a bigger table, should be run in parallel during less load period). The only change in the query will be the ordering of the columns in an Index. For this, I will recreate the Index on a Single Column and then execute the query to check for the Change in the CF after adding another column.

SQL> drop index vivek_test_idx;

Index dropped.

Elapsed: 00:00:00.07
SQL> create index vivek_test_idx on vivek_test(object_id);

SQL> select  sum(block_change) from (
  2  select  block_fno, object_id, bad_clustered, prev_bfno,        
  3   (case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from (
  4  select  block_fno, object_id, bad_clustered, lag(block_fno) over (order by object_id) prev_bfno from (
  5  select    dbms_rowid.rowid_block_number(rowid)||'.'||
  6    dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'VIVEK','VIVEK_TEST') block_fno, 
  7    object_id, bad_clustered
  8  from  VIVEK_TEST
  9  order by object_id, bad_clustered, block_fno)));

SUM(BLOCK_CHANGE)
-----------------
            41509

The output of this query should approximately match the CF calculated by the Optimizer and should be enough to proactively arrive at the impact due to the change before making the change.

ppi.sql

First K Rows Optimization : Query Performance issue with ROWNUM predicate

Post 10g Upgrade, have you ever come across of a Performance Issue of a Query with ROWNUM predicate ? Last few weeks, I was busy with two back to back issues, reported by two different customers, of a Query Performance Issue due to ROWNUM predicates. Since this was reported by two different customers, the problem reported were different, but ended up as what seemed to be a First K Rows Optimization Issue. The issue reported were :

  1. Performance of a Query, on UAT, degraded after creating a new Index. This new Index is created as a part of new deployment and is impacting a query already deployed sucessfully on Production.
  2. Performance of a Query, on production, degraded after upgrading to 10.2.0.3 from 9.2.0.8. Further, upgrading to 10.2.0.4 has also not helped as yet and it continues to consume most of the resources.

Prior to Oracle 9i, the only two cost-based optimizer modes available were either ALL_ROWS and FIRST_ROWS. The former was either explicitly specified, by way of initialization parameter or hint, or was triggered when the following two conditions were met: optimizer_mode=CHOOSE and statistics on any of the tables in the query were gathered. FIRST_ROWS optimization was triggered, either by way of parameter setting or Hint.

The Optimization Rules and Calculations of “First K Rows Optimization” is analogous to First_Rows_n, but does not require any specific parameter settings or Hints and is automatically invoked when some of the conditions are met. One such condition is a ROWNUM predicate. If a query block contains a ROWNUM predicate, then First K Rows optimization will be automatically invoked, where the value of K is set to the value specified to a ROWNUM predicate. For example, if a query block contains a predicate ROWNUM=1 or ROWNUM=10, then the value of K, for First K Rows Optimization, will be evaluated to 1 or 10, as a result, First_rows_n optimization will be triggered.

First K Rows Optimization working is similar to First_Rows_n. With this, the optimizer first estimates the number of rows that will be returned by completely analyzing the first Join Order. Once this is done, and the estimated rows to be processed is computed, the entire optimization is redone to find the execution plan that minimizes the resource consumption to process the query. It is the first step of calculatng estimated number of rows that drives whether the final execution plan generated will be optimal or sub-optimal.

ISSUE 1

In this case, the query has been deployed sucessfully on production and is running perfectly fine. On the UAT, a New Index was created for deployment of new module. While the creation of this new index optimized the to-be-deployed module, it impacted another module and this concern was raised by the customer. In this case, the better option was to drop the new index and re-optimize the to-be-deployed module, in such a way, that this new Index is not required.

Query and Analysis

SELECT 'X'   FROM NBFC_PMNT_DTL
WHERE	TXNADVICEID IN
	(SELECT (TXNADVICEID + 0 ) FROM NBFC_TXN_ADVICE_DTL
	WHERE	CASEID = :b1
	AND	STATUS = 'A'
	AND	TXNTYPE IN ( 'A','P'  )
	AND	CHARGEID IN
		(SELECT CHARGEID   FROM NBFC_CHARGES_M  WHERE CHARGECODEID = 2 ))
AND	STATUS IS NULL
AND	ROWNUM = 1

FROM UAT
========
-------------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                              | Name                    | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
-------------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                       |                         |       |       |     4 |           |       |       |
| 1   |  COUNT STOPKEY                         |                         |       |       |       |           |       |       |
| 2   |   FILTER                               |                         |       |       |       |           |       |       |
| 3   |    PARTITION RANGE ALL                 |                         |     1 |     9 |     2 |  00:00:01 | 1     | 21    |
| 4   |     TABLE ACCESS FULL                  | NBFC_PMNT_DTL           |     1 |     9 |     2 |  00:00:01 | 1     | 21    |
| 5   |    NESTED LOOPS                        |                         |     1 |    32 |     2 |  00:00:01 |       |       |
| 6   |     PARTITION RANGE SINGLE             |                         |     1 |    21 |     1 |  00:00:01 | KEY   | KEY   |
| 7   |      TABLE ACCESS BY LOCAL INDEX ROWID | NBFC_TXN_ADVICE_DTL     |     1 |    21 |     1 |  00:00:01 | KEY   | KEY   |
| 8   |       INDEX RANGE SCAN                 | NBFC_TXN_ADVICE_DTL_IDX5|     1 |       |     1 |  00:00:01 | KEY   | KEY   |
| 9   |     TABLE ACCESS BY INDEX ROWID        | NBFC_CHARGES_M          |     1 |    11 |     1 |  00:00:01 |       |       |
| 10  |      INDEX UNIQUE SCAN                 | NBFC_CHARGES_PK         |     1 |       |     1 |  00:00:01 |       |       |
-------------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
1 - filter(ROWNUM=1)
2 - filter( IS NOT NULL)
4 - filter("STATUS" IS NULL)
7 - filter(("TXNTYPE"='A' OR "TXNTYPE"='P'))
8 - access("STATUS"='A' AND "CASEID"=:B1)
8 - filter("TXNADVICEID"+0=:B1)
9 - filter("CHARGECODEID"=2)
10 - access("CHARGEID"="CHARGEID")

FROM PRODUCTION
===============
--------------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                               | Name                    | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
--------------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                        |                         |       |       |     7 |           |       |       |
| 1   |  COUNT STOPKEY                          |                         |       |       |       |           |       |       |
| 2   |   TABLE ACCESS BY LOCAL INDEX ROWID     | NBFC_PMNT_DTL           |     1 |     9 |     1 |  00:00:01 |       |       |
| 3   |    NESTED LOOPS                         |                         |     1 |    22 |     7 |  00:00:01 |       |       |
| 4   |     VIEW                                | VW_NSO_1                |     1 |    13 |     4 |  00:00:01 |       |       |
| 5   |      HASH UNIQUE                        |                         |     1 |    31 |       |           |       |       |
| 6   |       TABLE ACCESS BY LOCAL INDEX ROWID | NBFC_TXN_ADVICE_DTL     |     1 |    21 |     3 |  00:00:01 |       |       |
| 7   |        NESTED LOOPS                     |                         |     1 |    31 |     4 |  00:00:01 |       |       |
| 8   |         TABLE ACCESS BY INDEX ROWID     | NBFC_CHARGES_M          |     1 |    10 |     1 |  00:00:01 |       |       |
| 9   |          INDEX RANGE SCAN               | NBFC_CHARGES_M_IDX1     |     1 |       |     1 |  00:00:01 |       |       |
| 10  |         PARTITION RANGE SINGLE          |                         |    39 |       |     1 |  00:00:01 | KEY   | KEY   |
| 11  |          INDEX RANGE SCAN               | NBFC_TXN_ADVICE_DTL_IDX3|    39 |       |     1 |  00:00:01 | KEY   | KEY   |
| 12  |     PARTITION RANGE ITERATOR            |                         |     1 |       |     1 |  00:00:01 | KEY   | KEY   |
| 13  |      INDEX RANGE SCAN                   | NBFC_PMNT_DTL_IDX1      |     1 |       |     1 |  00:00:01 | KEY   | KEY   |
--------------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
1 - filter(ROWNUM=1)
2 - filter("STATUS" IS NULL)
6 - filter(("STATUS"='A' AND INTERNAL_FUNCTION("TXNTYPE")))
9 - access("CHARGECODEID"=2)
11 - access("CASEID"=:B1 AND "CHARGEID"="CHARGEID")
13 - access("TXNADVICEID"="$nso_col_1")

NBFC_TXN_ADVICE_DTL_IDX5 index, used on UAT, is a new Index. The query on production uses another index i.e.NBFC_TXN_ADVICE_DTL_IDX3. Though the statistics on production and uat are different, these were enough to get to the root cause of the issue. With ROWNUM=1 predicate, both the queries were optimized using First K Rows optimization, but the estimated number of rows, at the very first step played a major role in generating the final execution plan. On UAT, the New index is on STATUS,CASEID columns, while on production, the index IDX3 is only on CASEID. We all know that First_Row_n optimization means, fetch the Initial N rows as fast as possible. On UAT, since the Index is on two columns, based on the Distinct Keys (#DK), the optimizer estimates only 1 row (#Rows/#DK), while on Production, with IDX3, it estimates 39 Rows. As soon as the New Index (IDX5) on UAT was dropped, the estimated cardinality increased to 9 and this restored the optimized plan similar to production.

PRODUCTION Stats for NBFC_TXN_ADVICE_DTL
========================================
Table Stats::
  Table: NBFC_TXN_ADVICE_DTL  Alias: NBFC_TXN_ADVICE_DTL  (Using composite stats)
    #Rows: 893378705  #Blks:  985102  AvgRowLen:  162.00
Index Stats::
  Index: NBFC_TXN_ADVICE_DTL_IDX3  Col#: 28 3
    USING COMPOSITE STATS
    LVLS: 3  #LB: 3341430  #DK: 23223154  LB/K: 1.00  DB/K: 22.00  CLUF: 531943755.00

UAT Stats for NBFC_TXN_ADVICE_DTL
==================================
Table Stats::
  Table: NBFC_TXN_ADVICE_DTL  Alias: NBFC_TXN_ADVICE_DTL  (Using composite stats)
    #Rows: 20847998  #Blks:  8756  AvgRowLen:  164.00
Index Stats::
  Index: NBFC_TXN_ADVICE_DTL_IDX3  Col#: 28 3
    USING COMPOSITE STATS
    LVLS: 2  #LB: 52180  #DK: 2236889  LB/K: 1.00  DB/K: 5.00  CLUF: 12030805.00
  Index: NBFC_TXN_ADVICE_DTL_IDX5  Col#: 17 28 27 1
    USING COMPOSITE STATS
    LVLS: 2  #LB: 78305  #DK: 16932120  LB/K: 1.00  DB/K: 1.00  CLUF: 11248060.00

ISSUE 2

Fortunately, I could spend more time on this issue and the analysis in this case, I feel, should be of interest to you. This was an Apps seeded query and the performance of this query degraded post 10g Upgrade (10.2.0.3). The issue again was due to ROWNUM=1 predicate, and I could confirm this by comparing the response time and consistent gets of the query after and before removing ROWNUM=1 predicate and the difference was huge. This query is a part of a critical transactions, that use to take 4-5 seconds in 9208, but takes 20-30 minutes after the upgrade. On 10.2.0.3, the query performance improves after setting Optimizer_features_enable (OFE) to 9.2.0.8 or 10.1.0, but this would not be a feasible solution as it required testing of the entire application. The Query and the plan with OFE as default and set to 9.2.0.8 is pasted below :

SQL_ID        SQL_TEXT                                                     BUFFER_GETS EXECUTIONS
------------- ------------------------------------------------------------ ----------- ----------
a15w9fc43c6gr SELECT UOM.UOM_CODE FROM MTL_UNITS_OF_MEASURE_VL UOM WHERE U  2352410716         55
              OM.UNIT_OF_MEASURE = ( SELECT PL.UNIT_MEAS_LOOKUP_CODE FROM
              PO_LINES_ALL PL WHERE (PL.PO_HEADER_ID,PL.PO_LINE_ID) = ( SE
              LECT PD.PO_HEADER_ID, PD.PO_LINE_ID FROM PO_DISTRIBUTIONS_AL
              L PD WHERE PD.PO_DISTRIBUTION_ID IN ( SELECT DISTINCT RT.PO_
              DISTRIBUTION_ID FROM RCV_TRANSACTIONS RT WHERE RT.WIP_ENTITY
              _ID = :B4 AND NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:
              B3 , -1) AND RT.ORGANIZATION_ID = :B2 AND RT.WIP_OPERATION_S
              EQ_NUM = :B1 AND RT.PO_DISTRIBUTION_ID IS NOT NULL ) AND ROW
              NUM = 1 ) )

## Run time BAD Plan
--------------------
Plan hash value: 455173600
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |       |       |     8 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID      | MTL_UNITS_OF_MEASURE_TL    |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN               | MTL_UNITS_OF_MEASURE_TL_U1 |     1 |       |     1   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID    | PO_LINES_ALL               |     1 |    16 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN             | PO_LINES_U1                |     1 |       |     1   (0)| 00:00:01 |
|*  5 |      COUNT STOPKEY                |                            |       |       |            |          |
|   6 |       NESTED LOOPS SEMI           |                            |     2 |    80 |     3   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL          | PO_DISTRIBUTIONS_ALL       |  1275K|    19M|     2   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS BY INDEX ROWID| RCV_TRANSACTIONS           |     1 |    24 |     1   (0)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN          | RCV_TRANSACTIONS_N10       |     1 |       |     1   (0)| 00:00:01 |
|* 10 |     COUNT STOPKEY                 |                            |       |       |            |          |
|  11 |      NESTED LOOPS SEMI            |                            |     2 |    80 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL           | PO_DISTRIBUTIONS_ALL       |  1275K|    19M|     2   (0)| 00:00:01 |
|* 13 |       TABLE ACCESS BY INDEX ROWID | RCV_TRANSACTIONS           |     1 |    24 |     1   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN           | RCV_TRANSACTIONS_N10       |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   2 - access("UNIT_OF_MEASURE"= AND "LANGUAGE"=USERENV('LANG'))
   3 - filter("PL"."PO_HEADER_ID"=)
   4 - access("PL"."PO_LINE_ID"=)
   5 - filter(ROWNUM=1)
   8 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND
              NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1)) AND
              "PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID"))
   9 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
       filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)
  10 - filter(ROWNUM=1)
  13 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND
              NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1)) AND
              "PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID"))
  14 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
       filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)

## With OFE set to 9208
------------------------
------------------------------------------------------------------------+-----------------------------------+
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                    |                           |       |       |     9 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID        | MTL_UNITS_OF_MEASURE_TL   |     1 |    16 |     2 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN                 | MTL_UNITS_OF_MEASURE_TL_U1|     1 |       |     2 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID      | PO_LINES_ALL              |     1 |    16 |     2 |  00:00:01 |
| 4   |     INDEX UNIQUE SCAN               | PO_LINES_U1               |     1 |       |     2 |  00:00:01 |
| 5   |      COUNT STOPKEY                  |                           |       |       |       |           |
| 6   |       NESTED LOOPS                  |                           |     1 |    40 |     4 |  00:00:01 |
| 7   |        SORT UNIQUE                  |                           |     1 |    24 |     2 |  00:00:01 |
| 8   |         TABLE ACCESS BY INDEX ROWID | RCV_TRANSACTIONS          |     1 |    24 |     2 |  00:00:01 |
| 9   |          INDEX RANGE SCAN           | RCV_TRANSACTIONS_N10      |     1 |       |     2 |  00:00:01 |
| 10  |        TABLE ACCESS BY INDEX ROWID  | PO_DISTRIBUTIONS_ALL      |     1 |    16 |     2 |  00:00:01 |
| 11  |         INDEX UNIQUE SCAN           | PO_DISTRIBUTIONS_U1       |     1 |       |     2 |  00:00:01 |
| 12  |     COUNT STOPKEY                   |                           |       |       |       |           |
| 13  |      NESTED LOOPS                   |                           |     1 |    40 |     4 |  00:00:01 |
| 14  |       SORT UNIQUE                   |                           |     1 |    24 |     2 |  00:00:01 |
| 15  |        TABLE ACCESS BY INDEX ROWID  | RCV_TRANSACTIONS          |     1 |    24 |     2 |  00:00:01 |
| 16  |         INDEX RANGE SCAN            | RCV_TRANSACTIONS_N10      |     1 |       |     2 |  00:00:01 |
| 17  |       TABLE ACCESS BY INDEX ROWID   | PO_DISTRIBUTIONS_ALL      |     1 |    16 |     2 |  00:00:01 |
| 18  |        INDEX UNIQUE SCAN            | PO_DISTRIBUTIONS_U1       |     1 |       |     2 |  00:00:01 |
------------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("UNIT_OF_MEASURE"= AND "LANGUAGE"=USERENV('LANG'))
3 - filter("PL"."PO_HEADER_ID"=)
4 - access("PL"."PO_LINE_ID"=)
5 - filter(ROWNUM=1)
8 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1))))
9 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
9 - filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)
11 - access("PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID")
12 - filter(ROWNUM=1)
15 - filter(("RT"."PO_DISTRIBUTION_ID" IS NOT NULL AND "RT"."ORGANIZATION_ID"=:B2 AND NVL("RT"."WIP_REPETITIVE_SCHEDULE_ID",(-1))=NVL(:B3,(-1))))
16 - access("RT"."WIP_ENTITY_ID"=:B4 AND "RT"."WIP_OPERATION_SEQ_NUM"=:B1)
16 - filter("RT"."WIP_OPERATION_SEQ_NUM"=:B1)
18 - access("PD"."PO_DISTRIBUTION_ID"="RT"."PO_DISTRIBUTION_ID")

The BAD plan does Full Table Scan of PO_DISTRIBUTIONS_ALL, while the Good Plan, does an Unique Index Scan of this table. The Difference in I/O is 32 Million Logical Reads as against 22 Logical Reads. For my analysis, as I know the issue was with ROWNUM predicate, I removed the outer query block and retained only the relevant portion. The final query, that I worked on is pasted below :

SELECT	PD.PO_HEADER_ID, PD.PO_LINE_ID
FROM	apps.PO_DISTRIBUTIONS_ALL PD
WHERE	PD.PO_DISTRIBUTION_ID IN
	(SELECT	 DISTINCT RT.PO_DISTRIBUTION_ID
	FROM	apps.RCV_TRANSACTIONS RT
	WHERE	RT.WIP_ENTITY_ID = :B4
	AND	NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:B3 , -1)
	AND	RT.ORGANIZATION_ID = :B2
	AND	RT.WIP_OPERATION_SEQ_NUM = :B1
	AND	RT.PO_DISTRIBUTION_ID IS NOT NULL )
AND	ROWNUM = 1 ;

First K Row Optimization is not invoked, if the query block does not contain ROWNUM predicate, or, if you provide ALL_ROWS hint to the query, or if you provide PARALLEL hint into the query. Therefore, to make sure that this is a First K Row Optimization Issue, I executed the queries without ROWNUM predicate, with ALL_ROWS hint and with PARALLEL hint, and all these ran perfectly fine. With this analysis, I was sure that this seems to be an Issue with First K Rows Optimization. Unfortunately, there is no way to disable first k row optimization for this particular query, because of rownum=1. 10053 trace of the query reveals that the computed cardinality of the inner query on RCV_TRANSACTIONS is 1. See the bold portion below :

SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#49): WIP_ENTITY_ID(NUMBER)
    AvgLen: 2.00 NDV: 100442 Nulls: 8235076 Density: 9.9560e-06 Min: 20 Max: 1901958
  Column (#51): WIP_REPETITIVE_SCHEDULE_ID(NUMBER)
    AvgLen: 22.00 NDV: 0 Nulls: 8996812 Density: 0.0000e+00 Min: 0 Max: 0
  Column (#46): ORGANIZATION_ID(NUMBER)
    AvgLen: 4.00 NDV: 72 Nulls: 0 Density: 0.013889 Min: 10 Max: 703
  Column (#52): WIP_OPERATION_SEQ_NUM(NUMBER)
    AvgLen: 2.00 NDV: 169 Nulls: 8235076 Density: 0.0059172 Min: 1 Max: 2055
  Table: RCV_TRANSACTIONS  Alias: RT
    Card: Original: 8996812  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00

The computed cardinality of 1, alongwith the ROWNUM=1 predicate in the outer query block seems to be an issue. To get back to the 9i plan, I had to fake the statistics (NUM_ROWS) of RCV_TRANSACTIONS table, to a value, such that the computed cardinality increases from 1 to 65. Once done, the query performance improved and the transaction response time was restored from 20-30 minutes to 4-5 seconds.

In my opinion, the generation of sub-optimal execution plan with First K Rows Optimization seems to be a BUG. Therefore, for this issue (Issue no.2), the customer has raised an SR and I am currently working closed with the SR Analysts to get a fix of this issue. To reproduce this issue inhouse, I prepared a test case and with default optimizer settings, I could reproduce it on my local database. The test case is pasted below. If you are not able to reproduce the issue, do send a comment and I shall take this up for further investigation.

drop table po_distributions_all ;
drop table RCV_TRANSACTIONS;

exec dbms_random.seed(0);

create table po_distributions_all as
select rownum po_distribution_id, round(dbms_random.value(1,100000),0) po_header_id,
round(dbms_random.value(100001,1000000),0)  po_line_id,
rpad(dbms_random.string('A',20),100,'*') po_name
from	all_objects
where rownum<=10000;

create unique index po_distributions_u1 on po_distributions_all(po_distribution_id);

drop table rcv_transactions;

create table rcv_transactions as
select round(dbms_random.value(1,10000),0) po_distribution_id, round(dbms_random.value(1000,10000000),0) wip_entity_id,
100000 wip_repetitive_schedule_id, 1 wip_line_id, mod(rownum,72) organization_id,
mod(rownum,169) wip_operation_seq_num
from all_objects
where rownum<=10000;

update rcv_transactions set wip_repetitive_schedule_id=null;

create index rcv_transactions_n10 on rcv_transactions(wip_entity_id, wip_line_id, wip_operation_seq_num);

exec dbms_stats.gather_table_stats(user,'rcv_transactions',cascade=>true,method_opt=>'for all columns size 1');

exec dbms_stats.set_table_stats(user,'po_distributions_all',numrows=>1394862,numblks=>42951);
exec dbms_stats.set_column_stats(user,'po_distributions_all',colname=>'po_distribution_id',DISTCNT=>1394862,density=>1/1394862,nullcnt=>0);

exec dbms_stats.set_table_stats(user,'rcv_transactions',numrows=>8996812,numblks=>452191);
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'wip_entity_id',DISTCNT=>100442,density=>1/100442,nullcnt=>8235076);
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'wip_line_id',DISTCNT=>1,density=>1,nullcnt=>round(8996812*95/100,0));
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'wip_operation_seq_num',DISTCNT=>169,density=>1/169,nullcnt=>8235076);
exec dbms_stats.set_column_stats(user,'rcv_transactions',colname=>'po_distribution_id',DISTCNT=>1057573,density=>1/1057573,nullcnt=>6792851);

variable b1 number;
variable b2 number;
variable b3 number;
variable b4 number;
exec :b1:=5;
exec :b2:=5;
exec :b3:=null;
exec :b4:=4449100;

SELECT /*+ VIVEK_ORIGINAL */ PD.PO_HEADER_ID, PD.PO_LINE_ID
FROM PO_DISTRIBUTIONS_ALL PD
WHERE PD.PO_DISTRIBUTION_ID IN
 ( SELECT DISTINCT RT.PO_DISTRIBUTION_ID
 FROM RCV_TRANSACTIONS RT
 WHERE RT.WIP_ENTITY_ID = :B4
 AND NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:B3 , -1)
 AND RT.ORGANIZATION_ID = :B2
 AND RT.WIP_OPERATION_SEQ_NUM = :B1
 AND RT.PO_DISTRIBUTION_ID IS NOT NULL )
AND ROWNUM = 1;

## Fake Statistics
exec dbms_stats.set_table_stats(user,'rcv_transactions',numrows=>1118996812,numblks=>452191);

SELECT /*+ VIVEK_FAKE */ PD.PO_HEADER_ID, PD.PO_LINE_ID
FROM PO_DISTRIBUTIONS_ALL PD
WHERE PD.PO_DISTRIBUTION_ID IN
 ( SELECT DISTINCT RT.PO_DISTRIBUTION_ID
 FROM RCV_TRANSACTIONS RT
 WHERE RT.WIP_ENTITY_ID = :B4
 AND NVL(RT.WIP_REPETITIVE_SCHEDULE_ID, -1) = NVL(:B3 , -1)
 AND RT.ORGANIZATION_ID = :B2
 AND RT.WIP_OPERATION_SEQ_NUM = :B1
 AND RT.PO_DISTRIBUTION_ID IS NOT NULL )
AND ROWNUM = 1;

 

 To Summarize, First K Rows Optimization, invoked automatically with ROWNUM predicate, can generate a sub-optimal execution plan, if the statistics of the inner query computes a cardinality of less number of rows or 1. In this case, you may need to either re-write a query or fake the statistics. There are few bugs reported with this optimization and, if relevant for your case, the fixes provided as a BUG fix can be safely applied. In the case above, the fix is still awaited and I will keep you posted on this.