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

) 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

执行计划类似如下：

使用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
, 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;

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

该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监控

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