stored outlines set?
in [Oracle]
|
Prev: Latch / Wait Events
Next: Visit must
From: sbrjd on 17 Nov 2009 12:33 How can I determine if "alter system set use_stored_outlines.." has been issued? Steven Rosenthal databases etc Columbia University IT
From: Gerard H. Pille on 17 Nov 2009 14:00 sbrjd wrote: > How can I determine if "alter system set use_stored_outlines.." has > been issued? > > Steven Rosenthal > databases etc > Columbia University IT 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.
From: Mladen Gogala on 17 Nov 2009 14:45 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
From: Steve Howard on 17 Nov 2009 14:52 On Nov 17, 12:33 pm, sbrjd <s...(a)columbia.edu> wrote: > How can I determine if "alter system set use_stored_outlines.." has > been issued? > > Steven Rosenthal > databases etc > Columbia University IT 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
From: Mladen Gogala on 17 Nov 2009 15:09
On Tue, 17 Nov 2009 11:52:16 -0800, Steve Howard wrote: > On Nov 17, 12:33 pm, sbrjd <s...(a)columbia.edu> wrote: >> How can I determine if "alter system set use_stored_outlines.." has >> been issued? >> >> Steven Rosenthal >> databases etc >> Columbia University IT > > 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 As I said, I am no good at doing dumps.... -- http://mgogala.byethost5.com |