Cost based Optimizer ! Inefficient input yields inefficient Output…

September 4, 2010 Leave a comment

Since the time my old blog went inaccessible, and still is, I have been receiving several mails on my previous blogs that helped some of my readers relate and resolve their performance issues. They wanted access to my previous blogs and I have been requesting them to let me know any particular blog that was of interest and I may publish them right here. There were many requests to publish blogs on Cost Based Optimizer and therefore, thought of publishing some of them. While these blogs may not be relevant for the current versions of Oracle, there are still some production systems that are still on Oracle 8i and 9i, and these blogs may be of help for those instances. Wherever any of the Cost Based Optimization blog that are relevant for certain versions of Oracle, I will specifically mention them for the benefits of the readers.

This blog is relevant for Oracle 8i and 9i (without System Statistics)

Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers. The calculation made by the Optimizer to come-up with an Execution Plan has been doubted by many of the Oracle users, especially, when (to name a few) :

  1. The Application Performance Degrades after Version Upgrade
  2. Plan of a Query change after gathering of fresh Statistics, and
  3. The Performance of a Query with higher cost is much better that the plan with a least cost

Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade and leave no room with rigorous testing of the application before production upgrade. It is fact no.2 and 3 that raises a doubt over Cost Based Optimization.

Cost Based Optimizer is a piece of code, wherein, you provide Inputs, by way of Optimizer Parameters, Statistics and it computes and generates an Output which is seen as an Execution Plan. It is not only the Optimizer Parameters and Statistics, which is passed as an Input. There is one more component, which is mostly overlooked, a QUERY. The initial input passed is the Query and based on the way it is written, optimizer does its cost calculation by taking into account the other two : Parameters and Statistics. In this blog, I will address the last two facts stated above, plus, a common misconception that Index Scan is better that Full Table Scan and finally, the issue with the critical first component passed as an input to CBO, A Query.

Recently, was working on a performance issue, related to a critical process. The overall performance of the system was OK, but one critical process started taking 18-20 hours, failing several times with ora-1555. This process, usually takes 1 hour 30 minutes. Therefore, all of a sudden, why this degradation ?, was the question raised by the customer.

The Database version is 8174. This is one of a critical process and a part of seeded application. It connects to a user, dedicated for this process, therefore, when this process is running, it is very easy to get the sid of the session executing this process. While monitoring the performance of this process, by querying v$session_wait for the sid, I could see one particular query doing exorbitant amount of Logical Reads. The text of the query and execution plan is as under :

explain plan for
select CS.CO_ID ,CS.SPCODE ,CS.SNCODE ,CS.TMCODE ,CS.CS_SEQNO ,CS.CS_STAT_CHNG ,
to_char(CS.TRIAL_END_DATE,'YYYYMMDDHH24MISS')
from       CR_SERVICES CS
where      (CS.CO_ID not  in (select CO_ID
                             from CO_ALL
                             where (CO_ID=CS.CO_ID and CUSTOMER_ID=DEALER_ID))
and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (SNCODE=CS.SNCODE and RATING_IND='Y')));

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 141575 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   NESTED LOOPS            |                    |     1K|   77K| 141575 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS BY INDEX R|CR_SERVICES         |     3M|  120M|   1913 |       |       |
|     INDEX RANGE SCAN      |I1_CR_SERVICES      |     3M|      |   1616 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

There are three tables in the query. The Driving Table, from the explain plan, MPV is a small table with 220 rows and 80 Blocks, while the other two are huge tables with 69 Million and 30 Million Rows. Usually, such situation can crop-up due to following reasons :

  1. Change in Optimizer Parameters
  2. Change in Statistics
  3. Creation of New Index

Initial Investigation revealed that the Statistics of CR_SERVICES table and indexes were gathered the previous night. Once this was confirmed, Cost Based Optimization was at the receiving end. This is quite natural as without any change in the application query, the plan changed for bad, due to CBO. Therefore, thorough investigation was required about the cause of this plan change. Seeing the query and other optimizer statistics, Full Table Scan on CR_SERVICES would have been a better choice and therefore, my investigation was around this. Let us revisit the relevant portion of the Execution Plan, the statistics and the Query.

