一条sql语句的建议调优分析

前几天开发的同事问我一个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
               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
               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"
              "TRANSACTION_ID">=:3 AND "TRANSACTION_ID"
  14 - access("TRANSACTION_ID">=:3 AND "TRANSACTION_ID"
       filter("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 =xxx要好很多，而且可控性要好很多。
所以对于过滤条件啊的部分，建议是 (transaction >= and transaction
最后是一个补充的建议，即关键的表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
               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
               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 |       |       |        |      |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

时间： 2024-09-19 09:38:15

一条sql语句的建议调优分析的相关文章

mysqli多查询特性 实现多条sql语句查询_Mysql

mysqli相对于mysql有很多优势,建议大家使用,如果没有了解,可以查看mysql的基础教程: mysqli连接数据库 和 mysqli预处理prepare使用 .不仅如此,mysqli更是支持多查询特性,看下面这段php代码: 复制代码 代码如下: <?php $mysqli = new mysqli("localhost","root","","new"); $mysqli->query("set

iBatis习惯用的16条SQL语句_java

iBatis 简介: iBatis 是apache 的一个开源项目,一个O/R Mapping 解决方案,iBatis 最大的特点就是小巧,上手很快.如果不需要太多复杂的功能,iBatis 是能够满足你的要求又足够灵活的最简单的解决方案,现在的iBatis 已经改名为Mybatis 了. 官网为:http://www.mybatis.org/ 1.输入参数为单个值 <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLo

[20151209]一条sql语句的优化(续).txt

[20151209]一条sql语句的优化(续).txt http://blog.itpub.net/267265/viewspace-1852195/ --上次提到其中1条sql语句: 1.环境: SYSTEM@192.168.99.105:1521/dbcn> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------

通过一条sql语句访问不同数据库服务器中的数据库对象的方法

对象|访问|服务器|数据|数据库|语句 在我们做数据库程序开发的时候,经常会遇到这种情况:需要将一个数据库服务器中的数据导入到另一个数据库服务器的表中.通常我们会使用这种方法:先把一个数据库中的数据取出来放到某出,然后再把这些数据一条条插入到目的数据库中,这种方法效率较低,写起程序来也很繁琐,容易出错.另外一种方法是使用bcp或BULK INSERT语句,将数据导入到一个文件中,再从此文件中导出到目的数据库,这种方法虽然效率稍高,但也有很多不如意的地方,单是在导入时怎样找到另外一台机器上的数据导

sql oracle-关于怎么快速执行10000条sql语句

问题描述 关于怎么快速执行10000条sql语句 由于我的数据库有几千万条数据,每一条查询都会花费0.5秒,但是10000条查询需要半个多小时,所以希望有快速一点的方法,求各位大神指点,下面是我的函数. /** * 这是一个横着的for循环,图的缩放级别是13,11*10方格,不同区域到不同区域的上车点数量 */ public static void CountListPointsOfOnetoOne() { ArrayList ListSql = new ArrayList(); double

sqlserver-求各位大神给一条sql语句，急急急！！！

问题描述 求各位大神给一条sql语句,急急急!!! 我用的是sqlserver数据库,我希望完成这样的功能,有个数据库Test,里面有三张表ABC,我对其中的一张A表进行的分区,另外两张表没有分区,我希望通过sql语句查询到已经分区的表,就是要返回表A,只需获得表A的名称就行了.

把这四条sql语句从sqlsever变成oracle的sql语句，求帮忙

问题描述 把这四条sql语句从sqlsever变成oracle的sql语句,求帮忙 1C 1.SELECT top 1 MENUID+1 from app_menu ORDER BY CAST(MENUID AS int) desc 2.SELECT top 1 idfileNamefilePathcreateTimemd5fileSize FROM VIS_file WHERE md5=#md5# 3. SELECT top 1 USERIDREALNAMEGENDEROTELOEMAILQQ

请帮忙写一条SQL语句查询前一小时信息

问题描述 请帮忙写一条SQL语句查询前一小时信息 MSSQLServer,有个字段DateTime:2015-03-09 16:08:51.617 我想查询前一小时的信息,因为我公司是45分下班的,所以前一小时应该是14:45:00~14:45:00. 请问如何写一条语句可以查到当前小时的前一小时的信息呢?谢谢帮忙. 解决方案 select * from table where DATEDIFF('h',DateTime,now())>1 and DATEDIFF('h',DateTime,no

使用一条sql语句实现多表查询

问题描述 使用一条sql语句实现多表查询 假设有这样3张表: 学生表S (id,Tid,name,sex,age) 教师表T (id,Sid,name,sex,age) 成绩表R (id(学生的id),result(成绩)) 怎样使用一条sql查询成绩大于90的男学生的男性老师的姓名 解决方案 select * from R inner join S on R.id=S.Sid inner join T on xxxxxx where result>90 你学生和老师好像没有对应关系,所以第二个