Do missing binds prevent creation of baseline?
Date: Mon, 15 Apr 2013 06:56:12 -0500 (CDT)
Message-ID: <b15ce04f327275b5879f5a4399d48997.squirrel_at_society.servebeer.com>
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...
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 15 2013 - 13:56:12 CEST