1.buffer 命中率:select round((1-(physical.value-direct.value-lobs.value)/logical.value)*100,2) “buffer cache hit ratio” from v$sysstat physical,v$systat 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’;
buffer命中率的正常指标为90%-100%，但在数据库繁忙运行期间有可能低于90%。
2.library命中率:select round(sum(pins-reloads)/sum(pins)*100,2) “library cache hit ratio” from v$librarycache;
library命中率的正常指标范围是95%-100%
3.latch命中率:select round((1-sum(misses+immediate_misses)/sum(gets+immediate_gets))*100,2) “latch hit ratio” from v$latch;
内部结构维护锁命中率通常高于99%，如果该命中率较低，则是因为shared_pool_size参数值过大或者没有使用绑定变量导致硬解析过多。
4.In-Memory Sort命中率select round((1-disk.value/(disk.value+memory.value))*100,2) “in-memory sort ratio” from v$sysstat disk,v$sysstat memory where disk.name=’sorts (disk)’ and memory.name=’sorts (memory)’;
命中率正常指标为99%-100%。
5.buffer nowait 缓冲区中获取数据未等待的比率select round((1-busy.value/tol.value)*100,2) “buffer busy nowait ratio” from (select sum(count) value from v$waitstat where class in (‘dataq block’,’segment header’,’undo header’,’undo block’)) busy,(select value from v$sysstat where name=’session logical reads’) tol;
正确指标是99%-100%.
6.redo nowait 重做缓冲区中获取数据未等待的比率select round((1-waits.value/redos.value)*100,2) “redo nowait ratio” from v$sysstat waits,v$sysstat redos where waits.name=’redo log space requests’ and redos.name=’redo entries’;
正确指标为99%-100%.较低的原因可能是归档速度太慢，联机日志文件太小或者联机日志文件存放在比较慢的存储设备上。
7.parse cpu to parse elapsed 数据库用于分析cpu的时间和分析完成的比率，值较低说明在分析过程中cpu在等待其他的资源。select round((1-cpu.value/total.value)*100,2) “parse cpu to parse elapsed ratio” from v$sysstat cpu,v$sysstat total where cpu.name=’parse time cpu’ and total.name=’parse time elapsed’;
8.non-parse cpu 查询实际运行时间占sql语句运行加解析时间之和的比例，如果值太低表示解析sql语句消耗的时间过长。select round((1-parse.value/total.value)*100,2) “non-parse cpu ratio” from v$sysstat parse,v$sysstat total where parse.name=’parse time cpu’ and total.name=’CPU used by this session’;
9.碎片程度（FSFI）select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI from dba_free_space group by tablespace_name order by tablespace_name;