Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to prove the query in stored procedure use the outline?
Hello,
There is a column used in USER_OUTLINES that will become used if your stored
outline has been used.
If not so then maybe take care that the query you type and the query in the
outline must be exactly the same (space, uppercase ...)
You can use alter session set create_stored_outlines=true then run your
query to grab the exact query.
HTH, Laly.
"wangbin" <wangbin_at_start.com.au> a écrit dans le message de news:
2d15bd69.0311241819.2a0a7388_at_posting.google.com...
> I try to use outline following steps in Jonathan's article "Faking
> Stored Outlines in Oracle 9" (http://www.dbazine.com/jlewis5.html).
> The following are the my steps in test environment.
> 1. The query I try to tune is in a stored procedure. So first, I run
> the stored procedure and find the query in the trace file.
> 2. I drop one index so that the explain plan is the one I prefer.
> 3. I create outline for the new plan.
> 4. Then I recreate the index.
> 5. I create two sessions, one turns on use_stored_outlines, the other
> doesn't. Explain plan are different.
> 6. Everything looks fine so far. However, when I run the stored
> procedure in the session with use_stored_outlines = TRUE and check
> the trace file by using tkprof, it shows the wrong plan.
>
> How can I prove the query in stored procedure use the outline?
>
> If I use a query instead of stored procedure, the result is same. The
> only different is when I set autotrace on, the plan in sqlplus
> shows outline has been used, but trace file doesn't.
>
> Thanks,
> Bin
Received on Mon Nov 24 2003 - 22:24:34 CST