Re: PLAN_TABLE gotcha in 10g
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 22:18:05 +0100
Clearly someone from Oracle has been reading
my seminar notes - chapter 11 page 7 I think.
I think it great !! Now if only they change
autotrace so it doesn't do the delete bit
and waste resources - and then, of course,
every time you do an explain plan there are
not stats on the table, so every report starts
with a dynamic sample.
And have you noticed when you trace
dbms_xplan, the query against the plan
table doesn't do a connect by any more -
it a pure procedural process, walking the
lines in order, so you can't figure out what
sneaky tricks are built into the plan table
content.
On the plus side, the plan table had to be
'on commit preserve rows' - so you can
copy it out before you end your session.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: "Daniel Fink" <Daniel.Fink@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, May 05, 2004 10:08 PM
Subject: PLAN_TABLE gotcha in 10g
I've been working with some sql in a new 10g db doing the
typical 'explain plan for ' syntax. Today, I decided to preserve
some of the plans, so I set statement_ids and commited them. Lo
and behold, they were not there when I went back later in the
day. In 10g, a PLAN_TABLE$ table is created and given the PUBLIC
synonym PLAN_TABLE. Convenient, now I don't have to run utlxplan
for every user. Just be disciplined and use distinct
statement_ids. Unfortunately, the PLAN_TABLE$ is a global
temporary table, so my records are deleted when the session
ends! ARGH! FYI, this table is created with the catplan.sql
script that is called by catalog.sql.
Okay, Pete. Where's the doc on this one? ;)
Daniel
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
