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",
2 ESTD_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
144 1.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;