Finding the Right Index for this SQL Statement

1 04 2010

April 1, 2010

We have a SQL statement in our ERP package that looks like the following:

SELECT TRANSACTION_ID , WORKORDER_BASE_ID , WORKORDER_LOT_ID , WORKORDER_SPLIT_ID , WORKORDER_SUB_ID ,
OPERATION_SEQ_NO , RESOURCE_ID , TYPE , INDIRECT_ID , CLOCK_IN , TRUNC(TRANSACTION_DATE) , BREAK_CLOCKIN ,
HOURS_BREAK , HOURS_BREAK_IND , SHIFT_DATE , HOURS_PREVIOUS , HOURS_OVERALL , CLOCK_OUT , HOURS_WORKED ,
USER_ID , CREATE_DATE , STARTING_TRANS , UNADJ_CLOCK_IN , PRD_INSP_PLAN_ID , GOOD_QTY , BAD_QTY , DEVIATION_ID 
FROM LABOR_TICKET WHERE  EMPLOYEE_ID = :1  AND HOURS_WORKED IS NULL  ORDER BY EMPLOYEE_ID, TRANSACTION_ID DESC

This SQL statement is fired at the database many times a day, as employees on the production floor start and end production run batches (and also when they “clock-in” and “clock-out” for the day).  The ERP system has an index on the EMPLOYEE_ID column, but while that index will work great for the employee who was just recently hired, unfortunately that index will be inefficient for those employees with 15+ years of transaction history in the database.

One of the suggestions from the ERP user’s group back in 2001 was to create the following index to improve performance, which would hopefully eliminate the need to sort the rows retrieved by the query:

CREATE INDEX VT_LABOR_TICKET_1 ON LABOR_TICKET(EMPLOYEE_ID, TRANSACTION_ID DESC);

In case you are wondering, that descending index specification causes Oracle to create a function based index on recent Oracle releases, while that specification was silently ignored on older Oracle releases (prior to Oracle Database 8i for Enterprise Edition, if I recall correctly, and prior to Oracle Database 9i for Standard Edition).

Did the index help?  Maybe, maybe not.  Consider what would probably happen with a recent Oracle release.  The nightly automatic statistics collection process calculates that the clustering factor for this index is very high, and automatically generates a histogram on the column due to the uneven distribution of the column values.  With recent Oracle Database releases, bind variable peeking is enabled by default.  The employee with 15+ years of transaction history in the system is the first to sign in for the day – and a new child cursor is created for the SQL statement due to the statistics collection 5+ hours earlier.  Assuming that this entire table is in the KEEP buffer pool, the execution plan might look something like this:

Plan hash value: 2750877200

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |              |      1 |      1 |      0 |00:00:00.42 |   35610 |  1024 |  1024 |          |
|*  2 |   TABLE ACCESS FULL| LABOR_TICKET |      1 |      1 |      0 |00:00:00.42 |   35610 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("HOURS_WORKED" IS NULL AND "EMPLOYEE_ID"=:1))

A 10046 trace at level 12 for the query looks like this:

PARSING IN CURSOR #2 len=538 dep=0 uid=30 oct=3 lid=30 tim=402571814 hv=1657901498 ad='8af31240'
SELECT TRANSACTION_ID , WORKORDER_BASE_ID , WORKORDER_LOT_ID , WORKORDER_SPLIT_ID , WORKORDER_SUB_ID , OPERATION_SEQ_NO , RESOURCE_ID , TYPE , INDIRECT_ID , CLOCK_IN , TRUNC(TRANSACTION_DATE) , BREAK_CLOCKIN , HOURS_BREAK , HOURS_BREAK_IND , SHIFT_DATE , HOURS_PREVIOUS , HOURS_OVERALL , CLOCK_OUT , HOURS_WORKED , USER_ID , CREATE_DATE , STARTING_TRANS , UNADJ_CLOCK_IN , PRD_INSP_PLAN_ID , GOOD_QTY , BAD_QTY , DEVIATION_ID  FROM LABOR_TICKET WHERE  EMPLOYEE_ID = :1
AND HOURS_WORKED IS NULL
ORDER BY EMPLOYEE_ID, TRANSACTION_ID DESC
END OF STMT
PARSE #2:c=0,e=1198,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=402571806
BINDS #2:
kkscoacd
 Bind#0
  oacdty=96 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000010 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=2ee851f8  bln=32  avl=06  flg=05
  value="EMP103"
EXEC #2:c=0,e=5512,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=402577873
FETCH #2:c=406250,e=415234,p=0,cr=35610,cu=0,mis=0,r=0,dep=0,og=1,tim=402993364
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=35610 pr=0 pw=0 time=415238 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=12347 op='TABLE ACCESS FULL LABOR_TICKET (cr=35610 pr=0 pw=0 time=415190 us)'

