1 2 3 Previous Next 35 Replies Latest reply: Apr 15, 2014 1:32 AM by Roger22 RSS

    plan_table

    Roger22

      The plan_table is populated ONLY if we issue EXPLAIN PLAN FOR <select statement>, or it contains the execution plan even if we set SET AUTOTRACE TRACEONLY?

      I know the latter command just displays the plan without returning the result set. But in the case of SET AUTOTRACE TRACEONLY, is the execution plan stored also in the plan_table?

      In the docs i found that "The PLAN_TABLE is automatically created as a public synonym to a global temporary table. This temporary table holds the output of EXPLAIN PLAN statements for all users."

      Also, if we set SET AUTOTRACE TRACEONLY, I have read that the plan generated, might not be used by the optimizer when using bind peeking (EXPLAIN PLAN). But if we not use EXPLAIN PLAN, is the plan generated by SET AUTOTRACE TRACEONLY used by the optimizer?

       

      Thank you.

       

       

      edit: i'm connected with "system" user, and why I did not see the correct plan table output?

       

      SQL> explain plan for select * from hr.employees;

       

      Explained.

       

      Elapsed: 00:00:00.01

      SQL> select * from table(dbms_xplan.display());

       

      8 rows selected.

       

      Elapsed: 00:00:00.03

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 2137789089

       

      ---------------------------------------------------------------------------------------------

      | Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

      ---------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |

      |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |

      ---------------------------------------------------------------------------------------------

       

       

      Statistics

      ----------------------------------------------------------

               13  recursive calls

               12  db block gets

               53  consistent gets

                0  physical reads

                0  redo size

             1003  bytes sent via SQL*Net to client

              419  bytes received via SQL*Net from client

                2  SQL*Net roundtrips to/from client

                1  sorts (memory)

                0  sorts (disk)

                8  rows processed

        • 1. Re: plan_table
          sb92075

          How do I ask a question on the forums?

          https://forums.oracle.com/message/9362002#9362002

          • 2. Re: plan_table
            Roger22

            My question is clear and properly asked. I know you understand what i'm asking

            • 3. Re: plan_table
              Martin Preiss

              the plan you get here from autotrace is the plan for the query:

              select * from table(dbms_xplan.display());

              When you use autotrace you don't need the explicit explain plan for your query since AUTOTRACE implicitely issues the plan creation and rendering (with explain plan and dbms_xplan.display: Tuning SQL*Plus). Jonathan Lewis just started an series of articles to explain where to find and how to interpret execution plans on http://allthingsoracle.com/execution-plans-part-1-finding-plans/.

              • 4. Re: plan_table
                sb92075

                Roger22 wrote:

                 

                My question is clear and properly asked. I know you understand what i'm asking

                Properly asked?

                Only if the person to answer is behind you can see details you neglected to provide.

                might the answer & Oracle's behavior be version specific?

                • 5. Re: plan_table
                  Roger22

                  And in my case, why select * from table(dbms_xplan.display()); didn't displayed the plan which belongs to the query issued (that from hr.employees table).

                  So, does SET AUTOTRACE TRACEONLY store the dislpayed plan in PLAN_TABLE?

                  • 6. Re: plan_table
                    sb92075

                    Roger22 wrote:

                     

                    And in my case, why select * from table(dbms_xplan.display()); didn't displayed the plan which belongs to the query issued (that from hr.employees table).

                    So, does SET AUTOTRACE TRACEONLY store the dislpayed plan in PLAN_TABLE?

                    In  you showed any initiative, you would

                    ALTER SESSION SET SQL_TRACE=TRUE

                    to capture exactly & completely all SQL executed.

                     

                    >So, does SET AUTOTRACE TRACEONLY store the dislpayed plan in PLAN_TABLE?

                    YES!

                    • 7. Re: plan_table
                      Martin Preiss

                      the traceonly option of autotrace suppresses the query output and shows only the plan - so you don't see the output of the table function with the plan for the employee access (but only the plan for this operation). And, yes, autotrace uses the plan_table to store the plan (as mentioned in the articles I linked to).

                      • 8. Re: plan_table
                        Roger22

                        Thanks, guys!

                        I tried a simple example to see if my query's plan is in the PLAN_TABLE. So:

                         

                        C:\Documents and Settings\Sorin>sqlplus sh/sh@orcl

                         

                        SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 13 21:49:18 2014

                         

                        Copyright (c) 1982, 2011, Oracle.  All rights reserved.

                         

                         

                        Connected to:

                        Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

                        With the Partitioning, Oracle Label Security, OLAP, Data Mining

                        and Real Application Testing options

                         

                        SQL> set autotrace traceonly

                        SQL> select * from sales;

                         

                        918843 rows selected.

                         

                        Elapsed: 00:00:21.43

                         

                        Execution Plan

                        ----------------------------------------------------------

                        Plan hash value: 1550251865

                         

                        ---------------------------------------------------------------------------------------------

                        | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                        ---------------------------------------------------------------------------------------------

                        |   0 | SELECT STATEMENT    |       |   918K|    25M|   526   (2)| 00:00:07 |       |       |

                        |   1 |  PARTITION RANGE ALL|       |   918K|    25M|   526   (2)| 00:00:07 |     1 |    28 |

                        |   2 |   TABLE ACCESS FULL | SALES |   918K|    25M|   526   (2)| 00:00:07 |     1 |    28 |

                        ---------------------------------------------------------------------------------------------

                         

                         

                        Statistics

                        ----------------------------------------------------------

                                  1  recursive calls

                                  0  db block gets

                              62795  consistent gets

                               1619  physical reads

                                  0  redo size

                           33489026  bytes sent via SQL*Net to client

                             674235  bytes received via SQL*Net from client

                              61258  SQL*Net roundtrips to/from client

                                  0  sorts (memory)

                                  0  sorts (disk)

                             918843  rows processed

                         

                        SQL> select * from table(dbms_xplan.display());

                         

                        Elapsed: 00:00:00.01

                         

                        Execution Plan

                        ----------------------------------------------------------

                        Plan hash value: 2137789089

                         

                        ---------------------------------------------------------------------------------------------

                        | Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

                        ---------------------------------------------------------------------------------------------

                        |   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |

                        |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |

                        ---------------------------------------------------------------------------------------------

                         

                         

                        Statistics

                        ----------------------------------------------------------

                                 10  recursive calls

                                  0  db block gets

                                  3  consistent gets

                                  0  physical reads

                                  0  redo size

                                482  bytes sent via SQL*Net to client

                                419  bytes received via SQL*Net from client

                                  2  SQL*Net roundtrips to/from client

                                  1  sorts (memory)

                                  0  sorts (disk)

                                  1  rows processed

                         

                        SQL>


                        The select * from table(dbms_xplan.display()); , displayed that COLLECTION ITERATOR, which is not the actual execution plan of the latest query.

                        According to COLLECTION ITERATOR (PICKLER FETCH) Appearing in DBMS_XPLAN Output | Charles Hooper&amp;#039;s Oracle Notes , "the problem is caused by either enabling server output, or enabling SQL*Plus’ autotrace." (so in my case i have enabled autotrace before).

                        If SET AUTOTRACE TRACEONLY stores the execution plan in PLAN_TABLE, why I cannot see it?

                         

                        Not the same thing happens with EXPLAIN PLAN. In this case, I can see the correct execution plan with select * from table(dbms_xplan.display());

                         

                        SQL> set autotrace off

                        SQL> explain plan for select * from sales;

                         

                        Explained.

                         

                        Elapsed: 00:00:00.03

                        SQL> select * from table(dbms_xplan.display());

                         

                        PLAN_TABLE_OUTPUT

                        ----------------------------------------------------------------------------------------------------

                        ----------------------------------------------------------------------------------------------------

                        Plan hash value: 1550251865

                         

                        ---------------------------------------------------------------------------------------------

                        | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                        ---------------------------------------------------------------------------------------------

                        |   0 | SELECT STATEMENT    |       |   918K|    25M|   526   (2)| 00:00:07 |       |       |

                        |   1 |  PARTITION RANGE ALL|       |   918K|    25M|   526   (2)| 00:00:07 |     1 |    28 |

                        |   2 |   TABLE ACCESS FULL | SALES |   918K|    25M|   526   (2)| 00:00:07 |     1 |    28 |

                        ---------------------------------------------------------------------------------------------

                         

                        9 rows selected.

                         

                        Elapsed: 00:00:00.12

                        SQL>

                        • 9. Re: plan_table
                          sb92075

                          If  you showed any initiative, you would

                          ALTER SESSION SET SQL_TRACE=TRUE

                          to capture exactly & completely all SQL executed.

                          Then you could see for yourself all the SQL issued by your session.

                          • 10. Re: plan_table
                            Roger22

                            Ok, but I'm asking something different.. if autotrace stores the execution plan in PLAN_TABLE, how can I see it (this way)? Without setting SQL_TRACE=true

                             

                            And is the data still fetched when using SET AUTOTRACE TRACEONLY? I have read that "If STATISTICS is enabled, the query data is still fetched, but not printed".

                            SET AUTOTRACE TRACEONLY does automatically enable STATISTICS? I think that yes, because statistics are also dislpayed (there is another command, SET AUTOTRACE ON STATISTICS, which displays ONLY statistics, not the execution plan too).

                            • 11. Re: plan_table
                              sb92075

                              you would have the answer to your questions if you would

                              ALTER SESSION SET SQL_TRACE=TRUE

                              & then inspect the content of the trace file.


                              The choice is yours.

                              • 12. Re: plan_table
                                rp0428

                                The plan_table is populated ONLY if we issue EXPLAIN PLAN FOR <select statement>, or it contains the execution plan even if we set SET AUTOTRACE TRACEONLY?

                                I know the latter command just displays the plan without returning the result set. But in the case of SET AUTOTRACE TRACEONLY, is the execution plan stored also in the plan_table?

                                . . .

                                So, does SET AUTOTRACE TRACEONLY store the dislpayed plan in PLAN_TABLE?

                                . . .

                                Ok, but I'm asking something different.. if autotrace stores the execution plan in PLAN_TABLE, how can I see it (this way)?

                                I think part of the reason you are getting the answers you are getting is because there seems to be a lack of effort on your part to even TRY to answer your own questions before you post them here.

                                 

                                Let me state your question a slightly different way and see if you can understand what I mean.

                                 

                                1. You have a table whose name is 'TableForRoger22.

                                2. You execute some command on your system

                                3. Your question is: did the command you just executed stored ANY ROWS in the table named 'TableForRoger22'.

                                 

                                What is your answer?

                                 

                                Can you think of ANY way at all that you, on your own, using your own system, can determine if there are ANY ROWS in the table named 'TableForRoger22'?

                                 

                                SPOILER ALERT - The answer is given below. Do NOT read it until you TRY that thought experiment.

                                 

                                END OF SPOILER ALERT

                                 

                                Answer - Query the table named 'TableForRoger22' to see if it has the rows you are looking for.

                                 

                                If you reread your entire thread (just what you posted yourself) see if you can follow these clues to the answer to your questions:

                                The plan_table is populated ONLY . . .

                                Clue #1 - you KNOW that there is a table/synonym/object named 'PLAN_TABLE'

                                SQL> explain plan for select * from hr.employees;

                                Clue #2 - you know how to write a query that SELECTs from the 'hr.employees' table.

                                 

                                Question - can you think of ANY way to use a similar query that SELECTs from the PLAN_TABLE'?

                                 

                                Was this your answer?

                                SELECT * FROM PLAN_TABLE

                                Now for the hard part: you need to actually EXECUTE the query!

                                 

                                Post your results.

                                 

                                ---------

                                Updated to respond to Hemant's comments.

                                ---------

                                You should use your own plan table and NOT the GTT provided by Oracle. That avoids the issues that Hemant mentioned. See the docs.

                                 

                                http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#sthref1052

                                The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

                                . . .

                                Oracle recommends that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.

                                . . .

                                12.3.2 Specifying Different Tables for EXPLAIN PLAN

                                You can specify the INTO clause to specify a different table.

                                Example 12-4 Using EXPLAIN PLAN with the INTO Clause

                                EXPLAIN PLAN  INTO my_plan_table  FOR  SELECT last_name FROM employees;  

                                You can specify a statement ID when using the INTO clause.

                                EXPLAIN PLAN  SET STATEMENT_ID = 'st1'  INTO my_plan_table  FOR  SELECT last_name FROM employees;  

                                As section 12.4 explains the DBMS_XPLAN.DISPLAY table functions allows you to specify your own table. This is what you should be doing.

                                DBMS_XPLAN.DISPLAY table function

                                This function accepts options for displaying the plan table output. You can specify:

                                •   A plan table name if you are using a table different than PLAN_TABLE
                                •   A statement ID if you have set a statement ID with the EXPLAIN PLAN
                                •   A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

                                Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

                                SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

                                SELECT PLAN_TABLE_OUTPUT  FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL')); 
                                 
                                • 13. Re: plan_table
                                  Hemant K Chitale

                                  Two pointers :

                                  1.  Now PLAN_TABLE is a GTT.  So it doesn't preserve rows.

                                  2.  AUTOTRACE opens another database session.

                                   

                                  GTT data of one session isn't visible in another session.

                                   

                                  Hemant K Chitale

                                  • 14. Re: plan_table
                                    sb92075

                                    Hemant K Chitale wrote:

                                     

                                    Two pointers :

                                    1.  Now PLAN_TABLE is a GTT.  So it doesn't preserve rows.

                                    2.  AUTOTRACE opens another database session.

                                     

                                    GTT data of one session isn't visible in another session.

                                     

                                    Hemant K Chitale

                                     

                                    Neither of the above is the reason why OP has been unsuccessful in SELECT * from PLAN_TABLE..

                                     

                                    again & still the answer is clearly evident within the trace file.

                                    1 2 3 Previous Next