data buffer cache的一点总结。

最新推荐文章于 2021-07-25 09:45:48 发布

clt3617

最新推荐文章于 2021-07-25 09:45:48 发布

阅读量265

点赞数 1

文章标签：数据库 java

这部分东西涉及的参数较多，感觉稍微有些乱，简单的总结一下。[@more@]

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

系统已更改。

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

系统已更改。

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行。
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
的值。