fxnjbmhkk4pp4
select /*+ leading (wb,sb,qw) */ 'blocker('||wb.holding_session||':'||sb.username||')-sql:'||qb.sql_text blockers,'waiter ('||wb.waiting_session||':'||sw.username||')-sql:'||qw.sql_text waiters from dba_waiters wb, v$session sb, v$session sw, v$sqlarea qb, v$sqlarea qw where wb.holding_session=sb.sid and wb.waiting_session=sw.sid and sb.prev_sql_addr=qb.address and sw.sql_address=qw.address and wb.mode_held<>'None'
select count(*) from v$sqlarea;
dba_waiters wb, v$session sb, v$session sw, v$sqlarea qb, v$sqlarea qw
select count(*) from x$kslwt;
SQL> select count(*) from x$kslwt;
COUNT(*)
----------
181
select count(*) from x$ksuse;
SQL> select count(*) from v$session;
dba_waiters wb
| 5 | NESTED LOOPS | | 1 | 58 | 0
(0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSLWT | 1 | 29 | 0
|* 7 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 29 | 0
| 8 | FIXED TABLE FULL | X$KSQRS | 10544 | 185K| 4 (
100)| 00:00:01 |
| 9 | VIEW | GV$_LOCK | 16 | 192 | 6 (
in 11.1.0.7
Plan hash value: 536611791
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | NESTED LOOPS | | 1 | 1236 | 8 (100)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 1232 | 8 (100)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 1224 | 8 (100)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 690 | 8 (100)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 672 | 8 (100)| 00:00:01 |
|* 6 | HASH JOIN | | 1 | 643 | 8 (100)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 610 | 7 (100)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 592 | 7 (100)| 00:00:01 |
|* 9 | HASH JOIN | | 1 | 588 | 7 (100)| 00:00:01 |
|* 10 | HASH JOIN | | 1 | 576 | 1 (100)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 560 | 1 (100)| 00:00:01 |
|* 12 | HASH JOIN | | 1 | 552 | 1 (100)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KGLCURSOR_CHILD_SQLID | 1 | 534 | 0 (0)| |
|* 14 | FIXED TABLE FULL | X$KSUSE | 40 | 720 | 0 (0)| |
|* 15 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 8 | 0 (0)| |
|* 16 | FIXED TABLE FULL | X$KSUSE | 400 | 6400 | 0 (0)| |
| 17 | VIEW | GV$_LOCK | 10 | 120 | 6 (100)| 00:00:01 |
| 18 | UNION-ALL | | | | | |
|* 19 | FILTER | | | | | |
| 20 | VIEW | GV$_LOCK1 | 2 | 152 | 2 (100)| 00:00:01 |
| 21 | UNION-ALL | | | | | |
|* 22 | FIXED TABLE FULL | X$KDNSSF | 1 | 36 | 0 (0)| |
|* 23 | FIXED TABLE FULL | X$KSQEQ | 1 | 41 | 2 (100)| 00:00:01 |
|* 24 | FIXED TABLE FULL | X$KTADM | 1 | 39 | 3 (100)| 00:00:01 |
|* 25 | FIXED TABLE FULL | X$KTATRFIL | 1 | 36 | 0 (0)| |
|* 26 | FIXED TABLE FULL | X$KTATRFSL | 1 | 36 | 0 (0)| |
|* 27 | FIXED TABLE FULL | X$KTATL | 1 | 36 | 0 (0)| |
|* 28 | FIXED TABLE FULL | X$KTSTUSC | 1 | 41 | 0 (0)| |
|* 29 | FIXED TABLE FULL | X$KTSTUSS | 1 | 41 | 0 (0)| |
|* 30 | FIXED TABLE FULL | X$KTSTUSG | 1 | 36 | 0 (0)| |
|* 31 | FIXED TABLE FULL | X$KTCXB | 1 | 38 | 0 (0)| |
|* 32 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 0 (0)| |
|* 33 | FIXED TABLE FIXED INDEX | X$KSQRS (ind:1) | 1 | 18 | 0 (0)| |
|* 34 | FIXED TABLE FULL | X$KSLWT | 1 | 33 | 0 (0)| |
|* 35 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 29 | 0 (0)| |
|* 36 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | 18 | 0 (0)| |
|* 37 | FIXED TABLE FULL | X$KGLCURSOR_CHILD_SQLID | 1 | 534 | 0 (0)| |
|* 38 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 | 8 | 0 (0)| |
|* 39 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 4 | 0 (0)| |
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."KSUSESQL"="KGLHDPAR")
6 - access("R"."KSQRSID1"="S"."KSLWTP2" AND "R"."KSQRSID2"="S"."KSLWTP3" AND
"R"."KSQRSIDT"=CHR(BITAND("S"."KSLWTP1",(-16777216))/16777215)||CHR(BITAND("S"."KSLWTP1",16711680)/655
35))
9 - access("SADDR"="S"."ADDR")
10 - access("S"."KSUSENUM"="S"."INDX")
12 - access("S"."KSUSEPSQ"="KGLHDPAR")
13 - filter(("KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE')))
14 - filter((BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
"S"."INST_ID"=USERENV('INSTANCE')))
15 - filter("S"."INDX"="W"."KSLWTSID")
16 - filter("S"."INST_ID"=USERENV('INSTANCE'))
19 - filter(USERENV('INSTANCE') IS NOT NULL)
22 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
DECODE("KSQLKMOD",0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X
(SSX)',6,'Exclusive',TO_CHAR("KSQLKMOD"))<>'None' AND BITAND("KSSOBFLG",1)<>0 AND
"INST_ID"=USERENV('INSTANCE')))
23 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
24 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
25 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
26 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
27 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
28 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
29 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
30 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
31 - filter(("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
(SSX)',6,'Exclusive',TO_CHAR("KSQLKMOD"))<>'None' AND BITAND("KSSPAFLG",1)<>0 AND
32 - filter("W"."KSLWTEVT"="E"."INDX")
33 - filter("RADDR"="R"."ADDR")
34 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND
DECODE("S"."KSLWTINWAIT",0,DECODE(BITAND("S"."KSLWTFLAGS",256),0,(-2),DECODE(ROUND("S"."KSLWTSTIME"/10
000),0,(-1),ROUND("S"."KSLWTSTIME"/10000))),0)=0))
35 - filter(("E"."KSLEDNAM"='enqueue' AND "S"."KSLWTEVT"="E"."INDX"))
36 - filter((BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
"S"."INST_ID"=USERENV('INSTANCE') AND "S"."KSLWTSID"="S"."INDX"))
37 - filter(("KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE')))
38 - filter("S"."INDX"="W"."KSLWTSID")
39 - filter("W"."KSLWTEVT"="E"."INDX")
in 11.2.0.4
Execution Plan
----------------------------------------------------------
Plan hash value: 3485217249
--------------------------------------------------------------------------------
-------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 |
1539 | 1 (100)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 |
| 2 | NESTED LOOPS | | 1 |
1526 | 1 (100)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 |
1500 | 1 (100)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 |
1487 | 1 (100)| 00:00:01 |
|* 5 | HASH JOIN | | 1 |
1461 | 1 (100)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 |
927 | 1 (100)| 00:00:01 |
|* 7 | HASH JOIN | | 1 |
852 | 1 (100)| 00:00:01 |
|* 8 | HASH JOIN | | 1 |
820 | 1 (100)| 00:00:01 |
|* 9 | HASH JOIN | | 1 |
808 | 0 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 |
773 | 0 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 1 |
726 | 0 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 |
192 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KSLWT | 1 |
117 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 |
75 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KGLCURSOR_CHILD_SQLID | 1 |
534 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 |
47 | 0 (0)| 00:00:01 |
| 17 | FIXED TABLE FULL | X$KSQRS | 100 |
3500 | 0 (0)| 00:00:01 |
| 18 | VIEW | GV$_LOCK | 10 |
120 | 0 (0)| 00:00:01 |
| 19 | UNION-ALL | | |
| | |
|* 20 | FILTER | | |
| 21 | VIEW | GV$_LOCK1 | 2 |
24 | 0 (0)| 00:00:01 |
| 22 | UNION-ALL | | |
|* 23 | FIXED TABLE FULL | X$KDNSSF | 1 |
77 | 0 (0)| 00:00:01 |
|* 24 | FIXED TABLE FULL | X$KSQEQ | 1 |
|* 25 | FIXED TABLE FULL | X$KTADM | 1 |
|* 26 | FIXED TABLE FULL | X$KTATRFIL | 1 |
|* 27 | FIXED TABLE FULL | X$KTATRFSL | 1 |
|* 28 | FIXED TABLE FULL | X$KTATL | 1 |
|* 29 | FIXED TABLE FULL | X$KTSTUSC | 1 |
|* 30 | FIXED TABLE FULL | X$KTSTUSS | 1 |
|* 31 | FIXED TABLE FULL | X$KTSTUSG | 1 |
|* 32 | FIXED TABLE FULL | X$KTCXB | 1 |
|* 33 | FIXED TABLE FULL | X$KSUSE | 1 |
32 | 0 (0)| 00:00:01 |
|* 34 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 |
|* 35 | FIXED TABLE FULL | X$KGLCURSOR_CHILD_SQLID | 1 |
|* 36 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 |
26 | 0 (0)| 00:00:01 |
|* 37 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 |
13 | 0 (0)| 00:00:01 |
|* 38 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 |
|* 39 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 |
5 - access("S"."KSUSEPSQ"="KGLHDPAR")
7 - access("SADDR"="S"."ADDR")
8 - access("RADDR"="R"."ADDR")
9 - access("R"."KSQRSID1"="S"."KSLWTP2" AND "R"."KSQRSID2"="S"."KSLWTP3" AND
"R"."KSQRSIDT"=CHR(BITAND("S"."KSLWTP1",4278190080)/16777215)||CHR
(BITAND("S"."KSLWTP1",16711680)/65535
))
11 - access("S"."KSUSESQL"="KGLHDPAR")
13 - filter("S"."INST_ID"=USERENV('INSTANCE') AND
DECODE("S"."KSLWTINWAIT",0,DECODE(BITAND("S"."KSLWTFLAGS",256),0,(
-2),DECODE(ROUND("S"."KSLWTSTIME"/100
00),0,(-1),ROUND("S"."KSLWTSTIME"/10000))),0)=0)
14 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0
AND
BITAND("S"."KSUSEFLG",1)<>0 AND "S"."KSLWTSID"="S"."INDX")
15 - filter("KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE'))
16 - filter("E"."KSLEDNAM" LIKE 'enq:%' AND "S"."KSLWTEVT"="E"."INDX")
20 - filter(USERENV('INSTANCE') IS NOT NULL)
23 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
SERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0 AND DECODE("KSQLKMOD",0,'None',1,'Null',2,
'Row-S (SS)',3,'Row-X
(SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR("KSQLKMOD"
))<>'None')
24 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
25 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
26 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
27 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
28 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
29 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
30 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
31 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
32 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
BITAND("KSSPAFLG",1)<>0 AND DECODE("KSQLKMOD",0,'None',1,'Null',2,
33 - filter("S"."INST_ID"=USERENV('INSTANCE'))
34 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0
BITAND("S"."KSUSEFLG",1)<>0 AND "S"."KSUSENUM"="S"."INDX")
35 - filter("KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE'))
36 - filter("S"."INDX"="W"."KSLWTSID")
37 - filter("W"."KSLWTEVT"="E"."INDX")
Statistics
76 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
405 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
原先SQL:
select 'blocker('||wb.holding_session||':'||sb.username||')-sql:'||qb.sql_text blockers,'waiter ('||wb.waiting_session||':'||sw.username||')-sql:'||qw.sql_text waiters from dba_waiters wb, v$session sb, v$session sw, v$sqlarea qb, v$sqlarea qw where wb.holding_session=sb.sid and wb.waiting_session=sw.sid and sb.prev_sql_addr=qb.address and sw.sql_address=qw.address and wb.mode_held<>'None'
問題是
11,1,0,7 V$SQLAREA 查詢太慢，執行計劃沒法選擇最優，并且無法收集資料庫字典統計資訊
######改寫方法,使用标量子查詢強制使用小查詢在先，使用leading +nL 強制小表查詢在先
with db as
(select /*+materialize*/
wb.holding_session,
wb.waiting_session,
sb.username b_username,
sw.username w_username,
sb.sid b_sid,
sw.sid w_sid,
sw.sql_address w_address,
sb.prev_sql_addr b_address
from dba_waiters wb, v$session sb, v$session sw
where wb.holding_session = sb.sid
and wb.waiting_session = sw.sid
and wb.mode_held <> 'None')
select /*+ leading(db) USE_NL(db)*/
'blocker('||db.b_sid||':'||db.b_username||')-sql:'||qb.sql_text blockers,'waiter ('||db.w_sid||':'||db.w_username||')-sql:'||qw.sql_text waiters
from v$sqlarea qb, v$sqlarea qw,db where qb.address = db.b_address and qw.address=db.w_address
REF
https://blog.csdn.net/launch_225/article/details/27859219
https://blog.csdn.net/youyonghu001/article/details/45171351
https://blog.csdn.net/baidu_30527569/article/details/48680745
http://www.cnblogs.com/fightLonely/archive/2011/02/24/1963907.html
#################REF
原
标量子查詢SQL改寫一則（包括WITH的改寫）
2015年04月21日 15:46:46 遊泳虎 閱讀數：482
原SQL，執行了6.8小時後報ora-01555錯誤無法完成：
select t.operid || '|' || sum(score) || '|' ||
nvl((select sum(score)
from cs_score_operdayscore t1
where t1.operid = t.operid
and t1.scoredate > to_date(20120801, 'yyyymmdd')
and t1.scoreid in
(select dictname
from dict_item
where groupid in ('SHOPSCOREEXCHG',
'SCOREEXCHGFEE',
'SCOREEXCHGSELFBUSI',
'SCOREEXCHGSUPPERPRESENT',
'SCOREEXCHGXHNEWS',
'SCOREEXCHGMIFI',
'SCOREEXCHGTEMPPRESENT'))),
0) || '|' ||
nvl((select sum(score)
(select dictname
where groupid = 'NEWBUSISCORE')),
0)
from cs_score_operdayscore t
where scoredate > to_date(20120801, 'yyyymmdd')
group by operid;
sql monitor采集的執行計劃如下：
問題：
cs_score_operdayscore是一張大表（1億條以上），從謂詞條件看，将近3年的資料基本上不會過濾掉多少記錄，在如此大的一張表上，使用标量子查詢（即select 的column清單中使用了select子句），性能是非常非常差的，需要通過外關聯的方式進行改寫才能提高性能。
而同一張表在标量子查詢裡面又被用到，這種情況可以使用WITH來進行改寫；因為這個SQL是大表做統計分析，一般是DW系統或是OLTP系統晚上操作，為了縮短響應時間和資源消耗，一般建議增加并行操作，最終改寫後的SQL如下：
with main as
(select operid,score,scoreid from cs_score_operdayscore where scoredate > to_date(20120801, 'yyyymmdd') )
select /*+ parallel(8) */
t.operid || '|' || sum_t || '|' ||nvl(sum_t1,0) || '|' ||nvl(sum_t2,0) from
(select operid,sum(score) sum_t from main group by operid) t,
(select operid,sum(score) sum_t1 from main where scoreid in
(select /*+ full(dict_item) */dictname
'SCOREEXCHGTEMPPRESENT')
)group by operid
) t1,
(select operid,sum(score) sum_t2 from main where scoreid in
where groupid = 'NEWBUSISCORE')
group by operid
) t2
where t.operid=t1.operid(+) and
t.operid=t2.operid(+) ;
這個SQL最終的執行時間是4分鐘（并行度設定為8），下面是部分執行計劃的内容：
