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.