Oracle SGA自动管理特性(sga_target参数)(二)
--------------
shared_pool_size 293601280
large_pool_size 4194304
java_pool_size 4194304
streams_pool_size 4194304
db_cache_size 285212672
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
log_buffer 6120448
13 rows selected.
--使用临时的pfile来创建spfile
idle> create spfile from pfile='/u02/database/SYBO2SZ/SYBO2SZ.ora.tmp';
File created.
3、参数sga_target非零值的情形
[sql]
--重启db
idle> startup force;
ORACLE instance started.
idle> select distinct isspecified from v$spparameter;
ISSPEC
------
TRUE -->为true表名此时使用了spfile启动数据库
FALSE
--此时sga_max的值为572m
idle> show parameter sga_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 572M
--修改sga_target到非零值
idle> alter system set sga_target=572m;
System altered.
--此时sga_target变成了576m,这个是由于分配粒度和当前已分配内存之和造成的，自动向上取整
idle> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 576M
--参数文件指定的值分配的总大小为564m
idle> select (285212672+4194304+4194304+293601280+4194304)/1024/1024 from dual;
(285212672+4194304+4194304+293601280+4194304)/1024/1024
-------------------------------------------------------
564
--来看一下内存的分配粒度，此时为4mb
idle> @mem_granule
NAME VALUE DESCBTION
----------------------------------- ------------------------------ ----------------------------
_ksmg_granule_size 4194304 granule size in bytes
_ksmg_granule_locking_status 1 granule locking status
--值572m也是4的整数倍，这个应该是有一部分固定或其他方面内存耗用所致当前被分配了576m
--如果参数文件中总大小缩小的话，我们可以指定sga_target<=sga_max_size
idle> select 576/4 from dual;
576/4
----------
144
--从spfile生成pfile来观察sga_target非零值发生了什么变化
idle> create pfile='/tmp/tmp.ora' from spfile;
File created.
--可以看到增加了以dbname开头的且带下划线的和sga_target内存分配相关的项
idle> ho grep size /tmp/tmp.ora
SYBO2SZ.__db_cache_size=285212672
SYBO2SZ.__java_pool_size=4194304
SYBO2SZ.__large_pool_size=4194304
SYBO2SZ.__shared_pool_size=297795584
SYBO2SZ.__streams_pool_size=4194304
*.db_block_size=8192
*.db_cache_size=285212672
*.db_recovery_file_dest_size=1G
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=293601280
*.streams_pool_size=4194304
--/tmp/tmp.ora文件中的sga_target变成了603979776=576m
idle> ho grep sga_target /tmp/tmp.ora
*.sga_target=603979776
--下面编辑/tmp/tmp.ora，移除原来的有关内存的配值，用带下划线的值来启动数据库
idle> vi /tmp/tmp.ora
idle> ho grep size /tmp/tmp.ora
SYBO2SZ.__db_cache_size=