12C环境下分库分表改造查询优化

某交易查询库主要使用Oracle 12.1.0.2.0的In Memory特性缓存三张按月分区的大表，In Memory组件主要是针对OLAP应用的，而这种应用绝大部分的操作都是查询，而且很多时候只关心表中特定的一个或多个列，所以in memory特性还可以指定只把表中的特定的一个或多个列加载到in memory area当中。开始的情况由于并发等多种因素，跑的还是很快的。随着时间的推移，三个表的数据量越来越大，所占用内存资源也越来越多。总是出现这样那样的问题。如今年上半年该系统的一次故障。

SQL> r
1 select wait_class_id,wait_class,count() cnt
2 from dba_hist_active_sess_history
3 where snap_id between 12073 and 12074
4 group by wait_class_id,wait_class
5 order by 3 desc

WAIT_CLASS_ID WAIT_CLASS CNT

1740759767 User I/O 12472
2363
3386400367 Commit 2301
1893977003 Other 1093
3875070507 Concurrency 132
4217450380 Application 67
4108307767 System I/O 21
3290255840 Configuration 1

8 rows selected.

查询对应的IO情况所反应到数据库中的事件是什么
EVENT_ID EVENT CNT

3056446529 read by other session 6149
834992820 db file parallel read 4756
2652584166 db file sequential read 1418
3926164927 direct path read 993
506183215 db file scattered read 56
根据其等待时间，查看对应的SQL文本为：

SELECT
FROM (SELECT tmp_page., rownum row_id
FROM (SELECT t.TRAN_UUID,
t.IN_MNO,
t.EX_MNO merchantCode,
t.CARD_TYP,
t.CARD_DISP_NO,
t.TRAN_RESPONSE_CD,
t.TRAN_CD,
t.TRAN_STS,
t.TRAN_SEQ_NO,
t.TRAN_BAT_NO,
to_char(t.TRAN_DATE_TIME, 'YYYYMMDD') AS TRAN_DT,
to_char(t.TRAN_DATE_TIME, 'HH24MISS') AS TRAN_TM,
t.TRAN_IN_MOD payWay,
t.TERMINAL_NUM,
t.POS_SIGN_FLG,
t.TRAN_AMT,
t.RECEIVER_FEE_AMT,
t.TRAN_FLG,
t.ROOT_XXXX_ORG_NM belongtoOrgNm,
t.BUSINESS_EMP_NM empNm,
t.XXXX_ORG_NM directlyOrg,
t.XXXX_ORG_NO,
t.XXXX_ORG_PATH
FROM T_SSP_TRANDATA_MPOS t
WHERE t.TRAN_DATE BETWEEN TO_DATE(:1, 'yyyyMMdd') AND
TO_DATE(:2, 'yyyyMMdd')
AND t.ROOT_XXXX_ORG_NO = :3
AND t.XXXX_ORG_PATH LIKE :4 || '%'
ORDER BY t.TRAN_DATE_TIME DESC) tmp_page
WHERE rownum < = :5)
WHERE row_id > :6;

执行计划类似如下：

使用AWR对比相同时间不同日期时间段，查看该SQL在前一天单次执行时间为1,168毫秒，约0.01分。执行频率为171，故障时间段单次执行时间为102,929毫秒，约1.71分。执行的频率为248。故障时间段要比平时多执行77次。多出131.67分。
推测故障时间段明显比前一天的执行频率要高。是否存在前台的用户点击某个按钮，等了半天没响应，然后就一直点，导致这个SQL一直重复的运行。

IO资源几乎耗尽，会话a在进行把磁盘上的数据块读到内存，会话b，会话c 同时也请求这个数据块。就导致了b、c read by other session。
direct path read表小的时候将数据读到缓存中，表不断增大后，oracle算法干预在大于2%的cache后会采用直接路劲读的方式，跳过加载缓存。大量的反复读取磁盘IO会将IO耗尽，决定设定10949事件关闭该特性。
要使用IN MEMORY特性,需要设置parallel_degree_policy=AUTO和parallel_force_local=false,才能够真正意义上的启动IM特性,不然只是执行计划中的启用,是假象。
后将parallel_degree_policy改为AUTO。后又重新加载T_SSP_TRANDATA_MPOS表全部进入in memory。这么一折腾后，系统稳定了一段时间，可后期还有这样那样的问题。
在代码不改动的情况下，开发和架构部同事进行了拆表分库的方案。三个大表废弃一张表，另外两个表拆分成为4个表，并按月又进行了拆分，一个月有四个小表。新库迁移完成，投产当晚，进行数据校验的同时发现该查询功能还是跑不出结果该SQL单次执行时间150S以
上，改造这么久无法交差啊。

