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