生产环境sql语句调优实战第五篇(r2笔记41天)

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

EstimatedRows

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 (<0.1%)

11 (<0.1%)

.

.

...

11 (<0.1%)

->

7

.......INDEX FULL SCAN

CYC_PAYER_POP_PK

1

2444

..

..

1

793

.

.

.23172 (95%)

.

.

23172 (95%)

.100%

.

.

100%

.100%

.

.

100%

..

.

.

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

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

原文发表时间:2014-07-22

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Netkiller

数据库安全·开发加密插件

以下节选择《Netkiller Architect 手札》 作者:netkiller 地址 http://www.netkiller.cn/archit...

3257
来自专栏杨建荣的学习笔记

ORACLE数据文件名导致的奇怪问题 (51天)

今天创建了一些表空间,准备做data guard来看看效果。 为了方便起见,我用gridcontrol来做,主库也开了Omf,省去了好多步骤。 一路点下来,就等...

3254
来自专栏数据库新发现

如何使用USE_CONCAT提示

USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块. 最后合并所有查询块的结果,返回结果集给用户。

1292
来自专栏菩提树下的杨过

mybatis 使用经验小结

一、多数据源问题 主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Map...

2896
来自专栏杨建荣的学习笔记

关于drop user的cascade选项解惑(52天)

在数据库中,有时候需要删除用户,大多数时候都需要使用cascade选项,有些时候却不需要,想知道在这个简单的命令之后数据库倒底在干什么, 这时候给一些指定的操作...

3818
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第九篇(r3笔记第34天)

生产环境中有一些sql语句是不定时炸弹,不声不响的运行着,可能相关的表很大,运行时间达数小时,甚至数天。 上周在生产环境中发现一条sql语句,运行时间几乎是按照...

3445
来自专栏杨建荣的学习笔记

impdp ORA-39002,ORA-39166,ORA-39164的问题及解决(r2第6天)

今天在做imp和impdp的性能测试时,发现如果表中存在lob字段,加载真是慢的厉害,每秒钟大概1000条的样子,按照这种速度,基本上不用干活了。 比如5千万条...

3227
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第七篇(r2笔记99天)

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了。达到了37279秒。最后在改进调优之后执行速度在1分钟以内。 这个速度...

3478
来自专栏乐沙弥的世界

FORALL 之 SAVE EXCEPTIONS 子句应用一例

     对于大批量的DML操作中出现的错误,除了使用DML error logging特性来记录在DML期间出现的错误之外,使用批量SQL语句FORALL的S...

661
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

3723

扫码关注云+社区

领取腾讯云代金券