生产环境sql语句调优实战第五篇

今天在生产环境中发现一条sql语句尽管走了主键索引,但是查询还是很慢。
sql语句类似下面的形式:
SELECT /*+ index (bl1_cyc_payer_pop BL1_CYC_PAYER_POP_PK) */
 T_TAX.BA_NO,
 T_TAX.TOTAL_TAX_AMT,
 T_TAX.TAX_RELATION,
 T_TAX_ITEM.TAX_ITEM_SEQ_NO,
 T_TAX_ITEM.TAX_SEQ_NO,
 T_TAX_ITEM.TAX_AUTHORITY,
 T_TAX_ITEM.TAX_TYPE,
 T_TAX_ITEM.TAX_RATE,
 T_TAX_ITEM.TAX_AMOUNT,
 T_TAX_ITEM.TAXABLE_AMOUNT,
 .......
  FROM T_TAX, T_TAX_ITEM, T_DOCUMENT, T_CYC_PAYER_POP --这几张都是大表,少则500万左右,多则1000多万。
 WHERE T_TAX.TAX_ITEM_PERIOD_KEY = T_TAX_ITEM.PERIOD_KEY
   AND T_TAX.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
   AND T_TAX.BA_NO = T_CYC_PAYER_POP.BA_NO
   AND T_TAX.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
   AND T_TAX.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
   AND T_TAX.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
   AND T_TAX_ITEM.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
   AND T_TAX_ITEM.TAX_SEQ_NO = T_TAX.TAX_SEQ_NO
   AND T_DOCUMENT.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
   AND T_DOCUMENT.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
   AND T_DOCUMENT.BA_NO = T_CYC_PAYER_POP.BA_NO
   AND T_DOCUMENT.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
   AND T_DOCUMENT.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
   AND T_DOCUMENT.DOC_PRODUCE_IND IN ('Y', 'E')
   AND T_CYC_PAYER_POP.CUSTOMER_KEY = 78
   AND T_CYC_PAYER_POP.PERIOD_KEY = 55
   AND T_CYC_PAYER_POP.QA_GROUP = 3
   AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925

查看该sql的执行计划
发现有严重的io问题,瓶颈就在于使用的primary key对应的index
Id Operation Name Estimated
Rows
Cost Active Period 
(56s)
Execs Rows Memory Temp IO Requests CPU Activity Wait Activity

.

0 SELECT STATEMENT

.

.

.

.

.

.

1 67

.

.

.

.

.

1 . NESTED LOOPS

.

.

.

.

.

.

1 67

.

.

.

.

.

2 .. NESTED LOOPS

.

1 2447

.

.

.

1 67

.

.

.

.

.

3 ... NESTED LOOPS

.

1 2446

.

.

.

1 67

.

.

.

.

.

4 .... NESTED LOOPS

.

1 2445

.

.

.

1 9

.

.

.

.

.

5 .....PARTITION RANGE SINGLE

.

1 2444

.

.

.

1 9

.

.

.

.

.

6 ...... TABLE ACCESS BY LOCAL INDEX ROWID CYC_PAYER_POP 1 2444

.

.

.

1 9

.

.

11 (

.

.

-> 7 .......INDEX FULL SCAN CYC_PAYER_POP_PK 1 2444

.

.

1 793

.

.

.

23172 (95%)

.

100%

.

100%

这个问题很值得深究,完全可以使用如下的方式来验证。我尝试使用pk的Hint,另外不加任何hint,看表查询的时候会不会使用index
-->使用hint强制走主键查询
Plan hash value: 3105767292
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                      |     1 |    12 |  2501   (1)| 00:00:31 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                      |     1 |    12 |  2501   (1)| 00:00:31 |   171 |   171 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP      |     1 |    12 |  2501   (1)| 00:00:31 |   171 |   171 |
|*  3 |    INDEX FULL SCAN                 | T_CYC_PAYER_POP_PK   |   541 |       |  2444   (1)| 00:00:30 |   171 |   171 |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T_CYC_PAYER_POP"."QA_GROUP"=3)
   3 - access("T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55 AND
              "T_CYC_PAYER_POP"."CUSTOMER_KEY"=78)
       filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND
              "T_CYC_PAYER_POP"."PERIOD_KEY"=55)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      31395  consistent gets
          0  physical reads
          0  redo size
        910  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)
          8  rows processed

-->来看看不使用hint之后,发生了什么
select  T_CYC_PAYER_POP.CUSTOMER_KEY,
        T_CYC_PAYER_POP.PERIOD_KEY,
        T_CYC_PAYER_POP.QA_GROUP ,
    T_CYC_PAYER_POP.CYCLE_SEQ_NO 
    from T_CYC_PAYER_POP where  T_CYC_PAYER_POP.CUSTOMER_KEY = 78
   AND T_CYC_PAYER_POP.PERIOD_KEY = 55
   AND T_CYC_PAYER_POP.QA_GROUP = 3
   AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925 
    /

Execution Plan
----------------------------------------------------------
Plan hash value: 23637115
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |     1 |    12 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                       |     1 |    12 |     2   (0)| 00:00:01 |   171 |   171 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP     |     1 |    12 |     2   (0)| 00:00:01 |   171 |   171 |
|*  3 |    INDEX RANGE SCAN                | T_CYC_PAYER_POP_5IX |   535 |       |     1   (0)| 00:00:01 |   171 |   171 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55)
   3 - access("T_CYC_PAYER_POP"."QA_GROUP"=3 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        951  consistent gets
        644  physical reads
         80  redo size
        910  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)
          8  rows processed
启用了index range scan,而且从执行计划和统计信息来看,明显要比全索引扫描效率高得多。
可以看到使用index range scan之后,先查询了索引列的信息,然后无法走索引过滤了其他的条件。根据目前的数据情况,这个效率要比全索引效率还高的多。


以下是做了hint的改动之后,统计信息的情况,可以看到明显的改善。对于这个Hint的细节需要和客户做更多的确认,毕竟对于调优不能越调越差,稳定和高效才是关键。
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1178  consistent gets
        756  physical reads
          0  redo size
       3229  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         58  rows processed

上一篇:MySQL-Xtrabackup备份还原


下一篇:HTTP-点开浏览器输入网址背后发生的那点事