Do missing binds prevent creation of baseline?
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 15 Apr 2013 06:56:12 -0500 (CDT)
Hey all,
On 11.2.0.3.0, we had our DB server crash. DB and instance are fine
(whew!), but afterwards one UPDATE statement is now using an inefficient
index:
UPDATE MYSCHEMA.MYTABLE SET
LIPREQ=:BND1,LIOT1A=:BND2,LIQTTR=:BND3,LIQTIN=:BND4,LIJOBN=:BND5,LIPID=:BND6,LIUPMJ=:BND7,LIUSER=:BND8,LITDAY=:BND9,LIQTO1=:BND10,LIQTO2=:BND11,LISREQ=:BND12
WHERE ( LIITM = :KEY1 AND LIMCU = :KEY2 AND LILOCN = :KEY3 AND LILOTN =
:KEY4 )
This used to use the PK's index, which is comprised of exactly the columns
in the WHERE clause. Now it's using a NONUNIQUE index of LIITM, LIMCU, and
another column not associated with the statement. Of course, all 1.8M
updates are now taking 3 hours instead of 1 hour, exceeding the execution
window.
Trying the "quick" way first, I used the SQL Tuning Advisor from EM12c. It
complains "All alternative plans other than the Original Plan could not be
reproduced in the current environment". I then turned to manual baseline
creation, using the known "good" plan captured in a tuning set from the AWR.
But when I run DBMS_SPM.LOAD_PLANS_FROM_SQLSET, it returns "0" for the
number of plans loaded. Using SPM tracing, I see the tracefile has:
load sts: other_xml data missing, skipping it
I've opened an SR, but in the meantime, I see in V$SQL_BIND_CAPTURE that
none of the ":BNDn" variables are captured. Could this be the issue?
Anyone run into this before?
TIA!
Rich
p.s. The job running these UPDATEs is not explicitly alterable, of course...
--
//www.freelists.org/webpage/oracle-l
Other related posts:
