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_at_oneneck.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of richa03_at_gmail.com
>
> 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