12C環(huán)境下分庫分表改造查詢優(yōu)化

某交易查詢庫主要使用Oracle 12.1.0.2.0的In Memory特性緩存三張按月分區(qū)的大表，In Memory組件主要是針對(duì)OLAP應(yīng)用的，而這種應(yīng)用絕大部分的操作都是查詢，而且很多時(shí)候只關(guān)心表中特定的一個(gè)或多個(gè)列，所以in memory特性還可以指定只把表中的特定的一個(gè)或多個(gè)列加載到in memory area當(dāng)中。開始的情況由于并發(fā)等多種因素，跑的還是很快的。隨著時(shí)間的推移，三個(gè)表的數(shù)據(jù)量越來越大，所占用內(nèi)存資源也越來越多。總是出現(xiàn)這樣那樣的問題。如今年上半年該系統(tǒng)的一次故障。

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.

查詢對(duì)應(yīng)的IO情況所反應(yīng)到數(shù)據(jù)庫中的事件是什么
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
根據(jù)其等待時(shí)間，查看對(duì)應(yīng)的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;

執(zhí)行計(jì)劃類似如下：

使用AWR對(duì)比相同時(shí)間不同日期時(shí)間段，查看該SQL在前一天單次執(zhí)行時(shí)間為1,168毫秒，約0.01分。執(zhí)行頻率為171，故障時(shí)間段單次執(zhí)行時(shí)間為102,929毫秒，約1.71分。執(zhí)行的頻率為248。故障時(shí)間段要比平時(shí)多執(zhí)行77次。多出131.67分。
推測故障時(shí)間段明顯比前一天的執(zhí)行頻率要高。是否存在前臺(tái)的用戶點(diǎn)擊某個(gè)按鈕，等了半天沒響應(yīng)，然后就一直點(diǎn)，導(dǎo)致這個(gè)SQL一直重復(fù)的運(yùn)行。

IO資源幾乎耗盡，會(huì)話a在進(jìn)行把磁盤上的數(shù)據(jù)塊讀到內(nèi)存，會(huì)話b，會(huì)話c 同時(shí)也請(qǐng)求這個(gè)數(shù)據(jù)塊。就導(dǎo)致了b、c read by other session。
direct path read表小的時(shí)候?qū)?shù)據(jù)讀到緩存中，表不斷增大后，oracle算法干預(yù)在大于2%的cache后會(huì)采用直接路勁讀的方式，跳過加載緩存。大量的反復(fù)讀取磁盤IO會(huì)將IO耗盡，決定設(shè)定10949事件關(guān)閉該特性。
要使用IN MEMORY特性,需要設(shè)置parallel_degree_policy=AUTO和parallel_force_local=false,才能夠真正意義上的啟動(dòng)IM特性,不然只是執(zhí)行計(jì)劃中的啟用,是假象。
后將parallel_degree_policy改為AUTO。后又重新加載T_SSP_TRANDATA_MPOS表全部進(jìn)入in memory。這么一折騰后，系統(tǒng)穩(wěn)定了一段時(shí)間，可后期還有這樣那樣的問題。
在代碼不改動(dòng)的情況下，開發(fā)和架構(gòu)部同事進(jìn)行了拆表分庫的方案。三個(gè)大表廢棄一張表，另外兩個(gè)表拆分成為4個(gè)表，并按月又進(jìn)行了拆分，一個(gè)月有四個(gè)小表。新庫遷移完成，投產(chǎn)當(dāng)晚，進(jìn)行數(shù)據(jù)校驗(yàn)的同時(shí)發(fā)現(xiàn)該查詢功能還是跑不出結(jié)果該SQL單次執(zhí)行時(shí)間150S以
上，改造這么久無法交差啊。

著手查看SQL，進(jìn)行SQL優(yōu)化。
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;

如下是執(zhí)行計(jì)劃：

該表索引情況：

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

我們都知道創(chuàng)建索引需要查看該表的基數(shù)情況，根據(jù)基數(shù)與總行數(shù)的比值我們就能知道該表某個(gè)列的選擇性。

該7月表的總行數(shù)18228172條，ROOT_XXXX_ORG_NO列的基數(shù)為1，說明都是重復(fù)值該列。
而這個(gè)ROOT_XXXX_ORG_NO索引的選擇性太低了。絕對(duì)是不推薦創(chuàng)建索引的！當(dāng)一個(gè)表中的列選擇性大于20%的時(shí)候，說明該列數(shù)據(jù)分布比較均衡。且出現(xiàn)在where條件中，該列沒有創(chuàng)建索引，那么該列就必須創(chuàng)建索引。

不想多說什么了，既然開發(fā)部門的同事在領(lǐng)導(dǎo)面前無法交差，我們?cè)囍纯从袥]有優(yōu)化的余地。
首先收集一下該表的統(tǒng)計(jì)信息，以及做一下動(dòng)態(tài)采樣。執(zhí)行時(shí)間縮短不少。

明確一下分頁語句一定排序，要不然每次返回結(jié)果都不一樣。業(yè)務(wù)邏輯不嚴(yán)謹(jǐn)?shù)脑掃€行。
這里需要看where條件后面的字段了。
當(dāng)where條件是等值，oder by其他列，那么where條件的列在前，其他列在后。
當(dāng)where條件不等值，order by其他列，那么創(chuàng)建索引就不一定怎么建了，關(guān)鍵看過濾的數(shù)據(jù)多不多！??！
基于以上考慮情況，創(chuàng)建如下索引：

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;

結(jié)果秒出，開發(fā)部門的同事可以交差了。

通過我們的監(jiān)控系統(tǒng)也能感受到此次的優(yōu)化情況，如CPU利用率

內(nèi)存使用率

DBtime監(jiān)控

由原來的各種突起峰值，到現(xiàn)在的平穩(wěn)運(yùn)行。

這里有幾個(gè)疑問，這樣的索引跳掃是否有問題？返回的行數(shù)為什么不是10行？歡迎大家積極討論。

總得留點(diǎn)懸念吧