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

From: wangbin <wangbin_at_start.com.au>
Date: 24 Nov 2003 18:19:16 -0800
Message-ID: <2d15bd69.0311241819.2a0a7388@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 - 20:19:16 CST