Re: SQL stored outlines

I probably should have been more descriptive of what I'm trying to do...

I know I can use the view dba_outlines to see if application SQL is
using the stored outline (USED='USED'), and now I'd like to start
backing out of using the outlines and use the newer CBO in 10gR2
(also, not have to redo outlines after adding indexes, etc.).

My method for doing this is to get the SQL statement from sql_text in
dba_outlines, explain the plan in a session with
use_stored_outlines=true (they are all catagory DEFAULT), then explain
the plan with use_stored_outlines=false at the session level to see if
the plans differ - if they don't or if we get a better plan, I know I
can safely drop the outline, otherwise I know to check out why the
plan is "bad" (10053 trace, etc.).

I understand that outlines use simple text matching (the text must
match exactly including whitespace, right?), however I don't know how
to determine whether a particular statement entered at an SQL*Plus
prompt is using an outline or not - the type of the sql_text column is
a long type and I don't know where the line breaks are...

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?

Since this is a running system, the SQL will (and does) show USED.

I want to know for a fact that the SQL I enter into SQL*Plus is the
exact statement contained in dba_outlines, that it matched when
use_stored_outlines=true and that it didn't use the stored outline
when use_stored_outlines=false.  Is there any way to do this?

On 8/24/07, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
> select outline_category from v$sql where sql_id = <your_sql_id>;
>
> OR
>
> 1) execute dbms_outln.clear_used('<your_outline_name>');
> 2) run your query
> 3) select used from dba_outlines where name = '<your_outline_name>';
>
>
> Regards,
> Brandon
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of richa03@xxxxxxxxx
>
> Is there any way to determine if a SQL statement entered via SQL*Plus is
> using a stored outline other than the fact that the plan changes?
>
> Privileged/Confidential Information may be contained in this message or 
> attachments hereto. Please advise immediately if you or your employer do not 
> consent to Internet email for messages of this kind. Opinions, conclusions 
> and other information in this message that do not relate to the official 
> business of this company shall be understood as neither given nor endorsed by 
> it.
>
>
--
http://www.freelists.org/webpage/oracle-l


Other related posts: