Oracle Scratchpad

January 25, 2010

Old plan_table

Filed under: dbms_xplan — Jonathan Lewis @ 8:27 am BST Jan 25,2010

When using “explain plan” with “dbms_xplan.display()”, have you ever seen the following note at the end of the output:

Note
-----
   - 'PLAN_TABLE' is old version

It’s something I see surprisingly frequently, travelling as I do to many different sites, but it’s usually easy to deal with.

In 10g [corrected from 9i, see comment #1] Oracle got rid of the need for users to create a plan_table in their own schema by creating a global temporary table in the SYS  schema, creating a public synonym for it, and then granting all necessary privileges to PUBLIC. One of the nice side effects of this strategy is that every session gets its own private copy of the plan_table in its temporary tablespace, so concurrent sessions don’t interfere with each other and the content automatically disappears when the session ends. This table is created in the script catplan.sql – even though the old script utlxplan.sql still does things the old way.

Unfortunately, some systems probably had private versions of the plan_table lurking in separate schema when they upgraded to 10g; other systems may have had an implementation standard that included a directive to create a schema-level plan_table (using the old utlxplan.sql script) Then there’s a really irritating issue that is the one I see most frequently.

Some third party GUI tools have their own interface for generating and displaying execution plans. But all they’re doing (usually) is making calls to Oracle’s own “explain plan” and then querying the plan_table. Unfortunately some of these tools seem to query  user_tables or all_tables to see if the appropriate table exists – and then create one in the current schema if they can’t find one. But if it’s an older version of the GUI it’s likely to create an older version of the plan_table … hence the warning the next time an older version of a DBA comes in to investigate some execution plans the GUI-free way.

All you need to do (probably) is execute the statement:  drop table plan_table;

If you want to check that this is the solution, you could run a simple query:


select
        owner, object_type, object_name
from
        all_objects
where
        object_name like 'PLAN_TABLE%'
/

OWNER        OBJECT_TYPE              OBJECT_NAME
------------ ------------------------ ------------------------
SYS          TABLE                    PLAN_TABLE$
PUBLIC       SYNONYM                  PLAN_TABLE
SYSTEM       TABLE                    PLAN_TABLE
SH           TABLE                    PLAN_TABLE$

The things you’re looking for are sys.plan_table$ and the public synonym plan_table.

The example above comes from a 9i database that had been upgraded from 8i, so it still has a copy of an older version of plan_table in the SYSTEM schema, and it looks like someone may have run the script $ORACLE_HOME/rdbms/admin/utlxplan.sql from the SH schema – so I ‘ll want to check that the public synonym does point to sys.plan_table$, rather than sh.plan_table$ before taking further action.

Of course, you don’t drop an object from a production database without getting the appropriate approval – and if that’s going to take some time you can always work around the problem by employing a rarely used feature of “explain plan”:

explain plan into sys.plan_table$
for
{your SQL statement}
;

select  *
from    table(dbms_xplan.display('sys.plan_table$'))
;

I’ve lost count of the number of times I’ve told people that the predicate section of an execution plan is vital to understanding what’s really going on, and it’s usually the predicate section that’s missing if you have an old plan_table. So it’s worth fixing the problem – and making sure it’s a permanent fix.

14 Comments »

  1. Hi Jonathan,

    I thought that the “global temporary table in the SYS schema” came in at 10g not 9i. I’ve double checked a 9i database (wanted to make sure I’ve got my fact straight before leaving a comment on one of your posts :) and see the following:

    SQL> select table_name, owner, TEMPORARY from dba_tables where table_name like '%PLAN_TAB%'
    SQL> /
    
    no rows selected
    

    This is from a system where utlxplan.sql has not been run either.

    Comment by Neil — January 25, 2010 @ 9:38 am BST Jan 25,2010 | Reply

    • Neil,

      You’re absolutely right. On reading your post my first move was to check the utlxplan.sql script under $ORACLE_HOME/rdbms/admin for my 9i system to see if it created a GTT – and it did. But it also contained a comment: “JPL changed to use GTT”.

      When GTTs came in in 8i I started to advise clients that creating a plan_table as a GTT with a public synonym was a smart idea – although at the time I suggested putting it in the SYSTEM schema rather than SYS. I’d forgotten that the 9i GTT in SYS was also a hack – suggested only after I saw Oracle do the same in 10g.

      Comment by Jonathan Lewis — January 25, 2010 @ 5:20 pm BST Jan 25,2010 | Reply

  2. [...] 5-How to sort ‘PLAN_TABLE’ is old version problem Jonathan Lewis-old plan table [...]

    Pingback by Blogroll Report 22/01/2009 – 29/01/2010 « Coskan’s Approach to Oracle — February 21, 2010 @ 3:25 am BST Feb 21,2010 | Reply

  3. [...] Recipe 19-7 states that a PLAN_TABLE must exist, and that if it does not exist the utlxplan.sql script should be run to create a PLAN_TABLE in the user’s schema. As of Oracle Database 10.1.0.1 [Edit: it appears that this change happened prior to 10.1.0.1] user schemas should no longer have a PLAN_TABLE in their schema – a global temporary table owned by the SYS user is used instead. [Reference/Test Case - Check the Comments Section Also] [...]

    Pingback by Book Review: Oracle SQL Recipes: A Problem – Solution Approach « Charles Hooper's Oracle Notes — June 7, 2010 @ 10:56 am BST Jun 7,2010 | Reply

  4. [...] Page xxxvi describes running the utlxplan script as the user SYSTEM.  Running this script should not be necessary starting with Oracle Database 9i (Reference). [...]

    Pingback by Book Review: Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition « Charles Hooper's Oracle Notes — August 16, 2010 @ 10:32 am BST Aug 16,2010 | Reply

  5. Hi Jonathan,
    Your last tip was a life saver. I user the predicate information extensively when i tune SQLs and one set of my client’s instances never showed any. And naturally, it’s abig task to make them drop a few old plan_tables. So i used your workaround and wow… I have all the predicate info that I want. Thank you !!

    btw, i solved that issue too

    Jithin

    Comment by Jithin Sarath — September 15, 2010 @ 5:02 am BST Sep 15,2010 | Reply

  6. Jonathan,

    Not sure what I am missing but here I am checking 2 systems (10.2.0.5 on AIX & 10.2.0.4 on HP-UX) and on both the systems utlxplan.sql doesn’t create a global temp table. It is just a simple create table statement. PLAN_TABLE in SYS is created as GTT, though.

    Thanks,
    Amardeep Sidhu

    Comment by Amardeep Sidhu — April 6, 2011 @ 9:38 am BST Apr 6,2011 | Reply

    • Amardeep,

      I forgot to correct the note after Neil’s first comment.
      The script that creates the GTT is catplan.sql – but that is part of the 10g installation run, and contains lots of other bits too. The utlxplan.sql script should have been retired by Oracle. but is still lurking, waiting to trap people.

      Comment by Jonathan Lewis — April 6, 2011 @ 12:46 pm BST Apr 6,2011 | Reply

      • It might be interesting to see how many recently published (possibly the last 3-4 years) Oracle Database books suggest executing the utlxplan.sql script – the header of which in 11.2.0.2 shows that it was last modified May 8, 2004. At least two of the recently published books that I read in the last year suggested executing this script. It could very well be the case that developers/DBAs are just trying to follow what appears to be best practices (or so they thought until reading this blog article).

        Comment by Charles Hooper — April 6, 2011 @ 7:07 pm BST Apr 6,2011 | Reply

      • Right. Things make sense now.

        Yes, either they should retire utlxplan.sql or update it to reflect the changes done in the way plan_table is created.

        Comment by Amardeep Sidhu — April 7, 2011 @ 4:29 am BST Apr 7,2011 | Reply

        • Amardeep,

          I guess the 11g documentation is thinking of the case where something like TOAD kicks in and expects to find a real table. I think the best thing to do with utlxplan.sql is to change the code to create a GTT (and make sure it matches the one in catplan.sql!)

          Comment by Jonathan Lewis — April 7, 2011 @ 6:39 am BST Apr 7,2011

        • Could be possible.

          Not sure why these tools always want to create their own table. The things become dependent on the version of the Tool rather than the database and then those plans missing the predicate section.

          Another bit in support of SQL* Plus being the best tool for all this :)

          Comment by Amardeep Sidhu — April 7, 2011 @ 7:08 am BST Apr 7,2011

  7. It is good article. Especially the section which starts from here …

    ‘If you want to check that this is the solution, you could run a simple query:’

    Comment by Sumit — October 23, 2013 @ 2:17 pm BST Oct 23,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,876 other followers