1 2 3 Previous Next 35 Replies Latest reply: Apr 15, 2014 1:32 AM by Roger22 Go to original post RSS
      • 15. Re: plan_table
        rp0428
        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.

        Good points - but they assume that the default table is the one being used. I always suggest, as does Oracle, that orgs create and use their own plan tables.

         

        Doing so avoids the issues you mention.

        • 16. Re: plan_table
          Roger22

          Ok, this doesn't mean that AUTOTRACE does not store the plan in the PLAN_TABLE

          • 17. Re: plan_table
            Hemant K Chitale

            IF the PLAN_TABLE is a synonym to the GTT (Global Temporary Table) in the SYS schema, nothing is stored permanently in the PLAN_TABLE.   Whether it is EXPLAIN PLAN or DBMS_XPLAN.DISPLAY or DBMS_XPLAN.DISPLAY_CURSOR or AUTOTRACE.

             

            Hemant K Chitale


            • 18. Re: plan_table
              Roger22

              But not permanently.. in the current session only.

               

              If in the current session you issue

              set autotrace traceonly

              select * from ......

               

              , then the corresponding plan exists in the PLAN_TABLE (that session).

              • 19. Re: plan_table
                rp0428
                But not permanently.. in the current session only.

                 

                If in the current session you issue

                set autotrace traceonly

                select * from ......

                 

                , then the corresponding plan exists in the PLAN_TABLE (that session).

                What 'session' is 'current session' and 'that session'?

                 

                Did you read what was posted?

                AUTOTRACE opens another database session.

                That new session is NOT your 'current session'.

                • 20. Re: plan_table
                  sb92075

                  Roger22 wrote:

                   

                  Ok, this doesn't mean that AUTOTRACE does not store the plan in the PLAN_TABLE

                   

                  insert into plan_table (statement_id, timestamp, operation, options,

                    object_node, object_owner, object_name, object_instance, object_type,

                    search_columns, id, parent_id, position, other,optimizer, cost, cardinality,

                     bytes, other_tag, partition_start, partition_stop, partition_id,

                    distribution, cpu_cost, io_cost, temp_space, access_predicates,

                    filter_predicates, projection, time, qblock_name, object_alias, plan_id,

                    depth, remarks, other_xml )

                  values

                  (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,

                    :22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)

                  • 21. Re: plan_table
                    Roger22

                    As Martin said: "And, yes, autotrace uses the plan_table to store the plan (as mentioned in the articles I linked to)."

                    If autotrace opens a new database session, then the execution plan is stored in which session's PLAN TABLE? My session, or that one new opened?

                    • 22. Re: plan_table
                      sb92075

                      Roger22 wrote:

                       

                      As Martin said: "And, yes, autotrace uses the plan_table to store the plan (as mentioned in the articles I linked to)."

                      If autotrace opens a new database session, then the execution plan is stored in which session's PLAN TABLE? My session, or that one new opened?

                      I would like to see a reproducible test case that shows AUTOTRACE "opens a new database session".

                      • 23. Re: plan_table
                        Roger22

                        quote from an earlier post:

                         

                        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.

                         

                        I did not find anywhere on google that autotrace opens a new session. Now, reading all the recent posts, i'm more confused than I was initially....

                        • 24. Re: plan_table
                          sb92075

                          Roger22 wrote:

                           

                          quote from an earlier post:

                           

                          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.

                           

                          I did not find anywhere on google that autotrace opens a new session. Now, reading all the recent posts, i'm more confused that I was initially....

                          Anyone can state anything; but simply posting a statement does not necessarily make it true.

                           

                          The Earth is flat.

                          I can turn lead into gold.

                          My pet goat can fly.

                           

                          With free advice, you sometimes get what you paid for it.

                          • 25. Re: plan_table
                            Roger22

                            Then what's the truth in this case?

                            AUTOTRACE uses PLAN_TABLE to store the plan (also EXPLAIN PLAN FOR .... does the same thing). Where resides the PLAN TABLE? In the current session (the session from which I issued that sql statement)?

                            • 26. Re: plan_table
                              sb92075

                              Roger22 wrote:

                               

                              Then what's the truth in this case?

                              AUTOTRACE uses PLAN_TABLE to store the plan (also EXPLAIN PLAN FOR .... does the same thing). Where resides the PLAN TABLE? In the current session (the session from which I issued that sql statement)?

                              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.

                              • 27. Re: plan_table
                                ddf_dba_ifox

                                Oracle 11.2.0.3:

                                 

                                SQL> set autotrace on
                                SQL>SQL> select * From emp;
                                
                                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                                      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                                      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
                                      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                                      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                                      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                                      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                                      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                                      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
                                      7839 KING       PRESIDENT            17-NOV-81       5000                    10
                                      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                                      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
                                
                                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                                      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                                      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                                      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                                      7939 DUKE       CEO                  17-NOV-81       5000                    10
                                      7949 PRINCE     CFO                  17-NOV-81       5000                    10
                                      7959 QUEEN      CIO                  17-NOV-81       5000                    10
                                      7869 JACK       PRESIDENT            17-NOV-81       5000                    10
                                
                                18 rows selected.
                                
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 3956160932
                                
                                --------------------------------------------------------------------------
                                | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                                --------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT  |      |    18 |  1566 |     3   (0)| 00:00:01 |
                                |   1 |  TABLE ACCESS FULL| EMP  |    18 |  1566 |     3   (0)| 00:00:01 |
                                --------------------------------------------------------------------------
                                
                                Note
                                -----
                                   - dynamic sampling used for this statement (level=2)
                                   - SQL plan baseline "SQL_PLAN_62augpr4824hyd8a279cc" used for this statement
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          5  recursive calls
                                          0  db block gets
                                         17  consistent gets
                                          0  physical reads
                                          0  redo size
                                       1847  bytes sent via SQL*Net to client
                                        530  bytes received via SQL*Net from client
                                          3  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                         18  rows processed
                                
                                SQL>SQL> select * From plan_table;
                                
                                no rows selected
                                
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 103984305
                                
                                ---------------------------------------------------------------------------------
                                | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
                                ---------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT  |             |  8168 |    86M|     2   (0)| 00:00:01 |
                                |   1 |  TABLE ACCESS FULL| PLAN_TABLE$ |  8168 |    86M|     2   (0)| 00:00:01 |
                                ---------------------------------------------------------------------------------
                                
                                Note
                                -----
                                   - SQL plan baseline "SQL_PLAN_cmqgrmfwuaf77d250dffc" used for this statement
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          5  recursive calls
                                          4  db block gets
                                          4  consistent gets
                                          0  physical reads
                                          0  redo size
                                       3019  bytes sent via SQL*Net to client
                                        755  bytes received via SQL*Net from client
                                          2  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                          0  rows processed
                                
                                SQL>SQL> set autotrace traceonly
                                SQL>SQL> select * From emp;
                                
                                18 rows selected.
                                
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 3956160932
                                
                                --------------------------------------------------------------------------
                                | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                                --------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT  |      |    18 |  1566 |     3   (0)| 00:00:01 |
                                |   1 |  TABLE ACCESS FULL| EMP  |    18 |  1566 |     3   (0)| 00:00:01 |
                                --------------------------------------------------------------------------
                                
                                Note
                                -----
                                   - dynamic sampling used for this statement (level=2)
                                   - SQL plan baseline "SQL_PLAN_62augpr4824hyd8a279cc" used for this statement
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          0  recursive calls
                                          0  db block gets
                                          9  consistent gets
                                          0  physical reads
                                          0  redo size
                                       1847  bytes sent via SQL*Net to client
                                        530  bytes received via SQL*Net from client
                                          3  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                         18  rows processed
                                
                                SQL>SQL> select * From plan_table;
                                
                                no rows selected
                                
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 103984305
                                
                                ---------------------------------------------------------------------------------
                                | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
                                ---------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT  |             |  8168 |    86M|     2   (0)| 00:00:01 |
                                |   1 |  TABLE ACCESS FULL| PLAN_TABLE$ |  8168 |    86M|     2   (0)| 00:00:01 |
                                ---------------------------------------------------------------------------------
                                
                                Note
                                -----
                                   - SQL plan baseline "SQL_PLAN_cmqgrmfwuaf77d250dffc" used for this statement
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          0  recursive calls
                                          0  db block gets
                                          3  consistent gets
                                          0  physical reads
                                          0  redo size
                                       3019  bytes sent via SQL*Net to client
                                        755  bytes received via SQL*Net from client
                                          2  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                          0  rows processed
                                
                                SQL>

                                 

                                No data exists in PLAN_TABLE immediately after the autotrace report is generated, for the case where autotrace is ON and the case where autotrace is TRACEONLY.

                                 

                                Interesting how an example proves that.

                                 

                                 

                                David Fitzjarrell

                                • 28. Re: plan_table
                                  sb92075

                                  I am unclear what exactly has been proven.

                                   

                                  >Interesting how an example proves that.

                                  to what does "that" (above) refer?

                                  • 29. Re: plan_table
                                    ddf_dba_ifox

                                    Did  you not read through the entire example and see the two "select * From plan_table;" queries that produced "no rows selected"?  Those queries ran immediately after the autotrace output was displayed.  If PLAN_TABLE was a 'standard' table the generated output would remain after autotrace had reported its results.  It doesn't, and it is a GTT that won't preserve rows on commit.

                                     

                                    David Fitzjarrell