oracle statspack实例(二)

--内存调整select * from v$sga;--调整前SGANAME;;;VALUE-------------------- ----------Fixed Size;;;;452184Variable Size 402653184Database Buffers;;;251658240Redo Buffers;;667648select * from v$sgastat;POOL;;;;;NAME;BYTES----------- -------------------------- ---------- fixed_sga;;;452184 buffer_cache;;;;;251658240 log_buffer;;656384shared pool errors8940shared pool enqueue;;;;;171860shared pool KGK heap 3756shared pool KQR M PO;;;1393788shared pool KQR S PO;;;;177272shared pool KQR S SO 5120shared pool sessions;;;;410040shared pool sql area;;61446860POOL;;;;;NAME;BYTES ----------- -------------------------- ----------shared pool 1M buffer;;2098176shared pool KGLS heap;;2613480shared pool PX subheap;;;19684shared pool parameters;;;39012shared pool free memory 125812664shared pool PL/SQL DIANA; 3445584shared pool FileOpenBlock; 695504shared pool PL/SQL MPCODE; 637644shared pool PL/SQL PPCODE48400shared pool PL/SQL SOURCE14344shared pool library cache;;;;;19376952POOL;;;;;NAME;BYTES----------- -------------------------- ----------shared pool miscellaneous 8639216shared pool PLS non-lib hp2068shared pool joxs heap init4220shared pool table definiti2632shared pool trigger defini1128shared pool trigger inform;528shared pool trigger source;624shared pool Checkpoint queue;;;;564608 shared pool VIRTUAL CIRCUITS;;;;265160shared pool dictionary cache;;;1614976shared pool KSXR receive buffers; 1032500POOL;;;;;NAME;BYTES----------- -------------------------- ----------shared pool character set object432136shared pool FileIdentificatonBlock 319452shared pool message pool freequeue 833032shared pool KSXR pending messages que;;;840636shared pool event statistics per sess;;1908760shared pool fixed allocation callback 268large pool; free memory; 83886080Java poolfree memory; 8388608041 rows selected.--UGA的大小,UGA主要包含一下部分的内存设置show parameters; area_size;NAME TYPE;;;;;VALUE------------------------------------ ----------- ------------------------------bitmap_merge_area_size;;;;integer;;1048576create_bitmap_area_size;;;integer;;8388608hash_area_size;;;;integer;;1048576sort_area_size;;;;integer;;524288workarea_size_policy string;;;AUTO--计算数据缓冲区命中率select value from v$sysstat; where name='physical reads'; 4383475select * from v$sysstat; where name='physical reads direct'3834798select * from v$sysstat; where name='physical reads direct (lob)'; 374616select * from v$sysstat; where name like 'consistent gets'; 1198738167select * from v$sysstat; where name like 'db block gets'; 53472785 x=physical reads direct+physical reads direct (lob)100-(physical reads-x)/(consistent gets+db block gets-x)*100100-(4383475-3834798-374616)/(1198738167+53472785-3834798-374616)*100--共享池的命中率select sum(pinhits)/sum(pins)*100; 'hit radio' from v$librarycache;--关于排序部分select name,value; from v$sysstat where name like '%sort%';select sorts(disk)/(sorts (memory)+sorts(disk)) from dualselect 0/(17038425+0) from dual--关于log_bufferselect name,value from v$sysstat; where; name in('redo entries','redo buffer allocation retries');redo buffer allocation retries/redo entries; >1%; 考虑增加log_buffer--其他视图v$db_cache_advice、v$pga_target_advice、v$java_pool_advice 和 v$db_shared_pool_advice