This discussion is archived
10 Replies Latest reply: Jan 28, 2013 8:43 PM by 636309 RSS

is plan_table necessary for an explain plan?

636309 Newbie
Currently Being Moderated
Hi, I've noticed that an explain plan for a query can be obtained by setting autotrace on. However, when I get a result from the autotrace, it shows no records in the plan_table table. I just want to check with others to make sure it's clear that a plan_table table isn't necessary to obtain an explain plan for a query. I've also noticed that when I hit the explain plan button in sql developer, there's no records in the plan_table table.
  • 1. Re: is plan_table necessary for an explain plan?
    jgarry Guru
    Currently Being Moderated
    Please search the documentation for your version of oracle for where it explains the plan table.
  • 2. Re: is plan_table necessary for an explain plan?
    636309 Newbie
    Currently Being Moderated
    jgarry wrote:
    Please search the documentation for your version of oracle for where it explains the plan table.
    I did in the link below (among other links), but it doesn't answer my question.

    http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5127.htm

    BTW, 11gR2

    Edited by: arizona9952 on Jan 28, 2013 2:40 PM
  • 3. Re: is plan_table necessary for an explain plan?
    JohnWatson Guru
    Currently Being Moderated
    Release 11.x and (I think) 10.x comes with a public synonym plan_table pointing towards a global temporary table sys.plan_table$ so you don't need to create the table in your own schema any more.
  • 4. Re: is plan_table necessary for an explain plan?
    jgarry Guru
    Currently Being Moderated
    "While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema."

    John explains it a little better than that, as does what I find when I google oracle plan_table 11g: http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#i21501

    If you've upgraded to certain versions, see MOS Oracle Database Server Component Invalid After Upgrade To 10.2.0.4, 11.1.0.6 Or 11.1.0.7 [ID 782735.1]
  • 5. Re: is plan_table necessary for an explain plan?
    636309 Newbie
    Currently Being Moderated
    jgarry wrote:
    "While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema."

    John explains it a little better than that, as does what I find when I google oracle plan_table 11g: http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#i21501

    If you've upgraded to certain versions, see MOS Oracle Database Server Component Invalid After Upgrade To 10.2.0.4, 11.1.0.6 Or 11.1.0.7 [ID 782735.1]
    Thanks jgarry, but perhaps my question isn't clear enough. I'm trying to understand where the explain plan is coming from when I run autotrace. I never did "explain plan for..." on my query, nor have I manually created my own plan table. The plan_table table is also empty.
  • 6. Re: is plan_table necessary for an explain plan?
    JohnWatson Guru
    Currently Being Moderated
    arizona9952 wrote:
    Thanks jgarry, but perhaps my question isn't clear enough. I'm trying to understand where the explain plan is coming from when I run autotrace. I never did "explain plan for..." on my query, nor have I manually created my own plan table. The plan_table table is also empty.
    The plan comes from the global tempory table, accessed through the public synonym.
  • 7. Re: is plan_table necessary for an explain plan?
    jgarry Guru
    Currently Being Moderated
    Autotrace is doing it for you automatically. You don't see it by querying the sys.plan_table$ even in the same session because this sets a new environment. See http://tkyte.blogspot.com/2007/09/tuning-with-sqltracetrue.html for a bizarro example
  • 8. Re: is plan_table necessary for an explain plan?
    Padma.... Newbie
    Currently Being Moderated
    Hi,

    Autotrace on option gives the statistics and the explain plan after the statement finishes execution. The explain plan is the original plan used for execution of the corresponding query.

    Thanks
    Padma...
  • 9. Re: is plan_table necessary for an explain plan?
    Girish Sharma Guru
    Currently Being Moderated
    arizona9952 wrote:
    Hi, I've noticed that an explain plan for a query can be obtained by setting autotrace on. However, when I get a result from the autotrace, it shows no records in the plan_table table. I just want to check with others to make sure it's clear that a plan_table table isn't necessary to obtain an explain plan for a query. I've also noticed that when I hit the explain plan button in sql developer, there's no records in the plan_table table.
    What is your sql developer version ? If it is 3.0 then sql developer rollbacks the rows after fetching the plan for display. So you must rely on the Autotrace or Explain Plan result tabs.

    Similar discussion in below thread:
    Choose specific plan_table in SQL Developer 2+?

    Regards
    Girish Sharma
  • 10. Re: is plan_table necessary for an explain plan?
    636309 Newbie
    Currently Being Moderated
    Girish Sharma wrote:
    arizona9952 wrote:
    Hi, I've noticed that an explain plan for a query can be obtained by setting autotrace on. However, when I get a result from the autotrace, it shows no records in the plan_table table. I just want to check with others to make sure it's clear that a plan_table table isn't necessary to obtain an explain plan for a query. I've also noticed that when I hit the explain plan button in sql developer, there's no records in the plan_table table.
    What is your sql developer version ? If it is 3.0 then sql developer rollbacks the rows after fetching the plan for display. So you must rely on the Autotrace or Explain Plan result tabs.

    Similar discussion in below thread:
    Choose specific plan_table in SQL Developer 2+?

    Regards
    Girish Sharma
    Thanks Girish. I'm running SQL developer version 3.1, so that explains why the plan_table table shows empty after I run an explain plan in SQL developer.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points