Re: How to prove the query in stored procedure use the outline?

wangbin_at_start.com.au (wangbin) wrote in message 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

Do you have explain=<username/password> in your tkprof commandline? In that case the explain plan is generated again and is NOT the explain plan from the trace file.

Sybrand Bakker
Senior Oracle DBA Received on Tue Nov 25 2003 - 02:56:57 CST