RE: SQL stored outlines

>>Is there a way to determine if a SQL statement entered via SQL*Plus is
>>using a stored outline other than the fact that the plan changes?
 

Turn on SQL*Trace at level 12 and you would see queries accessing outln.ol$ with a signature. Bind 0 is the signature. So, for this SQL below, outline was used, since that outline exists in outln.ol$ table. You can also use outln_pkg.clear_used to some extent, although involves little bit more work.  

Here is an example:  

PARSING IN CURSOR #4 len=96 dep=1 uid=11 oct=3 lid=11 tim=3788782797512 hv=2771912725 ad='20ac13b0'

select /*+ INDEX(ol$ ol$signature) */ ol_name from ol$ where signature = :1 and category = :2

END OF STMT PARSE #4:c=0,e=835,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=3788782797503

BINDS #4:  bind 0: dty=23 mxl=32(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0

   bfp=ffffffff7cc751f8 bln=32 avl=16 flg=05

   value=

Dump of memory from 0xFFFFFFFF7CC751F8 to 0xFFFFFFFF7CC75208

FFFFFFFF7CC751F0                   A45D9393 F2EAF1E3          [.]......]

FFFFFFFF7CC75200 2B6DE37B 46009008                    [+m.{F...]

 

 bind 1: dty=1 mxl=32(07) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0

   bfp=ffffffff7cc66fa8 bln=32 avl=07 flg=05

   value="DEFAULT"  

  1* select ol_name from outln.ol$ where signature='A45D9393F2EAF1E32B6DE37B46009008' and category='DEFAULT'

SQL> /   OL_NAME

SYS_OUTLINE_070824094920228   Thanks  

Riyaj "Re-yas" Shamsudeen
ERP Financials DBA, New AT&T  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of richa03_at_gmail.com Sent: Friday, August 24, 2007 12:39 PM
To: Allen, Brandon
Cc: oracle-l_at_freelists.org
Subject: Re: SQL stored outlines

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