oracle statspack实例(二)

--内存调整

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 errors                           8940
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 PPCODE                   48400
shared pool PL/SQL SOURCE                   14344
shared pool library cache                19376952

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool miscellaneous                 8639216
shared pool PLS non-lib hp                   2068
shared pool joxs heap init                   4220
shared pool table definiti                   2632
shared pool trigger defini                   1128
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 object           432136
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 pool   free 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