1. DB_WRITER_PROCESSES参数，设置DBWn进程的个数。SQL> show parameter db_writer_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 1
修改db_writer_processes参数，必须要重新启动：
SQL> alter system set db_writer_processes=2 scope=spfile;
System altered.
2. buffer cache的重要参数的查看
SQL> select component,current_size,min_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE
------------------------------ ------------ ----------
shared pool 75497472 75497472
large pool 4194304 4194304
java pool 4194304 4194304
streams pool 0 0
DEFAULT buffer cache 130023424 121634816
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
DEFAULT 32K buffer cache 0 0
ASM Buffer Cache 0 0
13 rows selected.
3. buffer cache大小的设置
buffer cache大小由db_cache_size来设置，在OLTP系统中，对db_cache_size的设置，推荐大小为：
db_cache_size = SGA_MAX_SIZE/2 ~ SGA_MAX_SIZE*2/3
也可以使用advice来确认buffer cache的大小：
SQL> 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_read,
std_physical_read_time est_phy_read_t from v$db_cache_advice where name='DEFAULT'
and block_size=(select value from v$parameter where name='db_block_size');
Cache Size(MB) SIZE_FACTOR Buffers EST_READ_FACTOR ESTD_PHY_READ EST_PHY_READ_T
-------------- ----------- ---------- --------------- ------------- --------------
12 .0968 1497 4.5506 150739 1417
24 .1935 2994 2.8116 93133 840
36 .2903 4491 2.1741 72016 629
48 .3871 5988 1.8514 61328 521
60 .4839 7485 1.4618 48422 392
72 .5806 8982 1.2609 41769 325
84 .6774 10479 1.131 37463 282
96 .7742 11976 1.0366 34337 251
108 .871 13473 1.0078 33385 241
120 .9677 14970 1.0013 33168 239
124 1 15469 1 33125 239
132 1.0645 16467 .9967 33017 238
144 1.1613 17964 .9778 32389 231
156 1.2581 19461 .9589 31762 225
168 1.3548 20958 .9412 31178 219
180 1.4516 22455 .905 29977 207
192 1.5484 23952 .8432 27932 187
204 1.6452 25449 .8116 26883 176
216 1.7419 26946 .7926 26256 170
228 1.8387 28443 .7747 25661 164
240 1.9355 29940 .6643 22004 127
21 rows selected.
参数设置命令：
alter system set db_cache_size=2048M scope=both;
sga_max_size;
sga_target;
db_cache_size;
4. 查看某个对象在内存中有多少个buffer:
SQL> select object_name,dbarfil,dbablk from x$bh a,dba_objects b
where a.obj=b.object_id and object_name='TEST_INDEX';
OBJECT_NAME DBARFIL DBABLK
-------------------- ---------- ----------
TEST_INDEX 1 61198
TEST_INDEX 1 61431
TEST_INDEX 1 61664
TEST_INDEX 1 61520
TEST_INDEX 1 61753
... ...
TEST_INDEX 1 61818
TEST_INDEX 1 61208
TEST_INDEX 1 61441
TEST_INDEX 1 61674
TEST_INDEX 1 61530
TEST_INDEX 1 61763
131 rows selected.
表test_index在buffer cache中有131个buffer/block，而且列出了buffer所在的文件编号和block编号。
我们可以查看某个buffer/block的状态：
SQL> select class,flag,state,lru_flag from x$bh where dbarfil=1 and dbablk=61198;
CLASS FLAG STATE LRU_FLAG
---------- ---------- ---------- ----------
1 524288 1 0
1 0 0 6
state=0表示状态为free; state=1表示状态为clean.
查看某个对象占用了各种状态的buffer分别有多少个：
select o.object_name, decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pin')
state,count(*) blocks from x$bh b,dba_objects o where b.obj=o.data_object_id and state!=0
and o.object_name='TEST_INDEX' group by o.object_name,state;
OBJECT_NAME STATE BLOCKS
------------------------------ ----- ----------
TEST_INDEX xcur 692
查看占用了最多buffer的前30个对象:
select * from ( select o.object_name,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pin') 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 desc ) where rownum<30;
OBJECT_NAME STATE BLOCKS
------------------------------ ----- ----------
C_TOID_VERSION# xcur 1926
PARAMETER$ xcur 1926
RESULT$ xcur 1926
ATTRIBUTE$ xcur 1926
COLLECTION$ xcur 1926
METHOD$ xcur 1926
TYPE$ xcur 1926
COLTYPE$ xcur 863
SUBCOLTYPE$ xcur 863
NTAB$ xcur 863
COL$ xcur 863
ATTRCOL$ xcur 863
VIEWTRCOL$ xcur 863
ICOLDEP$ xcur 863
REFCON$ xcur 863
LOB$ xcur 863
TAB$ xcur 863
C_OBJ# xcur 863
CLU$ xcur 863
LIBRARY$ xcur 863
ICOL$ xcur 863
OPQTYPE$ xcur 863
TYPE_MISC$ xcur 863
IND$ xcur 863
TEST_INDEX xcur 692
OBJ$ xcur 612
IDL_UB1$ xcur 327
WRH$_SQL_PLAN xcur 248
SMON_SCN_TO_TIME xcur 173
29 rows selected.
5. 热块：被逻辑读的次数很多，即x$bh中的tch（touch）的值很大的块。
寻找热块：
SQL> select * from (select obj object,dbarfil file#,dbablk block#,tch touches from x$bh where tch>10 order by tch desc) where rownum<10;
OBJECT FILE# BLOCK# TOUCHES
---------- ---------- ---------- ----------
237 1 1658 1957
237 1 1657 1926
237 1 1659 1926
237 1 1660 1926
237 1 1661 1926
239 1 1674 1893
238 1 1666 178
10 1 92 174
105 1 794 158
9 rows selected.
根据上面的信息，我们可以找到热块所属于哪个对象：
SQL> select object_name from x$bh a,dba_objects b where a.obj=b.object_id and dbarfil=3 and dbablk=23499;
OBJECT_NAME
------------------------------
MGMT_METRIC_COLLECTIONS
MGMT_METRIC_COLLECTIONS
MGMT_METRIC_COLLECTIONS
buffer cache中总共有多少个buffer/block:
SQL> select sum(blocks) from dba_data_files;
SUM(BLOCKS)
-----------
124800
查看空闲空间的比例，最好要控制在10%以内：
SQL> 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 48
free 7675
available 7706
查看最消耗物理I/O的sql语句：
SQL> select * from (select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc) where rownum<3;
阅读(9974) | 评论(0) | 转发(0) |
