对oracle10g中buffer_cache设置大小的理解

Buffer_cache大小的设置及依据

一、查看当前db_writer_processes的VALUE值

SYS@ORCL>show parameter writer

NAME TYPE VALUE

------------------------- ------------------------------------

db_writer_processes integer 1

SYS@ORCL>

二、修改db_writer_processes的值（一般不要乱设置）

（注：startup froce只可以在测试环境）

SYS@ORCL>alter system set db_writer_processes=2;

alter system set db_writer_processes=2

*

ERROR at line 1:

ORA-02095: specified initialization parametercannot be modified

SYS@ORCL>alter system set db_writer_processes=2scope=spfile;

System altered.

SYS@ORCL>show parameter writer

NAME TYPE VALUE

-------------------------- -----------------------------------------

db_writer_processes integer 1

SYS@ORCL>startup force

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SYS@ORCL>show parameter writer

NAME TYPE VALUE

------------------------------ -----------------------------------------

db_writer_processes integer 2

SYS@ORCL>

三、buffer_cache的重要参数设置

SYS@ORCL>l

1* selectcomponent,current_size,min_size from v$sga_dynamic_components

四、配置buffer_cache的大小

SYS@ORCL>alter system set db_cache_size=100Mscope=both;

System altered.

SYS@ORCL>show parameter db_cache_size

NAME TYPE VALUE

------------------------------------ -----------------------------------------

db_cache_size big integer 100M

SYS@ORCL>

五、使用advice来确定buffer_cache的大小

SYS@ORCL>

select SIZE_FOR_ESTIMATE "Cache Size(MB)",SIZE_FACTOR,BUFFERS_FOR_ESTIMATE "Buffers",

2ESTD_PHYSICAL_READ_FACTOR,ESTD_PHYSICAL_READS,ESTD_PHYSICAL_READ_TIME

3 from v$db_cache_advice

4 where name='DEFAULT' and block_size=(selectvalue from v$parameter where name='db_block_size');

Cache Size (MB) SIZE_FACTOR Buffers ESTD_PHYSICAL_READ_FACTORESTD_PHYSICAL_READS ESTD_PHYSICAL_READ_TIME

--------------- ----------- ----------------------------------- ------------------- -----------------------

12 .0968 1497 1.2668 9207 11

24 .1935 2994 1.1497 8356 10

36 .2903 4491 1.0477 7615 9

48 .3871 5988 1 7268 9

60 .4839 7485 1 7268 9

72 .5806 8982 1 7268 9

84 .6774 10479 1 7268 9

96 .7742 11976 1 7268 9

108 .871 13473 1 7268 9

120 .9677 14970 1 7268 9

124 1 15469 1 7268 9

132 1.0645 16467 1 7268 9

1441.1613 17964 1 7268 9

156 1.2581 19461 1 7268 9

168 1.3548 20958 1 7268 9

180 1.4516 22455 1 7268 9

192 1.5484 23952 1 7268 9

204 1.6452 25449 1 7268 9

216 1.7419 26946 1 7268 9

228 1.8387 28443 1 7268 9

240 1.9355 29940 1 7268 9

21 rows selected.

SYS@ORCL>

六、将buffer_cache中占用buffer的情况显示出来

可以找出占用最多的那个对象

LIBRARY$xcur 863

OPQTYPE$xcur 863

ICOL$xcur 863

TYPE$ xcur 1926

RESULT$xcur 1926

METHOD$xcur 1926

COLLECTION$xcur 1926

PARAMETER$xcur 1926

C_TOID_VERSION# xcur 1926

ATTRIBUTE$xcur 1926

578 rows selected.

SYS@ORCL>l

1 selecto.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

2 from x$bh b,dba_objects o

3 where b.obj = o.data_object_id and state<>0

4 group by o.object_name,state

5* order byblocks asc

SYS@ORCL>

七、查看整个数据库所有文件的blocks总和

SYS@ORCL>select sum(blocks)

2 from dba_data_files;

SUM(BLOCKS)

-----------

110720

SYS@ORCL>

八、空闲空间的比例，最好控制在10%以内

BEING USED代表正在被使用的数据块 脏数据

AVAILABLE代表可用的数据块

SYS@ORCL>selectdecode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEINGUSED',state) "BLOCK STATUS",count(*)

2 from x$bh

3 group bydecode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEINGUSED',state);

BLOCK STATUS COUNT(*)

---------------------------------------- ----------

BEING USED 205

AVAILABLE 7395

九、消耗资源最大的SQL语句

1、最消耗物理读IO资源

SYS@ORCL>l

1 select disk_reads,substr(sql_text,1,4000)from v$sqlarea

2* order bydisk_reads asc

SYS@ORCL>

2、最消耗内存读（CPU）资源

SYS@ORCL>selectBUFFER_GETS,substr(sql_text,1,4000) from v$sqlarea

2 order by BUFFER_GETS asc;