着手查看SQL，进行SQL优化。
SELECT
FROM (SELECT tmp_page., rownum row_id
FROM (SELECT to_char(TRAN_DATE_TIME, 'yyyyMMdd HH24:mm:ss'),
t.TRAN_UUID,
t.IN_MNO,
t.EX_MNO merchantCode,
t.CARD_TYP,
t.CARD_DISP_NO,
t.TRAN_RESPONSE_CD,
t.TRAN_CD,
t.TRAN_STS,
t.TRAN_SEQ_NO,
t.TRAN_BAT_NO,
to_char(t.TRAN_DATE_TIME, 'YYYYMMDD') AS TRAN_DT,
to_char(t.TRAN_DATE_TIME, 'HH24MISS') AS TRAN_TM,
t.TRAN_IN_MOD payWay,
t.TERMINAL_NUM,
t.POS_SIGN_FLG,
t.TRAN_AMT,
t.RECEIVER_FEE_AMT,
t.TRAN_FLG,
t.XXXX_ORG_NO,
t.XXXX_ORG_PATH
FROM T_TRADE_201807_MPOS_2_0001 t
WHERE t.TRAN_DATE BETWEEN TO_DATE('20180701', 'yyyyMMdd') AND
TO_DATE('20180730', 'yyyyMMdd')
AND t.ROOT_XXXX_ORG_NO = '6AAAAAAAAA'
AND t.XXXX_ORG_PATH LIKE '0FDAFDS%'
ORDER BY t.TRAN_DATE_TIME DESC) tmp_page
WHERE rownum < = 10)
WHERE row_id > 0;

如下是执行计划：

该表索引情况：

OWNER INDEX_NAME COLUMN_NAME

XXXX IDX_1807_MPOS_21_XXXX_ORG_NO XXXX_ORG_NO
XXXX IDX_1807_MPOS_21_IN_MNO IN_MNO
XXXX IDX_1807_MPOS_21_ROOT_XXXX_N ROOT_XXXX_ORG_NO
XXXX IDX_1807_MPOS_21_TRAN_DT TRAN_DATE
XXXX IDX_1807_MPOS_21_TRAN_TM TRAN_DATE_TIME
XXXX PK_T_SSP_1807_MPOS_21 TRAN_UUID
XXXX PK_T_SSP_1807_MPOS_21 TRAN_DATE

我们都知道创建索引需要查看该表的基数情况，根据基数与总行数的比值我们就能知道该表某个列的选择性。

该7月表的总行数18228172条，ROOT_XXXX_ORG_NO列的基数为1，说明都是重复值该列。
而这个ROOT_XXXX_ORG_NO索引的选择性太低了。绝对是不推荐创建索引的！当一个表中的列选择性大于20%的时候，说明该列数据分布比较均衡。且出现在where条件中，该列没有创建索引，那么该列就必须创建索引。

不想多说什么了，既然开发部门的同事在领导面前无法交差，我们试着看看有没有优化的余地。
首先收集一下该表的统计信息，以及做一下动态采样。执行时间缩短不少。

明确一下分页语句一定排序，要不然每次返回结果都不一样。业务逻辑不严谨的话还行。
这里需要看where条件后面的字段了。
当where条件是等值，oder by其他列，那么where条件的列在前，其他列在后。
当where条件不等值，order by其他列，那么创建索引就不一定怎么建了，关键看过滤的数据多不多！！！
基于以上考虑情况，创建如下索引：

create index xxx.IDX_1807_MPOS_21_NO_PA on xxx.T_TRADE_201807_MPOS_2_0001 ("TRAN_DATE_TIME","ROOT_xxxx_ORG_NO","xxxx_ORG_PATH") tablespace XXX_IDX online nologging;

结果秒出，开发部门的同事可以交差了。

通过我们的监控系统也能感受到此次的优化情况，如CPU利用率

内存使用率

DBtime监控

由原来的各种突起峰值，到现在的平稳运行。

这里有几个疑问，这样的索引跳扫是否有问题？返回的行数为什么不是10行？欢迎大家积极讨论。

总得留点悬念吧