cannot fetch pln for SQL_ID

SQL tuningの過程ではSQL文を分析する実行計画と切り離せない。一回で実行計画を抽出する時cannot fetch pln for SQL_に会います。IDのエラーメッセージ。エラーメッセージによると、SQLのサブラベルまたは実行計画がv$sql_にないか確認する必要があります。このような状況は一般的に存在しません。先ほど実行したSQL文はこんなに速くはv$sql_からできませんから。プレーン除去。エラーの説明と処理方法を説明します。

1、    
-->  display_cursor        
  admin@CADB> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
  
  PLAN_TABLE_OUTPUT
  ---------------------------------------------------------------------------------------------------
  SQL_ID  9babjv8yq8ru3, child number 3
  
  BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
  
  NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 3
        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)

-->  serveroutput   ，   ON，    dbms_xplan.display_cursor，      
  scott@CADB> show serveroutput
  serveroutput ON SIZE 1000000 FORMAT WORD_WRAPPED
  scott@CADB> select /*+ gather_plan_statistics */ * from dept;
  
      DEPTNO DNAME          LOC
  ---------- -------------- -------------
          10 ACCOUNTING     NEW YORK
          20 RESEARCH       DALLAS
          30 SALES          CHICAGO
          40 OPERATIONS     BOSTON
          50 DEVELOPE       CHINA
  
  scott@CADB> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
  
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------
  SQL_ID  9babjv8yq8ru3, child number 2
  
  BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
  
  NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 2
        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)

2、    
-->  serveroutput  
  scott@CADB> set serveroutput off;
  scott@CADB> select /*+ gather_plan_statistics */ * from dept;
  
      DEPTNO DNAME          LOC
  ---------- -------------- -------------
          10 ACCOUNTING     NEW YORK
          20 RESEARCH       DALLAS
          30 SALES          CHICAGO
          40 OPERATIONS     BOSTON
          50 DEVELOPE       CHINA

  /**************************************************/
  /* Author: Robinson Cheng                         */
  /* Blog:   http://blog.csdn.net/robinson_0612     */
  /* MSN:    [email protected]              */
  /* QQ:     645746311                              */
  /**************************************************/
  
-->          
  scott@CADB> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
  
  PLAN_TABLE_OUTPUT
  ------------------------------------------------------------------------------------------------
  SQL_ID  08u3pfapxj6g5, child number 0
  -------------------------------------
  select /*+ gather_plan_statistics */ * from dept
  
  Plan hash value: 3383998547
  
  ------------------------------------------------------------------------------------
  | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
  ------------------------------------------------------------------------------------
  |   1 |  TABLE ACCESS FULL| DEPT |      1 |      5 |      5 |00:00:00.01 |       8 |
  ------------------------------------------------------------------------------------
  
  12 rows selected.  

-->    explain plan       plan_table，    dbms_xplan.display      
-->         ，serveroutput    plan_table      
  scott@CADB> show serveroutput
  serveroutput ON SIZE 1000000 FORMAT WORD_WRAPPED
  scott@CADB> explain plan for select count(*) from emp;
  
  Explained.
  
  scott@CADB> select * from table(dbms_xplan.display);    
  
  PLAN_TABLE_OUTPUT
  -----------------------------------------------------------------------------------------------------------------------------------
  Plan hash value: 1858788047
  
  -------------------------------------------------------------------------
  | Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------
  |   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |
  |   1 |  SORT AGGREGATE  |              |     1 |            |          |
  |   2 |   INDEX FULL SCAN| I_EMP_DEPTNO |    14 |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------
  
  9 rows selected.

-->    DBA login.sql   glogin.sql     serveroutput ON，     PL/SQL      ，    SQL      ，  
-->                。  serveroutput    plan_table      。

計画実行に関するリンク：
dbms_xplanのdisplaycursor関数の使用
dbms_xplanのdisplay関数の使用
実行計画の各フィールドのモジュール説明
EXPLAN PLANを使ってSQL文を取得して実行計画を実行します。 
AUTOTRACE機能を有効にします。
 
原文のリンク:http://blog.csdn.net/robinson_0612/articale/detail/7178504