数据缓冲区详解
确认数据缓冲区大小：
select name ,SIZE_FOR_ESTIMATE s_estimate,SIZE_FACTOR s_factor,BUFFERS_FOR_ESTIMATE b_estimate, ESTD_PHYSICAL_READ_FACTOR physical_factor,ESTD_PHYSICAL_READS e_physical from v$db_cache_advice where ESTD_PHYSICAL_READ_FACTOR is not null;
查看sga基本信息：
select name ,bytes/1024/1024 m from sga_info;
查看缓存信息
show parameter cache;
查看表的空块信息，前提是收集表的基本信息:
select table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner='SCOTT';
分析表的基本信息：
analyze table table_name compute statistics;
analyze table table_name estimate statistics;
analyze table table_name estimate statistics sample 10 percent;
收集表的统计信息：
exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
查看表的空块的信息：
select table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner='SCOTT';
手动条带：
手动增加空间
alter table scott.emp allocate extent(size 20m datafile '/u01/app/oracle/oradata/PROD/disk4/diskusers01.dbf');
收回空间：
alter table scott.emp deallocate(解除分配) unused;
查询段头热块：
col class for a20；
col segment_type for a15；
col partitioned_name for a15
col segment_name for a15
select 'Segment Header' class, a.segment_type, a.segment_name, a.partition_name
from dba_segments a, v$session_wait b
where a.header_file = b.p1 and a.header_block = b.p2 and b.event = 'buffer busy waits' union
select 'Freelist Groups' class, a.segment_type, a.segment_name, a.partition_name from dba_segments a, v$session_wait b where b.p2 between a.header_block + 1 and (a.header_block + a.freelist_groups)
and a.header_file = b.p1 and a.freelist_groups > 1 and b.event = 'buffer busy waits'
union
select a.segment_type || ' block' class, a.segment_type, a.segment_name, a.partition_name from dba_extents a, v$session_wait b where b.p2 between a.block_id and a.block_id+a.blocks - 1 and a.file_id=b.p1 and b.event='buffer busy waits' and not exists (select 1 from dba_segments where header_file = b.p1 and header_block = b.p2);
数据缓存区：
①等待事件：
free buffer inspected不断检测 数越大说明数据缓存区不够大
select name,value from v$sysstat where name=’free buffer inexpected’);
buffer busy wait 缓存忙等待
查看buffer busy wait 情况：
select se.sid,se.p1 "file#",se.p2 "block#",se.p3 "id" from v$session_wait se,v$event_name ev where se.event=ev.name and ev.name like 'buffer busy waits';
free buffer wait 缓存闲等待
②keep 存放老而有用的数据，提高效率
alter system set db_keep_cache_size=10m;
alter table scott.emp storage(buffer_pool keep);
③rate 命中率
当est physical read factor降幅平缓了，选择差不多的值就可以
选取72即可，因此空间288够用
热块：通常表现形式为CPU使用率很高，但吞吐量很低。原因可能是数据库设置导致或者重复执行的SQL 频繁访问一些相同的数据块导致。
查看表空间的管理方式：
select tablespace_name,contents,status,extent_management,segment_space_management from dba_tablespaces;
查看哪个表发生热块：
col class for a20 col segment_type for a15 col segment_name for a15 col partitioned_name for a15
select 'Segment Header' class, a.segment_type, a.segment_name, a.partition_name from dba_segments a, v$session_wait b where a.header_file = b.p1 and a.header_block = b.p2 and b.event = 'buffer busy waits' union select 'Freelist Groups' class, a.segment_type, a.segment_name, a.partition_name from dba_segments a, v$session_wait b where b.p2 between a.header_block + 1 and (a.header_block + a.freelist_groups) and a.header_file = b.p1 and a.freelist_groups > 1 and b.event = 'buffer busy waits'
当一个会话需要访问一个数据块，而这个数据块正在被另一个用户从磁盘读取到内存中或者这个数据块正在被另一个会话修改时，当前的会话就需要等待，就会产生一个buffer busy waits等待，也伴随着Latch争用。如果太多的会话去访问相同的数据块导致长时间的 buffer busy waits等待 ，通常表现形式为CPU使用率很高，但吞吐量很低。造成热快的原因可能是数据库设置导致或者重复执行的SQL 频繁访问一些相同的数据块导致。热块产生的原因不尽相同，按照数据块的类型，可以分成以下几种热块类型，不同热块类型处理的方式都是不同的：表数据块、索引数据块、索引根数据块、文件头数据块和数据块自身的争用。
表数据块
比如在OLTP系统中，对于一些小表，会出现某些数据块被频繁查询或者修改的操作，这时候这些被频繁访问的数据块就会变成热块，导致内存中Latch的争用。
如果出现这样的热块情况，并且表不太大，一个方法是可以考虑将表数据分布在更多的数据块上，减少数据块被多数会话同时访问的频率。
可以通过下面的命令将每个数据块存放记录的数量减到最少：
alter table minimize records_per_block;
我们也可以通过调大PCTFREE来实现，不过在调整PCTFREE时， 我们要注意的是，这个参数虽然是可以动态调整的，但是我们调整PCTFREE参数只能对新的数据插入起作用，对于已经填充过满的老数据块，是无法起作用的，数据分布问题，必须调整参数后，对表进行重组，才能对表中的所有数据块都起作用。对表重组的办法有很多，比如ALTER TABLE ... MOVE或者EXP/IMP等。 除了调整 PCTFREE外， 减少热块冲突的更好的办法是将这张表放在BLOCK_SIZE较小的表空间里。以上所有的方法都是将数据尽可能地分布到多个数据块为指导思想进行实现的。
请看下面的例子，创建两个数据量相同的表，让一个表上每个数据块存放记录数尽可能地小，另一个表保持默认的情况，来看数据块访问情况：
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index t_inx on t(object_id);
Index created.
SQL> exec
dbms_stats.gather_table_stats(‘HR','T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select 'T' tbl_name, rows_per_block,
count(*) number_of_such_blocks from (
2 select dbms_rowid.ROWID_BLOCK_NUMBER
( rowid ), count(*) rows_per_block
3 from t
4 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid
))
5 group by 'T', rows_per_block;
--- -------------- ---------------------
T
82
16
T
76
18
T
74
22
T
77
16
T
65
3
T
87
2
T
84
5
T
69
76
T
93
2
T
91
1
T
75
20
TB1 ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
--- -------------- ---------------------
T
68
47
T
72
48
T
94
1
T
88
1
T
73
34
T
81
15
T
79
27
T
92
1
T
62
1
T
83
18
T
71
113
TB1 ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
--- -------------- ---------------------
T
70
153
T
67
11
T
86
2
T
80
18
T
78
15
T
85
4
T
64
1
29 rows selected.
SQL> create table t1 as select * from dba_objects where rownum <3;
Table created.
SQL> select 'T' tb1_name,rows_per_block,count(*) number_of_such_blocks
from (select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from
t1 group by dbms_rowid.rowid_block_number(rowid)) group by 'T',rows_per_block
TB1 ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
--- -------------- ---------------------
T
2
1
1 rows selected.
SQL> insert into t1 select * from
dba_objects;
50833 rows created.
SQL> commit;
Commit complete.
SQL> create index t1_inx on t1(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(’HR','t1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select 'T' tbl_name, rows_per_block,
count(*) number_of_such_blocks from (
2 select dbms_rowid.ROWID_BLOCK_NUMBER
( rowid ), count(*) rows_per_block
3 from t1
4 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid
))
5 group by 'T', rows_per_block;
6 /
TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-- -------------- ---------------------
T1
2
25297
T1表上将每个数据块上存放的记录数缩小为2条，这样T1表上有数据的数据块数量要远远多于T表。
SQL> set autotrace traceonly
SQL> select * from t where object_id<1000
953 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id |
Operation
| Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 954 | 88722
| 25 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T
| 954 | 88722 | 25 (0)| 00:00:01 |
|* 2 | INDEX RANGE
SCAN | T_IDX
| 954 |
| 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
91547 bytes sent via SQL*Net to client
1078 bytes received via SQL*Net from
client
65 SQL*Net roundtrips
to/from client
0 sorts (memory)
0 sorts (disk)
953 rows processed
SQL> select * from t1 where object_id<1000
955 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1311207630
--------------------------------------------------------------------------------------
| Id |
Operation
| Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
| | 956 | 88908
| 740 (1)| 00:00:09 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1
| 956 | 88908 | 740 (1)| 00:00:09 |
|* 2 | INDEX RANGE
SCAN | T1_INX
| 956 |
| 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
620 consistent gets
238 physical reads
0 redo size
91701 bytes sent via SQL*Net to client
1078 bytes received via SQL*Net from
client
65 SQL*Net roundtrips
to/from client
0 sorts (memory)
0 sorts (disk)
955 rows processed
当我们分别从T，T1表中选出1000条数据时，可以看到两个表的执行计划是相同的，返回的结果集是相同的，但T1表的一致性读的数量是620，要 比T表143大出很多，原因就是T1表中的行分布在更多的数据块上，导致Oracle需要读取更多的数据块来完成查询。从另一个角度看，我们把数据分布到 了更多的数据块上，大大降低了一个数据块被重复读取的概率。
但是这种方式的缺点是显而易见的，就是降低了数据的性能，在这种情况下，访问相同的数据意味着需要读取更多的数据块，性能会有所降低。
不过如果存在这样的情况，比如很多用户只修改1条记录并且各自更新的数据不同，那么这样做的好处就非常明显。在T表中，一个数据块上大约有70条记录，就是说很可能70个用户会同时访问一个数据块，这必然导致热块的出现；当我们让每个数据块只存放2条记录时，最多只会有2个用户会同时访问一个数据 块，这样热块的概率将会被极大地降低。
除了这种方法外我们还可以从data
buffer中着手解决，主要思路为划分缓存池和提高SQL命中率。在DATA BUFFER中有如下3种pool
KEEP pool 缓冲池将一直保留存储在其中的方案对象的数据，维护一个适当尺寸的KEEP缓冲池可以使oracle在内存中保留数据库对象而避免I/O操作。在表、聚集、实体化视图或实体化视图日志上，KEEP子句的优先权大于NOCACHE子句 ，需要显示指定。
Keep Buffer Pool的说明
KeepBuffer Pool 的作用是缓存那些需要经常查询的对象但又容易被默认缓冲区置换出去的对象，按惯例，Keep pool设置为合理的大小，以使其中存储的对象不再age out，也就是查询这个对象的操作不会引起磁盘IO操作，可以极大地提高查询性能。
注意一点，不是设置了keep pool 之后，热点表就一定能够缓存在 keep pool ，keep pool 同样也是由LRU 链表管理的，当keep pool不够的时候，最先缓存到 keep pool的对象会被挤出，不过与default pool 中的LRU的管理方式不同，在keep pool中表永远是从MRU移动到LRU，不会由于你做了FTS（全表扫描）而将表缓存到LRU端，在keep pool中对象永远是先进先出。
在这点上对于DefaultPool就有所不同。默认情况下是nocache,即全表扫描数据在正常情况下是放到LRU的冷端,使其尽快page out(这是default pool的默认策略), 而指定了alter
table cache后,该表的全表扫描数据就不是放到LRU的冷端, 而是放到热端（MRU）了,从而使该得数据老化较慢,即保留的时间长。
因为这个原因，如果keep
pool空间比table 小，导致不能完全把table keep下，那么在keep pool 中最早使用的数据还是有可能被清洗出去的。还是会产生大量的逻辑读，这样就起不到作用，所以，如果采用keep，就必须全部keep下，要么就不用keep。
综上所述，我们可以总结为：KeepPool改变的是存储位置，Cache改变的是存储方式,Cache则可以认为是无法使用keep的情况下的折中选择。
注意：如果采用Keep
Pool，Data 都会cache到内存中，所以会忽略对象本身的Cache 和Nocache。
在Oracle 10g中SGA自动管理，ORACLE并不会管理keep pool，ORACLE只会管理default pool。
默认的情况下db_keep_cache_size=0，未启用，如果想要启用，需要手工设置db_keep_cache_size的值，设置了这个值之后 db_cache_size 会减少。
将表keep到内存中的过程如下
SQL> select * from v$sgainfo;
NAME
BYTES RESIZEABL
------------------------- ---------- ---------
Fixed SGA
Size
1218992 No
Redo
Buffers
2973696 No
Buffer Cache Size
197132288 Yes
Shared Pool
Size 75497472
Yes
Large Pool
Size
4194304 Yes
Java Pool
Size
4194304 Yes
Streams Pool
Size
0 Yes
Granule
Size
4194304 No
Maximum SGA Size
285212672 No
Startup overhead in Share 37748736 No
d Pool
NAME
BYTES RESIZEABL
------------------------- ---------- ---------
Free SGA Memory Available
0
11 rows selected.
SQL> SELECT x.ksppinm NAME,y.ksppstvl
VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx =
y.indx AND x.ksppinm LIKE '%db_cache_size%'
NAME
VALUE
DESCRIB
------------------------- --------------------
-----------------------------------------------------------------
__db_cache_size
197132288
Actual size of DEFAULT buffer pool for standard block size buffer
s
db_cache_size
0
Size of DEFAULT buffer pool for standard block size buffers
SQL> alter system set db_keep_cache_size=50m;
System altered.
查看 db_keep_cache_size和db_cache_size的大小 ，可以看到db_cache_size变小了
SQL> show parameter db_keep_cache_size
NAME
TYPE
VALUE
------------------------------------ --------------------------------- ------------------------------
db_keep_cache_size
big
integer
52M
SQL> SELECT x.ksppinm NAME,y.ksppstvl
VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx =
y.indx AND x.ksppinm LIKE '%__db_cache_size%'
NAME
VALUE
DESCRIB
------------------------- --------------------
-----------------------------------------------------------------
__db_cache_size
142606336
Actual size of DEFAULT buffer pool for standard block size buffer
s
将表keep到keep
pool中
SQL> create table t1_keep as select
rownum id,object_name from dba_objects;
Table created.
SQL> alter table t1_keep storage(buffer_pool keep);
Table altered.
SQL> create table t2 storage(buffer_pool keep) as select rownum
id,object_name from dba_objects;
Table created.
取消keep
SQL> alter table t2 storage(buffer_pool default);
Table altered.
启用cache
SQL> alter table t2 cache;
Table altered.
关闭cache
SQL> alter table t2 nocache;
Table altered.
RECYCLE pool 缓冲池将随时清除存储在其中不再被用户需要的数据 ，一个适当尺寸的RECYCLE缓冲池可以减少默认缓冲池为RECYCLE缓冲池的数据库对象的数量，以避免它们占用不必要的缓冲空间 ，需要显示指定。设置表使用RECYCLE pool
SQL> alter table t2 storage(buffer_pool
recycle);
Table altered.
DEFAULT pool 缓冲池中存储的是没有被指定使用其他缓冲池的方案对象的数据，以及被显式地指定使用DEFAULT缓冲池的方案对象的数据。
实际生产环境下，我们可以将被访问次数较多的热块，存放在keep pool中；将被访问次数较较少的冷块，存放在recycle pool中；将被访问次数一般，则保持default pool不变
注意：上述三种数据缓冲池只有标准容量的数据缓存区才具备。非标准容量数据块使用的数据缓冲区中只有 DEFAULT 缓冲池。
关于提高buffer
cache提高缓存命中率实际上最有效的办法，是从优化sql入手，不良的sql往往带来大量的不必要的访问，这是造成热点块的根源。比如本该通过全表扫描的查询却走了索引的range scan，这样将带来大量的对块的重复访问。从而形成热点问题。再或者比如不当地走了nested loops的表连接，也可能对非驱动表造成大量的重复访问。那么在这个时候，我们的目标就是找出这些sql来并尝试优化。关于如何找出造成热块的sql可参考
http://czmmiao.iteye.com/blog/1292412
最直接的就是扩大db_cache_size。在10g以后，oracle建议采用sga的自动内存管理，但是我们可以为我们data buffer设置最小值。
SQL> alter system set db_cache_size=100M;
System altered.
设置data
buffer的最小值为100MB。
索引数据块
这样的情况通常发生在一个RAC架构里，某个表的索引键值呈现出典型的"右倾"现象，比如一个表的主键使用一个序列来生成键值，那么这个主键在索引数据块上的键值就是以一种顺序递增的方式排列的，比如1,2,3,4,5,…，由于这些键值分布得非常接近，当许多用户在RAC的不同实例来向表中插入主键时，就会出现相同的索引数据块在不同实例的内存中被调用，形成一种数据块的争用，对于这样的情况，使用反向索引可以缓解这种争用。反向索引是将从前的索引键值按照反向的方式排列，在正常的主键B-TREE索引中，键值会按照大小顺序的排列，比如这样：
1234、1235、1236、1237
而反向索引之后，键值变成下面的样子：
4321、5321、6321、7321
这样，本来是放在相同的索引数据块上的键值，现在分布到不同的数据块上，这样用户在RAC不同的实例上插入的主键值因为分布在不同的数据块上，所以不会导致热块的产生，这基本上是反向索引被使用的唯一情况。
反向索引使用场合之所以如此受限，是因为它丢弃了B-TREE索引的一个最重要的功能：
INDEX RANGE SCAN
索引访问方式中，这个方式最为常见，但是反向索引却不能使用这个功能，究其原因就是，反向索引已经把键值的排列顺序打乱，当按照键值顺序地查找一个范围时，在反向索引中，由于键值被反向存储，这些值已经不是连续存放的了。所以INDEX RANGE SCAN的方式在反向索引中没有任何意义。看下面的例子：
SQL> set autotrace trace explain
SQL> select * from t where id > 1234 and id < 1999;
Execution Plan
----------------------------------------------------------
Plan hash value: 2152055767
--------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 765 | 3060 | 3 (0)|
00:00:01 |
|* 1 | INDEX RANGE SCAN| T_INX | 765 | 3060
| 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID">1234 AND "ID"<1999)
SQL> select * from t1 where id > 1234 and id < 1999;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 765 | 3060 | 22 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 765 |
3060 | 22 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<1999 AND "ID">1234)
SQL> select /*+ index(t1,t1_inx) */* from t1 where id > 1234 and id <
1999;
Execution Plan
----------------------------------------------------------
Plan hash value: 3995001570
---------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 765 | 3060 | 114 (2)| 00:00:02 |
|* 1 | INDEX FULL SCAN | T1_INX | 765 | 3060
| 114 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<1999 AND "ID">1234)
我们看到，对于一个在实际应用中非常常见的条件范围的查询，在反向索引中却只能够通过全表扫描或者全索引扫描的方式来实现，这就是反向索引的一个非常严重的缺陷，所以在生产环境下使用它时要十分慎重。
索引根数据块
热块也可能发生在索引的根数据块上。我们知道在B-TREE索引里，当Oracle访问一个索引键值时，首选访问索引的根，然后是索引的分支，最后才是索引的叶块，索引的键值就是存储在叶块上面，下图是B-TREE索引示意图。