The production floor employee is happy to get back to work with only a 1/2 second delay.  But what would happen if the table was not fully cached in the buffer cache, or if a new employee made his way to the computer first after the statistics collection?  Either the VT_LABOR_TICKET_1 index or the index just on the EMPLOYEE_ID column might have been used for the execution plan, and our long term employee might have had to endure a much longer wait to get back to work, and certainly would not leave the computer in a happy mood.

If we could modify the hard-coded SQL statement, we might try forcing the use of the VT_LABOR_TICKET_1 index, and we might even specify to only look at those transactions that we created in the last couple of days.  Let’s just try a hint to use the index, which we could implement using this approach:

SELECT /*+ INDEX(LABOR_TICKET VT_LABOR_TICKET_1) */ TRANSACTION_ID , WORKORDER_BASE_ID ,
WORKORDER_LOT_ID , WORKORDER_SPLIT_ID , WORKORDER_SUB_ID , OPERATION_SEQ_NO , RESOURCE_ID , TYPE ,
INDIRECT_ID , CLOCK_IN , TRUNC(TRANSACTION_DATE) , BREAK_CLOCKIN , HOURS_BREAK , HOURS_BREAK_IND ,
SHIFT_DATE , HOURS_PREVIOUS , HOURS_OVERALL , CLOCK_OUT , HOURS_WORKED , USER_ID , CREATE_DATE ,
STARTING_TRANS , UNADJ_CLOCK_IN , PRD_INSP_PLAN_ID , GOOD_QTY , BAD_QTY , DEVIATION_ID  FROM
LABOR_TICKET WHERE  EMPLOYEE_ID = :1  AND HOURS_WORKED IS NULL  ORDER BY EMPLOYEE_ID,
TRANSACTION_ID DESC;

For one of the employees with 15+ years of transaction history, the execution plan might look something like this:

Plan hash value: 1422135358

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| LABOR_TICKET      |      1 |      1 |      0 |00:00:00.05 |    8684 |
|*  2 |   INDEX RANGE SCAN          | VT_LABOR_TICKET_1 |      1 |  11719 |  16376 |00:00:00.01 |      66 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HOURS_WORKED" IS NULL)
   2 - access("EMPLOYEE_ID"=:1)
       filter("EMPLOYEE_ID"=:1)

The execution completed 10 times faster than the full table scan, which was certainly helped by careful use of the KEEP buffer pool to limit the impact of the index’s high clustering factor.  The 10046 trace at level 12 might look something like this:

PARSING IN CURSOR #13 len=583 dep=0 uid=30 oct=3 lid=30 tim=2180285132 hv=989309132 ad='7c9e2b58'
SELECT /*+ INDEX(LABOR_TICKET VT_LABOR_TICKET_1) */ TRANSACTION_ID , WORKORDER_BASE_ID , WORKORDER_LOT_ID , WORKORDER_SPLIT_ID , WORKORDER_SUB_ID , OPERATION_SEQ_NO , RESOURCE_ID , TYPE , INDIRECT_ID , CLOCK_IN , TRUNC(TRANSACTION_DATE) , BREAK_CLOCKIN , HOURS_BREAK , HOURS_BREAK_IND , SHIFT_DATE , HOURS_PREVIOUS , HOURS_OVERALL , CLOCK_OUT , HOURS_WORKED , USER_ID , CREATE_DATE , STARTING_TRANS , UNADJ_CLOCK_IN , PRD_INSP_PLAN_ID , GOOD_QTY , BAD_QTY , DEVIATION_ID  FROM LABOR_TICKET WHERE  EMPLOYEE_ID = :1
AND HOURS_WORKED IS NULL
ORDER BY EMPLOYEE_ID, TRANSACTION_ID DESC
END OF STMT
PARSE #13:c=0,e=1218,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2180285124
BINDS #13:
kkscoacd
 Bind#0
  oacdty=96 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000010 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=2db72048  bln=32  avl=06  flg=05
  value="EMP103"
EXEC #13:c=15625,e=5540,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=2180291322
WAIT #13: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2180291466
FETCH #13:c=46875,e=54726,p=0,cr=8684,cu=0,mis=0,r=0,dep=0,og=1,tim=2180346313
STAT #13 id=1 cnt=0 pid=0 pos=1 obj=12347 op='TABLE ACCESS BY INDEX ROWID LABOR_TICKET (cr=8684 pr=0 pw=0 time=54719 us)'
STAT #13 id=2 cnt=16376 pid=1 pos=1 obj=14555 op='INDEX RANGE SCAN VT_LABOR_TICKET_1 (cr=66 pr=0 pw=0 time=51 us)'

