共享池的管理

show sga

Total System Global Area  726540288 bytes
Fixed Size      2230040 bytes
Variable Size    545261800 bytes
Database Buffers   176160768 bytes
Redo Buffers      2887680 bytes

 

select * from v$sgastat where rownum <=10;
POOL         NAME                            BYTES
------------ -------------------------- ----------
             fixed_sga                     2230040
             buffer_cache                176160768
             log_buffer                    2887680
shared pool  KFG SO child                     8184
shared pool  v_inc_meter_info_problem          664
shared pool  dpslut_kfdsg                      512
shared pool  hot latch diagnostics             160
shared pool  vips_package_file                1320
shared pool  kgqbt_alloc_block                2048
shared pool  kkj jobq  wor                    4128

select pool,sum(bytes)/1024/1024 M from v$sgastat where pool is not null group by pool;
POOL                  M
------------ ----------
java pool             4
streams pool          4
shared pool         268
large pool            4

select sum(bytes)/1024/1024 M from v$sgastat;
         M
----------
452.880638

show parameter shared_pool_size

NAME_COL_PLUS_SHOW_PARAM       TYPE    VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
shared_pool_size        big integer 0

alter system set shared_pool_size=268M;

System altered.

 show parameter shared_pool_size

NAME_COL_PLUS_SHOW_PARAM       TYPE    VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
shared_pool_size        big integer 268M

select * from v$sga_dynamic_components;

select COMPONENT,CURRENT_SIZE,MIN_SIZE,USER_SPECIFIED_SIZE from v$sga_dynamic_components;
COMPONENT                                                        CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE
---------------------------------------------------------------- ------------ ---------- -------------------
shared pool                                                         281018368  264241152           281018368
large pool                                                            4194304    4194304                   0
java pool                                                             4194304    4194304                   0
streams pool                                                          4194304    4194304                   0
DEFAULT buffer cache                                                176160768  176160768                   0
KEEP buffer cache                                                           0          0                   0
RECYCLE buffer cache                                                        0          0                   0
DEFAULT 2K buffer cache                                                     0          0                   0
DEFAULT 4K buffer cache                                                     0          0                   0
DEFAULT 8K buffer cache                                                     0          0                   0
DEFAULT 16K buffer cache                                                    0          0                   0
DEFAULT 32K buffer cache                                                    0          0                   0
Shared IO Pool                                                              0          0                   0
ASM Buffer Cache                                                            0          0                   0