*Soft parse ratio :这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如,软解析率仅为0.2则表示硬解析率太高。不过,如果总解析量 (parse count total) 偏低,这项值可以被忽略。
公式:1 - (parse count (hard)/parse count (total))
select (1-(a.value/b.value))*100 "Soft parse ratio(%)"
from v$sysstat a,v$sysstat b
where a.name='parse count (hard)' and b.name='parse count (total)'
*Buffer cache hit ratio :该项显示buffer cache大小是否合适。
公式: physical reads-physical reads direct-physical reads direct (lob)
1- ----------------------------------------------------------------------------------
db block gets+consistent gets-(physical reads direct+physical reads direct (lob))
select (1-(a.value-b.value-c.value)/(d.value+e.value-(b.value+c.value)))*100 "Buffer hit ratio"
from
v$sysstat a,
v$sysstat b,
v$sysstat c,
v$sysstat d,
v$sysstat e
where
a.name='physical reads' and
b.name='physical reads direct' and
c.name='physical reads direct (lob)' and
d.name='db block gets' and
e.name='consistent gets';
*In-memory sort ratio :该项显示内存中完成的排序所占比例。最理想状态下,在OLTP系统中,大部分排序不仅小并且能够完全在内存里完成排序。
公式:sorts (memory)/ (sorts (memory)+sorts (disk))
select a.value/(a.value+b.value)*100 "In-memory sort ratio"
from v$sysstat a,v$sysstat b
where a.name='sorts (memory)' and b.name='sorts (disk)';
*Parse to execute ratio :在生产环境,最理想状态是一条sql语句一次解析多数运行。
公式:1 - (parse count/execute count)
select (1-(a.value/b.value))*100 "Parse to execute ratio"
from v$sysstat a,v$sysstat b
where a.name='parse count (total)' and b.name='execute count';
*Parse CPU to total CPU ratio :该项显示总的CPU花费在执行及解析上的比率。如果这项比率较低,说明系统执行了太多的解析。
公式:1 - (parse time cpu / CPU used by this session)
select (1-a.value/b.value)*100 "Parse CPU to total CPU ratio"
from v$sysstat a,v$sysstat b
where a.name='parse time cpu' and b.name='CPU used by this session';
*Parse time CPU to parse time elapsed :通常,该项显示锁竞争比率。这项比率计算是否时间花费在解析分配给CPU进行周期运算(即生产工作)。解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费
公式:parse time cpu / parse time elapsed
select (a.value /b.value)*100 "Parse time CPU to parse time elapsed"
from v$sysstat a,v$sysstat b
where a.name ='parse time cpu' and b.name='parse time elapsed';
计算每个事务中block changes可用如下公式:
db block changes/( user commits+user rollbacks)
select a.value/(b.value+c.value) "db Block change"
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='db block changes' and b.name='user commits' and c.name='user rollbacks';
*Blocks changed for each read : 这项显示出block changes在block reads中的比例。它将指出是否系统主要用于只读访问或是主要进行诸多数据操作( 如: inserts/updates/deletes)
公式:db block changes / session logical reads
select a.value/b.value*100 "Blocks changed for each read"
from v$sysstat a,v$sysstat b
where a.name='db block changes' and b.name='session logical reads';
*Rows for each sort :每次排序的列
公式:sorts (rows)/(sorts (memory)+sorts (disk))
select (a.value/(b.value+c.value)) "Rows for each sort"
from v$sysstat a,v$sysstat b,v$sysstat c
公式:1 - (parse count (hard)/parse count (total))
select (1-(a.value/b.value))*100 "Soft parse ratio(%)"
from v$sysstat a,v$sysstat b
where a.name='parse count (hard)' and b.name='parse count (total)'
*Buffer cache hit ratio :该项显示buffer cache大小是否合适。
公式: physical reads-physical reads direct-physical reads direct (lob)
1- ----------------------------------------------------------------------------------
db block gets+consistent gets-(physical reads direct+physical reads direct (lob))
select (1-(a.value-b.value-c.value)/(d.value+e.value-(b.value+c.value)))*100 "Buffer hit ratio"
from
v$sysstat a,
v$sysstat b,
v$sysstat c,
v$sysstat d,
v$sysstat e
where
a.name='physical reads' and
b.name='physical reads direct' and
c.name='physical reads direct (lob)' and
d.name='db block gets' and
e.name='consistent gets';
*In-memory sort ratio :该项显示内存中完成的排序所占比例。最理想状态下,在OLTP系统中,大部分排序不仅小并且能够完全在内存里完成排序。
公式:sorts (memory)/ (sorts (memory)+sorts (disk))
select a.value/(a.value+b.value)*100 "In-memory sort ratio"
from v$sysstat a,v$sysstat b
where a.name='sorts (memory)' and b.name='sorts (disk)';
*Parse to execute ratio :在生产环境,最理想状态是一条sql语句一次解析多数运行。
公式:1 - (parse count/execute count)
select (1-(a.value/b.value))*100 "Parse to execute ratio"
from v$sysstat a,v$sysstat b
where a.name='parse count (total)' and b.name='execute count';
*Parse CPU to total CPU ratio :该项显示总的CPU花费在执行及解析上的比率。如果这项比率较低,说明系统执行了太多的解析。
公式:1 - (parse time cpu / CPU used by this session)
select (1-a.value/b.value)*100 "Parse CPU to total CPU ratio"
from v$sysstat a,v$sysstat b
where a.name='parse time cpu' and b.name='CPU used by this session';
*Parse time CPU to parse time elapsed :通常,该项显示锁竞争比率。这项比率计算是否时间花费在解析分配给CPU进行周期运算(即生产工作)。解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费
公式:parse time cpu / parse time elapsed
select (a.value /b.value)*100 "Parse time CPU to parse time elapsed"
from v$sysstat a,v$sysstat b
where a.name ='parse time cpu' and b.name='parse time elapsed';
计算每个事务中block changes可用如下公式:
db block changes/( user commits+user rollbacks)
select a.value/(b.value+c.value) "db Block change"
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='db block changes' and b.name='user commits' and c.name='user rollbacks';
*Blocks changed for each read : 这项显示出block changes在block reads中的比例。它将指出是否系统主要用于只读访问或是主要进行诸多数据操作( 如: inserts/updates/deletes)
公式:db block changes / session logical reads
select a.value/b.value*100 "Blocks changed for each read"
from v$sysstat a,v$sysstat b
where a.name='db block changes' and b.name='session logical reads';
*Rows for each sort :每次排序的列
公式:sorts (rows)/(sorts (memory)+sorts (disk))
select (a.value/(b.value+c.value)) "Rows for each sort"
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='sorts (rows)' and b.name='sorts (memory)' and c.name='sorts (disk)';
详见《学习动态性能表(一)--v$sysstat》http://www.5ienet.com/note/html/vtables/vtables-v$sysstat.shtml