当前位置： 首页 > Oracle, oracle 10g, oracle 11g > 正文

分区表相关SQL优化

Jul312015
作者：dbdream   发布：2015-07-31 00:48   分类：Oracle, oracle 10g, oracle 11g     4条评论  

昨晚压力测试，在测试一张大表的时候，50个并非数据库压力立马上来，已经影响线上业务，测试的SQL如下：

SELECT COUNT(*)
  FROM (SELECT 1
          FROM ORD_ORD_BSC_M T, ORD_ORD_DTL_D T1
         WHERE T.ORD_ID = T1.ORD_ID
           AND T1.INST_DTM >=
               to_date('2015-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
           AND T1.INST_DTM <=
               to_date('2015-05-02 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
           AND T1.RJT_RTN_YN = 'N'
         GROUP BY T.ORD_ID) A

ORD_ORD_BSC_M表和ORD_ORD_DTL_D表都是分区表，按照日期每天一个分区，查询一天的数据应该很快，这个SQL写的有问题，单次运行需要2秒，以下是这条SQL的执行计划和统计信息。

SQL> SELECT COUNT(*) 
  2  FROM (SELECT 1 
  3  FROM ORD_ORD_BSC_M T, ORD_ORD_DTL_D T1 
  4  WHERE T.ORD_ID = T1.ORD_ID 
  5  AND T1.INST_DTM >= 
  6  to_date('2015-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
  7  AND T1.INST_DTM <= 
  8  to_date('2015-05-02 23:59:59', 'YYYY-MM-DD HH24:MI:SS') 
  9  AND T1.RJT_RTN_YN = 'N' 
 10  GROUP BY T.ORD_ID) A   
 11  /

  COUNT(*)
----------
     24921

Elapsed: 00:00:02.15

Execution Plan
----------------------------------------------------------
Plan hash value: 1972055624

20150730-00001

 

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

   4 - access("T"."ORD_ID"="T1"."ORD_ID")
   7 - access("T1"."INST_DTM">=TO_DATE(' 2015-05-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T1"."RJT_RTN_YN"='N' AND
              "T1"."INST_DTM"<=TO_DATE(' 2015-05-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter("T1"."RJT_RTN_YN"='N')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     101215  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

从执行计划可以看到，这条SQL走了ORD_ORD_BSC_M表的全局主键，而且对性能影响最大的部分也是全局主键，物理读将近800M，原因是ORD_ORD_DTL_D表指定了时间范围，而ORD_ORD_BSC_M没有指定时间范围，如果ORD_ORD_BSC_M表也指定时间范围，直接定位到分区，性能会提高很多，简单修改了SQL，在ORD_ORD_BSC_M表也指定时间，SQL运行只需要不到0.15秒。

SQL> SELECT COUNT(*)
  2  FROM (SELECT 1
  3  FROM ORD_ORD_BSC_M T, ORD_ORD_DTL_D T1
  4  WHERE T.ORD_ID = T1.ORD_ID
  5  AND T1.INST_DTM >=
  6  to_date('2015-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  7  AND T1.INST_DTM <=
  8  to_date('2015-05-02 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
  9   AND T.INST_DTM >=
 10  to_date('2015-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 11  AND T.INST_DTM <=
 12  to_date('2015-05-02 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
 13  AND T1.RJT_RTN_YN = 'N'
 14  GROUP BY T.ORD_ID) A
 15  /

  COUNT(*)
----------
     24921

Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
Plan hash value: 2087218976

20150730-00002

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

   7 - filter("T"."INST_DTM">=TO_DATE(' 2015-05-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."INST_DTM"<=TO_DATE('
              2015-05-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
   8 - access("T"."ORD_ID"="T1"."ORD_ID")
       filter("T"."ORD_ID"="T1"."ORD_ID")
  11 - access("T1"."INST_DTM">=TO_DATE(' 2015-05-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T1"."RJT_RTN_YN"='N' AND
              "T1"."INST_DTM"<=TO_DATE(' 2015-05-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter("T1"."RJT_RTN_YN"='N')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      23708  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

可见在ORD_ORD_BSC_M同时指定时间范围，SQL的执行计划明显改变，物理读从800M降为180M，运行时间也从2秒多降为0.15秒，压测可以通过。可是我总感觉不对，我记得ORD_ORD_DTL_D表并不是按照INST_DTM时间字段分区的，而是由INST_DTM时间字段修改成了另一个时间类型的字段，于是查了下，发现ORD_ORD_DTL_D表真的不是按照INST_DTM时间分区的。

SQL> SELECT * FROM USER_PART_KEY_COLUMNS WHERE NAME in ('ORD_ORD_DTL_D','ORD_ORD_BSC_M');

NAME                           OBJEC COLUMN_NAME          COLUMN_POSITION
------------------------------ ----- -------------------- ---------------
ORD_ORD_BSC_M                  TABLE INST_DTM                           1
ORD_ORD_DTL_D                  TABLE ORD_ACP_DTM                        1

经和相关人员确认，INST_DTM字段和ORD_ACP_DTM的时间基本一致，正常就应该使用ORD_ACP_DTM字段的，是程序没有更新，修改查询条件为ORD_ACP_DTM后，压测的性能又提高很多，以下是SQL的执行计划等信息。

SQL> SELECT COUNT(*)
  2  FROM (SELECT 1
  3  FROM ORD_ORD_BSC_M T, ORD_ORD_DTL_D T1
  4  WHERE T.ORD_ID = T1.ORD_ID
  5  AND T1. ORD_ACP_DTM >=
  6  to_date('2015-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  7  AND T1. ORD_ACP_DTM <=
  8  to_date('2015-05-02 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
  9  AND T.INST_DTM >=
 10  to_date('2015-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 11  AND T.INST_DTM <=
 12  to_date('2015-05-02 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
 13  AND T1.RJT_RTN_YN = 'N'
 14  GROUP BY T.ORD_ID) A
 15  /

  COUNT(*)
----------
     24921

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 1378240629

20150730-00003

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

   4 - access("T"."ORD_ID"="T1"."ORD_ID")
   6 - filter("T"."INST_DTM">=TO_DATE(' 2015-05-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."INST_DTM"<=TO_DATE(' 2015-05-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
   8 - filter("T1"."RJT_RTN_YN"='N' AND "T1"."ORD_ACP_DTM">=TO_DATE(' 2015-05-02 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "T1"."ORD_ACP_DTM"<=TO_DATE(' 2015-05-02 23:59:59', 'syyyy-mm-dd
              hh24:mi:ss'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5135  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

虽然执行时间没有明显变化，可是COST从2万多降为1千多，物理读从180M降为40M，对频繁执行的SQL来说，这会大大降低磁盘I/O的压力。开发人员问为什么没有走索引，从这个SQL看，只有RJT_RTN_YN列会用到索引，可是这个列只有Y和N两个值，所以并没有 走索引。开发人员考虑增加商品信息字段后在测试下，增加商品信息查询条件后，SQL执行计划走了索引，SQL运行时间从0.14秒降为0.02秒。

SQL> SELECT COUNT(*)
  2    FROM (SELECT 1
  3            FROM ORD_ORD_BSC_M T, ORD_ORD_DTL_D T1
  4      WHERE T.ORD_ID = T1.ORD_ID
  5        AND T.INST_DTM >=
  6            to_date('2015-07-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  7        AND T.INST_DTM <=
  8            to_date('2015-07-14 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
  9        AND T1.ORD_ACP_DTM >=
 10            to_date('2015-07-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 11        AND T1.ORD_ACP_DTM <=
 12            to_date('2015-07-14 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
 13        AND T1.RJT_RTN_YN = 'N'
 14               AND T1.PRD_ID = '504989'
 15         AND T1.UNT_PRD_ID = '504989101'
 16         GROUP BY T.ORD_ID) A
 17  /

  COUNT(*)
----------
         0

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1539515252

20150730-00004

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

   7 - filter("T1"."ORD_ACP_DTM">=TO_DATE(' 2015-07-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T1"."RJT_RTN_YN"='N'
              AND "T1"."ORD_ACP_DTM"<=TO_DATE(' 2015-07-14 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  11 - access("T1"."PRD_ID"='504989')
  14 - access("T1"."UNT_PRD_ID"='504989101')
       filter("T1"."UNT_PRD_ID"='504989101')
  15 - access("T"."ORD_ID"="T1"."ORD_ID")
  16 - filter("T"."INST_DTM">=TO_DATE(' 2015-07-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."INST_DTM"<=TO_DATE('
              2015-07-14 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

物理读只有100多K，这才是正常的SQL嘛，压测毫无压力，压测最担心的问题就这样解决掉了。

本文固定链接: https://www.dbdream.com.cn/2015/07/%e5%88%86%e5%8c%ba%e8%a1%a8%e7%9b%b8%e5%85%b3sql%e4%bc%98%e5%8c%96/ | 信春哥，系统稳，闭眼上线不回滚！

该日志由 dbdream 于2015年07月31日发表在 Oracle, oracle 10g, oracle 11g 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 分区表相关SQL优化 | 信春哥，系统稳，闭眼上线不回滚！
关键字: ,
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！

分区表相关SQL优化：目前有4 条留言

  1. 板凳
    luxus:

    可见在ORD_ORD_BSC_M同时指定时间范围，SQL的执行计划明显改变，物理读从800M降为180M，在哪里看是800M到180M呀

    2015-07-31 14:11 [回复]
    • 宋春风:

      101215 consistent gets 800M23708 consistent gets 180M5135 consistent gets 40M

      2015-07-31 20:34 [回复]
      • luxus:

        学习了

        2015-08-03 08:24 [回复]
  2. 沙发
    yudenanren:

    那个group by 没有用吧，应该可以去掉吧?

    2015-12-08 13:58 [回复]

发表评论

快捷键：Ctrl+Enter