1、block、buffer的概念
复习段区块
2、buffer cache的意义
减少IO
物理IO：磁盘读
逻辑IO：内存读
构造cr(consistent read)块：
undo表空间：回滚未提交数据；构造cr块
只要未提交，就可以回滚
只要未提交，别的会话就看不见修改
3、buffer cache的内存组织结构（块的头部挂到chain）
buffer的三种性质：
1：free
2: clear
3: dirty
CBC：cache buffer chain根据block地址找block的时候，需要使用到CBC chain
LRU：最近最少使用(Least Recently Used)
CHECKPOINT QUEUE
4、DB_WRITER_PROCESSES
默认CPU数量/8
5、Buffer Cache的重要参数配置
SELECT component,current_size,min_size FROM v$sga_dynamic_components;
SGA各个组件分配都是以granule作为一个单位来分配，而并不是一次分配1M或1K。
granule大小是以SGA大小由系统设定的，当SGA小于1G时，
granule大小为4M,当SGA大于1G的时候granule大小为16M。
SGA大小
SQL> select sum(value) from v$sga;
各个组件的granule大小
SQL> select component,granule_size from v$sga_dynamic_components;
granule size 粒度大小受内部隐含参数_ksmg_granule_size的控制
Buffer Cache的大小配置
alter system set db_cache_size=50M scope=both;
memory_target
sga_max_size memory_max_target -- 限制内存
sga_target
db_cache_size
在OLTP系统中，对于DB_CACHE_SIZE的设置，推荐配置是：
DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3
使用advice来确认buffer cache的大小
在OLAP 系统中
DB_CACHE_SIZE = SGA_MAX_SIZE/2
--内存自动管理：SGA 和 PGA 自动调整
memory_max_target big integer 1004M
memory_target big integer 1004M
--关闭SGA和 PGA 自动调整
memory_target = 0
sga_target = 100M
--关内存自动管理
memory_target = 0
sga_target =0
SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate "Buffers",
estd_physical_read_factor est_read_factor,
estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
FROM v$db_cache_advice
WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size');
6、buffer状态
state:
0, FREE, no valid block image
1, XCUR, a current mode block, exclusive to this instance
2, SCUR, a current mode block, shared with other instances
3, CR, a consistent read (stale) block image
4, READ, buffer is reserved for a block being read from disk
5, MREC, a block in media recovery mode
6, IREC, a block in instance (crash) recovery mode
select state from x$bh group by state;
一个对象占用buffer的具体情况
SQL>
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 = 'T'
group by o.object_name, state
order by blocks desc;
--对象在哪个数据文件和块
select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and object_name='T'
寻找热块
SELECT
obj object,
dbarfil file#,
dbablk block#,
tch touches
FROM
x$bh
WHERE
tch > 10
ORDER BY
tch asc;
select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and DBARFIL=1 and DBABLK=2009
整个数据库所有文件中block的总和
select
sum(blocks)*8/1024 M
from
dba_data_files;
空闲空间的比例，最好控制在10%以内
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);
找出消耗物理IO资源最大的的SQL语句
select disk_reads, substr(sql_text,1,4000) from v$sqlarea order by disk_reads asc;
select BUFFER_GETS, substr(sql_text,1,4000) from v$sqlarea order by BUFFER_GETS asc;
buffer cache中每一个对象的使用情况
SELECT t.name AS tablespace_name,
o.object_name,
SUM(DECODE(bh.status, 'free', 1, 0)) AS free,
SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur,
SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur,
SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr,
SUM(DECODE(bh.status, 'read', 1, 0)) AS read,
SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec,
SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec
FROM v$bh bh
JOIN dba_objects o ON o.data_object_id = bh.objd
JOIN v$tablespace t ON t.ts# = bh.ts#
GROUP BY t.name, o.object_name
order by xcur desc
create table xing_objects as select * from dba_objects;
select rowid,OBJECT_NAME from XING_OBJECTS;
select
dbms_rowid.rowid_object('AAAR4cAAFAAAACDAAA') object,
dbms_rowid.rowid_relative_fno('AAAR4cAAFAAAACDAAA') file_num,
dbms_rowid.rowid_block_number('AAAR4cAAFAAAACDAAA') block_num ,
dbms_rowid.rowid_row_number('AAAR4cAAFAAAACDAAA') row_number
from dual;
SELECT executions,
buffer_gets,
disk_reads,
first_load_time,
sql_text
FROM v$sqlarea
ORDER BY disk_reads
7、清空buffer cache
alter system flush buffer_cache;
8
create table t2 as select rownum id from dba_objects;
create or replace
PROCEDURE pselect1
AS
l_num number;
BEGIN
FOR i IN 1..100000
LOOP
SELECT count(*) into l_num FROM T1 ;
END LOOP;
END;
create or replace
PROCEDURE pselect2
AS
l_num number;
BEGIN
FOR i IN 1..100000
LOOP
SELECT count(*) into l_num FROM T2 ;
END LOOP;
END;
create or replace
PROCEDURE pselect3
AS
l_num number;
BEGIN
FOR i IN 1..100000
LOOP
SELECT count(*) into l_num FROM T3 ;
END LOOP;
END;
网友评论