--内存调整
select * from v$sga;
--调整前SGA
NAME;;;VALUE
-------------------- ----------
Fixed Size;;;;452184
Variable Size 402653184
Database Buffers;;;251658240
Redo Buffers;;667648
select * from v$sgastat;
POOL;;;;;NAME;BYTES
----------- -------------------------- ----------
fixed_sga;;;452184
buffer_cache;;;;;251658240
log_buffer;;656384
shared pool errors8940
shared pool enqueue;;;;;171860
shared pool KGK heap 3756
shared pool KQR M PO;;;1393788
shared pool KQR S PO;;;;177272
shared pool KQR S SO 5120
shared pool sessions;;;;410040
shared pool sql area;;61446860
POOL;;;;;NAME;BYTES
----------- -------------------------- ----------
shared pool 1M buffer;;2098176
shared pool KGLS heap;;2613480
shared pool PX subheap;;;19684
shared pool parameters;;;39012
shared pool free memory 125812664
shared pool PL/SQL DIANA; 3445584
shared pool FileOpenBlock; 695504
shared pool PL/SQL MPCODE; 637644
shared pool PL/SQL PPCODE48400
shared pool PL/SQL SOURCE14344
shared pool library cache;;;;;19376952
POOL;;;;;NAME;BYTES
----------- -------------------------- ----------
shared pool miscellaneous 8639216
shared pool PLS non-lib hp2068
shared pool joxs heap init4220
shared pool table definiti2632
shared pool trigger defini1128
shared pool trigger inform;528
shared pool trigger source;624
shared pool Checkpoint queue;;;;564608
shared pool VIRTUAL CIRCUITS;;;;265160
shared pool dictionary cache;;;1614976
shared pool KSXR receive buffers; 1032500
POOL;;;;;NAME;BYTES
----------- -------------------------- ----------
shared pool character set object432136
shared pool FileIdentificatonBlock 319452
shared pool message pool freequeue 833032
shared pool KSXR pending messages que;;;840636
shared pool event statistics per sess;;1908760
shared pool fixed allocation callback 268
large pool; free memory; 83886080
Java poolfree memory; 83886080
41 rows selected.
--UGA的大小,UGA主要包含一下部分的内存设置
show parameters; area_size;
NAME TYPE;;;;;VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size;;;;integer;;1048576
create_bitmap_area_size;;;integer;;8388608
hash_area_size;;;;integer;;1048576
sort_area_size;;;;integer;;524288
workarea_size_policy string;;;AUTO
--计算数据缓冲区命中率
select value from v$sysstat; where name='physical reads'; 4383475
select * from v$sysstat; where name='physical reads direct'3834798
select * from v$sysstat; where name='physical reads direct (lob)'; 374616
select * from v$sysstat; where name like 'consistent gets'; 1198738167
select * 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)*100
100-(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 dual
select 0/(17038425+0) from dual
--关于log_buffer
select 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
