Re: v$sql_plan shows different exection plan

When you say that sqlplus uses the index do you actually execute the sql and look at the sql trace or v$sqlplan for the sql, or are you looking at autotrace (traceonly) or explain?
Explain, and therefore autotrace, is unreliable - if not to say a bloody liar - if the sql has bind variables. Even more so now in Oracle 9 (or 10) with bind variable peeking. Explain does not peek!!!

david hill wrote:

> I have a strange little problem this morning
> It's a simple little query being executed by our JDE application, er
> Peoplesoft, er Oracle=20
>
> SELECT * FROM CRPDTA.F0901 WHERE ( GMMCU =3D :KEY1 AND GMOBJ =3D :KEY2 AND
> GMSUB =3D :KEY3 )
>
> And the table has an index on GMMCU, GMSUB, GMOBJ
>
> Now when I pull the execution plan from v$sql_plan I see that it is doing a
> full table scan of this table
>
> but when I execute the query with autotrace on, or use explain plan for,
> Everything looks good the query uses the index,=20
>
> I've refreshed the stats, checked for stored outlines, there are none, and
> bounced the db, and I always get the same result.
>
> The application does a Full table scan and in sqlplus and index,=20
> when running the test I do keep the bind variables.
>
> Anyone have any ideas on this one??
>
>
> Thanks
> David Hill
>
>
>
>
>
>
>
> CONFIDENTIALITY NOTICE
> This message contains confidential information intended only for the use of
> the individual or entity named as recipient. Any dissemination, distribution
> or copying of this communication by anyone other than the intended recipient
> is strictly prohibited. If you have received this message in error, please
> immediately notify us and delete your copy. Thank you.
>
> AVIS DE CONFIDENTIALIT=C9
> Les informations contenues aux pr=E9sentes sont de nature privil=E9gi=E9e et
> confidentielle. Elles ne peuvent =EAtre utilis=E9es que par la personne ou
> l'entit=E9 dont le nom para=EEt comme destinataire. Si le lecteur du pr=E9s=
> ent
> message n'est pas le destinataire pr=E9vu, il est par les pr=E9sentes pri=
> =E9 de
> noter qu'il est strictement interdit de divulguer, de distribuer ou de
> copier ce message. Si ce message vous a =E9t=E9 transmis par m=E9garde, veu=
> illez
> nous en aviser imm=E9diatement et supprimer votre copie. Merci.
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l