昨天同事找我，让我帮忙看两个sql问题，第一个问题是一个sql语句执行频率极高，但是目前的执行速度还是比较慢，希望我看看能不能调优一下。
另外一个问题是一个查询执行速度比较慢，但是执行频率不高。
我们还是循序渐进，一个一个来看。
第一个sql语句如下
5knr1ywqugtq8
/* TransactionLog_selectByAccRangeTrx_3 */
SELECT TRANSACTION_LOG.TRANS_DATE,
TRANSACTION_LOG.TRANS_TYPE,
TRANSACTION_LOG.PARTITION_ID,
TRANSACTION_LOG.ACCOUNT_ID,
TRANSACTION_LOG.TRANSACTION_ID,
TRANSACTION_LOG.SUB_TRANSACTION_ID,
TRANSACTION_LOG.SYS_UPDATE_DATE,
TRANSACTION_LOG.SYS_CREATION_DATE,
TRANSACTION_LOG.DL_SERVICE_CODE,
TRANSACTION_LOG.APPLICATION_ID,
TRANSACTION_LOG.DL_UPDATE_STAMP,
TRANSACTION_LOG.OPERATOR_ID,
TRANSACTION_LOG.PERIOD_KEY,
TRANSACTION_LOG.ENTITY_PERIOD_KEY
FROM TRANSACTION_LOG
WHERE TRANSACTION_LOG.ACCOUNT_ID = :1
AND TRANSACTION_LOG.PARTITION_ID = :2
AND TRANSACTION_LOG.TRANSACTION_ID >= :3
AND TRANSACTION_LOG.TRANSACTION_ID
AND TRANSACTION_LOG.TRANS_TYPE IN
(SELECT /*+ cardinality(1)*/
DISTINCT column_value as transType
FROM table (SELECT CAST(:5 AS ar1_ Varchar2Array_tp) FROM DUAL))
执行计划如下，从执行计划来看，看起来执行计划还是不错的。索引也使用到了，而且消耗也不高。
Plan hash value: 88692238
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 93 (100)| | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 81 | 93 (2)| 00:00:02 | | |
| 3 | VIEW | VW_NSO_1 | 1 | 12 | 19 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 2 | | | | |
|* 5 | FILTER | | | | | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | 8168 | 16336 | 19 (0)| 00:00:01 | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE MULTI-COLUMN | | 7 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 9 | INDEX RANGE SCAN | TRANSACTION_LOG_2IX | 7 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID | TRANSACTION_LOG | 1 | 69 | 73 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------------
索引情况如下：
INDEX_NAME INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
TRANSACTION_LOG_1IX NORMAL NONUNIQUE YES TRANSACTION_ID,TRANS_TYPE TABLE N/A 270774323 01-APR-15 N
TRANSACTION_LOG_2IX NORMAL NONUNIQUE YES ACCOUNT_ID,TRANS_TYPE TABLE N/A 270776953 01-APR-15 N
TRANSACTION_LOG_PK NORMAL UNIQUE YES SUB_TRANSACTION_ID,PARTITION_ID,PERIOD_KEY TABLE N/A 270772197 01-APR-15 N
可以看到索引还是用了TRANSACTION_LOG_2IX，即使用了字段ACCOUNT_ID,TRANS_TYPE来进行数据过滤。
从sql语句的执行和数据分布来看，还是合理的，没有什么问题。根据现在的执行情况，每次执行大约需要2秒以上。期望还是能够达到1秒以内甚至更低。
我们来看看索引TRANSACTION_LOG_1IX和TRANSACTION_LOG_2IX，其实会发现无论是使用第一个还是第二个，数据的分布还是基本平均的。
从集群因子也能看出。
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
----------------- -- -------------------- ----------------- ---------- ----------
TRANSACTION_LOG TRANSACTION_LOG_1IX 183188039 2912269 270768896
TRANSACTION_LOG TRANSACTION_LOG_2IX 237869001 2912269 270768896
TRANSACTION_LOG TRANSACTION_LOG_PK 186148006 2912269 270768896
不过多多少少还是有些差别的。不过差别还是不大，我们还是用sql profile来看看能够给点什么建议。
很快生成了报告，可以看出，改进其实不大。大约只有不到20%的改进。
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000389 .00033 15.16 %
CPU Time (s): .000399 .000299 25.06 %
User I/O Time (s): 0 0
Buffer Gets: 0 0
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 0 0
Fetches: 0 0
Executions: 1 1
而且从改进的执行计划来看,其实主要的差别就在于索引扫描了。索引扫描从原来的range scan建议改为skip scan.
2- Using SQL Profile
--------------------
Plan hash value: 1415584969
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 93 (2)| 00:00:02 | | |
| 1 | NESTED LOOPS | | 1 | 81 | 93 (2)| 00:00:02 | | |
| 2 | VIEW | VW_NSO_1 | 8168 | 98016 | 19 (0)| 00:00:01 | | |
| 3 | HASH UNIQUE | | 1 | 16336 | | | | |
|* 4 | FILTER | | | | | | | |
| 5 | COLLECTION ITERATOR PICKLER FETCH| | 8168 | 16336 | 19 (0)| 00:00:01 | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE MULTI-COLUMN | | 1 | 69 | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | AR1_TRANSACTION_LOG | 1 | 69 | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 9 | INDEX SKIP SCAN | AR1_TRANSACTION_LOG_1IX | 1 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|
---------------------------------------------------------------------------------------------------------------------------------
这个时候，还是很有顾虑的，因为在生产中已经完全能禁用skip scan了，根据产品线的建议是需要禁用的，因为在实际使用中还是碰到了不少的问题，skip scan在有些场景中反而不如range scan,会有full index scan的效果。
所以即使在Hint中知名需要skip scan在生产中也是很可能会走range scan. 在数据库参数中，禁用skip scan的部分如下：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_skip_scan_enabled boolean FALSE
不过对此，也不是没有办法，可以使用opt_param来启用，使用hint的实例如下：
/*+opt_param('_optimizer_skip_scan_enabled', 'true')*/
不过虽然可行，但是不一定我们需要确实值得这么去做。我们还是需要评估一下。
首先skip scan在一些场景中已经碰到了一些相关的问题，不能直接根据执行计划的情况进行调优。执行计划可以作为参考，但不是全部。
二来目前的执行情况来看，就算启用skip scan，收效也不大，提高20%左右尽管在1秒以上,但是还是存在问题。
三来从这个语句来看，这个查询其实还是一个很简单的语句，没有关联表，使用了索引，查询条件也不复杂,调优的空间确实有限。就算退一步来看看使用另外一个索引，其实改进空间也是不大的。
这个可以简单的测试证明，这个查询没有使用transaction_id相关的索引列，其实很大一部分就是因为>=,
我们可以模拟测试一下，把=改为>= 得到的执行计划就会是下面的样子
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 93 (2)| 00:00:02 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 81 | 93 (2)| 00:00:02 | | |
| 3 | VIEW | VW_NSO_1 | 1 | 12 | 19 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 1 | 2 | | | | |
|* 5 | FILTER | | | | | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | 8168 | 16336 | 19 (0)| 00:00:01 | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE MULTI-COLUMN | | 7 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 9 | INDEX RANGE SCAN | TRANSACTION_LOG_2IX | 7 | | 73 (0)| 00:00:01 |KEY(MC)|KEY(MC)|
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID | TRANSACTION_LOG | 1 | 69 | 73 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------------
改进空间还是很有限的。
对此我的建议还是能够从多个方面来考虑。
首先就是索引，目前存在3个索引，其实TRANSACTION_LOG_1IX和TRANSACTION_LOG_2IX都是非唯一性索引,如果能够使用TRANSACTION_LOG_PK其实还是有很大的改进空间。和开发同事的交流来看，他们认为改动难度较大，需要改动的代码量很大,可能需要的时间也较长。但是也不失为一种方式。所以这个方案也是可行但是能不能落地还是一个问号。毕竟在双方的一个权衡中也需要很多的协调。
第二个改进就是和开发的交流和自己抓取的一些信息，发现transaction_id的取值范围还是很大，这也是数据过滤较慢的一个原因。可以从执行计划的谓词信息中看出来一些。首先是根据account_id来进行定位，但是数据过滤是根据transaction_id来做的。做这个时候
9 - access("TRANSACTION_LOG"."ACCOUNT_ID"=:1 AND "TRANSACTION_LOG"."TRANS_TYPE"="TRANSTYPE")
10 - filter(("TRANSACTION_LOG"."TRANSACTION_ID">=:3 AND "TRANSACTION_LOG"."TRANSACTION_ID"
"TRANSACTION_LOG"."PARTITION_ID"=:2))
所以经过讨论一种可行的效果就是能够不尽可能的缩短transaction_id的范围。使得transaction_id的范围尽量小一些，数据过滤就会更加高效。这个从前端来说还是比较容易来实现的。
经过验证发现速度还是比较理想的，调整了范围之后，速度都在毫秒。
Elapsed: 00:00:00.70