--根据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