a sql tuning

最新推荐文章于 2021-11-01 22:28:12 发布

cixipeng2404

最新推荐文章于 2021-11-01 22:28:12 发布

阅读量92

点赞数

SELECT D.C3815 AS SIHARAINAIYOU,
   E.C3815 AS SAISINSIHARAINAIYOU,
   F.C3815 AS NYUUKINNAIYOU,
   G.C3815 AS C0813MEI,
   H.C3815 AS C0812MEI,
   I.C5654 AS C1856SEI,
   I.C5659 AS C1856MEI,
   J.C3597,J.D12652,
   L.C5654 AS D12241TbSEI,
   L.C5659 AS D12241TbMEI,
   C.*,

       K.ROWID,
   Y.C3815 AS SEIKYUUPATTERNMEI
  FROM (SELECT A.C1647 AS C1647Ta,A.C0352 AS C0352Ta,
            A.C0930 AS C0930Ta,A.C1008 AS C1008Ta,
            A.C0538 AS C0538Ta,A.C0026 AS C0026Ta,
            A.C0543 AS C0543Ta,A.C0818 AS C0818Ta,
            A.C0831 AS C0831Ta,A.C3630,A.D12564,A.C1890,
            A.C3645 AS C3645Ta,A.C3183,A.C3638,A.C3639,
            A.C0804,A.C0799,A.C0801,A.C2719,A.C0877,A.C3523,
            A.C3396,A.C0811,A.C0823,A.C0819,A.C0250,A.C1648,
            A.C0805,A.C3581,A.C3583,A.C3584,A.C0813,A.C0812,
            A.C0814,A.D10979,A.D10982,A.D10980,A.D10983,A.D10985,
            A.D10987,A.C3640,A.C0808,A.D12665,
            A.C3419 AS C3419Ta,A.C3417 AS C3417Ta,
            A.C3435 AS C3435Ta,A.C3510 AS C3510Ta,
            A.C3512 AS C3512Ta,A.D12238 AS D12238Ta,
            A.D12239 AS D12239Ta,A.D12240 AS D12240Ta,
            A.D12241 AS D12241Ta,A.D12244 AS D12244Ta,
            A.D12245 AS D12245Ta,B.C1647 AS C1647Tb,
            B.C0352 AS C0352Tb,B.C0930 AS C0930Tb,
            B.C1008 AS C1008Tb,B.C0538 AS C0538Tb,
            B.C0026 AS C0026Tb,B.C0543 AS C0543Tb,
            B.C0818 AS C0818Tb,B.C0831 AS C0831Tb,
            B.C3628,B.C4950,B.C4949,B.C3625,B.C3622,B.C3623,
            B.C3434,B.C3646,B.C3629 AS C3629Tb,
            B.C3620,B.C3643,B.C4933,B.C3540,B.C3441,B.C3707,
            B.C3626,B.C3624,B.C3621,B.D12689,B.C3648,B.C3633,
            B.C3651,B.C3650,B.C3649,B.C3652,B.C3644,B.C3693,
            B.C3599,B.C6401,B.C3660,B.C3588,B.C3591,B.C3589,
            B.C3685,B.C3686,B.C3698,B.C3697,B.C3695,B.C3696,
            B.C3608,B.C6698,B.C6699,B.C1856,B.C5329,
            B.C3419 AS C3419Tb,B.C3417 AS C3417Tb,
            B.C3435 AS C3435Tb,B.C3510 AS C3510Tb,
            B.C3512 AS C3512Tb,B.D12238 AS D12238Tb,
            B.D12239 AS D12239Tb,B.D12240 AS D12240Tb,
            B.D12241 AS D12241Tb,B.D12242,B.D12243,
            B.D12244 AS D12244Tb,B.D12245 AS D12245Tb
      FROM Ta A, Tb B
      WHERE
            A.C1647 = B.C1647 AND A.C0352 = B.C0352 AND
            A.C0930 = B.C0930 AND A.C1008 = B.C1008 AND
            A.C0538 = B.C0538 AND A.C0026 = B.C0026 AND
            A.C0543 = B.C0543 AND A.C0818 = B.C0818 AND
            A.C0831 = B.C0831 AND B.C3629 >= '40000000' AND
            (A.C2719 <= '20060405' AND B.C6698 <= '20060400')) C,
   T1H10 D,T1H10 E,T1H10 F,T1T10 G,
   T1T10 H,T3001 I,H40011 J,T3001 L,Ta K,T1H10 Y