If you have read many of Richard Foote’s blog articles, you are probably well aware that B*Tree indexes do not store NULL values… at least without using fancy tricks as described on his blog… or if there are multiple columns in the index and at least one column value is not NULL.  So, we create a new index:

CREATE INDEX IND_LT_EMPLOYEE_HW ON LABOR_TICKET(EMPLOYEE_ID,HOURS_WORKED);

The execution plan might look like the following for the employee with the 15+ years of transaction history:

SELECT /*+ INDEX(LABOR_TICKET IND_LT_EMPLOYEE_HW) */ TRANSACTION_ID , WORKORDER_BASE_ID , WORKORDER_LOT_ID , WORKORDER_SPLIT_ID
, WORKORDER_SUB_ID , OPERATION_SEQ_NO , RESOURCE_ID , TYPE , INDIRECT_ID , CLOCK_IN , TRUNC(TRANSACTION_DATE) , BREAK_CLOCKIN ,
HOURS_BREAK , HOURS_BREAK_IND , SHIFT_DATE , HOURS_PREVIOUS , HOURS_OVERALL , CLOCK_OUT , HOURS_WORKED , USER_ID , CREATE_DATE
, STARTING_TRANS , UNADJ_CLOCK_IN , PRD_INSP_PLAN_ID , GOOD_QTY , BAD_QTY , DEVIATION_ID  FROM LABOR_TICKET WHERE  EMPLOYEE_ID
= :1  AND HOURS_WORKED IS NULL  ORDER BY EMPLOYEE_ID, TRANSACTION_ID DESC

Plan hash value: 2790784761

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY               |                    |      1 |      1 |      0 |00:00:00.01 |       3 |  1024 |  1024 |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| LABOR_TICKET       |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IND_LT_EMPLOYEE_HW |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMPLOYEE_ID"=:1 AND "HOURS_WORKED" IS NULL)

And a 10046 trace at level 12 might look like this:

PARSING IN CURSOR #6 len=584 dep=0 uid=30 oct=3 lid=30 tim=2919660608 hv=4195357598 ad='66272978'
SELECT /*+ INDEX(LABOR_TICKET IND_LT_EMPLOYEE_HW) */ TRANSACTION_ID , WORKORDER_BASE_ID , WORKORDER_LOT_ID , WORKORDER_SPLIT_ID , WORKORDER_SUB_ID , OPERATION_SEQ_NO , RESOURCE_ID , TYPE , INDIRECT_ID , CLOCK_IN , TRUNC(TRANSACTION_DATE) , BREAK_CLOCKIN , HOURS_BREAK , HOURS_BREAK_IND , SHIFT_DATE , HOURS_PREVIOUS , HOURS_OVERALL , CLOCK_OUT , HOURS_WORKED , USER_ID , CREATE_DATE , STARTING_TRANS , UNADJ_CLOCK_IN , PRD_INSP_PLAN_ID , GOOD_QTY , BAD_QTY , DEVIATION_ID  FROM LABOR_TICKET WHERE  EMPLOYEE_ID = :1
AND HOURS_WORKED IS NULL
ORDER BY EMPLOYEE_ID, TRANSACTION_ID DESC
END OF STMT
PARSE #6:c=0,e=1230,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2919660600
BINDS #6:
kkscoacd
 Bind#0
  oacdty=96 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000010 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=2db7d620  bln=32  avl=06  flg=05
  value="EMP103"
EXEC #6:c=0,e=5468,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=2919666706
FETCH #6:c=0,e=103,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,tim=2919667106
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 time=114 us)'
STAT #6 id=2 cnt=0 pid=1 pos=1 obj=12347 op='TABLE ACCESS BY INDEX ROWID LABOR_TICKET (cr=3 pr=0 pw=0 time=72 us)'
STAT #6 id=3 cnt=0 pid=2 pos=1 obj=102065 op='INDEX RANGE SCAN IND_LT_EMPLOYEE_HW (cr=3 pr=0 pw=0 time=64 us)'

Five times faster than using the VT_LABOR_TICKET_1 index?  No, not if you look at the STAT lines in the 10046 trace.  When the VT_LABOR_TICKET_1 index was used, the top STAT line shows time=54719 us, while the top STAT line when the IND_LT_EMPLOYEE_HW index was used shows time=72 us – that is about 760 times faster.  As you might imagine, the employee overjoyed to return to the production floor 0.054647 seconds faster.  :-)

But this whole exercise is not about doing something 0.054647 seconds faster, but instead about eliminating unnecessary work.  We should be looking at what else that is executing simultaneously against the database is now able to complete faster due to less CPU and I/O competition from the needless work that was caused by this SQL statement.








Follow

Get every new post delivered to your Inbox.

Join 139 other followers