## Table Statistics of CR_SERVICES

OWNER                          PAR   NUM_ROWS     BLOCKS 
------------------------------ --- ---------- ---------- 
SM                             NO    69875510    1107733 

## Column Stats of the Columns used on the Query

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY
------------------------------ ------------ ---------- ----------
CO_ID                               6863377          0 1.4570E-07
SNCODE                                  370          0 .002702703  @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
|   NESTED LOOPS            |                    |     1K|   77K| 141575 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS BY INDEX R|CR_SERVICES         |     3M|  120M|   1913 |       |       |
|     INDEX RANGE SCAN      |I1_CR_SERVICES      |     3M|      |   1616 |       |       |

The Query is using an Index on SNCODE column, which is a very low cardinality column. Therefore, this was my primary target and I worked on the Optimizer Calculation to get the possible cause of this Index Scan.

Optimizer Calculation


The Cost Calculation of an Index Scan and Table Scan via Index is

Index Cost = Blevel+ceil(leaf_blocks*ix_selectivity) 
Table Scan = ceil(clustering_factor*table_selectivity) 

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370) from dual;
Index Cost = 1616 ## This matches the execution plan
Table Scan = 109533
Final Cost = 1616+109533=111149

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370*1/370) from dual;
Index Cost = 1616
Table Scan = 297
Final Cost = 1616+297=1913 ## This Matches with the Cost calculated by the Optimizer

This additional 1/370, was due to the fact that SNCODE column is used twice in the query. One for Index Filter, therefore considered only once for an index cost, and twice for Table cost due to SNCODE=CS.SNCODE join. The final Nested Loop Cost is 141575 and this is calculated as 

NL Cost = Cost of an Outer Table + (Cost of Inner Table * Cardinality of Outer Table)
NL Cost = 13 + (1913*74)
NL Cost = 141575

Cost of Full Table Scan = Num Blocks / 6.59 (8k block size)
Cost of Full Table Scan = 1107733 / 6.59
Cost of Full Table Scan = 168093.02

Clearly, the cost of a Full Table Scan is higher than the Nested Loop Join and optimizer opted for the cheaper costed plan, which is, a Nested Loop Join and this requires an Index Scan of an Inner Table. When I checked the execution plan of this query with a FULL hint on CR_SERVICES, the FTS cost was 168200. I am yet to figure out the difference between my calculation and optimizer calculation, but for investigation purpose, I considered the calculation done by the optimizer. Based on this calculation, I came out with three possible solutions, these are :

  1. Modify the Query (Immediately Not possible, as it is a seeded query)
  2. Change Optimizer Statistics
  3. Change Optimizer Parameters

The first optimization was a better and safe approach, but was not possible immediately. The other two required some testing, as change in Optimizer Statistis can have negative impact on other queries using these objects and, change in optimizer parameters at db level can have an global impact on the overall performance. As mentioned earlier, this process connects to a user dedicated for this process, therefore, ON-LOGON trigger with the Optimizer Parameter change at session level was thought of as a better option without impacting overall application performance. The parameter, I could immediately think of was, OPTIMIZER_INDEX_COST_ADJ. This parameter was default i.e.100. For this query, to force Index Cost be Costlier than FTS, the calculation is OICA = (FTS Cost/NL Cost)*100 = (168200/141575)*100 = 118.80. Any value above this, would compute the NL cost be costlier than Full Table Cost and would opt for FTS and Hash Join. Initially, the Change was done at session level and the plan changed.

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 168219 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   HASH JOIN               |                    |     1K|   77K| 168219 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS FULL      |CR_SERVICES         |     3M|  120M| 168200 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

Finally, ON-LOGON trigger was created to set this parameter to 120, whenever, this user logged on to the database. The process, when re-executed, completed in 1 Hour 15 minutes and the desired performance was achieved.

Optimizer Calculation with OICA as 120


NL Table Scan Cost = (Current Cost of Inner Table Scan * Outer Table Card)*OICA/100
NL Table Scan Cost = (1913*74)*120/100
NL Table Scan Cost = (1913*74)*120/100
NL Table Scan Cost = 169874.4 > 168200 (FTS)

