data buffer cache的一點總結 -- 轉

這部分東西涉及的引數較多，感覺稍微有些亂，簡單的總結一下。

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter keep

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
control_file_record_keep_time integer 7
db_keep_cache_size big integer 0
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 160M
sga_target big integer 160M
SQL> alter system set sga_max_size=200m scope=spfile;

系統已更改。

SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。

Total System Global Area 209715200 bytes
Fixed Size 1248164 bytes
Variable Size 113247324 bytes
Database Buffers 92274688 bytes
Redo Buffers 2945024 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 200M
sga_target big integer 160M
SQL> alter system set db_keep_cache_size=5m;

系統已更改。

SQL> alter table tt storage (buffer_pool keep);

表已更改。
SQL> select table_name,buffer_pool from dba_tables where table_name='TT' and own
er='SYS';

TABLE_NAME BUFFER_
------------------------------ -------
TT KEEP

SQL> select tt.*,rowid from tt;

ID SCN ROWID
---------- ---------- ------------------
1 230015 AAACgaAAFAAAACQAAA
2 230127 AAACgaAAFAAAACQAAB
3 230243 AAACgaAAFAAAACQAAC
4 230282 AAACgaAAFAAAACQAAD
4 250729 AAACgaAAFAAAACQAAE
5 250741 AAACgaAAFAAAACQAAF
6 250888 AAACgaAAFAAAACQAAG
7 251493 AAACgaAAFAAAACQAAH
8 251590 AAACgaAAFAAAACQAAI
9 251595 AAACgaAAFAAAACQAAJ
10 251602 AAACgaAAFAAAACQAAK

ID SCN ROWID
---------- ---------- ------------------
11 11 AAACgaAAFAAAACQAAL
12 11 AAACgaAAFAAAACQAAM

已選擇13行。

[@more@]

SQL> select file_id,extent_id , block_id ,blocks FROM dba_extents where owner='S
YS' and segment_name='TT';

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
5 0 137 8

SQL> select data_object_id from dba_objects where owner='SYS' and object_name='T
T';

DATA_OBJECT_ID
--------------
10266

SQL> select file#,block#,status from v$bh where objd=10266;

FILE# BLOCK# STATUS
---------- ---------- -------
5 139 xcur
5 142 xcur
5 140 xcur
5 143 xcur
5 141 xcur
5 144 xcur

已選擇6行。

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_numb
er(rowid) bno from tt;

FNO BNO
---------- ----------
5 144
5 144
5 144
5 144
5 144
5 144
5 144
5 144
5 144
5 144
5 144

FNO BNO
---------- ----------
5 144
5 144

已選擇13行。

SQL> analyze table tt compute statistics;

表已分析。
SQL> select blocks FROM dba_tables where table_name='TT' and owner='SYS';

BLOCKS
----------
5

SQL> alter system flush buffer_cache;

系統已更改。

SQL> select file#,block#,status from v$bh where objd=10266;

FILE# BLOCK# STATUS
---------- ---------- -------
5 139 free
5 142 free
5 137 free
5 140 free
5 143 free
5 141 free
5 144 free

已選擇7行。

SQL>
--======================================
SQL> show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> COL SEGMENT_NAME FORMAT A10
SQL> select segment_name,bytes/1024/1024 m from dba_segments where owner='SYS' a
nd segment_name='T';

SEGMENT_NA M
---------- ----------
T .375

SQL> alter system set db_recycle_cache_size=5m;

系統已更改。

SQL> alter table t storage (buffer_pool recycle);

表已更改。
SQL> select table_name,buffer_pool from dba_tables where table_name='T' and owne
r='SYS';

TABLE_NAME BUFFER_
------------------------------ -------
T RECYCLE

SQL> select count(*) from t;

COUNT(*)
----------
9848

SQL> select data_object_id from dba_objects where owner='SYS' and object_name='T
';

DATA_OBJECT_ID
--------------
10299

SQL> select file#,block#,status from v$bh where objd=10299;

FILE# BLOCK# STATUS
---------- ---------- -------
8 55 xcur
7 76 xcur
8 42 xcur
7 63 xcur
8 50 xcur
7 71 xcur
8 58 xcur
7 79 xcur
8 45 xcur
7 66 xcur
8 53 xcur

FILE# BLOCK# STATUS
---------- ---------- -------
7 74 xcur
7 61 xcur
8 48 xcur
7 69 xcur
8 56 xcur
7 77 xcur
8 43 xcur
7 64 xcur
8 51 xcur
7 72 xcur
7 59 xcur

FILE# BLOCK# STATUS
---------- ---------- -------
7 80 xcur
8 46 xcur
7 67 xcur
8 54 xcur
7 75 xcur
8 41 xcur
7 62 xcur
8 49 xcur
7 70 xcur
8 57 xcur
7 78 xcur

FILE# BLOCK# STATUS
---------- ---------- -------
8 44 xcur
8 52 xcur
7 60 xcur
8 47 xcur
7 68 xcur

已選擇38行。

