Oracle服务器性能调整操作手册.docx

内存性能：衡量服务器内存的使用和分配情况1、缓冲区的命中率分类所需等级当前值缓冲区命中率98%数据字典命中率98%库命中率98%内存中的排序98%State=0时X$BH中的缓存10~25%使用最多的前10个SQL语句占用的内存5%调整使用最多的前25个sql语句（内存使用情况最糟糕时）固定/高速缓存经常使用的对象未做程序1：查询缓冲区命中率 select (1 - (sum(decode(name,physical reads,value,0))/ (sum(decode(name,db block gets,value,0))+ sum(decode(name,consistent gets,value,0))))) *100 Hit Ratiofrom v$sysstat程序2：查询数据字典命中率 select (1 - (sum(getmisses)/sum(gets)))*100 Hit Ratiofrom v$rowcache程序3：查询库命中率 select sum(pins)/(sum(pins) +sum(reloads)) * 100 Hit Ratiofrom v$LibraryCache;程序4：查询内存和磁盘排序 select a.value Disk Sorts,b.value Memory Sorts, round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) Pct Memory Sortsfrom v$sysstat a,v$sysstat bwhere a.name= sorts (disk) and b.name=sorts (memory)程序5：查询空闲的数据缓冲区select decode(state,0,FREE, 1,decode(lrba_seq,0,AVAILABLE,BEING USED), 3,BEING USED,state) BLOCK STATUS,COUNT(*)from x$bhgroup by decode(state,0,FREE, 1,decode(lrba_seq,0,AVAILABLE,BEING USED), 3,BEING USED,state)程序6：查询使用最多的前10个SQL语句占用的内存declarecursor c1 isselect buffer_getsfrom v$sqlareaorder by buffer_gets desc;cursor c2 isselect sum(buffer_gets)from v$sqlareaorder by buffer_gets desc;sumof10 number:=0;mybg number;mytotbg number;begindbms_output.put_line(Percent);dbms_output.put_line(----------);open c1;for i in 1..10 loop fetch c1 into mybg; sumof10:=sumof10+mybg;end loop;close c1;open c2;fetch c2 into mytotbg;close c2;dbms_output.put_line(sumof10/mytotbg*100);end;select sum(pct_bufgets)from (select rank() over (order by buffer_gets desc) as rank_bufgets, to_char(100* ratio_to_report(buffer_gets)over(),999.99) pct_bufgets from v$sqlarea) where rank_bufgets11;程序7：查询25个最浪费内存的语句declaretop25 number;text1 varchar2(4000);x number;len1 number;cursor c1 isselect buffer_gets,substr(sql_text,1,4000)from v$sqlareaorder by buffer_gets desc;begindbms_output.put_line(Gets Text);dbms_output.put_line(--------- ----------------------------------------);open c1;for i in 1..25 loopfetch c1 into top25,text1;dbms_output.put_line(rp