执行计划的偏差导致的性能问题

在生产环境中有一条sql语句，查看执行计划来看，效果还是可以接受的。

sql语句类似下面的样子，可以看到里面还使用了比较纠结的外连接。从执行计划来说，默认是走nested loop join，数据的查取中会走索引，从oracle的分析来说这样的效果要好一些。

SELECT /*+ leading(cyc_cust,cyc_pay) */

CUST.CUSTOMER_ID,

CUST.WEIGHT,

CYC_CUST.STATUS,

CYC_CUST.DB_STATUS,

CYC_CUST.UNDO_REQ_TYPE,

CYC_PAY.BA_NO AS BA_NO,

CYC_PAY.STATUS AS PAY_STATUS,

CYC_PAY.DB_STATUS AS PAY_DB_STATUS,

CYC_PAY.UNDO_REQ_TYPE AS PAY_UNDO_REQ_TYPE,

CYC_CUST.CYCLE_SEQ_RUN

FROM CUSTOMER CUST,

CYCLE_CUSTOMERS CYC_CUST,

CYC_PAYER_POP CYC_PAY

WHERE CYC_CUST.PERIOD_KEY = 57

AND CYC_CUST.CUSTOMER_KEY = 87

AND CYC_CUST.CYCLE_SEQ_NO =3325

AND CUST.CUSTOMER_ID = CYC_CUST.CUSTOMER_NO

AND CYC_PAY.PERIOD_KEY(+) = CYC_CUST.PERIOD_KEY

AND CYC_PAY.CUSTOMER_KEY(+) = CYC_CUST.CUSTOMER_KEY

AND CYC_PAY.CUSTOMER_NO(+) = CYC_CUST.CUSTOMER_NO

AND CYC_PAY.CYCLE_SEQ_NO(+) = CYC_CUST.

CYCLE_SEQ_NO

AND (CYC_PAY.UNDO_REQ_TYPE 'N' OR CYC_CUST.UNDO_REQ_TYPE 'N')

ORDER BY CUSTOMER_ID;

预计时间在1分半左右，在生产环境中单个执行的时候差不多控制在20~30秒左右。这样看来实际效果比期望的要好。

---------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2152 | 138K| 8254 (1)| 00:01:40 | | |

| 1 | NESTED LOOPS | | | | | | | |

| 2 | NESTED LOOPS | | 2152 | 138K| 8254 (1)| 00:01:40 | | |

|* 3 | FILTER | | | | | | | |

| 4 | NESTED LOOPS OUTER | | 2152 | 119K| 8039 (1)| 00:01:37 | | |

| 5 | PARTITION RANGE SINGLE | | 4304 | 109K| 6317 (1)| 00:01:16 | KEY | KEY |

| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| CYCLE_CUSTOMERS | 4304 | 109K| 6317 (1)| 00:01:16 | KEY | KEY |

|* 7 | INDEX FULL SCAN | CYCLE_CUSTOMERS_PK | 24 | | 5944 (1)| 00:01:12 | KEY | KEY |

| 8 | PARTITION RANGE SINGLE | | 1 | 31 | 1 (0)| 00:00:01 | KEY | KEY |

|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| CYC_PAYER_POP | 1 | 31 | 1 (0)| 00:00:01 | KEY | KEY |

|* 10 | INDEX RANGE SCAN | CYC_PAYER_POP_1IX | 3 | | 1 (0)| 00:00:01 | KEY | KEY |

|* 11 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | |

| 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | |

但是在生产环境中执行的时候，是通过一个程序来触发的，会并行的执行多个这样的查询，结果性能是越来越糟糕，最后本来执行20秒的语句现在又变成了1分半了。

在尝试加入4个并行之后，效果没有任何的改观。

在排查这个问题的时候有两个方面的考虑，也是在调优中可能比较纠结的问题。

一个是nested loop join和hash join的取舍

这个问题按照理论来说是比较好判断的，如果大表，小表关联，是建议采用nested loop join的。

如果大表，大表关联，是建议采用hash join的。

但是在一些sql的执行过程中，可能根据执行计划走了索引，结果按照预期，过滤出的数据应该很少，在解析的时候就走了nested loop join，但是实际中可能数据分布很不均匀，有时候过滤出的数据多，有时候过滤出的数据少。

这样一来，如果过滤出的数据量大的时候，走了nested loop join 结果就会发送大量的io请求，然后不停的去走关联，瓶颈都消耗在io等待上了。

第二个问题是关于索引和全表扫描

这个问题，可能开发人员的普遍认识就是全表扫描要比索引要慢，大多数情况是，但是dba碰到的更多情况是希望走全表扫描，因为走索引的代价其实更大。如果表很小的情况下，走索引和全表扫描来说，全表扫描要更好一些，

如果表很大的情况下，但是通过索引过滤出的数据也比较多，那么也就建议还是走全表吧。

现在生产环境中碰到的这个sql问题就有如上的两种情况。

这几张表都是上千万的大表，有的表中的数据分布确实不透均匀，结果就在处理某一批数据的时候，性能还可以接受，但是在处理另外一批数据的时候性能抖动就很明显。

如果同时运行多个查询的情况下。可能等待时间都在io上了。

在查看awr报告中的一些指标之后，我尝试修改表的访问路径，把原本的两个表关联由nested loop join变为hash join。

然后两个表都坚持走全表扫描，当然了考虑到了执行的效率，加了4个并行。这样来说就算比较笼统的平衡了数据不均匀的问题。

