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

[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;