用户反映有一条统计SQL好几个小时跑不完
SQL原文
(部分表名字段名被替换掉了)
(t1和t2都是100多G的大表)
select * from t1 a
where 1 = 1
and not exists (select 'X'
from t2
where date1 is null
and state = '1'
and statetype = 'Available'
and id = a.id)
and not exists
(select 'X'
from t3
where flag = '1'
and t3id = a.t3id)
and a.AppFlag = '1'
and '1' in
(select
(case
when a.rnewflag = '-1' then
(case
when a.date1 <= date
'2014-09-17' - (Select NVL(riskvalue,10)
From risk
Where risk.risktype = '97'
And riskid = a.riskid) then
'1'
else
'0'
end)
else
(case
when a.date1 <= date '2014-09-17' then
'1'
else
'0'
end)
end)
from dual)
and a.dept like '8624%';
表信息
SQL> select count(*) from t1 a where a.AppFlag = '1' and a.dept like '8624%';
COUNT(*)
----------
1056918
SQL> select count(distinct id)
2 from t2
3 where date1 is null
4 and state = '1'
5 and statetype = 'Available'
6 ;
COUNT(DISTINCTid)
--------------------
3708532
SQL> select count(*) from t3
2 where flag = '1';
COUNT(*)
----------
54056
关联后剩余的数据数据量
第一个和第二个子查询都过滤不了几条数据,只有最后一个子查询能过滤最多数据
SQL> select /*+ full(a) */ count(*) from t1 a where a.AppFlag = '1' and a.dept like '8624%';
COUNT(*)
----------
1056918
SQL> select /*+ optimizer_features_enable('11.2.0.3') full(a) use_hash(t2@sel$2) */
2 count(*)
3 from t1 a
4 where 1 = 1
5 and not exists (select 'X'
6 from t2
7 where date1 is null
8 and state = '1'
9 and statetype = 'Available'
10 and id = a.id)
11 and a.AppFlag = '1'
12 and a.dept like '8624%';
COUNT(*)
----------
1007662
SQL> select /*+ optimizer_features_enable('11.2.0.3') full(a)*/count(*)
2 from t1 a
3 where not exists
4 (select 'X'
5 from t3
6 where flag = '1'
7 and t3id = a.t3id)
8 and a.AppFlag = '1'
9 and a.dept like '8624%';
COUNT(*)
----------
1055014
SQL> select /*+ optimizer_features_enable('11.2.0.3') full(a) */count(*)
2 from t1 a
3 where a.AppFlag = '1'
4 and a.dept like '8624%'
5 and '1' in
6 (select
7 (case
8 when a.rnewflag = '-1' then
9 (case
10 when a.date1 <= date
11 '2014-09-17' - (Select NVL(riskvalue,10)
12 From risk
13 Where risk.risktype = '97'
14 And riskid = a.riskid) then
15 '1'
16 else
17 '0'
18 end)
19 else
20 (case
21 when a.date1 <= date '2014-09-17' then
22 '1'
23 else
24 '0'
25 end)
26 end)
27 from dual);
COUNT(*)
----------
7750
调整方案
1 用full(a)让大表t1走direct path read
2 用push_subq让最后一个子查询提前执行,尽早过滤掉最多的数据
执行时间从几小时所减少到10分钟
SQL> select
2 /*+
3 optimizer_features_enable('11.2.0.3')
4 full(a)
5 push_subq(@sel$4)
6 */ *
7 from t1 a
8 where 1 = 1
9 and not exists (select 'X'
10 from t2
11 where date1 is null
12 and state = '1'
13 and statetype = 'Available'
14 and id = a.id)
15 and not exists
16 (select 'X'
17 from t3
18 where flag = '1'
19 and t3id = a.t3id)
20 and a.AppFlag = '1'
21 and '1' in
22 (select
23 (case
24 when a.rnewflag = '-1' then
25 (case
26 when a.date1 <= date
27 '2014-09-17' - (Select NVL(riskvalue,10)
28 From risk
29 Where risk.risktype = '97'
30 And riskid = a.riskid) then
31 '1'
32 else
33 '0'
34 end)
35 else
36 (case
37 when a.date1 <= date '2014-09-17' then
38 '1'
39 else
40 '0'
41 end)
42 end)
43 from dual)
44 and a.dept like '8624%';
6092 rows selected.
Elapsed: 00:0 9:35.87
Execution Plan
----------------------------------------------------------
Plan hash value: 1148293032
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 2133 | | 6339K (1)| 21:07:56 |
| 1 | NESTED LOOPS ANTI | | 3 | 2133 | | 6339K (1)| 21:07:56 |
|* 2 | HASH JOIN RIGHT ANTI | | 283 | 184K| 2120K| 6338K (1)| 21:07:39 |
|* 3 | TABLE ACCESS FULL | t3 | 54187 | 1481K| | 6498 (1)| 00:01:18 |
|* 4 | TABLE ACCESS FULL | t1 | 28272 | 17M| | 6330K (1)| 21:06:10 |
|* 5 | FILTER | | | | | | |
| 6 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PK_risk | 1 | 15 | | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| t2 | 103M| 4444M| | 5 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | t2_INDEX_5 | 2 | | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("t3id"="A"."t3id")
3 - filter("flag"='1')
4 - filter("A"."dept" LIKE '8624%' AND "A"."APPFLAG"='1' AND EXISTS (SELECT /*+ PUSH_SUBQ
*/ 0 FROM "SYS"."DUAL" "DUAL" WHERE CASE :B1 WHEN (-1) THEN CASE WHEN TO_DATE(' 2014-09-17
00:00:00', 'syyyy-mm-dd hh24:mi:ss')-TO_NUMBER( (SELECT NVL("riskvalue",'10') FROM
"."risk" "risk" WHERE "risk"."risktype"='97' AND "riskid"=:B2))>=:B3
THEN '1' ELSE '0' END ELSE CASE WHEN :B4<=TO_DATE(' 2014-09-17 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') THEN '1' ELSE '0' END END ='1'))
5 - filter(CASE :B1 WHEN (-1) THEN CASE WHEN TO_DATE(' 2014-09-17 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')-TO_NUMBER( (SELECT NVL("riskvalue",'10') FROM "."risk" "risk"
WHERE "risk"."risktype"='97' AND "riskid"=:B2))>=:B3 THEN '1' ELSE '0' END ELSE CASE
WHEN :B4<=TO_DATE(' 2014-09-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') THEN '1' ELSE '0' END END ='1')
7 - access("riskid"=:B1 AND "risk"."risktype"='97')
8 - filter("date1" IS NULL AND "STATE"='1' AND "STATETYPE"='Available')
9 - access("id"="A"."id")
Statistics
----------------------------------------------------------
1 recursive calls
8 db block gets
23443214 consistent gets
23422067 physical reads
0 redo size
1361528 bytes sent via SQL*Net to client
4990 bytes received via SQL*Net from client
408 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6092 rows processed
然后用profile固定执行计划,用户重新发起查询,执行速度很快。
但是故事没有完
第二天用户又反映这条SQL好几个小时执行不玩。
经过查看,profile是生效的,执行计划也没问题,这就奇怪了。
后来在session历史中发现扫描都是通过db file scattered read,从p3看每次只读取几十甚至十几个数据块。
同时还伴随大量的对system表空间的db file parallel read,而且p2和p3都一样,也即是说都是一块一块读的。
猜想是因为t1或t2这种大表有很大比例已经存在于buffer cache,导致oracle放弃了direct path read。
并且,假如buffer cache里1号和10号数据块,oracle就必须在system表空间查找2到9号数据块的位置,单后发起一次p3参数为8的db file scattered read。不但要时常访问system表空间,多快读每次也只能读几十块甚至十几块。
同样的SQL在测试库上又试了两遍,日期用9月22,9:45.51结束;日期改成6月22的,8:49.83。扫描方式全是direct path read,每次都能读128个块。
对比生产库和测试库的session历史:
生产库
.............
2014-09-22 07:27:08 db file sequential read 16 699070 1
2014-09-22 07:27:09 db file scattered read 16 700348 2
2014-09-22 07:27:10 gc current block 2-way 16 702389 1
2014-09-22 07:27:11 db file scattered read 16 703443 3
2014-09-22 07:27:12 db file sequential read 17 687406 1
2014-09-22 07:27:13 db file sequential read 17 688521 1
2014-09-22 07:27:14 db file scattered read 17 689726 4
2014-09-22 07:27:15 db file sequential read 17 690882 1
2014-09-22 07:27:16 db file scattered read 17 691995 3
2014-09-22 07:27:17 db file scattered read 17 693226 3
2014-09-22 07:27:18 db file scattered read 18 686614 3
2014-09-22 07:27:19 db file parallel read 1 2 2
2014-09-22 07:27:20 db file parallel read 1 2 2
2014-09-22 07:27:21 db file scattered read 18 690640 2
2014-09-22 07:27:22 db file parallel read 1 2 2
2014-09-22 07:27:23 db file sequential read 18 692920 1
2014-09-22 07:27:24 db file sequential read 18 694422 1
2014-09-22 07:27:25 db file scattered read 19 687397 2
2014-09-22 07:27:26 db file parallel read 1 9 9
2014-09-22 07:27:27 gc cr multi block request 19 689350 1
2014-09-22 07:27:28 gc cr grant 2-way 19 690437 1
2014-09-22 07:27:29 db file sequential read 19 691593 1
2014-09-22 07:27:30 db file scattered read 19 692734 3
2014-09-22 07:27:31 db file sequential read 19 693720 1
2014-09-22 07:27:32 db file parallel read 1 3 3
2014-09-22 07:27:33 db file parallel read 1 2 2
....................
一共一万多行
测试库
............................
2014-09-22 09:46:07 direct path read 69 1282 126
2014-09-22 09:46:08 direct path read 27 1538 126
2014-09-22 09:46:09 direct path read 68 1664 128
2014-09-22 09:46:10 direct path read 73 1792 128
2014-09-22 09:46:11 direct path read 88 2688 128
2014-09-22 09:46:12 direct path read 92 3200 128
2014-09-22 09:46:13 direct path read 6 3072 128
2014-09-22 09:46:14 15 2692 124
2014-09-22 09:46:15 direct path read 22 3200 128
2014-09-22 09:46:16 direct path read 28 4352 128
2014-09-22 09:46:17 direct path read 35 5248 128
2014-09-22 09:46:18 70 7168 128
2014-09-22 09:46:19 87 7040 128
2014-09-22 09:46:20 direct path read 95 14080 128
2014-09-22 09:46:21 15 6400 128
2014-09-22 09:46:22 direct path read 24 8448 128
2014-09-22 09:46:23 direct path read 32 9344 128
2014-09-22 09:46:24 direct path read 70 12288 128
2014-09-22 09:46:25 direct path read 90 12160 128
2014-09-22 09:46:26 direct path read 95 19328 128
2014-09-22 09:46:28 direct path read 6 33152 128
2014-09-22 09:46:29 direct path read 10 30464 128
..........................
SQL原文
(部分表名字段名被替换掉了)
(t1和t2都是100多G的大表)
select * from t1 a
where 1 = 1
and not exists (select 'X'
from t2
where date1 is null
and state = '1'
and statetype = 'Available'
and id = a.id)
and not exists
(select 'X'
from t3
where flag = '1'
and t3id = a.t3id)
and a.AppFlag = '1'
and '1' in
(select
(case
when a.rnewflag = '-1' then
(case
when a.date1 <= date
'2014-09-17' - (Select NVL(riskvalue,10)
From risk
Where risk.risktype = '97'
And riskid = a.riskid) then
'1'
else
'0'
end)
else
(case
when a.date1 <= date '2014-09-17' then
'1'
else
'0'
end)
end)
from dual)
and a.dept like '8624%';
表信息
SQL> select count(*) from t1 a where a.AppFlag = '1' and a.dept like '8624%';
COUNT(*)
----------
1056918
SQL> select count(distinct id)
2 from t2
3 where date1 is null
4 and state = '1'
5 and statetype = 'Available'
6 ;
COUNT(DISTINCTid)
--------------------
3708532
SQL> select count(*) from t3
2 where flag = '1';
COUNT(*)
----------
54056
关联后剩余的数据数据量
第一个和第二个子查询都过滤不了几条数据,只有最后一个子查询能过滤最多数据
SQL> select /*+ full(a) */ count(*) from t1 a where a.AppFlag = '1' and a.dept like '8624%';
COUNT(*)
----------
1056918
SQL> select /*+ optimizer_features_enable('11.2.0.3') full(a) use_hash(t2@sel$2) */
2 count(*)
3 from t1 a
4 where 1 = 1
5 and not exists (select 'X'
6 from t2
7 where date1 is null
8 and state = '1'
9 and statetype = 'Available'
10 and id = a.id)
11 and a.AppFlag = '1'
12 and a.dept like '8624%';
COUNT(*)
----------
1007662
SQL> select /*+ optimizer_features_enable('11.2.0.3') full(a)*/count(*)
2 from t1 a
3 where not exists
4 (select 'X'
5 from t3
6 where flag = '1'
7 and t3id = a.t3id)
8 and a.AppFlag = '1'
9 and a.dept like '8624%';
COUNT(*)
----------
1055014
SQL> select /*+ optimizer_features_enable('11.2.0.3') full(a) */count(*)
2 from t1 a
3 where a.AppFlag = '1'
4 and a.dept like '8624%'
5 and '1' in
6 (select
7 (case
8 when a.rnewflag = '-1' then
9 (case
10 when a.date1 <= date
11 '2014-09-17' - (Select NVL(riskvalue,10)
12 From risk
13 Where risk.risktype = '97'
14 And riskid = a.riskid) then
15 '1'
16 else
17 '0'
18 end)
19 else
20 (case
21 when a.date1 <= date '2014-09-17' then
22 '1'
23 else
24 '0'
25 end)
26 end)
27 from dual);
COUNT(*)
----------
7750
调整方案
1 用full(a)让大表t1走direct path read
2 用push_subq让最后一个子查询提前执行,尽早过滤掉最多的数据
执行时间从几小时所减少到10分钟
SQL> select
2 /*+
3 optimizer_features_enable('11.2.0.3')
4 full(a)
5 push_subq(@sel$4)
6 */ *
7 from t1 a
8 where 1 = 1
9 and not exists (select 'X'
10 from t2
11 where date1 is null
12 and state = '1'
13 and statetype = 'Available'
14 and id = a.id)
15 and not exists
16 (select 'X'
17 from t3
18 where flag = '1'
19 and t3id = a.t3id)
20 and a.AppFlag = '1'
21 and '1' in
22 (select
23 (case
24 when a.rnewflag = '-1' then
25 (case
26 when a.date1 <= date
27 '2014-09-17' - (Select NVL(riskvalue,10)
28 From risk
29 Where risk.risktype = '97'
30 And riskid = a.riskid) then
31 '1'
32 else
33 '0'
34 end)
35 else
36 (case
37 when a.date1 <= date '2014-09-17' then
38 '1'
39 else
40 '0'
41 end)
42 end)
43 from dual)
44 and a.dept like '8624%';
6092 rows selected.
Elapsed: 00:0 9:35.87
Execution Plan
----------------------------------------------------------
Plan hash value: 1148293032
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 2133 | | 6339K (1)| 21:07:56 |
| 1 | NESTED LOOPS ANTI | | 3 | 2133 | | 6339K (1)| 21:07:56 |
|* 2 | HASH JOIN RIGHT ANTI | | 283 | 184K| 2120K| 6338K (1)| 21:07:39 |
|* 3 | TABLE ACCESS FULL | t3 | 54187 | 1481K| | 6498 (1)| 00:01:18 |
|* 4 | TABLE ACCESS FULL | t1 | 28272 | 17M| | 6330K (1)| 21:06:10 |
|* 5 | FILTER | | | | | | |
| 6 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PK_risk | 1 | 15 | | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| t2 | 103M| 4444M| | 5 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | t2_INDEX_5 | 2 | | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("t3id"="A"."t3id")
3 - filter("flag"='1')
4 - filter("A"."dept" LIKE '8624%' AND "A"."APPFLAG"='1' AND EXISTS (SELECT /*+ PUSH_SUBQ
*/ 0 FROM "SYS"."DUAL" "DUAL" WHERE CASE :B1 WHEN (-1) THEN CASE WHEN TO_DATE(' 2014-09-17
00:00:00', 'syyyy-mm-dd hh24:mi:ss')-TO_NUMBER( (SELECT NVL("riskvalue",'10') FROM
"."risk" "risk" WHERE "risk"."risktype"='97' AND "riskid"=:B2))>=:B3
THEN '1' ELSE '0' END ELSE CASE WHEN :B4<=TO_DATE(' 2014-09-17 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') THEN '1' ELSE '0' END END ='1'))
5 - filter(CASE :B1 WHEN (-1) THEN CASE WHEN TO_DATE(' 2014-09-17 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')-TO_NUMBER( (SELECT NVL("riskvalue",'10') FROM "."risk" "risk"
WHERE "risk"."risktype"='97' AND "riskid"=:B2))>=:B3 THEN '1' ELSE '0' END ELSE CASE
WHEN :B4<=TO_DATE(' 2014-09-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') THEN '1' ELSE '0' END END ='1')
7 - access("riskid"=:B1 AND "risk"."risktype"='97')
8 - filter("date1" IS NULL AND "STATE"='1' AND "STATETYPE"='Available')
9 - access("id"="A"."id")
Statistics
----------------------------------------------------------
1 recursive calls
8 db block gets
23443214 consistent gets
23422067 physical reads
0 redo size
1361528 bytes sent via SQL*Net to client
4990 bytes received via SQL*Net from client
408 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6092 rows processed
然后用profile固定执行计划,用户重新发起查询,执行速度很快。
但是故事没有完
第二天用户又反映这条SQL好几个小时执行不玩。
经过查看,profile是生效的,执行计划也没问题,这就奇怪了。
后来在session历史中发现扫描都是通过db file scattered read,从p3看每次只读取几十甚至十几个数据块。
同时还伴随大量的对system表空间的db file parallel read,而且p2和p3都一样,也即是说都是一块一块读的。
猜想是因为t1或t2这种大表有很大比例已经存在于buffer cache,导致oracle放弃了direct path read。
并且,假如buffer cache里1号和10号数据块,oracle就必须在system表空间查找2到9号数据块的位置,单后发起一次p3参数为8的db file scattered read。不但要时常访问system表空间,多快读每次也只能读几十块甚至十几块。
同样的SQL在测试库上又试了两遍,日期用9月22,9:45.51结束;日期改成6月22的,8:49.83。扫描方式全是direct path read,每次都能读128个块。
对比生产库和测试库的session历史:
生产库
.............
2014-09-22 07:27:08 db file sequential read 16 699070 1
2014-09-22 07:27:09 db file scattered read 16 700348 2
2014-09-22 07:27:10 gc current block 2-way 16 702389 1
2014-09-22 07:27:11 db file scattered read 16 703443 3
2014-09-22 07:27:12 db file sequential read 17 687406 1
2014-09-22 07:27:13 db file sequential read 17 688521 1
2014-09-22 07:27:14 db file scattered read 17 689726 4
2014-09-22 07:27:15 db file sequential read 17 690882 1
2014-09-22 07:27:16 db file scattered read 17 691995 3
2014-09-22 07:27:17 db file scattered read 17 693226 3
2014-09-22 07:27:18 db file scattered read 18 686614 3
2014-09-22 07:27:19 db file parallel read 1 2 2
2014-09-22 07:27:20 db file parallel read 1 2 2
2014-09-22 07:27:21 db file scattered read 18 690640 2
2014-09-22 07:27:22 db file parallel read 1 2 2
2014-09-22 07:27:23 db file sequential read 18 692920 1
2014-09-22 07:27:24 db file sequential read 18 694422 1
2014-09-22 07:27:25 db file scattered read 19 687397 2
2014-09-22 07:27:26 db file parallel read 1 9 9
2014-09-22 07:27:27 gc cr multi block request 19 689350 1
2014-09-22 07:27:28 gc cr grant 2-way 19 690437 1
2014-09-22 07:27:29 db file sequential read 19 691593 1
2014-09-22 07:27:30 db file scattered read 19 692734 3
2014-09-22 07:27:31 db file sequential read 19 693720 1
2014-09-22 07:27:32 db file parallel read 1 3 3
2014-09-22 07:27:33 db file parallel read 1 2 2
....................
一共一万多行
测试库
............................
2014-09-22 09:46:07 direct path read 69 1282 126
2014-09-22 09:46:08 direct path read 27 1538 126
2014-09-22 09:46:09 direct path read 68 1664 128
2014-09-22 09:46:10 direct path read 73 1792 128
2014-09-22 09:46:11 direct path read 88 2688 128
2014-09-22 09:46:12 direct path read 92 3200 128
2014-09-22 09:46:13 direct path read 6 3072 128
2014-09-22 09:46:14 15 2692 124
2014-09-22 09:46:15 direct path read 22 3200 128
2014-09-22 09:46:16 direct path read 28 4352 128
2014-09-22 09:46:17 direct path read 35 5248 128
2014-09-22 09:46:18 70 7168 128
2014-09-22 09:46:19 87 7040 128
2014-09-22 09:46:20 direct path read 95 14080 128
2014-09-22 09:46:21 15 6400 128
2014-09-22 09:46:22 direct path read 24 8448 128
2014-09-22 09:46:23 direct path read 32 9344 128
2014-09-22 09:46:24 direct path read 70 12288 128
2014-09-22 09:46:25 direct path read 90 12160 128
2014-09-22 09:46:26 direct path read 95 19328 128
2014-09-22 09:46:28 direct path read 6 33152 128
2014-09-22 09:46:29 direct path read 10 30464 128
..........................
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-1391975/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26239116/viewspace-1391975/