修改hint为/*+ use_hash(CYC_CUST,CYC_PAY) parallel(CYC_CUST 4) */

从执行计划来看是有些得不偿失的，cpu消耗要高很多。但是在实际执行的时候效果却很好。平均执行时间为10~15秒，执行的很稳定。在做了多个session的并发查询的情况下，速度还是很稳定。

Plan hash value: 4044834379

-------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 567 | 37422 | 45236 (1)| 00:09:03 | | | | | |

| 1 | PX COORDINATOR | | | | | | | | | | |

| 2 | PX SEND QC (ORDER) | :TQ10003 | 567 | 37422 | 45236 (1)| 00:09:03 | | | Q1,03 | P->S | QC (ORDER) |

| 3 | SORT ORDER BY | | 567 | 37422 | 45236 (1)| 00:09:03 | | | Q1,03 | PCWP | |

| 4 | PX RECEIVE | | | | | | | | Q1,03 | PCWP | |

| 5 | PX SEND RANGE | :TQ10002 | | | | | | | Q1,02 | P->P | RANGE |

| 6 | NESTED LOOPS | | | | | | | | Q1,02 | PCWP | |

| 7 | NESTED LOOPS | | 567 | 37422 | 45235 (1)| 00:09:03 | | | Q1,02 | PCWP | |

|* 8 | FILTER | | | | | | | | Q1,02 | PCWC | |

|* 9 | HASH JOIN OUTER | | 567 | 32319 | 45219 (1)| 00:09:03 | | | Q1,02 | PCWP | |

| 10 | PX RECEIVE | | 1134 | 29484 | 8563 (1)| 00:01:43 | | | Q1,02 | PCWP | |

| 11 | PX SEND HASH | :TQ10001 | 1134 | 29484 | 8563 (1)| 00:01:43 | | | Q1,01 | P->P | HASH |

| 12 | PX BLOCK ITERATOR | | 1134 | 29484 | 8563 (1)| 00:01:43 | 171 | 171 | Q1,01 | PCWC | |

|* 13 | TABLE ACCESS FULL | CYCLE_CUSTOMERS | 1134 | 29484 | 8563 (1)| 00:01:43 | 171 | 171 | Q1,01 | PCWP | |

| 14 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |

| 15 | PX RECEIVE | | 1355 | 42005 | 36656 (1)| 00:07:20 | | | Q1,02 | PCWP | |

| 16 | PX SEND HASH | :TQ10000 | 1355 | 42005 | 36656 (1)| 00:07:20 | | | | S->P | HASH |

| 17 | PARTITION RANGE SINGLE| | 1355 | 42005 | 36656 (1)| 00:07:20 | 171 | 171 | | | |

|* 18 | TABLE ACCESS FULL | CYC_PAYER_POP | 1355 | 42005 | 36656 (1)| 00:07:20 | 171 | 171 | | | |

|* 19 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |

| 20 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |

所以执行计划只能作为大家调优的一个参考而已，dba需要对于数据需要有更加深入的了解。自己做到心中有数。

最后分享一下两种方案的执行效果。

采用nested loop join+索引扫描

new1.log:Elapsed: 00:00:56.30

new1.log:Elapsed: 00:00:23.51

new1.log:Elapsed: 00:00:01.46

new1.log:Elapsed: 00:00:01.29

new1.log:Elapsed: 00:00:01.27

new1.log:Elapsed: 00:00:01.28

new1.log:Elapsed: 00:00:01.26

new2.log:Elapsed: 00:00:42.79

new2.log:Elapsed: 00:00:23.51

new2.log:Elapsed: 00:00:01.29

new2.log:Elapsed: 00:00:01.28

new2.log:Elapsed: 00:00:01.27

采用hash join+全表扫描

new1.log:Elapsed: 00:00:14.95

new1.log:Elapsed: 00:00:13.70

new1.log:Elapsed: 00:00:12.81

new1.log:Elapsed: 00:00:13.89

new1.log:Elapsed: 00:00:14.56

new1.log:Elapsed: 00:00:11.61

new1.log:Elapsed: 00:00:11.45

new1.log:Elapsed: 00:00:10.84

new1.log:Elapsed: 00:00:11.31

new2.log:Elapsed: 00:00:12.13

new2.log:Elapsed: 00:00:11.85

new2.log:Elapsed: 00:00:14.44

new2.log:Elapsed: 00:00:15.60

new2.log:Elapsed: 00:00:12.55

new2.log:Elapsed: 00:00:11.09

new2.log:Elapsed: 00:00:11.11

new2.log:Elapsed: 00:00:11.04

new2.log:Elapsed: 00:00:11.14

执行计划的偏差导致的性能问题

继续阅读

ASP编程中20个非常有用的例子

龙珠训练营task04

阿里云天池龙珠计划SQL训练营打卡

阿里云天池龙珠计划SQL训练营day1

赋予用户dba权限--解释以上：

实验楼sql进阶之成绩管理系统的数据操作(window)

Oracle的基本操作

为什么要选择UniDAC

SQL优化SQL语句优化的目的

JAVA高效编程指南

windows不能在本地计算机上运行oracleDbConsoleorcl

关于SQL语言

SQL语言基础：常用的数据查询语句

neo4j之cypher使用文档

sqlServer根据经纬查距离

Oracle 批量查询传入List 返回List