--根据sql_id 查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',SQL_CHILD_NUMBER)); select sql_id, sql_text, version_count, loads, hash_value, address, plan_hash_value, old_hash_value, last_active_child_address from v$sqlarea a where a.sql_id='6k78zb0r1y6fk' SQL_ID 6k78zb0r1y6fk SQL_TEXT SELECT COUNT(1) FROM BP_TRANSLIST_TB A, BP_TASKSTATE_TB B WHERE A.DOC_ID = :B1 AND B.TASK_STATE < '4' AND A.TASK_ID = B.TASK_ID VERSION_COUNT 8 LOADS 2 HASH_VALUE 773790162 ADDRESS 0000001A0CC87FE8 PLAN_HASH_VALUE 3533317669 OLD_HASH_VALUE 2641330776 LAST_ACTIVE_CHILD_ADDRESS 0000001CA2021718 select sql_id, sql_text, a.loaded_versions, hash_value, address, a.old_hash_value, a.plan_hash_value, a.child_number, a.child_address from v$sql a where a.sql_id='6k78zb0r1y6fk' SQL_ID 6k78zb0r1y6fk SQL_TEXT SELECT COUNT(1) FROM BP_TRANSLIST_TB A, BP_TASKSTATE_TB B WHERE A.DOC_ID = :B1 AND B.TASK_STATE < '4' AND A.TASK_ID = B.TASK_ID LOADED_VERSIONS 1 HASH_VALUE 773790162 ADDRESS 0000001A0CC87FE8 OLD_HASH_VALUE 2641330776 PLAN_HASH_VALUE 3533317669 CHILD_NUMBER 2 CHILD_ADDRESS 0000001CA2021718 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6k78zb0r1y6fk',2)); SQL_ID 6k78zb0r1y6fk, child number 2 ------------------------------------- SELECT COUNT(1) FROM BP_TRANSLIST_TB A, BP_TASKSTATE_TB B WHERE A.DOC_ID = :B1 AND B.TASK_STATE < '4' AND A.TASK_ID = B.TASK_ID Plan hash value: 3533317669 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 19 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 70 | | | | | | 2 | NESTED LOOPS | | 4 | 280 | 19 (0)| 00:00:01 | | | | 3 | NESTED LOOPS | | 4 | 280 | 19 (0)| 00:00:01 | | | | 4 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| BP_TRANSLIST_TB | 4 | 160 | 7 (0)| 00:00:01 | ROWID | ROWID | |* 5 | INDEX RANGE SCAN | BP_TRANSLIST_IDX6 | 4 | | 3 (0)| 00:00:01 | | | |* 6 | INDEX RANGE SCAN | BP_TASKSTATE_TB_01 | 1 | | 2 (0)| 00:00:01 | | | |* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID | BP_TASKSTATE_TB | 1 | 30 | 3 (0)| 00:00:01 | ROWID | ROWID | ------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("A"."DOC_ID"=:B1) 6 - access("A"."TASK_ID"="B"."TASK_ID") 7 - filter("B"."TASK_STATE"<4) SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6k78zb0r1y6fk',7)); PLAN_TABLE_OUTPUT SQL_ID 6k78zb0r1y6fk, child number 7 ------------------------------------- SELECT COUNT(1) FROM BP_TRANSLIST_TB A, BP_TASKSTATE_TB B WHERE A.DOC_ID = :B1 AND B.TASK_STATE < '4' AND A.TASK_ID = B.TASK_ID Plan hash value: 3074402375 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2127 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 70 | | | | | | 2 | NESTED LOOPS | | 4 | 280 | 2127 (0)| 00:00:01 | | | | 3 | NESTED LOOPS | | 4 | 280 | 2127 (0)| 00:00:01 | | | | 4 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| BP_TRANSLIST_TB | 4 | 160 | 7 (0)| 00:00:01 | ROWID | ROWID | |* 5 | INDEX RANGE SCAN | BP_TRANSLIST_IDX6 | 4 | | 3 (0)| 00:00:01 | | | | 6 | PARTITION RANGE ALL | | 1 | | 529 (0)| 00:00:01 | 1 |1048575| |* 7 | INDEX RANGE SCAN | BP_TASKSTATE_PK | 1 | | 529 (0)| 00:00:01 | 1 |1048575| |* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | BP_TASKSTATE_TB | 1 | 30 | 530 (0)| 00:00:01 | 1 | 1 | ----------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("A"."DOC_ID"=:B1) 7 - access("A"."TASK_ID"="B"."TASK_ID") 8 - filter("B"."TASK_STATE"<4) Note ----- - SQL profile 6K78ZB0R1Y6FK_3074402375 used for this statement