检测buffer cache的命中率:
SELECT 100-(a.pr-b.prd-c.prdlob)/(d.cg+e.dbg-b.prd-c.prdlob)*100 FROM
(SELECT VALUE pr FROM v$sysstat WHERE NAME='physical reads') a,
(SELECT VALUE prd FROM v$sysstat WHERE NAME='physical reads direct') b,
(SELECT VALUE prdlob FROM v$sysstat WHERE NAME='physical reads direct (lob)') c,
(SELECT VALUE cg FROM v$sysstat WHERE NAME='consistent gets') d,
(SELECT VALUE dbg FROM v$sysstat WHERE NAME='db block gets') e
如果低于结果低于90%,应考虑增大数据缓冲区;
检测共享池的命中率:
SELECT SUM(pinhits)/SUM(pins)*100 hr FROM v$librarycache
低于95%应考虑增大共享池(可能因为没有使用绑定变量,造成共享池空间使用过多)。
sort_area_size的检测:
SELECT dik/dik+mem FROM
(SELECT VALUE dik FROM v$sysstat WHERE NAME='sorts (disk)') a,
(SELECT VALUE mem FROM v$sysstat WHERE NAME='sorts (memory)') b
比例过高应考虑增大sort_area_size