Re: Do missing binds prevent creation of baseline?

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Mon, 15 Apr 2013 14:36:29 +0100
Message-ID: <CAGRZYUduiadUqm3shwv_9vPsrMF-s0Sywu5fBgAEwGc9jA+8kw_at_mail.gmail.com>

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_at_society.servebeer.com> 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...
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 15 2013 - 15:36:29 CEST