SQL>
The RECYCLE buffer pool eliminates data blocks from memory as soon as they are no longer needed.
根據上面doc所言，在迴圈池中的block不被使用就被清除出去了，select count(*) from t被執行之後，
表t的block算是被使用完了嗎？透過驗證很顯然我們發現這些block沒有被flush出recycle pool中

SQL> alter system set db_16k_cache_size=5m;

系統已更改。

SQL> create tablespace tbs16 datafile 'E:ORACLEPRODUCT10.2.0ORADATAORCLTB
S16.DBF' SIZE 3M reuse blocksize 16k;

表空間已建立。

SQL> create table t1 (id int) tablespace tbs16;

表已建立。

SQL> select table_name,buffer_pool from dba_tables where table_name='T1';

TABLE_NAME BUFFER_
------------------------------ -------
T1 DEFAULT
SQL> select file_id,extent_id,block_id,blocks,bytes from dba_extents where segme
nt_name='T1';

FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
---------- ---------- ---------- ---------- ----------
9 0 5 8 131072

SQL> alter table t1 storage (buffer_pool keep);

表已更改。

SQL> select table_name,buffer_pool from dba_tables where table_name='T1';

TABLE_NAME BUFFER_
------------------------------ -------
T1 KEEP

SQL> alter table t1 storage (buffer_pool recycle);

表已更改。

SQL> select table_name,buffer_pool from dba_tables where table_name='T1';

TABLE_NAME BUFFER_
------------------------------ -------
T1 RECYCLE
Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.
另外根據上面doc，說Multiple buffer pools(keep,recycle)僅僅可用對標準block size，但是和顯然對目前db的db_block_size=8k
,也就是標準block size 是8k，那麼16k大小的block很顯然不能被放在keep和recycle池中，事實上透過上面查詢buffer_pool of dba_tables我們發現t1
是可以放在keep和recycle中，但是事實上block是否被cache在keep和recycle中的那塊memory中我暫時無法考證。
另外透過doc對引數DB_KEEP_CACHE_SIZE的解釋似乎我們也能看出oracle的意思是隻keep由引數db_block_size指定的block
DB_KEEP_CACHE_SIZE specifies the size of the KEEP buffer pool. The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).
SQL> select id,name,block_size from v$buffer_pool;

ID NAME BLOCK_SIZE
---------- -------------------- ----------
1 KEEP 8192
2 RECYCLE 8192
3 DEFAULT 8192
7 DEFAULT 16384

SQL>
SQL> select component,current_size/1024/1024 from v$sga_dynamic_components where
current_size<>0;

COMPONENT CURRENT_SIZE/1024/1024
------------------------------ ----------------------
shared pool 60
large pool 4
java pool 4
DEFAULT buffer cache 64
KEEP buffer cache 8
RECYCLE buffer cache 8
DEFAULT 16K buffer cache 8

已選擇7行。

SQL> show sga

Total System Global Area 209715200 bytes
Fixed Size 1248164 bytes
Variable Size 113247324 bytes
Database Buffers 92274688 bytes
Redo Buffers 2945024 bytes
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 200M
sga_target big integer 160M
SQL>
--=============================
Automatically Managed SGA Components
When you set a value for SGA_TARGET, Oracle Database 10g automatically sizes the most commonly configured components, including:

The shared pool (for SQL and PL/SQL execution)

The Java pool (for Java execution state)

The large pool (for large allocations such as RMAN backup buffers)

The buffer cache

The Streams pool
--==============================
從上面doc我們知道sga自動只能管理上面幾個pool
下面的這些需要手動管理：
Manually Managed SGA Components
There are a few SGA components whose sizes are not automatically adjusted. The administrator needs to specify the sizes of these components explicitly, if needed by the application. Such components are:

Keep/Recycle buffer caches (controlled by DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE)

Additional buffer caches for non-standard block sizes (controlled by DB_nK_CACHE_SIZE, n = {2, 4, 8, 16, 32})

The sizes of these components is determined by the administrator-defined value of their corresponding parameters. These values can, of course, be changed any time either using Enterprise Manager or from the command line with an ALTER SYSTEM statement.

The memory consumed by manually sized components reduces the amount of memory available for automatic adjustment. For example, in the following configuration:

SGA_TARGET = 256M
DB_8K_CACHE_SIZE = 32M

The instance has only 224 MB (256 - 32) remaining to be distributed among the automatically sized components.
--=============================
從上面的各種doc以及試驗看出
data buffer cache的大小是由下面這些引數指定的值的總和所決定：
default pool,keep pool recycle pool
default pool中可以設定不同的引數：
db_16k_cache_size
db_2k_cache_size
db_32k_cache_size
db_4k_cache_size
db_8k_cache_size
其中db_cache_size指定的memory只能cache由引數db_block_size指定的大小的block
db_16k_cache_size
db_2k_cache_size
db_32k_cache_size
db_4k_cache_size
db_8k_cache_size
上面這些引數如果設定值，那麼他們直接使用sga中的memory而不會瓜分db_cache_size
的值。

轉：http://warehouse.itpub.net/post/777/488678