NOTE: cannot fetch plan for SQL_ID_在plsql developer无法获取sql执行计划

/********在工具plsql developer*******查看执行sql的执行计划出错************/
SQL> select count(a) from t_policy;
 
  COUNT(A)
----------
    100000
 
SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9m7787camwh4m, child number 4
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 4
      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_p
 
8 rows selected

/******查看上述报错的sql_id****************/
SQL> select sql_text,parse_calls,executions,version_count from v$sqlarea where sql_id='9m7787camwh4m';
 
SQL_TEXT                                                                         PARSE_CALLS EXECUTIONS VERSION_COUNT
-------------------------------------------------------------------------------- ----------- ---------- -------------
begin :id := sys.dbms_transaction.local_transaction_id; end;                             585        585             6

/******************发现报错的sql_id是plsql developer自行封装的plsql 代码块语句，而非最上面真正执行的sql*************/
SQL> select sql_text,executions,version_count,child_number from v$sql where sql_id='9m7787camwh4m';
 
select sql_text,executions,version_count,child_number from v$sql where sql_id='9m7787camwh4m'
 
ORA-00904: "VERSION_COUNT": invalid identifier
 
SQL> select sql_text,executions,child_number from v$sql where sql_id='9m7787camwh4m';
 
SQL_TEXT                                                                         EXECUTIONS CHILD_NUMBER
-------------------------------------------------------------------------------- ---------- ------------
begin :id := sys.dbms_transaction.local_transaction_id; end;                            347            0
begin :id := sys.dbms_transaction.local_transaction_id; end;                             87            1
begin :id := sys.dbms_transaction.local_transaction_id; end;                              5            2
begin :id := sys.dbms_transaction.local_transaction_id; end;                              3            3
begin :id := sys.dbms_transaction.local_transaction_id; end;                            117            4
begin :id := sys.dbms_transaction.local_transaction_id; end;                             30            5
 
6 rows selected
 
SQL>

/******上述同样的sql在sqlplus中运行提取执行计划一切正常**************/
SQL> select /*aa*/  count(a) from t_policy;

  COUNT(A)
----------
    100000

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

SQL_ID  azgpcazx5tn56, child number 0
-------------------------------------
select /*aa*/  count(a) from t_policy

Plan hash value: 1176293347

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

------

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

     |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

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

------

|   0 | SELECT STATEMENT      |              |       |       |    66 (100)|
     |

|   1 |  SORT AGGREGATE       |              |     1 |    13 |            |
     |

|   2 |   INDEX FAST FULL SCAN| IDX_T_POLICY | 96905 |  1230K|    66   (2)| 00:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

0:01 |

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

------

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

 

 

 

 

 

 

 

 

SQL> select /*aa*/  count(a) from t_policy;

  COUNT(A)
----------
    100000

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

SQL_ID  azgpcazx5tn56, child number 0
-------------------------------------
select /*aa*/  count(a) from t_policy

Plan hash value: 1176293347

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

------

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

     |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

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

------

|   0 | SELECT STATEMENT      |              |       |       |    66 (100)|
     |

|   1 |  SORT AGGREGATE       |              |     1 |    13 |            |
     |

|   2 |   INDEX FAST FULL SCAN| IDX_T_POLICY | 96905 |  1230K|    66   (2)| 00

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

0:01 |

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

------

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-757283/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-757283/