Re: Stored outline not being used all the times...

---

From:  "Charles Schultz" <sacrophyte@gmail.com>
Reply-To:  sacrophyte@gmail.com
To:  binhpham15@hotmail.com
CC:  oracle-l@freelists.org
Subject:  Re: Stored outline not being used all the times...
Date:  Thu, 7 Dec 2006 13:05:04 -0600

What does v$SQL_SHARED_CURSOR show? Also remember that outlines will follow cursor sharing rules; if you expect SIMILAR cursors to be shared, the outline must be created with cursor_sharing = SIMILAR. If the queries are exactly the same and still not being shared, hopefully the view will help identify the reason.

On 12/6/06, Binh Pham <binhpham15@hotmail.com> wrote:

I have a stored outline that has been indicated as being used ( in v$sql, column outline_category = PROD).  However, in V$SQL_PLAN it shows a different plan than in the stored outline and also when I do explain of the SQL.

We have the "alter system set use_stored_outlines = PROD" in our database startup trigger.  We've also verified that other outlines are being used.

Why was this one not using the stored ouline?

I've noticed that in V$SQLAREA, this particular SQL has 5 invalidations and 6 loads.  Even with the multiple invalidations and loads, Oracle should have used the outline.

Any idea?

Thanks.

---

View Athlete's Collections with Live Search

--

http://www.freelists.org/webpage/oracle-l