[20180810]exadata--豆腐渣系統的保護神.txt

--//最近一段時間,一直在看exdata方面的書籍,我個人的感覺exadata并非善長oltp系統,能通過OLTP獲得好處的就算exadata的閃存(也叫
--//智能閃存).當然大部分系統負載類型都是混合型的,但是如果你系統OLTP占的比例越大,使用exadata帶來的受益越小.
--//如同你買了一輛豪華平跑車,卻跑在鄉間的小道上.
--//一次開會,跟一位同行閑聊,我跟他提到我們使用exadata更多的是掩蓋應用系統拙劣的設計,拙劣sql語句,保證業務能正常運行.^_^.
--//因為沒有exadata,會頻繁出現性能問題.

--//我拿一個我們生產系統的例子來說明,最近看awr報表發現(自己好久沒看生產系統的awr報表):

IOStat by Function/Filetype summary
'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
Ordered by (Data Read + Write) desc for each function
Function/File Name????????????? Reads: Data?? Reqs per sec??? Data per sec???? Writes: Data? Reqs per sec?? Data per sec Waits: Count Avg Tm(ms)
Smart Scan????????????????????? 267.9G???????? 77.60????????? 76.622M??????????????????? 0M????????? 0.00???????????? 0M?????????? 0? ?
Smart Scan (Data File)????????? 267.9G???????? 77.60????????? 76.622M??????????????????? 0M????????? 0.00???????????? 0M?????????? 0? ?
Buffer Cache Reads????????????? 11.4G???????? 359.56????????? 3.235M???????????????????? 0M????????? 0.00???????????? 0M???? 1228.4K???? 0.50
Buffer Cache Reads (Data File)? 11.4G???????? 359.56????????? 3.235M???????????????????? 0M????????? 0.00???????????? 0M???? 1228.4K???? 0.50
....

--//我注意到以前Smart Scan,Buffer Cache Reads基本一樣,當然排除一些開發執行的一些sql.而現在Smart Scan高出許多倍.有時候更
--//高.明顯不正常
--//即使這樣:
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
log file sync 412,930 0 866 2 0.90 7.56
cell single block physical read 549,727 0 756 1 1.19 6.60
enq: TX - row lock contention 88 0 286 3248 0.00 2.50
SQL*Net more data to client 13,886,052 0 180 0 30.18 1.58
reliable message 394,893 0 140 0 0.86 1.22
cell list of blocks physical read 37,672 0 64 2 0.08 0.56
cell multiblock physical read 41,216 0 52 1 0.09 0.45
cell smart table scan 28,895 44 22 1 0.06
--//cell smart table scan Total Wait Time (s)也就22秒.

--//查詢:
select count(*),sql_id from v$active_session_history where event='cell smart table scan' group by sql_id order by 1 desc;
...

--//當我拿看到的這些sql_id查詢awr報表時,發現這些sql語句根本不出現在awr報表?
--//而我執行如下:
select * from v$active_session_history where sql_id='&sql_id' order by 2 desc;

--//我發現這些語句10分鐘調用1次.而awr報表10秒取樣一次,這些語句被漏掉了.僅僅存在v$active_session_history視圖.
--//我拿其中一條語句分析:

/* Formatted on 2018/8/10 9:34:52 (QP5 v5.269.14213.34769) */
SELECT a.zyh presno
????? ....
????? ,a.jfrq oderDatetime
????? ,'''''' AS diagnosis
? FROM yf_zyfymx a
????? ,yk_typk c
????? ,ms_brda d
????? ,gy_ksdm g
????? ,yf_yflb l
????? ,yk_cddz m
????? ,zy_brry n
?WHERE???? a.zyh = n.zyh
?????? AND n.mzhm = d.mzhm
?????? AND a.ypxh = c.ypxh
?????? AND a.lybq = g.ksdm
?????? AND a.yfsb = l.yfsb
?????? AND a.ypcd = m.ypcd
?????? AND a.yfsb = 4
?????? AND a.ypsl > 0
?????? AND a.jfrq > TO_DATE ('2017-09-20', 'yyyy-mm-dd')
?????? AND NOT EXISTS
????????????? (SELECT? jlxh
???????????????? FROM YF_ZY_LY_UPLOAD
??????????????? WHERE jlxh = a.jlxh AND fy = 1)
?? ??? ??? ? ?
--//注:語句輸出字段很多,我省略了.
--//很明顯a.kfrq查詢范圍很大,導致yf_zyfymx表走全表掃描(表大小10g).走直接路徑讀.類似這樣的語句有4條.
--//僅僅fy = 1 變成別的字段 = 1.

--//還有的問題就是不應該寫成NOT EXISTS,注:fy 僅僅有2個取值.而應該寫成如下:
AND EXISTS (SELECT? jlxh FROM YF_ZY_LY_UPLOAD WHERE jlxh = a.jlxh AND fy = 0)
--//這樣建立fy建立索引,如果fy=0很少的話,也可以加快查詢.但是問題的本質還是前面的查詢時間范圍太大.
--//要修改必須2個都要,這樣效果就很明顯了.

--//實際上正是exadata運行太快,我估計存儲索引在這里發揮很大作用,導致這樣的語句沒有出現在awr報表導致這個語句到現在才發現,我
--//甚至估計a.kfrq > TO_DATE('2017-09-20', 'yyyy-mm-dd')時間是某個銜接項目的上線時間.開發寫這樣代碼我自己真心很無語..

