instance= memory + background process
memory= sga +pga
sga=shared_pool + db_buffer_cache+log_buffer
shared_pool=libary_cache+dictionary_cache
---A 数据缓冲区的命中率--- should be more than 90%
select 1 -
sum(case when name like '%physical reads%' then value else 0 end) /
(sum(case when name like '%db block gets%' then value else 0 end ) +
sum(case when name like '%consistent gets%' then value else 0 end))
as hit_ratio
from v$sysstat;
---B 共享池命中率---should be more than 60%
select s.used,p.VALUE sharedpool_usage_ratio from (select sum(bytes) used from v$sgastat where pool ='shared pool' and name <>'free memory') s,v$parameter p where p.NAME='shared_pool_size';
-----------------1、sql,pl/sql内存命中率,library cache库缓存命中率)
select ((sum(pinhits)/sum(pins))) pinhitratio,sum(reloads) misses ,((sum(pins)/(sum(pins)+sum(reloads)))) relhitratio from v$librarycache
-----------------2、dictionary cache命中率(95%-99%)
select (sum(gets - getmisses -fixed))/sum(gets) "data dictionary hit ratio" from v$rowcache;
---C pga命中率
-----------------1、workarea_size_policy=auto
select * from v$pgastat where name like 'cache hit percentage';
-----------------2、workarea_size_policy=manul
select name,value from V$sysstat where name like '%sort%';
select * From v$sga;
select * from v$sgainfo;
select * from v$sga_dynamic_components;