db_cache_size、shared_pool_size一些初始化參數為什麼是0？

http://blog.csdn.net/wh62592855/article/details/4873859

先來看一個“看起來”比較奇怪的現象

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

(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

_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

  6* and x.ksppinm like '%db_cache_size%'

SQL> /

__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

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

DEFAULT 8K buffer ca            0          0 STATIC

DEFAULT 16K buffer c            0          0 STATIC

ache