oracle 分区分库,Oracle数据库之12C环境下分库分表改造查询优化

本文主要向大家介绍了Oracle数据库之12C环境下分库分表改造查询优化，通过具体的内容向大家展现，希望对大家学习Oracle数据库有所帮助。

某交易查询库主要使用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行？欢迎大家积极讨论。

总得留点悬念吧

本文由职坐标整理并发布，希望对同学们学习Oracle有所帮助，更多内容请关注职坐标数据库Oracle数据库频道！