本章主要阐述SGA中的Database buffer cache。
Buffer cache {
1.pin (读写瞬间的状态)
2.clean
3.dirty
4.unused
}
--Database buffer cache
概述：
1.段、区、块的概念：
create table t2 (
id int,
name varchar2(30)
);
建立一个表，同时建立一个段，
建立段的同时，会给段分配一个区，
区是物理上连续的几个块。
区是oracle给段分配的最小单位。
块是oracle I/O的最小单位。
insert into t2 values(1,'xiaobei');
访问：
访问一个块，
一般情况下，一个块中有多个行（行链接，行迁移除外）。
2.buffer cache的意义：
①减少I/O：物理I/O，逻辑I/O
②构造cr块
引入undo：Oracle在修改数据以前，会将数据写到undo中。
undo的作用{
回滚未提交事物；
构造cr块。
}
这里会引出两个更深入的问题：①会话2如何知道会话1没有提交？②cr块如何从undo空间中找到被删除的那一行。
3.buffer cache的内存组织结构
CBC、LRU、LRUW、CHECKPOINT QUEUE
CBC 按照块地址将buffer链起来。
LRU 最近最少使用的顺序链起来。可用块~
LRU，MRU（最近最多使用）
LRUW 脏块的，DBWn以此为标准优先写最近最少使用的脏块。
CHECKPOINT QUEUE 也是关于脏块的，按块第一次脏的时间点链起来。
注：在Oracle中，一般都是采用双向的链。
4.DB_WRITER_PROCESSES
|
1
2
3
4
5
6
7
|
SQL>
NAME
TYPE VALUE
------------------------------------
db_writer_processes
integer
1
SQL>
alter
system
set
db_writer_processes = 2 scope=spfile;
--DBWn数量一般设定是服务器核心数/8.
|
5.buffer cache的重要参数配置
|
1
2
3
4
5
6
7
8
9
10
|
alter
system
set
db_cache_size=200m scope=both;
设置顺序：
sga_max_size
sga_target
db_cache_size
在OLTP系统中，buffer
DB_CACHE_SIZE
使用advice来确认buffer
根据语句查询buffer
平日注意收集积累一些常用的语句》。
|
6.Block状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
buffer
SQL>
desc
x$bh
state：0~8
0，
free
1，XCUR
2，SCUR
3，CR
4，
READ
从block写入buffer的那个过程
5，MREC
6，IREC
7，write
8，pi
SQL>
select
distinct
state
from
x$bh;
非RAC环境下，
current
永远等于xcur。
RAC下，有scur
|
Q：current块和cr块区别？
A：
|
1
2
|
cr块被构造后被读出，就马上没意义了。。马上就可以被覆盖了。。
要修改一个块，只能修改
current
块。
|
Q：查看一个对象占用了多少不同状态的buffer？
A：
|
1
2
3
4
5
6
7
8
9
10
|
select
o.object_name,
decode(state,0,
'free'
,1,
'xcur'
,2,
'scur'
,3,
'cr'
,4,
'read'
,5,
'mrec'
,
6,
'irec'
,7,
'write'
,8,
'pi'
) state,
count
(*) blocks
from
x$bh b, dba_objects o
where
b.obj = o.data_object_id
and
o.object_name =
'T2'
group
by
o.object_name, state
order
by
blocks
desc
|
注：实验的时候可以先执行
|
1
|
alter
system flush buffer_cache;
|
7.使用各种sql熟知buffer cache使用情况
|
1
2
|
select
distinct
object_name, dbarfil, dbablk
from
x$bh a, dba_objects b
where
a.obj=b.object_id
and
object_name=
'T2'
;
|
OBJECT_NAME DBARFIL DBABLK
-------------------- ---------- ----------
T2 1 86184
T2 1 86185
|
1
2
|
select
object_name, dbarfil, dbablk
from
x$bh a, dba_objects b
where
a.obj=b.object_id
and
object_name=
'T2'
;
|
Q：将buffer cache中占用buffer最多的对象给找出来？
A：
|
1
2
3
4
5
6
7
8
9
|
select
o.object_name,
decode(state,0,
'free'
,1,
'xcur'
,2,
'scur'
,3,
'cr'
,4,
'read'
,5,
'mrec'
,
6,
'irec'
,7,
'write'
,8,
'pi'
) state,
count
(*) blocks
from
x$bh b, dba_objects o
where
b.obj = o.data_object_id
and
state<>0
group
by
o.object_name, state
order
by
blocks
asc
;
|
Q：寻找热块
A：
|
1
2
3
4
5
6
7
8
9
|
select
obj
dbarfil
dbablk
tch
from
x$bh
where
tch>10
order
by
tch
asc
;
|
OBJECT FILE# BLOCK# TOUCHES
---------- ---------- ---------- ----------
5847 1 11752 29
5834 1 11664 29
5834 1 11665 29
5832 1 11649 30
5833 1 11657 30
5834 1 11666 31
287 1 2009 45
287 1 2008 45
96 rows selected.
注：块被逻辑读的次数多，就是热端，根据块的tch属性判断。
|
1
2
|
select
object_name, dbarfil, dbablk
from
x$bh a, dba_objects b
where
a.obj=b.object_id
and
dbarfil=1
and
dbablk=287
|
Q：块的总数
A：
|
1
|
select
sum
(blocks)
from
dba_data_files;
|
SUM(BLOCKS)
-----------
176640
Q：查询当前空闲空间的比例，最好控制在10%以内
A：
|
1
2
3
|
select
decode(state,0,
'FREE'
,1,decode(lrba_seq,0,
'AVAILABLE'
,
'BEING USED'
),3,
'BEING USED'
, state)
"BLOCK STATUS"
,
count
(*)
from
x$bh
group
by
decode(state,0,
'FREE'
,1,decode(lrba_seq,0,
'AVAILABLE'
,
'BEING USED'
),3,
'BEING USED'
,state);
|
BLOCK STATUS COUNT(*)
---------------------------------------- ----------
BEING USED 74
AVAILABLE 5385
FREE 10696
Q：最浪费内存的前10个语句占所有的比例，建议控制在5%以内
A：
|
1
2
3
|
select
sum
(pct_bufgets)
"Percent"
from
(
select
rank() over (
order
by
buffer_gets
desc
)
as
rank_bufgets, to_char(100 * ratio_to_report(buffer_gets) over(),
'999.99'
)pct_bufgets
from
v$sqlarea)
where
rank_bufgets < 11;
|
Q：找出消耗物理I/O资源最大的SQL语句
A：
|
1
2
3
|
select
disk_reads, substr(sql_text,1,4000)
from
v$sqlarea
order
by
disk_reads
asc
;
|
Q：常见的操作系统命令，查看
A：
|
1
2
3
4
5
6
7
|
iostat
1
10
vmstat
1
10
mpstat
1
10
mpstat
0
1
mpstat
1
1
top
free
|
内存读：消耗CPU资源
物理读：消耗I/O资源