标量子查询调优SQL

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;

COUNT(*)
----------
181

dba_waiters wb
| 5 | NESTED LOOPS | | 1 | 58 | 0
(0)| 00:00:01 |

|* 6 | FIXED TABLE FULL | X$KSLWT | 1 | 29 | 0
(0)| 00:00:01 |

|* 7 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 29 | 0
(0)| 00:00:01 |

| 8 | FIXED TABLE FULL | X$KSQRS | 10544 | 185K| 4 (
100)| 00:00:01 |

| 9 | VIEW | GV$_LOCK | 16 | 192 | 6 (
100)| 00:00:01 |

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
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')))
24 - 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')))
25 - 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')))
26 - 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')))
27 - 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')))
28 - 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')))
29 - 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')))
30 - 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')))
31 - 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("KSSPAFLG",1)<>0 AND
"INST_ID"=USERENV('INSTANCE')))
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 |
1539 | 1 (100)| 00:00:01 |

| 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 |
77 | 0 (0)| 00:00:01 |

|* 25 | FIXED TABLE FULL | X$KTADM | 1 |
77 | 0 (0)| 00:00:01 |

|* 26 | FIXED TABLE FULL | X$KTATRFIL | 1 |
77 | 0 (0)| 00:00:01 |

|* 27 | FIXED TABLE FULL | X$KTATRFSL | 1 |
77 | 0 (0)| 00:00:01 |

|* 28 | FIXED TABLE FULL | X$KTATL | 1 |
77 | 0 (0)| 00:00:01 |

|* 29 | FIXED TABLE FULL | X$KTSTUSC | 1 |
77 | 0 (0)| 00:00:01 |

|* 30 | FIXED TABLE FULL | X$KTSTUSS | 1 |
77 | 0 (0)| 00:00:01 |

|* 31 | FIXED TABLE FULL | X$KTSTUSG | 1 |
77 | 0 (0)| 00:00:01 |

|* 32 | FIXED TABLE FULL | X$KTCXB | 1 |
77 | 0 (0)| 00:00:01 |

|* 33 | FIXED TABLE FULL | X$KSUSE | 1 |
32 | 0 (0)| 00:00:01 |

|* 34 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 |
75 | 0 (0)| 00:00:01 |

|* 35 | FIXED TABLE FULL | X$KGLCURSOR_CHILD_SQLID | 1 |
534 | 0 (0)| 00:00:01 |

|* 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 |
26 | 0 (0)| 00:00:01 |

|* 39 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 |
13 | 0 (0)| 00:00:01 |

--------------------------------------------------------------------------------
-------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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
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')

25 - 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')

26 - 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')

27 - 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')

28 - 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')

29 - 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')

30 - 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')

31 - 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')

32 - filter("KSQLKLBLK"=1 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=U
SERENV('INSTANCE') AND

BITAND("KSSPAFLG",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')

33 - filter("S"."INST_ID"=USERENV('INSTANCE'))
34 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0
AND

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")
38 - filter("S"."INDX"="W"."KSLWTSID")
39 - 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)
             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 = '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
                     from dict_item
                    where groupid in ('SHOPSCOREEXCHG',
                                      'SCOREEXCHGFEE',
                                      'SCOREEXCHGSELFBUSI',
                                      'SCOREEXCHGSUPPERPRESENT',
                                      'SCOREEXCHGXHNEWS',
                                      'SCOREEXCHGMIFI',
                                      'SCOREEXCHGTEMPPRESENT')
                   )group by operid
) t1,
(select operid,sum(score) sum_t2 from main where scoreid in
                  (select /*+ full(dict_item) */dictname
                     from dict_item
                    where groupid = 'NEWBUSISCORE')
                   group by operid
) t2
where t.operid=t1.operid(+) and
      t.operid=t2.operid(+) ; 

这个SQL最终的执行时间是4分钟（并行度设置为8），下面是部分执行计划的内容：