生产环境sql语句调优实战第五篇-阿里云开发者社区

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

2014-07-22 864

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 今天在生产环境中发现一条sql语句尽管走了主键索引，但是查询还是很慢。 sql语句类似下面的形式： SELECT /*+ index (bl1_cyc_payer_pop BL1_CYC_PAYER_POP_PK) */ T_TAX.

今天在生产环境中发现一条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

Operation

Name

Estimated
Rows

Cost

Active Period
(56s)

Execs

Rows

Memory

Temp

IO Requests

CPU Activity

Wait Activity

SELECT STATEMENT

. NESTED LOOPS

.. NESTED LOOPS

2447

... NESTED LOOPS

2446

.... NESTED LOOPS

2445

.....PARTITION RANGE SINGLE

2444

...... TABLE ACCESS BY LOCAL INDEX ROWID

CYC_PAYER_POP

2444

11 (

.......INDEX FULL SCAN

CYC_PAYER_POP_PK

2444

793

23172 (95%)

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

文章标签：

SQL

索引

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景