With this setting, the plan changed. Now for the reverse engineering. I wanted to check for any of the Index Statistics, say Clustering Factor, that would have calculated NL Join costlier than Full Table Scan. The Cost of NL Join with OICA is 169874.4. The cost of Table Scan via Index should be round(169874.4/74), which is 2296. Index Scan Cost is 1616, so the Table Scan cost component should be 2296-1616 is 680. Based on this, I calculated that if the Clustering Factor is changed to 93092000, then without any parameter setting, the query would go for a Full Table Scan. 

SQL> exec dbms_stats.SET_INDEX_STATS('SM','I1_CR_SERVICES',CLSTFCT=>93092000);

PL/SQL procedure successfully completed.

SQL> @index_stats
Enter value for o: SM
old   3: where table_owner='&O'
new   3: where table_owner='SM'
Enter value for t: CR_SERVICES
old   4: and   table_name='&T'
new   4: and   table_name='CR_SERVICES'

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
I1_CR_SERVICES                   70724121          3      596745      11207514          93092000 

12 rows selected.

explain plan for
select CS.CO_ID ,CS.SPCODE ,CS.SNCODE ,CS.TMCODE ,CS.CS_SEQNO ,CS.CS_STAT_CHNG ,
to_char(CS.TRIAL_END_DATE,'YYYYMMDDHH24MISS')
from       CR_SERVICES CS
where      (CS.CO_ID not  in (select CO_ID
                             from CO_ALL
                             where (CO_ID=CS.CO_ID and CUSTOMER_ID=DEALER_ID))
and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (SNCODE=CS.SNCODE and RATING_IND='Y')));

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 168219 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   HASH JOIN               |                    |     1K|   77K| 168219 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS FULL      |CR_SERVICES         |     3M|  120M| 168200 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

11 rows selected.

Back to the problem, does it look like to be an Optimizer Issue ? This table has a very high growth rate. While the num_blocks value of a table grows faster, the statistics of Leaf Blocks, BLevel and Clustering Factor does not change significantly. This is enough to blow up the cost of a Full Table Scan. If you see the query and the relevant portion of the query, pasted above, the join on SNCODE is really not required as it uses an IN clause. This unwanted join, forced the optimizer to consider the Density of SNCODE twice in Table Scan cost calculation.

## Relevant portion of the Query and Plan

and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (SNCODE=CS.SNCODE and RATING_IND='Y')));

## Optimizer Calculation (1/370 is considered twice to get Table Scan cost of 297, thus making it cheaper).
Index Cost = Blevel+ceil(leaf_blocks*ix_selectivity) 
Table Scan = ceil(clustering_factor*table_selectivity) 

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370*1/370) from dual;
Index Cost = 1616
Table Scan = 297
Final Cost = 1616+297=1913 

If this portion of the query is modified as :


and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (RATING_IND='Y')));

## Optimizer Calculation, with this change
Index Cost = Blevel+ceil(leaf_blocks*ix_selectivity) 
Table Scan = ceil(clustering_factor*table_selectivity) 

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370) from dual;
Index Cost = 1616
Table Scan = 109533
Final Cost = 1616+109533= 111149

## With this Cost, the cost of NL Join would have been

NL Cost = Cost of an Outer Table + (Cost of Inner Table * Cardinality of Outer Table)
NL Cost = 13 + (111149*74)
NL Cost = 8225039 > 168200 (FTS)

One unwanted Join Condition made Cost based Optimizer to come up with an in-efficient plan. Therefore, as mentioned earlier, my Initial and Crucial input to the optimizer is a Query and if these are not optimally written, Cost Based Optimizer is bound to compute an sub-optimal plan. The Query, modified by removing unwanted join and no parameter change or statistics change, gave the desired and better plan (see below ).

explain plan for
select CS.CO_ID ,CS.SPCODE ,CS.SNCODE ,CS.TMCODE ,CS.CS_SEQNO ,CS.CS_STAT_CHNG ,
to_char(CS.TRIAL_END_DATE,'YYYYMMDDHH24MISS')
from       CR_SERVICES CS  
where      (CS.CO_ID not  in (select CO_ID
                               from CO_ALL
                               where (CO_ID=CS.CO_ID and CUSTOMER_ID=DEALER_ID))
and        CS.SNCODE in (select distinct SNCODE  from MPV
                       where (RATING_IND='Y')));

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 168219 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   HASH JOIN               |                    |     1K|   77K| 168219 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS FULL      |CR_SERVICES         |     3M|  120M| 168200 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

