RE: Why is USE_STORED_OUTLINES not an initialization parameter?

I ended up with a database startup trigger, although I am a little concerned about the impact to all sessions checking both the library cache and outlines for a match (mentioned in one of Jonathan Lewis's books). Is this not like double parsing? The question is, does the amount of resources that we save by using plans outweigh the cost of parsing with outlines (two totally different resources)?

Fairlie's note was very helpful, and I have followed up with another enhancement request for Oracle to lock away in some long-term storage unit.

-----Original Message-----

From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com] Sent: Thursday, May 25, 2006 2:36 PM
To: ramick_at_dotster.com; fairlie_r_at_yahoo.com; Schultz, Charles; oracle-l Subject: RE: Why is USE_STORED_OUTLINES not an initialization parameter?

I wonderd about the same thing a while back but never came up with an answer. I ended up using a simple logon trigger (see code below) and that has worked fine for us.

create or replace trigger
use_outlines
after logon on database
begin
execute immediate 'ALTER SESSION SET USE_STORED_OUTLINES=TRUE'; end;

Also, you can find out if a session is using a stored outline by checking the OUTLINE_CATEGORY column in v$sql for the cursor that the session is executing (join v$sql.hash_value to v$session.sql_hash_value).

HTH,
Brandon

Also IIRC the only way to find out if a session is using a stored outline and its category is using oradebug as illustrated in

HTH,
Fairlie  

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 Received on Thu May 25 2006 - 14:49:20 CDT