WHERE D.C3813(+) = 'H012' AND D.C3814(+) = C.C3523 || C.C3396 AND
   E.C3813(+) = 'H012' AND E.C3814(+) = C.C4950 || C.C4949 AND
   F.C3813(+) = 'H012' AND F.C3814(+) = C.C4933 || C.C3540 AND
   G.C3813(+) = 'T12' AND G.C3814(+) = C.C0813 AND
   H.C3813(+) = 'T19' AND H.C3814(+) = C.C0812 AND
   I.C5388(+) = C.C1856 AND J.C1647(+) = C.C1647Ta AND
   L.C5388(+) = C.D12241Tb AND Y.C3813(+) = 'H005' AND
   Y.C3814(+) = C.C0823 AND K.C1647 = C.C1647Ta AND
   K.C0352 = C.C0352Ta AND K.C0930 = C.C0930Ta AND
   K.C1008 = C.C1008Ta AND K.C0538 = C.C0538Ta AND
   K.C0026 = C.C0026Ta AND K.C0543 = C.C0543Ta AND
   K.C0818 = C.C0818Ta AND K.C0831 = C.C0831Ta
ORDER BY C.C1647Ta,C.C0352Ta,C.C0930Ta,C.C1008Ta,C.C0538Ta,
      C.C0026Ta,C.C0543Ta,C.C0818Ta,C.C0831Ta

耗时: 00:00:13.07

执行计划
----------------------------------------------------------
0    SELECT STATEMENT ptimizer=ALL_ROWS (Cost=8257 Card=16019 Bytes=40976602)
1 0 NESTED LOOPS (OUTER) (Cost=8257 Card=16019 Bytes=40976602)
2 1    NESTED LOOPS (OUTER) (Cost=7812 Card=414 Bytes=1046178)
3 2    NESTED LOOPS (Cost=6983 Card=414 Bytes=972900)
4 3       NESTED LOOPS (OUTER) (Cost=6932 Card=51 Bytes=116433)
5 4          NESTED LOOPS (OUTER) (Cost=6931 Card=51 Bytes=114852)
6 5          NESTED LOOPS (OUTER) (Cost=5346 Card=51 Bytes=110517)
7 6             MERGE JOIN (Cost=3762 Card=51 Bytes=106182)
8 7                SORT (JOIN) (Cost=1810 Card=2500 Bytes=3170000)
9 8                HASH JOIN (RIGHT OUTER) (Cost=1142 Card=2500 Bytes=3170000)
  10 9                   MAT_VIEW ACCESS (FULL) OF 'T01T10' (MAT_VIEW) (Cost=357 Card=76 Bytes=6460)
  11 9                   HASH JOIN (RIGHT OUTER) (Cost=785 Card=2500 Bytes=2957500)
  12 11                      MAT_VIEW ACCESS (FULL) OF 'T01H10' (MAT_VIEW) (Cost=32 Card=24 Bytes=2040)
  13 11                      HASH JOIN (RIGHT OUTER) (Cost=752 Card=2500 Bytes=2745000)
  14 13                      MAT_VIEW ACCESS (FULL) OF 'T01T10' (MAT_VIEW) (Cost=357 Card=18 Bytes=1530)
  15 13                      HASH JOIN (RIGHT OUTER) (Cost=395 Card=2500 Bytes=2532500)
  16 15                         MAT_VIEW ACCESS (FULL) OF 'T01H10'(MAT_VIEW) (Cost=32 Card=4 Bytes=340)
  17 15                         PARTITION LIST (SINGLE) (Cost=362 Card=2500 Bytes=2320000)
  18 17                            TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'H41011' (TABLE) (Cost=362 Card=2500 Bytes=2320000)
  19 18                            INDEX (RANGE SCAN) OF 'H41011_IDX5' (INDEX) (Cost=20 Card=2500)
  20 7                SORT (JOIN) (Cost=1952 Card=166 Bytes=135124)
  21 20                PARTITION LIST (SINGLE) (Cost=1951 Card=166 Bytes=135124)
  22 21                   TABLE ACCESS (FULL) OF 'H41012' (TABLE)(Cost=1951 Card=166 Bytes=135124)
  23 6             MAT_VIEW ACCESS (FULL) OF 'T01H10' (MAT_VIEW)(Cost=31 Card=1 Bytes=85)
  24 5          MAT_VIEW ACCESS (FULL) OF 'T01H10' (MAT_VIEW) (Cost=31 Card=1 Bytes=85)