Many developers are of the Opinion that an Execution Plan with cheaper cost is better than a Plan with higher cost, and Index Scans are better than Full Table Scans. This blog demonstrates that these are just misconceptions and it is the application design and knowledge that should be taken into account while writing an effecient code.

To sum up, it is just not the Optimizer Statistics and Parameter that control the Cost Based Optimizer, Queries play an important role too. A well written code should never bother or doubt about Cost Based Optimization.

Filed under Optimizer, Performance Tagged with , , , , ,

Optimizer Hint that Ignores Histogram and Column Stats

July 28, 2010 1 Comment

There was an Internal Forum and the question raised was “is there any optimizer hint that causes the optimizer to ignore histogram information ?” The question seemed to be interesting and thought of posting my reply to the question.

CARDINALITY hint is the one that directs the Optimizer to take the value of the Number of Rows to be processed by a Rowset or a Table. This hint is particularly usefull in Versions prior to 10g due to Optimizer Calculation mismatch with dependant columns. A quick demonstration below :

  1. I created a table vivek_test with 10000 rows
  2. Gathered Stats on this table, with Histogram on TEMPORARY column
  3. Then I executed the first query without any Hint and the Optimizer accurately calculated 53 rows for TEMPORARY=’Y’
  4. Then the second query with CARDINALITY hint and the Optimizer ignored histogram and column stats
create table vivek_test as
select * from all_objects where rownum<=10000;

exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',null,null,null,'FOR ALL COLUMNS SIZE 1 FOR COLUMNS TEMPORARY SIZE 10');
SQL> select temporary, count(*) from vivek_test group by temporary;

T   COUNT(*)
- ----------
Y         53
N       9947

select /*+ NO_HINT */ owner, object_name
from vivek_test
where temporary='Y';

column sql_text for a50 wrap

select sql_id, sql_text, buffer_gets, executions, version_count
from	v$sqlarea
where	sql_text like 'select /*+ NO_HINT%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
6xhaxy9xb82zn select /*+ NO_HINT */ owner, object_name from vive         132          1             1
              k_test where temporary='Y'


Elapsed: 00:00:00.23

select plan_table_output from table(dbms_xplan.display_cursor('6xhaxy9xb82zn'));

SQL_ID  6xhaxy9xb82zn, child number 0
-------------------------------------
select /*+ NO_HINT */ owner, object_name from vivek_test where
temporary='Y'

Plan hash value: 3275957777

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |    85 (100)|          |
|*  1 |  TABLE ACCESS FULL| VIVEK_TEST |    53 |  1431 |    85   (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"='Y')


19 rows selected.


select /*+ CARDINALITY(vivek_test,300) */ owner, object_name
from vivek_test
where temporary='Y';



select sql_id, sql_text, buffer_gets, executions, version_count
from	v$sqlarea
where	sql_text like 'select /*+ CARDINALITY(vivek_test,300%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
5xw7ckfhkz07p select /*+ CARDINALITY(vivek_test,300) */ owner, o         132          1             1
              bject_name from vivek_test where temporary='Y'


Elapsed: 00:00:00.02

select plan_table_output from table(dbms_xplan.display_cursor('5xw7ckfhkz07p'));

SQL_ID  5xw7ckfhkz07p, child number 0
-------------------------------------
select /*+ CARDINALITY(vivek_test,300) */ owner, object_name from
vivek_test where temporary='Y'

Plan hash value: 3275957777

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |    85 (100)|          |
|*  1 |  TABLE ACCESS FULL| VIVEK_TEST |   300 |  8100 |    85   (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"='Y')


19 rows selected.

Filed under Optimizer Tagged with , ,

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

November 17, 2009 4 Comments

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

Filed under Optimizer, Performance Tagged with , , ,

First K Rows Optimization : Query Performance issue with ROWNUM predicate

October 8, 2009 2 Comments

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.

Filed under Optimizer, Performance Tagged with , , ,