Rich Is the PK index status UNUSABLE? could the DB crash have happened at an inconvenient point in a batch data load, for example? See eg http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1859798300346695894 Regards Nigel On 15 April 2013 12:56, Rich Jesse <rjoralist2@xxxxxxxxxxxxxxxxxxxxx> wrote: > 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 > > > -- //www.freelists.org/webpage/oracle-l