db_cache_size、shared_pool_size一些初始化参数为什么是0？

先来看一个“看起来”比较奇怪的现象

SQL> col name for a40
SQL> col value for a30
SQL> select name,value
  2  from v$parameter
  3  where name in
  4  ('large_pool_size','java_pool_size','shared_pool_size','streams_pool_size',
'db_cache_size');

NAME                                     VALUE
---------------------------------------- ------------------------------
shared_pool_size                         0
large_pool_size                          0
java_pool_size                           0
streams_pool_size                        0
db_cache_size                            0

 

怎么回事，怎么会都是0呢？上REFERENCES查一下db_cache_size这个参数，其中有一句话如下：

If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

这下就很清楚了吧，因为使用了10G ASSM自动共享内存管理。

那么如何查看系统在某个时刻这些参数到底被自动的调整到了什么值呢？

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from sys.x$ksppi x,sys.x$ksppcv y
  3  where x.inst_id=userenv('Instance')
  4  and y.inst_id=userenv('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm like '%pool_size%'
  7  /

NAME                 VALUE      DESCRIB
-------------------- ---------- ----------------------------------------
_NUMA_pool_size      Not specif aggregate size in bytes of NUMA pool
                     ied

__shared_pool_size   79691776   Actual size in bytes of shared pool
shared_pool_size     0          size in bytes of shared pool
__large_pool_size    4194304    Actual size in bytes of large pool
large_pool_size      0          size in bytes of large pool
__java_pool_size     4194304    Actual size in bytes of java pool
java_pool_size       0          size in bytes of java pool
__streams_pool_size  0          Actual size in bytes of streams pool
streams_pool_size    0          size in bytes of the streams pool

NAME                 VALUE      DESCRIB
-------------------- ---------- ----------------------------------------
_io_shared_pool_size 4194304    Size of I/O buffer pool from SGA
_backup_io_pool_size 1048576    memory to reserve from the large pool
global_context_pool_            Global Application Context Pool Size in
size                            Bytes

olap_page_pool_size  0          size of the olap page pool in bytes

13 rows selected.

SQL> edit
Wrote file afiedt.buf

  1  select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from sys.x$ksppi x,sys.x$ksppcv y
  3  where x.inst_id=userenv('Instance')
  4  and y.inst_id=userenv('Instance')
  5  and x.indx=y.indx
  6* and x.ksppinm like '%db_cache_size%'
SQL> /

NAME                 VALUE      DESCRIB
-------------------- ---------- ----------------------------------------
__db_cache_size      83886080   Actual size of DEFAULT buffer pool for s
                                tandard block size buffers

db_cache_size        0          Size of DEFAULT buffer pool for standard
                                 block size buffers
从上面结果中黑体部分可以看到，这些由两个下划线开头的参数决定了当前SGA的分配，也是动态内存管理调整的参数。这些参数的更改会被记录到SPFILE文件中，在下一次数据库启动时仍然有效。

通过ORACLE 10G新增加的动态视图v$sga_dynamic_components，可以看各个动态组件调整的时间和调整类型等信息。

SQL> select component,current_size,min_size,last_oper_type,
  2  last_oper_mode,to_char(last_oper_time,'yyyy-mm-dd hh24:mi:ss') LOT
  3  from v$sga_dynamic_components
  4  /

COMPONENT            CURRENT_SIZE   MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
-------------------- ------------ ---------- ------------- --------- -----------
--------
shared pool              79691776   75497472 GROW          IMMEDIATE 2009-11-25
20:11:08
large pool                4194304    4194304 STATIC
java pool                 4194304    4194304 STATIC
streams pool                    0          0 STATIC
DEFAULT buffer cache     83886080   83886080 SHRINK        IMMEDIATE 2009-11-25
20:11:08
KEEP buffer cache               0          0 STATIC
RECYCLE buffer cache            0          0 STATIC
DEFAULT 2K buffer ca            0          0 STATIC
che

DEFAULT 4K buffer ca            0          0 STATIC

COMPONENT            CURRENT_SIZE   MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
-------------------- ------------ ---------- ------------- --------- -----------
--------
che

DEFAULT 8K buffer ca            0          0 STATIC
che

DEFAULT 16K buffer c            0          0 STATIC
ache

DEFAULT 32K buffer c            0          0 STATIC
ache

COMPONENT            CURRENT_SIZE   MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
-------------------- ------------ ---------- ------------- --------- -----------
--------
ASM Buffer Cache                0          0 STATIC

13 rows selected.