sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle data buffer cache详细研究笔记

时间:2017-01-02 21:37   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库的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 owner='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='SYS' 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_number(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> 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' and 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 owner='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行。

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.0ORADATAORCLTBS16.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 segment_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> select component,current_size/1024/1024 from v$sga_dynamic_components wherecurrent_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

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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle data buffer cache详细研究笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1440.html

Oracle研究中心

关键词:

Oracle data buffer cache

Oracle 设置表空间的data buffer cache