Bind Peeking

We have this really dandy performance monitoring tool which breaks down database activity (wait events) time wise, module wise, user wise etc. I am usually concerned with Top SQL tab which shows most non performing SQL (Top SQL !! misnomer I guess). So out of no where for few days one SQL got this top most spot and disappeared automagically. I decided to sleuth a little bit about this mysterious SQL.


The suspect

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = :B1


The Investigation


IC_LOTS_MST is Oracle Process Manufacturing table which stores lot information for the item. ITEM_ID and LOT_ID forms combinational primary key and there exists a unique index, IC_LOTS_MST_PK, on these columns. LOT_ID is unique but for the fact that there can be non lot controlled item for which LOT_ID = 0. So we also have non-unique index IC_LOTS_MST_I2 on LOT_ID.


select lot_dist, count(*)
from (select decode (lot_id,0,'ZERO','NONZERO') lot_dist
from ic_lots_mst)
group by lot_dist

LOT_DIST    COUNT
---------------------
ZERO       14,432
NONZERO  2,31,886

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 0


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 13 K 1734
TABLE ACCESS FULL GMI.IC_LOTS_MST 13 K 604 K 1734


If I pass 0 as literal, CBO performs a FTS on IC_LOTS_MST. Make sense


SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 12345

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 4
TABLE ACCESS BY INDEX ROWID GMI.IC_LOTS_MST 1 45 4
INDEX RANGE SCAN GMI.IC_LOTS_MST_I2 1 3


If I pass a non zero as literal, CBO picks a index. Make more sense.

The Findings
When we bounced database, the query in question was executed with a value 0. CBO peeked into the value first time, created a plan with FTS and continued using this. Until we bounced the database again and this time a non zero value was bound.