一条sql语句的建议调优分析(r5笔记第73天)

前几天开发的同事问我一个sql的问题,目前在测试环境中发现这条sql语句执行时间很长,希望我们能够给一些建议,能够尽快做一些改进。 sql语句类似下面的形式。

SELECT /*+ INDEX(ACCOUNT,ACCOUNT_PK)INDEX(ACCOUNT_EXT ACCOUNT_EXT_PK) */
 ACCOUNT.ACCOUNT_ID,
 ACCOUNT.BE,
 ACCOUNT.CUSTOMER_NO,
 ACCOUNT.AR_BALANCE,
 ACCOUNT_EXT.CYCLE_CODE,
 ACCOUNT_EXT.CYCLE_MONTH,
 ACCOUNT_EXT.CYCLE_YEAR,
 TRX_LOG.MAX_TRX_ID,
 ACCOUNT.L3_AGREEMENT_ID,
 ACCOUNT_EXT.UNBILLED_OC_AMT,
 ACCOUNT_EXT.UB_PEND_CRD,
 ACCOUNT_EXT.BILLED_UNCONF_OC,
 ACCOUNT_EXT.BILLED_UNCONF_RC,
 ACCOUNT_EXT.BILLED_UNCONF_UC,
 NVL(DISPUTE_BALANCE, 0),
 ACCOUNT.L9_CRD_LMT_CALC_FORMULA
  FROM ACCOUNT,
       ACCOUNT_EXT,
       (SELECT /*+ NO_MERGE INDEX(TRANSACTION_LOG,
        TRANSACTION_LOG_1IX) PARALLEL(TRANSACTION_LOG, 8) */
         MAX(TRANSACTION_ID) MAX_TRX_ID, ACCOUNT_ID
          FROM TRANSACTION_LOG
         WHERE ((TRANSACTION_ID >= :1 and
               sys_creation_date <=
               to_date(to_char(sysdate - :2 / 24 / 60 / 60,
                                 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')) OR
               (TRANSACTION_ID >= :3 AND TRANSACTION_ID <= :4 AND
               DL_UPDATE_STAMP = 0))
           and (TRANSACTION_LOG.PERIOD_KEY in (:5, :6, :7))
           AND TRANSACTION_LOG.TRANS_TYPE IN
               (SELECT /*+
                cardinality(1)*/
                DISTINCT column_value as transType
                  FROM table (SELECT CAST(:8 AS Varchar2Array_tp)
                                FROM DUAL))
         GROUP BY ACCOUNT_ID) TRX_LOG
 WHERE ACCOUNT.ACCOUNT_ID = TRX_LOG.ACCOUNT_ID
   AND ACCOUNT.ACCOUNT_ID = ACCOUNT_EXT.ACCOUNT_ID
 ORDER BY TRX_LOG.MAX_TRX_ID

可以看出sql语句似乎是有调优的痕迹的,但是从执行计划来看,似乎还是有些地方出现了问题。 执行计划如下:

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         | 11076 |   941K|       |   445K  (1)| 01:29:05 |       |       |
|   1 |  SORT ORDER BY                             |                         | 11076 |   941K|  1240K|   445K  (1)| 01:29:05 |       |       |
|   2 |   NESTED LOOPS                             |                         |       |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                            |                         | 11076 |   941K|       |   445K  (1)| 01:29:02 |       |       |
|   4 |     NESTED LOOPS                           |                         | 11076 |   594K|       |   444K  (1)| 01:28:49 |       |       |
|   5 |      VIEW                                  |                         | 11076 |   205K|       |   442K  (1)| 01:28:35 |       |       |
|   6 |       HASH GROUP BY                        |                         | 11076 |   389K|       |            |          |       |       |
|   7 |        CONCATENATION                       |                         |       |       |       |            |          |       |       |
|   8 |         NESTED LOOPS                       |                         |  1510K|    51M|       |   263K  (1)| 00:52:39 |       |       |
|   9 |          PARTITION RANGE INLIST            |                         |  5549 |   184K|       |   166K  (1)| 00:33:21 |KEY(I) |KEY(I) |
|* 10 |           TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG         |  5549 |   184K|       |   166K  (1)| 00:33:21 |KEY(I) |KEY(I) |
|* 11 |            INDEX RANGE SCAN                | TRANSACTION_LOG_1IX     |  2436K|       |       |  1811   (1)| 00:00:22 |KEY(I) |KEY(I) |
|* 12 |          COLLECTION ITERATOR PICKLER FETCH |                         |   272 |   544 |       |    17   (0)| 00:00:01 |       |       |
|  13 |           FAST DUAL                        |                         |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|  14 |         NESTED LOOPS                       |                         |  1506K|    51M|       |   179K  (1)| 00:35:56 |       |       |
|  15 |          PARTITION RANGE INLIST            |                         |  5535 |   183K|       | 83402   (1)| 00:16:41 |KEY(I) |KEY(I) |
|* 16 |           TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG         |  5535 |   183K|       | 83402   (1)| 00:16:41 |KEY(I) |KEY(I) |
|* 17 |            INDEX RANGE SCAN                | TRANSACTION_LOG_1IX     |  1218K|       |       |   942   (1)| 00:00:12 |KEY(I) |KEY(I) |
|* 18 |          COLLECTION ITERATOR PICKLER FETCH |                         |   272 |   544 |       |    17   (0)| 00:00:01 |       |       |
|  19 |           FAST DUAL                        |                         |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|  20 |      TABLE ACCESS BY INDEX ROWID           | ACCOUNT                 |     1 |    36 |       |     1   (0)| 00:00:01 |       |       |
|* 21 |       INDEX UNIQUE SCAN                    | ACCOUNT_PK              |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|* 22 |     INDEX UNIQUE SCAN                      | AR9_ACCOUNT_EXT_PK      |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  23 |    TABLE ACCESS BY INDEX ROWID             | AR9_ACCOUNT_EXT         |     1 |    32 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------

对于这条语句的性能瓶颈还是在于下面的子查询,根据执行计划可以看到走了笛卡尔积。

((TRANSACTION_ID >= :1 and
               sys_creation_date <=
               to_date(to_char(sysdate - :2 / 24 / 60 / 60,
                                 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')) OR
               (TRANSACTION_ID >= :3 AND TRANSACTION_ID <= :4 AND
               DL_UPDATE_STAMP = 0))

一般看到这个问题,感觉笛卡尔积性能是非常差的,这个也是相对的。至少从谓词信息来看,优化器还是在内部做了不少的工作,不能直接就说笛卡尔积是低效的。对于笛卡尔积的情况,在itpub中也有一些帖子有相关的讨论,可以参考。http://www.itpub.net/thread-1511375-4-1.html 谓词信息如下:

Predicate Information (identified by operation id):
---------------------------------------------------
   9 - filter(("TRANSACTION_LOG"."PERIOD_KEY"=:5 OR "TRANSACTION_LOG"."PERIOD_KEY"=:6 OR
              "TRANSACTION_LOG"."PERIOD_KEY"=:7) AND ("TRANSACTION_ID">=:1 AND
              "SYS_CREATION_DATE"<=TO_DATE(TO_CHAR(SYSDATE@!-:2/24/60/60,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') OR
              "TRANSACTION_ID">=:3 AND "TRANSACTION_ID"<=:4 AND "DL_UPDATE_STAMP"=0))
  14 - access("TRANSACTION_ID">=:3 AND "TRANSACTION_ID"<=:4)
       filter("TRANSACTION_ID"<=:4 AND "TRANSACTION_ID">=:3)
  17 - access("TRANSACTION_ID">=:1)
       filter("TRANSACTION_ID">=:1)
  18 - filter("TRANSACTION_LOG"."TRANS_TYPE"=VALUE(KOKBF$))
  21 - access("ACCOUNT"."ACCOUNT_ID"="TRX_LOG"."ACCOUNT_ID")
  22 - access("ACCOUNT"."ACCOUNT_ID"="ACCOUNT_EXT"."ACCOUNT_ID")

对于这条语句的调优来说,尽管空间很小,但是还有一些改进的地方。 从调优的Hint来看,有些hint其实是没有使用到的,比如并行的hint,其实这个时候还是能够合理利用起来。改为 parallel_index PARALLEL_INDEX(TRANSACTION_LOG, 8) 接着就是性能瓶颈的过滤条件了,其实过滤条件中最好还是能够有一个范围id的情况,比如(transaction_id >= and transaction_id <=xx 这种情况要比只是指定transaction_id>=xxx要好很多,而且可控性要好很多。 所以对于过滤条件啊的部分,建议是 (transaction >= and transaction <=xx)的形式。 最后是一个补充的建议,即关键的表TRANSACTION_LOG 是一个分区表,所以可以尽可能的使用分区键值。

TABLE_NAME           PARTITION PARTITION_COUNT COLUMN_LIST                    PART_COUNTS SUBPAR_COUNT STATUS
-------------------- --------- --------------- ------------------------------ ----------- ------------ ------
TRANSACTION_LOG  RANGE                 366 PERIOD_KEY,PARTITION_ID                  2            0 VALID
当前表的查询语句只使用到了period_key,如果能够使用到partition_id,会更加高效,所以建议增加一个条件为partition_id
修改后的语句如下:
SELECT /*+ INDEX(ACCOUNT,ACCOUNT_PK)INDEX(ACCOUNT_EXT ACCOUNT_EXT_PK) */
ACCOUNT.ACCOUNT_ID,
ACCOUNT.BE,
ACCOUNT.CUSTOMER_NO,
ACCOUNT.AR_BALANCE,
ACCOUNT_EXT.CYCLE_CODE,
ACCOUNT_EXT.CYCLE_MONTH,
ACCOUNT_EXT.CYCLE_YEAR,
TRX_LOG.MAX_TRX_ID,
ACCOUNT.L3_AGREEMENT_ID,
ACCOUNT_EXT.UNBILLED_OC_AMT,
ACCOUNT_EXT.UB_PEND_CRD,
ACCOUNT_EXT.BILLED_UNCONF_OC,
ACCOUNT_EXT.BILLED_UNCONF_RC,
ACCOUNT_EXT.BILLED_UNCONF_UC,
NVL(DISPUTE_BALANCE, 0),
ACCOUNT.L9_CRD_LMT_CALC_FORMULA
  FROM ACCOUNT,
       ACCOUNT_EXT,
       (SELECT /*+  INDEX(TRANSACTION_LOG,
        TRANSACTION_LOG_1IX) PARALLEL_INDEX(TRANSACTION_LOG, 8) */
         MAX(TRANSACTION_ID) MAX_TRX_ID, ACCOUNT_ID
          FROM TRANSACTION_LOG
         WHERE ((TRANSACTION_ID >= :1 and TRANSACTION_ID <= :4 and
               sys_creation_date <=
               to_date(to_char(sysdate - :2 / 24 / 60 / 60,
                                 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')) OR
               (TRANSACTION_ID >= :3 AND TRANSACTION_ID <= :4 AND
               DL_UPDATE_STAMP = 0))
           and (TRANSACTION_LOG.PERIOD_KEY in (:5, :6, :7))
           and    TRANSACTION_LOG.partition_id in ()
           AND TRANSACTION_LOG.TRANS_TYPE IN
               (SELECT /*+cardinality(1)*/
                DISTINCT column_value as transType
                  FROM table (SELECT CAST(:8 AS Varchar2Array_tp)
                                FROM DUAL))
         GROUP BY ACCOUNT_ID) TRX_LOG
WHERE ACCOUNT.ACCOUNT_ID = TRX_LOG.ACCOUNT_ID
   AND ACCOUNT.ACCOUNT_ID = ACCOUNT_EXT.ACCOUNT_ID
ORDER BY TRX_LOG.MAX_TRX_id

修改后的执行计划如下:

Execution plan as below.
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                         |   530 | 46110 | 24465   (1)| 00:04:54 |       |       |        |      |            |
|   1 |  SORT ORDER BY                               |                         |   530 | 46110 | 24465   (1)| 00:04:54 |       |       |        |      |            |
|   2 |   NESTED LOOPS                               |                         |       |       |            |          |       |       |        |      |            |
|   3 |    NESTED LOOPS                              |                         |   530 | 46110 | 24464   (1)| 00:04:54 |       |       |        |      |            |
|   4 |     NESTED LOOPS                             |                         |   530 | 29150 | 24457   (1)| 00:04:54 |       |       |        |      |            |
|   5 |      VIEW                                    |                         |   530 | 10070 |   176K (87)| 00:35:13 |       |       |        |      |            |
|   6 |       HASH GROUP BY                          |                         |   530 | 20670 |            |          |       |       |        |      |            |
|   7 |        CONCATENATION                         |                         |       |       |            |          |       |       |        |      |            |
|   8 |         NESTED LOOPS                         |                         |  6867 |   261K| 83837   (1)| 00:16:47 |       |       |        |      |            |
|   9 |          PX COORDINATOR                      |                         |       |       |            |          |       |       |        |      |            |
|  10 |           PX SEND QC (RANDOM)                | :TQ10000                |    25 |   925 | 83400   (1)| 00:16:41 |       |       |  Q1,00 | P->S | QC (RAND)  |
|  11 |            PX PARTITION RANGE INLIST         |                         |    25 |   925 | 83400   (1)| 00:16:41 |KEY(I) |KEY(I) |  Q1,00 | PCWC |            |
|* 12 |             TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG         |    25 |   925 | 83400   (1)| 00:16:41 |KEY(I) |KEY(I) |  Q1,00 | PCWP |            |
|* 13 |              INDEX RANGE SCAN                | TRANSACTION_LOG_1IX     |  1218K|       |   942   (1)| 00:00:12 |KEY(I) |KEY(I) |  Q1,00 | PCWP |            |
|* 14 |          COLLECTION ITERATOR PICKLER FETCH   |                         |   272 |   544 |    17   (0)| 00:00:01 |       |       |        |      |            |
|  15 |           FAST DUAL                          |                         |     1 |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|  16 |         NESTED LOOPS                         |                         |   137K|  5229K| 92165   (1)| 00:18:26 |       |       |        |      |            |
|  17 |          PX COORDINATOR                      |                         |       |       |            |          |       |       |        |      |            |
|  18 |           PX SEND QC (RANDOM)                | :TQ20000                |   504 | 18648 | 83400   (1)| 00:16:41 |       |       |  Q2,00 | P->S | QC (RAND)  |
|  19 |            PX PARTITION RANGE INLIST         |                         |   504 | 18648 | 83400   (1)| 00:16:41 |KEY(I) |KEY(I) |  Q2,00 | PCWC |            |
|* 20 |             TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG         |   504 | 18648 | 83400   (1)| 00:16:41 |KEY(I) |KEY(I) |  Q2,00 | PCWP |            |
|* 21 |              INDEX RANGE SCAN                | TRANSACTION_LOG_1IX     |  1218K|       |   942   (1)| 00:00:12 |KEY(I) |KEY(I) |  Q2,00 | PCWP |            |
|* 22 |          COLLECTION ITERATOR PICKLER FETCH   |                         |   272 |   544 |    17   (0)| 00:00:01 |       |       |        |      |            |
|  23 |           FAST DUAL                          |                         |     1 |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|  24 |      TABLE ACCESS BY INDEX ROWID             | ACCOUNT                 |     1 |    36 |     1   (0)| 00:00:01 |       |       |        |      |            |
|* 25 |       INDEX UNIQUE SCAN                      | ACCOUNT_PK              |     1 |       |     1   (0)| 00:00:01 |       |       |        |      |            |
|* 26 |     INDEX UNIQUE SCAN                        | ACCOUNT_EXT_PK          |     1 |       |     1   (0)| 00:00:01 |       |       |        |      |            |
|  27 |    TABLE ACCESS BY INDEX ROWID               | ACCOUNT_EXT             |     1 |    32 |     1   (0)| 00:00:01 |       |       |        |      |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-06-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序员互动联盟

【Windows编程】创建基本控件

前一篇文章我们一起学习了Windows编程基本框架,几乎所有的Windows编程都是以这个模式开始,剩下的就是如何怎么框架的基础上如何添加枝叶实现不同的功能了。...

31660
来自专栏JetpropelledSnake

SQL学习笔记之SQL查询练习1

–1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Co...

11820
来自专栏数据小魔方

MySQL入门学习笔记——七周数据分析师实战作业

本篇推送主要涉及SQL语言中较为复杂的子查询与函数嵌套。 虽然这个MySQL系列取名为MySQL基础入门,但是个人不打算做单个函数的用法总结,或者说简单罗列,...

51570
来自专栏简书专栏

mysql实训2

查询日期为2011-04-05这一天进行过存款的客户ID、客户姓名、银行名称、存款金额。

13520
来自专栏吾爱乐享

mysql数据库高级查询相对比较全的练习题

25210
来自专栏java学习

Oracle基础试题与答案!

表结构: create table tbEmp --职员表 ( eID number(7) primarykey, ...

380120
来自专栏Java技术分享圈

2018-06-03-oracleTest

9830
来自专栏影子

PostgreSQL=>递归查询

转载请注明源地址:http://www.cnblogs.com/funnyzpc/p/8232073.html

17430
来自专栏恰童鞋骚年

走向面试之数据库基础:一、你必知必会的SQL语句练习-Part 1

本文是在Cat Qi的参考原帖的基础之上经本人一题一题练习后编辑而成,非原创,仅润色而已。另外,本文所列题目的解法并非只有一种,本文只是给出比较普通的一种而已,...

20330
来自专栏李蔚蓬的专栏

RFID课程前置——SQL巩固练习

-- 5) 输出所有数据的拨号流水,并且在最后一行添加总呼叫时长。 -- 记录呼叫员编号、对方号码、通话时长 --... -- 汇总[市内号码总时长][长...

14640

扫码关注云+社区

领取腾讯云代金券