3 Replies Latest reply: Oct 9, 2013 7:06 AM by user10274093 RSS

    cannot fetch plan for SQL_ID

    user10274093

      Hi all,

      and Thanks for help.

      on 11.2.0.3 on Win 2008 server I run

       

      explain plan for

      select machine, sid, osuser from v$session ;

      select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

       

      And I receive :

      NOTE: cannot fetch plan for SQL_ID: 382zzpu0dtfwn, CHILD_NUMBER: 2                                                                                                                                                                                                                                          

      Please verify value of SQL_ID and CHILD_NUMBER;

       

      But if I run

      explain plan for

      select machine, sid, osuser from v$session ;

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

       

      It would be OK and I have EXPLAIN PLAN :

       

       

      Plan hash value: 644658511                                                                                                                                                                                                                                                                                  

                                                                                                                                                                                                                                                                                                                  

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

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

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

      |   0 | SELECT STATEMENT          |                 |     1 |   142 |     0   (0)| 00:00:01 |                                                                                                                                                                                                               

      |   1 |  NESTED LOOPS             |                 |     1 |   142 |     0   (0)| 00:00:01 |                                                                                                                                                                                                               

      |   2 |   NESTED LOOPS            |                 |     1 |   129 |     0   (0)| 00:00:01 |                                                                                                                                                                                                               

      |*  3 |    FIXED TABLE FULL       | X$KSUSE         |     1 |   103 |     0   (0)| 00:00:01 |                                                                                                                                                                                                               

      |*  4 |    FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |    26 |     0   (0)| 00:00:01 |                                                                                                                                                                                                               

      |*  5 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |    13 |     0   (0)| 00:00:01 |                                                                                                                                                                                                               

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

       

      What is the problem ? Thanks for help.                                                                                                                                                                                  

        • 1. Re: cannot fetch plan for SQL_ID
          Hoek

          The problem could be SERVEROUTPUT being set to ON:

           

          SQL> select /*+ gather_plan_statistics */ * from dual;

           

          D

          -

          X

           

          1 row selected.

           

          SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

           

          PLAN_TABLE_OUTPUT

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

          SQL_ID  9babjv8yq8ru3, child number 0

           

          BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

           

          NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0

                Please verify value of SQL_ID and CHILD_NUMBER;

                It could also be that the plan is no longer in cursor cache (check v$sql_plan)

           

           

          8 rows selected.

           

          SQL> sho serverout

          serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

          SQL> set serverout off

          SQL> sho serverout

          serveroutput OFF

          SQL> select /*+ gather_plan_statistics */ * from dual;

           

          D

          -

          X

           

          1 row selected.

           

          SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

           

          PLAN_TABLE_OUTPUT

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

          SQL_ID  437sjd08wq9f6, child number 0

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

          select /*+ gather_plan_statistics */ * from dual

           

          Plan hash value: 272002086

           

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

          | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

          |   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       2 |

          |   1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |      1 |00:00:00.01 |       2 |

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

           

           

          13 rows selected.

          • 2. Re: cannot fetch plan for SQL_ID
            Dom Brooks

            You are mixing up your methods.

             

            Sql id 382zzpu0dtfwn is literally

            explain plan for
            select machine, sid, osuser from v$session ;
            
            

             

            When you do an explain plan, then the appropriate DBMS_XPLAN api is DBMS_XPLAN.DISPLAY.

             

            When you do an actual execution - i.e. not with explain plan - then DBMS_XPLAN.DISPLAY_CURSOR is the appropriate api.

             

            If you want the actual execution statistics, then you have to actually execute it AND you need statistics_level of all or the gather_plan_statistics hint

             

            explain plan for
            select machine, sid, osuser from v$session where 1=0;
            
            plan FOR succeeded.
            
            select * from table(dbms_xplan.display);
            
            PLAN_TABLE_OUTPUT 
            -------------------
            Plan hash value: 3405918260
            
            ----------------------------------------------------------------------------------------------
            | Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
            ---------------------------------------------------------------------------------------------- 
            |   0 | SELECT STATEMENT           |                 |     1 |   142 |     0   (0)|          |  
            |*  1 |  FILTER                    |                 |       |       |            |          | 
            |   2 |   NESTED LOOPS             |                 |     1 |   142 |     0   (0)| 00:00:01 |
            |   3 |    NESTED LOOPS            |                 |     1 |   129 |     0   (0)| 00:00:01 |
            |*  4 |     FIXED TABLE FULL       | X$KSUSE         |     1 |   103 |     0   (0)| 00:00:01 |
            |*  5 |     FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |    26 |     0   (0)| 00:00:01 | 
            |*  6 |    FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |    13 |     0   (0)| 00:00:01 |
            ----------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               1 - filter(NULL IS NOT NULL)
               4 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
                          BITAND("S"."KSUSEFLG",1)<>0)
               5 - filter("S"."INDX"="W"."KSLWTSID")
               6 - filter("W"."KSLWTEVT"="E"."INDX")
            
            22 rows selected
            
            alter session set statistics_level = all;
            
            session SET altered.
            
            select machine, sid, osuser from v$session where 1=0;
            
            no rows selected
            
            select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
            
            PLAN_TABLE_OUTPUT 
            -------------------
            SQL_ID  6wq0xktdw9kxz, child number 0 
            ------------------------------------- 
            select machine, sid, osuser from v$session where 1=0 
            
            Plan hash value: 3405918260
            
            ----------------------------------------------------------------------------------------------
            | Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   | 
            ----------------------------------------------------------------------------------------------  
            |   0 | SELECT STATEMENT           |                 |      1 |        |      0 |00:00:00.01 |   
            |*  1 |  FILTER                    |                 |      1 |        |      0 |00:00:00.01 |    
            |   2 |   NESTED LOOPS             |                 |      0 |      1 |      0 |00:00:00.01 |     
            |   3 |    NESTED LOOPS            |                 |      0 |      1 |      0 |00:00:00.01 |      
            |*  4 |     FIXED TABLE FULL       | X$KSUSE         |      0 |      1 |      0 |00:00:00.01 |       
            |*  5 |     FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |      0 |      1 |      0 |00:00:00.01 | 
            |*  6 |    FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |      0 |      1 |      0 |00:00:00.01 | 
            ----------------------------------------------------------------------------------------------  
            
            Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
            ---------------------------------------------------                                                                                                                                                                                                                                                         
            
               1 - filter(NULL IS NOT NULL)
               4 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND 
                          BITAND("S"."KSUSEFLG",1)<>0)) 
               5 - filter("S"."INDX"="W"."KSLWTSID") 
               6 - filter("W"."KSLWTEVT"="E"."INDX") 
            
            27 rows selected
            
            
            
            • 3. Re: cannot fetch plan for SQL_ID
              user10274093

              Yes thank you :

               

              When you do an actual execution - i.e. not with explain plan - then DBMS_XPLAN.DISPLAY_CURSOR is the appropriate api.