SQL> set linesize 200
SQL> set pagesize 200
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
会话已更改。
SQL> select sql_text
from v$sqlarea
where (address, hash_value) in
(select DECODE(sql_hash_value, 0, prev_sql_addr, sql_address),
DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value)
from v$session
where sid =
(select sid
from v$session
where paddr = (select ADDR from v$process where SPID = '9647'))); 2 3 4 5 6 7 8 9 10
未选定行
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3uqsxsu1pxthv, child number 0
-------------------------------------
select sql_text from v$sqlarea where (address, hash_value) in
(select DECODE(sql_hash_value, 0, prev_sql_addr, sql_address),
DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value)
from v$session where sid = (select sid
from v$session where paddr = (select ADDR
from v$process where SPID = '9647')))
Plan hash value: 1681910329
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.70 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.70 |
|* 2 | FIXED TABLE FULL | X$KGLCURSOR_CHILD_SQLID | 1 | 1 | 836 |00:00:00.03 |
| 3 | NESTED LOOPS | | 836 | 1 | 0 |00:00:00.66 |
| 4 | NESTED LOOPS | | 836 | 1 | 0 |00:00:00.66 |
|* 5 | FIXED TABLE FULL | X$KSUSE | 836 | 1 | 0 |00:00:00.66 |
| 6 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 |
| 7 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 |
|* 8 | FIXED TABLE FULL | X$KSUSE | 1 | 1 | 0 |00:00:00.01 |
|* 9 | FIXED TABLE FULL | X$KSUPR | 1 | 1 | 0 |00:00:00.01 |
|* 10 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 0 | 1 | 0 |00:00:00.01 |
|* 11 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 0 | 1 | 0 |00:00:00.01 |
|* 12 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 0 | 1 | 0 |00:00:00.01 |
|* 13 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 0 | 1 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - filter(("KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE')))
5 - filter((DECODE("S"."KSUSESQH",0,"S"."KSUSEPSQ","S"."KSUSESQL")=:B1 AND
DECODE("S"."KSUSESQH",0,"S"."KSUSEPHA","S"."KSUSESQH")=:B2 AND "S"."INST_ID"=USERENV('INSTANCE')
AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND "S"."INDX"=))
8 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0 AND "S"."KSUSEPRO"=))
9 - filter(("KSUPRPID"='9647' AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
10 - filter("S"."INDX"="W"."KSLWTSID")
11 - filter("W"."KSLWTEVT"="E"."INDX")
12 - filter("S"."INDX"="W"."KSLWTSID")
13 - filter("W"."KSLWTEVT"="E"."INDX")
已选择46行。
hint UNNEST 可以提示CBO进行Subquery Unnesting
已选择46行。
SQL> select sql_text
from v$sqlarea
where (address, hash_value) in
(select /*+ unnest*/DECODE(sql_hash_value, 0, prev_sql_addr, sql_address),
DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value)
from v$session
where sid =
(select sid
from v$session
where paddr = (select ADDR from v$process where SPID = '9647'))); 2 3 4 5 6 7 8 9 10
未选定行
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3ayrgwftuy4k4, child number 0
-------------------------------------
select sql_text from v$sqlarea where (address, hash_value) in
(select /*+ unnest*/DECODE(sql_hash_value, 0, prev_sql_addr,
sql_address), DECODE(sql_hash_value, 0, prev_hash_value,
sql_hash_value) from v$session where sid =
(select sid from v$session
where paddr = (select ADDR from v$process where SPID = '9647')))
Plan hash value: 4083784634
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.02 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 1 | 0 |00:00:00.02 | 866K| 866K| 1293K (0)|
|* 2 | FIXED TABLE FULL | X$KGLCURSOR_CHILD_SQLID | 1 | 1 | 838 |00:00:00.01 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 1 | 0 |00:00:00.01 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | | | |
|* 6 | FIXED TABLE FULL | X$KSUSE | 1 | 1 | 0 |00:00:00.01 | | | |
| 7 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | | | |
| 8 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | | | |
|* 9 | FIXED TABLE FULL | X$KSUSE | 1 | 1 | 0 |00:00:00.01 | | | |
|* 10 | FIXED TABLE FULL | X$KSUPR | 1 | 1 | 0 |00:00:00.01 | | | |
|* 11 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 0 | 1 | 0 |00:00:00.01 | | | |
|* 12 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 0 | 1 | 0 |00:00:00.01 | | | |
|* 13 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 0 | 1 | 0 |00:00:00.01 | | | |
|* 14 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 0 | 1 | 0 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("KGLHDPAR"="$kkqu_col_1" AND "KGLNAHSH"="$kkqu_col_2")
2 - filter(("KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE')))
6 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND
"S"."INDX"=))
9 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND
"S"."KSUSEPRO"=))
10 - filter(("KSUPRPID"='9647' AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
11 - filter("S"."INDX"="W"."KSLWTSID")
12 - filter("W"."KSLWTEVT"="E"."INDX")
13 - filter("S"."INDX"="W"."KSLWTSID")
14 - filter("W"."KSLWTEVT"="E"."INDX")
已选择46行。