Indexing the 'Order By' Clause

I got a call last week from a client running a Siebel application, he complained of slow performance on a particular screen, following a new release.

Since the objects statistics were not stale and the indexes had been rebuilt a few days ago, I extracted the execution plan of the screen’s query.

As you can see, the explain plan showed a near optimal access:

SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID, T1.ATTRIB_02, T1.ATTRIB_03,T1.X_OFFER_HISTORY
FROM SBL.S_ASSET_XM T1
WHERE (T1.TYPE = ‘Offer History’)
AND (T1.PAR_ROW_ID= :1)
ORDER BY T1.PAR_ROW_ID DESC, T1.CREATED DESC;

Id  Operation                          Name           Rows        Bytes     Cost
0   SELECT STATEMENT                                  1              74        4
1   SORT ORDER BY                                     1              74        4
2   TABLE ACCESS BY INDEX ROWID     S_ASSET_XM        1              74        1
3   INDEX RANGE SCAN                S_ASSET_XM_U1     1                        3

 

At this point, I extracted a statspack from the last 7 hour workload. The query showed up as the top query in the ‘Buffer gets’ and ‘Physical reads’ sections.

                                                   CPU      Elapsd
 Physical Reads   Executions Reads per Exec %Total Time (s) Time (s)

     28,679,016        1,050       27,313.3   64.7   3267.2  4488.59
 Buffer Gets   
     37,389,651                    35,609.2   14.3

 

Notice that the buffer hit ratio is not quite up to par as well as the execution timings.

 

Since the result must be sorted in descending order, let’s first check the selectivity of the fields from the ORDER BY clause. The current index has a 13% selectivity, the 2 fields in the ORDER BY have a 21% selectivity, not enough to make a strong difference, however since the result is required in descending order, let’s create an index sorted in descending order on the ORDER BY clause.

For this purpose, since I didn’t have a test environment with the same data volume, I created a virtual index in production:

  • alter session set “_use_nosegment_indexes” = true;

  • create index test_desc on sbl.s_asset_xm(par_row_id desc,created desc) nosegment;

 

Then I extracted the execution plan:


Id  Operation                          Name           Rows        Bytes     Cost
0   SELECT STATEMENT                                  1              74        1
1   TABLE ACCESS BY INDEX ROWID     S_ASSET_XM        1              74        1
2   INDEX RANGE SCAN                S_ASSET_XM_OBDESC 1                       12

 

At first look, it does look slightly faster. What really makes a huge difference is the access itself:

PLAN TABLE OUTPUT

   1 - filter("T1"."TYPE"='Offer History')
   2 - access(SYS_OP_DESCEND("T1"."PAR_ROW_ID")=SYS_OP_DESCEND(:Z))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("T1"."PAR_ROW_ID"))=:Z)

 

The most selective field is accessed in descending order therefore eliminating the need for a costly sort. So let’s see after implementation the actual performance gain…

 

Following the index implementation, the query didn’t show up in the top queries anymore, so I extracted the hash value and ran the ?/rdbms/admin/sprepsql.sql, the performance gain was bigger than I expected.

                                                      
                     Statement Total      Per Execute     Before Index: Per execute
                     
        Buffer Gets:          17,707              5.5                       27313.3
         Disk Reads:             276              0.1                       35606.2
     Rows processed:          14,518              4.5
     CPU Time(s/ms):               1               .2
 Elapsed Time(s/ms):               2               .7
              Sorts:               0               .0
        Parse Calls:             745               .2
      Invalidations:               0
      Version count:               2
    Sharable Mem(K):              35
         Executions:           3,192

 

Pierre Roussin

Advertisements