/********在工具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.