使用select语句查看Oracle的各种命中率和性能统计数据

使用select语句查看Oracle的各种命中率和性能统计数据

--buffer命中率 缓冲区命中率获得的数据与所有访问数据之间的比例,正常指标90%~100%。但在数据库繁忙运行期间也可能低于
--90%。buffer命中率受Oracle sga中data block buffers参数设置影响，也可以通过调整buffer池的使用方法来提高buffer命中率
select round((1-(physical.value-direct.value-lobs.value)/logical.value)*100,2)"缓冲区命中率"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.NAME='physical reads'
      and direct.name='physical reads direct'
      and lobs.name='physical reads direct (lob)'
      and logical.NAME='session logical reads';

--library命中率 库缓冲区命中率指oracle在执行sql语句过程中，，通过内存之间得到对象命名空间的比率,正常指标95%~100%
--受oracle sga中的共享池参数设置影响
select round(sum(pins-reloads)/sum(pins)*100,2)"库缓冲区命中率"
from v$librarycache;

--latch命中率 内部结构维护锁命中率,正常指标99%~100%。如命中率较低，则通常是因为shared_pool_size参数值过大，或者没有使用绑定变量导致硬解析过多
select round((1-sum(misses+immediate_misses)/sum(gets+immediate_gets))*100,2)"latch命中率" from v$latch;

--in-memory sort命中率 指内存中完成排序的数量和在硬盘上进行排序的数量比,正常指标99%~100%。受sort_area_size或pga_aggregate_target参数影响
select round((1-disk.value/(disk.value+memory.value))*100,2)"in-memory sort命中率" from v$sysstat disk,v$sysstat memory where
      disk.NAME='sorts (disk)' and memory.NAME='sorts (memory)';

--buffer nowait 指在缓冲区获取数据未等等待的比率,正常指标99%~100%。受db_block_buffers或db_cache_szie等参数影响
select round((1-busy.value/tol.value)*100,2)"buffer busy nowait ratio" from (select sum(count) value from v$waitstat where
class in ('data block','segment header','undo header','undo block'))busy,
(select value from v$sysstat where)tol;

--redo nowait 指在重做缓冲区中获取数据未等待的比率,正常指标99%~100%。受log_buffer_size等参数影响，比较低
--原因可能是归档数度太慢，联机日志文件太小或联机日志文件存放在比较慢的存储设备上
select round((1-waits.value/redos.value)*100,2)"redo nowait" from v$sysstat waits,v$sysstat redos where
 waits.NAME='redo log space requests'
 and redos.NAME='redo entries';

--execute to parse指sql语句执行和分析的比率,正常指标接近100%。受share_pool_size等参数影响
select round((1-hard.value/total.value)*100,2)"execute to parse" from v$sysstat hard,v$sysstat total
where hard.NAME='parse count (hard)'
and total.NAME='parse count (total)';

--parse cpu to parse elapsed 指数据库用于分析cpu的时间和分析完成的比率,,值比较低说明分析过程中cpu在等待其它资源
select round((1-cpu.value/total.value)*100,2)"parse cpu to parse elapsed" from v$sysstat cpu,v$sysstat total
where cpu.NAME='parse time cpu'
and total.NAME='parse time elapsed';

--non-parse cpu 指查询实际运行时间占sql语句运行加解析时间之和的比例。如果该值太低则表示解析sql语句消耗的时间过长。
select round((1-parse.value/total.value)*100,2)"non-parse cpu" from v$sysstat parse,v$sysstat total
where parse.NAME='parse time cpu'
and total.NAME='CPU used by this session';

--回滚段竞争情况,回滚段竞争情况比率越小越好，该值受回滚大小影响
select name,waits,gets,round(waits/gets*100,2)"回滚段竞争情况"
from v$rollstat a,v$rollname b
where a.USN=b.usn;

--表空间的I/O比例,比例越小越好该值受db_block_size参数值的影响，并且和数据文件在磁盘的分布有关
select df.tablespace_name,sum(f.PHYRDS),sum(f.PHYBLKRD),sum(f.PHYRDS),sum(f.PHYBLKWRT) from v$filestat f,dba_data_files df
where f.FILE#=df.file_id
group by df.tablespace_name order by df.tablespace_name;

--数据文件的I/O比例,比例越小越好该值受db_block_size参数值的影响，并且和数据文件在磁盘的分布有关
select df.name,sum(f.PHYRDS),sum(f.PHYBLKRD),sum(f.PHYRDS),sum(f.PHYBLKWRT) from v$filestat f,v$datafile df
where f.FILE#=df.file#
group by df.name order by df.name;

--重做日志缓冲区命中率,越大越好正常指标应大于90%。该值log_buffer_size参数值的影响。
select name,gets,misses,immediate_gets,immediate_misses,
100-round(decode(gets,0,0,misses/gets*100),2)ratio1,
100-round(decode(immediate_gets+immediate_misses,0,0,immediate_gets/(immediate_gets+immediate_misses)*100),2)ratio2
from v$latch where name in('redo allocation','redo copy');

--碎片程度,越大越好正常指标应大于30%。该值db_block_size和segment_size参数值的影响
select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(count(blocks)))fsfl
from dba_free_space
group by tablespace_name order by tablespace_name;

相关阅读：

浅谈从Oracle数据库中取出10条数据的Select语句与SQL Server、MySQL的区别

Oracle 学习之：for循环中包涵Select语句

Oracle数据库之SQL---Select查询

审计导致Select * 报ORA-01435: user does not exist 

Oracle Select 语句字段连接