Systems Engineering and RDBMS

_SORT_ELIMINATION_COST_RATIO parameter

Posted by decipherinfosys on October 1, 2008

This undocumented parameter recently created a havoc at one of our client sites.  This parameter represents the cost ratio for sort elimination process under the first_rows and first_rows_n optimization modes.  The plan generated by the Oracle optimizer was not optimal and after working for sometime with Oracle support on this issue, we had to put in this undocumented parameter in and set it’s value to 4 to have the right plan generated.

The table had close to 7 million records – the statistics were up to date using dbms_stats.  There were two indexes on the table – one on the Primary key column and another one on the status column.  Here is the data distribution for the Status (STAT_CODE) column:

STAT_CODE COUNT(*)
---------- ----------
0          10858
10         10990
15         2
20         4108
30         6948
40         439
50         6131
90         6719601
99         77935

The value that was used for STAT_CODE was 20 and the code used a constant value to not run into the bind variable peeking issues.

Index 1: CARTON_NBR (Primary Key)

Index 2: STAT_CODE

The Query:

SELECT CARTON_HDR.CARTON_NBR AS A1,
CARTON_HDR.PLT_ID AS A29,
CARTON_HDR.PKT_CTRL_NBR AS A2,
CARTON_HDR.TOTAL_QTY AS A21,
CARTON_HDR.STAT_CODE AS A6,
CARTON_HDR.CURR_LOCN_ID AS A12,
CARTON_HDR.WAVE_NBR AS A4,
CARTON_HDR.STAGE_INDIC AS A7,
CARTON_HDR.LOAD_NBR AS A119,
CARTON_HDR.SHPMT_NBR AS A35,
CARTON_HDR.MISC_CARTON AS A146,
CARTON_HDR.CHUTE_ID AS A132
FROM CARTON_HDR
WHERE CARTON_HDR.STAT_CODE = 20 AND CARTON_HDR.WHSE = ‘IY’
ORDER BY CARTON_HDR.CARTON_NBR ASC

Plan used:

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4108 | 449K| 436K
(0)| |* 1 | TABLE ACCESS BY INDEX ROWID| CARTON_HDR | 4108 | 449K| 436K
(0)| | 2 | INDEX FULL SCAN | PK_CARTON_HDR | 6837K| | 26649
(0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CARTON_HDR"."STAT_CODE"=20 AND "CARTON_HDR"."WHSE"='IY')

Do note that WHSE has only 2 distinct values in this table.  Also, the filter should apply before the sort operation happens and given the granularity of the STAT_CODE data distribution and the fact that this query always queries based on the data value of 20, it should have used that index rather than doing a FULL scan of the PK index.

Also we used the Event 10053 to see why Oracle was using such high cost index.  When using the Index hint…query uses the right plan.  When using ALL_ROWS hint…query usess the right plan.  However, in this vendor application, it is required to use the first_rows_n optimizer mode since this is an OLTP application.

We could have also created/modified the second index to include CARTON_NBR as the second column to resolve this issue.  When Oracle parses the SQL statement , and it uses an order by clause, it has to calculate the cost of the sort.  And if the cost of the sort is high, then CBO will decide to use an index that has data already sorted and ready to send the output.  If _SORT_ELIMINATION_COST_RATIO = 0 (the default value), this simply means that no matter how more expensive the index you used to avoid sort (order by) it will be used.

To change this behavior, we can set this parameter to any value, (suggested values: 4-5), so _SORT_ELIMINATION_COST_RATIO = 4 means that the index will be used to avoid sorts if the cost is 4 times or less than the cheapest cost to access the table but if it is more than that Oracle will use the other index and will do the sorting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: