先来看一个“看起来”比较奇怪的现象
SQL> col name for a40
SQL> col value for a30
SQL> select name,value
'db_cache_size');
NAME
---------------------------------------- ------------------------------
shared_pool_size
large_pool_size
java_pool_size
streams_pool_size
db_cache_size
怎么回事,怎么会都是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自动共享内存管理。
如果设置了sga_target的值,没设置这个参数值的话默认就是0,如果设置的话,这是设定该参数的最小值
那么如何查看系统在某个时刻这些参数到底被自动的调整到了什么值呢?
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
NAME
-------------------- ---------- ----------------------------------------
_NUMA_pool_size
__shared_pool_size
shared_pool_size
__large_pool_size
large_pool_size
__java_pool_size
java_pool_size
__streams_pool_size
streams_pool_size
NAME
-------------------- ---------- ----------------------------------------
_io_shared_pool_size 4194304
_backup_io_pool_size 1048576
global_context_pool_
size
olap_page_pool_size
13 rows selected.
SQL> edit
Wrote file afiedt.buf
SQL> /
NAME
-------------------- ---------- ----------------------------------------
__db_cache_size
db_cache_size
从上面结果中黑体部分可以看到,这些由两个下划线开头的参数决定了当前SGA的分配,也是动态内存管理调整的参数。这些参数的更改会被记录到SPFILE文件中,在下一次数据库启动时仍然有效。
通过ORACLE 10G新增加的动态视图v$sga_dynamic_components,可以看各个动态组件调整的时间和调整类型等信息。
SQL> select component,current_size,min_size,last_oper_type,
COMPONENT
-------------------- ------------ ---------- ------------- --------- -----------
--------
shared pool
20:11:08
large pool
java pool
streams pool
DEFAULT buffer cache
20:11:08
KEEP buffer cache
RECYCLE buffer cache
DEFAULT 2K buffer ca
che
DEFAULT 4K buffer ca
COMPONENT
-------------------- ------------ ---------- ------------- --------- -----------
--------
che
DEFAULT 8K buffer ca
che
DEFAULT 16K buffer c
ache
DEFAULT 32K buffer c
ache
COMPONENT
-------------------- ------------ ---------- ------------- --------- -----------
--------
ASM Buffer Cache
13 rows selected.
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wh62592855/archive/2009/11/25/4873859.aspx