--//結果集隨著時間流逝,變得越來越大....真心不知道開發為什么要這樣寫....

--//查詢Segments by Physical Reads部分:

Segments by Physical Reads
Total Physical Reads: 36,791,770
Captured Segments account for 93.1% of Total
Owner????? Tablespace Name Object Name??????????????? Subobject Name Obj. Type Physical Reads %Total
xxxxxx_yyy xxxxxx_yyy????? MS_CF01?????????????????????????????????? TABLE???????? 17,796,271 48.37
xxxxxx_yyy xxxxxx_yyy????? YF_ZYFYMX???????????????????????????????? TABLE???????? 15,197,689 41.31
xxxxxx_yyy xxxxxx_yyy????? IDX_ZY_FYMX_FYRQ????????????????????????? INDEX??????????? 642,671 1.75
xxxxxx_zzz xxxxxx_zzz????? I_EMR_BL_BASYSJ_JZHM_XMXH_QZ????????????? INDEX??????????? 144,043 0.39
xxxxxx_yyy xxxxxx_yyy????? BQ_TJ02?????????????????????????????????? TABLE??????????? 101,577 0.28

--//從這里也相互驗證.前面2個占了48.37,41.31.

15197689*8192/1024/1024/1024 = 115.94916534423828125000 = 116G
17796271*8192/1024/1024/1024 = 135.77477264404296875000 = 136G
116+136 = 252 G
--//與前面看到IOStat by Function/Filetype summary 的Smart Scan= 267.9G很接近.

總結:
正是exadata的特性掩蓋問題的本質.如果這樣的系統遷移到非exadata設備,系統根本沒法用.換一句話講,上了賊床根本下不來.
也正是我要表達的思想:exadata--豆腐渣系統的保護神.
總而言之,寫好sql語句.優化sql語句才是關鍵.合理的設計才是最重要的.
在加上exadata的特性才能如虎添翼.

實際上我們團隊的態度更加讓人感到沮喪,不去查找問題的本質...而是等待問題的出現....

--//后記:開發修改代碼后YF_ZYFYMX從Segments by Physical Reads消失.上班在看看a.kfrq 的查詢范圍.

Segments by Physical Reads
Total Physical Reads: 4,605,265
Captured Segments account for 76.4% of Total
Owner????? Tablespace Name Object Name??????????????? Subobject Name Obj. Type Physical Reads %Total
xxxxxx_yyy xxxxxx_yyy????? MS_CF01?????????????????????????????????? TABLE???????? 13,165,929 88.75
xxxxxx_yyy xxxxxx_yyy????? ZY_FYMX?????????????????????????????????? TABLE???????????? 86,625 1.88
xxxxxx_yyy xxxxxx_yyy????? BQ_TJ02?????????????????????????????????? TABLE???????????? 53,719 1.17
xxxxxx_zzz xxxxxx_zzz????? I_EMR_BL_BASYSJ_JZHM_XMXH_QZ????????????? INDEX???????????? 40,006 0.87
xxxxxx_yyy xxxxxx_yyy????? I_ZY_FYMX_JFRQ??????????????????????????? INDEX???????????? 25,916 0.56

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
cell smart table scan 5,882 48 3 0 0.01 0.02
--//cell smart table scan Total Wait Time (s)也就3秒.換一句話講僅僅帶來不到20秒的受益.
--//甚至可以這么講,可能走直接路徑讀使用cell smart table scan可能還更快.^_^.我估計可能a.kfrq查詢范圍應該是幾天之前的.
--//這樣走索引效率也不會太高(因為返回記錄多),優化感覺還是很矛盾...
--//順便提一下表MS_CF01也是一樣的問題.類似語句如下:

SELECT a.cfhm presno
....
????? ,k.sfrq oderDatetime
????? ,'''''' AS diagnosis
? FROM ms_cf01 a
????? ,ms_cf02 b
????? ,yk_typk c
????? ,ms_brda d
????? ,gy_ksdm g
????? ,zy_ypyf h
????? ,gy_sypc i
????? ,ms_mzxx k
????? ,yf_yflb l
????? ,yk_cddz m
?WHERE???? a.cfsb = b.cfsb
?????? AND b.ypxh = c.ypxh
?????? AND d.brid = a.brid
?????? AND a.ksdm = g.ksdm
?????? AND b.gytj = h.ypyf(+)
?????? AND b.ypyf = i.pcbm(+)
?????? AND a.fphm = k.fphm
?????? AND a.yfsb = l.yfsb
?????? AND m.ypcd = b.ypcd
?????? AND a.yfsb IN (1, 4, 5)
?????? AND a.kfrq > TO_DATE ('2017-06-26', 'yyyy-mm-dd')
?? ??? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
?????? AND a.zfpb = 0
?????? AND a.fphm IS NOT NULL
?????? AND a.mzxh <> DECODE (a.upload_ly_sf, '', 0, a.upload_ly_sf)
?????? AND a.mzxh <> 0
ORDER BY cfsb DESC;

轉載于:https://www.cnblogs.com/lfree/p/9458407.html

總結

以上是生活随笔為你收集整理的[20180810]exadata--豆腐渣系统的保护神.txt的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。