用push_subq优化批处理统计SQL

用户反映有一条统计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
..........................

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26239116/viewspace-1391975/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26239116/viewspace-1391975/