[20180810]exadata--豆腐渣系統的保護神.txt--//最近一段時間,一直在看exdata方面的書籍,我個人的感覺exadata並非善長oltp系統,能通過OLTP獲得好處的就算exadata的快閃記憶體(也叫--//智能快閃記憶體).當然大部分系統負載類型都是混合型的,但是如果你系統OLTP占 ...
[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;
