RE: SQL stored outlines
- From: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
- To: <richa03@xxxxxxxxx>, "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- Date: Fri, 24 Aug 2007 12:55:52 -0500
>>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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of richa03@xxxxxxxxx
Sent: Friday, August 24, 2007 12:39 PM
To: Allen, Brandon
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL stored outlines
Other related posts:
