stored outlines set?

sbrjd

unread,

Nov 17, 2009, 12:33:55 PM11/17/09

to

How can I determine if "alter system set use_stored_outlines.." has
been issued?

Steven Rosenthal
databases etc
Columbia University IT

Gerard H. Pille

unread,

Nov 17, 2009, 2:00:25 PM11/17/09

to

No way. But the dba_outlines view indicates if an outline has been used. I found it impossible
to determine why an outline was not being used. I guess that maybe a plan was already stored in
the SGA for a very frequently used query, and a flush shared_pool had no effect until I issued
it at a calmer time.

But that was not your question, sorry.

Mladen Gogala

unread,

Nov 17, 2009, 2:45:13 PM11/17/09

to

I don't think you can. The most comprehensive set of the parameters
resides in X$KSPPI and there is nothing there:

SQL> select ksppinm from x$ksppi where ksppinm like '%outl%';

KSPPINM
--------------------------------------------------------------------------------
create_stored_outlines
_plan_outline_data
_outline_bitmap_tree

Elapsed: 00:00:00.16
SQL>

Maybe, it can be read if you dump SGA but I don't know how to read Oracle
dumps.

--
http://mgogala.byethost5.com

Steve Howard

unread,

Nov 17, 2009, 2:52:16 PM11/17/09

to

Hi Steven,

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga sgauso
qolprm sgauso_ [060021418, 06002143C) = 00000002 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
SQL> alter system set use_stored_outlines = true;

System altered.

Elapsed: 00:00:00.00
SQL> oradebug dumpvar sga sgauso
qolprm sgauso_ [060021418, 06002143C) = 00000003 45440007 4C554146
00000054 00000000 00000000 00000000 00000000 00000000
SQL> alter system set use_stored_outlines = false;

System altered.

Elapsed: 00:00:00.00
SQL> oradebug dumpvar sga sgauso
qolprm sgauso_ [060021418, 06002143C) = 00000004 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
SQL>

00000054 for the fourth value indicates they are enabled at the system
level.

HTH,

Steve

Mladen Gogala

unread,

Nov 17, 2009, 3:09:13 PM11/17/09

to

As I said, I am no good at doing dumps....

--
http://mgogala.byethost5.com

Mark D Powell

unread,

Nov 18, 2009, 9:35:33 AM11/18/09

to

On Nov 17, 2:45 pm, Mladen Gogala <n...@email.here.invalid> wrote:
> On Tue, 17 Nov 2009 09:33:55 -0800, sbrjd wrote:
> > How can I determine if "alter system set use_stored_outlines.." has been
> > issued?
>
> > Steven Rosenthal
> > databases etc
> > Columbia University IT
>
> I don't think you can. The most comprehensive set of the parameters
> resides in X$KSPPI and there is nothing there:
>
> SQL> select ksppinm from x$ksppi where ksppinm like '%outl%';
>
> KSPPINM

> --------------------------------------------------------------------------------

> create_stored_outlines
> _plan_outline_data
> _outline_bitmap_tree
>
> Elapsed: 00:00:00.16
> SQL>
>
> Maybe, it can be read if you dump SGA but I don't know how to read Oracle
> dumps.
>
> --http://mgogala.byethost5.com

Did you check the value of create_stored_outlines when you set this
session parameter? I ask because I wondered why you could not find
the parameter value in v$parameter / v$spparameter which I tested.
Then I found Oracle note: Session Parameter: USE_STORED_OUTLINES #ID
68642.1 which says the session parameter is not a database parameter.
However, the parameter turns on and off generating outlines and create
seems like it might be a fit.

I just do not have a system I think I can risk testing on right now.

HTH -- Mark D Powell --

Mark D Powell

unread,

Nov 18, 2009, 9:40:20 AM11/18/09

to

> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

Withdraw my comments. The create obviously corresponds to ALTER
SYSTEM SET CREATE_STORED_OUTLINES = TRUE | FALSE. Reviewing too many
Oracle documents too quickly caused a brain fizz.

Mladen Gogala

unread,

Nov 18, 2009, 11:03:12 AM11/18/09

to

This is a system parameter and should have been present in the
V$PARAMETER table. Unfortunately, it's not.

--
http://mgogala.byethost5.com