Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why is USE_STORED_OUTLINES not an initialization parameter?

RE: Why is USE_STORED_OUTLINES not an initialization parameter?

From: Michael Fontana <MFontana_at_verio.net>
Date: Fri, 26 May 2006 11:58:38 -0400
Message-ID: <87E07E8CF4B26D4587451BE788F5C321B60BC8@IAD-WPRD-XCHB02.corp.verio.net>


Great informational thread.

I have also logged an enhancement request.

This has been long needed!

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Schultz, Charles
> Sent: Thursday, May 25, 2006 2:49 PM
> To: oracle-l
> Subject: 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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 26 2006 - 10:58:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US