| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: PLAN_TABLE gotcha in 10g
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/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
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
 |
 |