oracle sga与granule（引用）
SQL> show sga
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 100666664 bytes
Database Buffers 62914560 bytes
Redo Buffers 2924544 bytes
SQL>
NAME BYTES RES
-------------------- ---------- ---
Fixed SGA Size 1266392 No
Redo Buffers 2924544 No
Buffer Cache Size 62914560 Yes
Shared Pool Size 92274688 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 167772160 No
Startup overhead in 33554432 No
Free SGA Memory Avai 0
SQL>
SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool 4194304
large pool 4194304
java pool 4194304
streams pool 4194304
DEFAULT buffer cache 4194304
KEEP buffer cache 4194304
RECYCLE buffer cache 4194304
DEFAULT 2K buffer cache 4194304
DEFAULT 4K buffer cache 4194304
DEFAULT 8K buffer cache 4194304
DEFAULT 16K buffer cache 4194304
DEFAULT 32K buffer cache 4194304
ASM Buffer Cache 4194304
13 rows selected.
Elapsed: 00:00:00.06
SQL>
SQL> desc v$sga_dynamic_components;
Name Type
--------------------------------------------------------
COMPONENT VARCHAR2(64)
CURRENT_SIZE NUMBER
MIN_SIZE NUMBER
MAX_SIZE NUMBER
USER_SPECIFIED_SIZE NUMBER
OPER_COUNT NUMBER
LAST_OPER_TYPE VARCHAR2(13)
LAST_OPER_MODE VARCHAR2(9)
LAST_OPER_TIME DATE
GRANULE_SIZE NUMBER
SQL>
SQL> select * from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
-------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------
shared pool 92274688 92274688 0 0 0 STATIC 4194304
large pool 4194304 4194304 0 0 0 STATIC 4194304
java pool 4194304 4194304 0 0 0 STATIC 4194304
streams pool 0 0 0 0 0 STATIC 4194304
DEFAULT buffer cache 62914560 62914560 0 0 0 INITIALIZING 4194304
KEEP buffer cache 0 0 0 0 0 STATIC 4194304
RECYCLE buffer cache 0 0 0 0 0 STATIC 4194304
DEFAULT 2K buffer ca 0 0 0 0 0 STATIC 4194304
DEFAULT 4K buffer ca 0 0 0 0 0 STATIC 4194304
DEFAULT 8K buffer ca 0 0 0 0 0 STATIC 4194304
DEFAULT 16K buffer c 0 0 0 0 0 STATIC 4194304
DEFAULT 32K buffer c 0 0 0 0 0 STATIC 4194304
ASM Buffer Cache 0 0 0 0 0 STATIC 4194304
13 rows selected.
Elapsed: 00:00:00.03
SQL>
--------------------------------------------
select * from dba_tab_cols ;
Shared Pool Size
共享池，包含：library cache 和data dictionary cache。
alter system set shared_pool_size = xxx m
library cache：放解析过的sql语句和plsql块，合理设置有助于系统效率。
data dictionary cache： 放数据库的控制信息，描述了数据库的各个方面，使用频繁。
Buffer Cache Size
数据缓存，调高数据命中率可以提高性能。按数据块存放。
db_cache_size
db_keep_cache_size
db_recycle_cache_size
alter system set db_cache_size = xxx M
SQL> show parameter advice
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------
db_cache_advice string ON
SQL>
SQL> select * from v$db_cache_advice;
SQL>
Large Pool Size rman 和一些并行处理时候会用到。
Java Pool Size java存储过程的支持
Streams Pool Size
Redo Buffers
Redo Log Buffer Content
The Oracle server processes copy redo entries from the user’s memory space to the redo log buffer for each DML or DDL statement.
The redo entries contain the information necessary to reconstruct or redo changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. They are used for database recovery. They take up continuous, sequential space in the buffer.
REDO是为了重新实现你的操作，
UNDO是为了撤销你做的操作，
比如你得一个TRANSACTION执行失败了或你自己后悔了，则需要用ROLLBACK命令回退到操作之前。回滚是在逻辑层面实现而不是物理层面，因为在一个多用户系统中，数据结构，blocks等都在时时变化，比如我们INSERT一个数据，表的空间不够，扩展了一个新的EXTENT，我们的数据保存在这新的EXTENT里，其它用户随后也在这EXTENT里插入了数据，而此时我想ROLLBACK，那么显然物理上讲这EXTENT撤销是不可能的，因为这么做会影响其他用户的操作。所以，ROLLBACK是逻辑上回滚，比如对INSERT来说，那么ROLLBACK就是DELETE了。
上文源自：http://blog.sina.com.cn/s/blog_6d6e54f70100o4q3.html
-------------------------------------------------------------------------------------------------------------
关于《深入浅出Oracle》中granule的补充
自9i开始，Oracle引入新的初始化参数db_cache_size;该参数定义主Block Size(db_block_size定义的块大小)的default缓冲池的大小；
Db_cache_size最小值为一个粒度（granule）。
粒度（granule）：粒度是连续虚拟内存分配的单位，粒度是9i新引入的参数，其大小取决于SGA_MAX_SIZE参数所定义的SGA总的大小
当SGA<128M时，粒度值为4M；
否则粒度值为16M
粒度大小受内部隐含参数_ksmg_granule_size的控制
有朋友在Windows2003上测试得出了不同的结论:
SQL> show sga
Total System Global Area 319888364 bytes
Fixed Size 453612 bytes
Variable Size 310378496 bytes
Database Buffers 8388608 bytes
Redo Buffers 667648 bytes
SQL> set linesize 120
SQL> col name for a30
SQL> col value for a20
SQL> col describ for a60
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 '%&par%'
7 /
Enter value for par: _ksmg_granule_size
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%_ksmg_granule_size%'NAME VALUE DESCRIB
------------------------------ -------------------- ---------------------------
_ksmg_granule_size 8388608 granule size in bytes
SQL> alter system set sga_max_size=100M scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.Total System Global Area 114367248 bytes
Fixed Size 453392 bytes
Variable Size 109051904 bytes
Database Buffers 4194304 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。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 '%&par%'
7 /
输入 par 的值: _ksmg_granule_size
原值 6: AND x.ksppinm LIKE '%&par%'
新值 6: AND x.ksppinm LIKE '%_ksmg_granule_size%'NAME VALUE DESCRIB
------------------------------ -------------------- ----------------------------
_ksmg_granule_size 4194304 granule size in bytes
其实这个granule_size在不同平台、不同版本中，Oracle的设置的缺省值都可能不同，书中提到的是一种通常设置，具体的我们知道了Oracle的内存管理方式就好。
Oracle9i的官方文档如下描述:
Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.
Oracle10gR2文档如下描述:
Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger
SGAs. Some platform dependencies arise. For example, on 32-bit Windows, the
granule size is 8 M for SGAs larger than 1 GB.
上文源自：http://www.eygle.com/archives/2007/01/oracle_granule_size.html
