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

Other related posts: