检查行缓存命中率等

–检查行缓存命中率
select sum(a.value) / count() pct
from vmetric_history a where a.metric_name = 'Row Cache Hit Ratio' and group_id = 2 and a.begin_time >= sysdate - 1 and a.end_time < sysdate; ---2.5.5 检查库缓存命中率 select sum(a.value) / count(*) pct from vmetric_history a
where a.metric_name = ‘Library Cache Hit Ratio’
and group_id = 2
and a.begin_time >= sysdate - 1
and a.end_time < sysdate;
—检查软分析百分比
select sum(a.value) / count() pct
from vmetric_history a where a.metric_name = 'Soft Parse Ratio' and group_id = 2 and a.begin_time >= sysdate - 1 and a.end_time < sysdate; ---2.5.7 检查share pool可用空间百分比 select freesize freesize_MB, totalsize totalsize_MB, round((freesize / totalsize) * 100, 2) " FREE%" from (select ceil(sum(bytes) / (1024 * 1024)) totalsize from vsgastat
where pool = ‘shared pool’),
(select ceil(bytes / (1024 * 1024)) freesize
from vsgastat where name = 'free memory' and pool = 'shared pool'); ------2.5.8 检查GLOBAL CACHE的性能 : “AVG BLOCK RECEIVE TIME”小于等于15ms。 select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED", b1.value "GCS CR BLOCK RECEIVE TIME", ((b1.value / decode(b2.value, 0, 0.0000001, b2.value)) * 10) "AVG CR BLOCK RECEIVE TIME(ms)", b4.value "GCS CUR BLOCKS RECEIVED", b3.value "GCS CUR BLOCK RECEIVE TIME", ((b3.value / decode(b4.value, 0, 0.0000001, b4.value)) * 10) "AVG CUR BLOCK RECEIVE TIME(ms)" from gvsysstat b1, gv$sysstat b2, gv$sysstat b3, gv$sysstat b4
where b1.name = ‘gc cr block receive time’
and b2.name = ‘gc cr blocks received’
and b1.inst_id = b2.inst_id
and b3.inst_id = b4.inst_id
and b2.inst_id = b3.inst_id
and b3.name = ‘gc current block receive time’
and b4.name = ‘gc current blocks received’;

--------2.5.9 检查GLOBAL CACHE LOCK性能 AVG GLOBAL LOCK GET TIME小于或等于30ms。
select b1.inst_id,
(b1.value + b2.value) “GLOBAL LOCK GETS”,
b3.value “GLOBAL LOCK GET TIME”,
(b3.value /
decode((b1.value + b2.value), 0, 0.0000001, (b1.value + b2.value)) * 10) “AVG GLOBAL LOCK GET TIME (ms)”
from gv$sysstat b1, gv$sysstat b2, gvsysstat b3 where b1.name = 'global lock sync gets' and b2.name = 'global lock async gets' and b3.name = 'global lock get time' and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id or b1.name = 'global enqueue gets sync' and b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time' and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id; --------2.5.12 检查数据库的内存排序比例 数据库的内存排序比例应该高于98%。 select sum(a.value) / count(*) pct from vmetric_history a
where a.metric_name = ‘Memory Sorts Ratio’
and group_id = 2
and a.begin_time >= sysdate - 1
and a.end_time < sysdate;
----检查数据文件IO分布与性能 :系统平均物理读时间小于20ms（“avreads(ms)”
select ts#, file#, name, “avreads(ms)”, phyreads, “avwrites(ms)”, phywrites
from (select b.ts#,
b.file#,
b.name,
ceil(a.value * 10) as “avreads(ms)”,
c.value phyreads,
ceil(d.value * 10) as “avwrites(ms)”,
e.value phywrites
from v$metric a, v$datafile b, v$metric c, v$metric d, vmetric e where a.entity_id = b.file# and a.metric_id = 7000 and c.metric_id = 7002 and d.metric_id = 7001 and e.metric_id = 7003 and c.entity_id = a.entity_id and d.entity_id = a.entity_id and e.entity_id = a.entity_id) order by ts#, file#; ---检查SQL是否绑定变量 select substr(sql_text, 1, 500) "SQL", count(*), sum(executions) "TotExecs" from vsqlarea
where executions < 5
group by substr(sql_text, 1, 500)
having count(*) > 30
order by 2 desc;