25 4          REMOTE* OF 'T03001' (REMOTE) (Cost=1 Card=1 Bytes= 31)
  26 3       PARTITION LIST (SINGLE) (Cost=1 Card=8 Bytes=536)
  27 26          INDEX (RANGE SCAN) OF 'H41011_IDX' (INDEX (UNIQUE)) (Cost=1 Card=8 Bytes=536)
  28 2    PARTITION LIST (SINGLE) (Cost=2 Card=1 Bytes=177)
  29 28       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'H40011' (TABLE) (Cost=2 Card=1 Bytes=177)
  30 29          INDEX (RANGE SCAN) OF 'H40011_INDEX' (INDEX (UNIQUE)) (Cost=1 Card=1)

统计
----------------------------------------------------------
      124  recursive calls
      4  db block gets
   38181  consistent gets
   5639  physical reads
      0  redo size
   11410  bytes sent via SQL*Net to client
   1825  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      11  sorts (memory)
      1  sorts (disk)
      5  rows processed

1.看这个执行计划，中间有个MERGE JOIN (Cost=3762 Card=51 Bytes=106182)代价很大，
如果改成hash应该能好些吧，得测试一下。

2.最后结果集才 5 rows processed 应该不会这么慢吧
连接的表的个数有这么多可以尝试一下指定连接顺序按照形成最小结果集顺序连接
因为表的个数这么多 cbo在判断最适合的连接顺序的时候会要花费很多时间

3.最小连接顺序的指定hint没有？或者按照最小连接顺序使用rbo
或者先去掉排序看快一些没有？发生了一个11 sorts (memory)
1 sorts (disk)其中有一个磁盘排序

4.既然是远程表，又是NL连接居多，应该将其作为驱动表，以减少重复的远程查找。毕竟远程查找代价比本地查找高了几个数量级。

5.如果去掉排序快很多则把原语句的order 提出来变为 select * from (原语句去排序) a order by
排序字段因为最后结果集只有5行只要排序5行了

6.主要排序是merge join引起的，像我上面说的改为hash join就能避免这个。最后结果集的排序问题不大，因为其数据量小。

7.远程表已经有了MAT_VIEW ACCESS (FULL) 物化视图应该和本地表区别不大了吧? 如果是在物化视图里访问的，那跟这个查询没关系。
执行计划里也没有REMOTE字样阿。

8.把remote表先用create table localtable as select * from remotetable@dblink
看看是否快很多？如果快很多，则可以考虑用物化视图把远程表数据刷新到本地库

9.1,未修改前的SQL执行在35秒
2,去除order　ｂｙ后为５秒
3,把order　ｂｙ移到外边,查询为14秒
也就是说,检索结果(5条),order　ｂｙ耗费了9秒的时间?

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

转载于:http://blog.itpub.net/20945761/viewspace-558017/