今天做了一次statspack分析,发现buffer hit 太低了,才能72.98%, 此数据库上做了物化视图(快速刷新),下面是分析报告
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 72.98 In-memory Sort %: 100.00
Library Hit %: 99.24 Soft Parse %: 98.19
Execute to Parse %: 41.94 Latch Hit %: 99.85
Parse CPU to Parse Elapsd %: 81.20 % Non-Parse CPU: 98.52Execs Buffer get % of total Disk reads Elapsed time (ms)CPU time (ms) SQL statement
840 95052487 5.457046405 86282304 18834037.57 5495690 delete from "HFMIS"."MLOG$_FL" where snaptime$$ <= :1
2022 74544790 4.279681585 69944590 16290974.34 4771100 delete from "HFMIS"."MLOG$_CPZGLSZ" where snaptime$$ <= :1
2063 68900196 3.955620508 64357054 14527408.17 4754970 delete from "HFMIS"."MLOG$_SSUNITINFO" where snaptime$$ <= :1注:MLOG$_FL为物化视图日表,更新方式为主键更新(些表没有建索引,冒似也是无法建索引的)
\请大侠路过帮忙下
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 72.98 In-memory Sort %: 100.00
Library Hit %: 99.24 Soft Parse %: 98.19
Execute to Parse %: 41.94 Latch Hit %: 99.85
Parse CPU to Parse Elapsd %: 81.20 % Non-Parse CPU: 98.52Execs Buffer get % of total Disk reads Elapsed time (ms)CPU time (ms) SQL statement
840 95052487 5.457046405 86282304 18834037.57 5495690 delete from "HFMIS"."MLOG$_FL" where snaptime$$ <= :1
2022 74544790 4.279681585 69944590 16290974.34 4771100 delete from "HFMIS"."MLOG$_CPZGLSZ" where snaptime$$ <= :1
2063 68900196 3.955620508 64357054 14527408.17 4754970 delete from "HFMIS"."MLOG$_SSUNITINFO" where snaptime$$ <= :1注:MLOG$_FL为物化视图日表,更新方式为主键更新(些表没有建索引,冒似也是无法建索引的)
\请大侠路过帮忙下
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货