10 Replies Latest reply: Jan 28, 2013 10:43 PM by 636309 RSS

    is plan_table necessary for an explain plan?

    636309
      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
          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
            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
              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
                "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
                  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
                    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
                      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....
                        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
                          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
                            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.