本帖最后由 guoyJoe 于 2013-10-26 20:40 编辑
[size=13.913043975830078px]最近在看吕大师的大作《Oracle核心揭密》，这部大作可以与Jonathan Lewis大师的《Oracle Core_ Essential Internals for DBA》相提并论，看了几天收益颇多，哈哈美国有Lewis，中国有VAGE
。
gyj@OCM> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
gyj@OCM> show parameter mem
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address
integer 0
memory_max_target
big integer 0
memory_target
big integer 0
shared_memory_address
integer 0
gyj@OCM> show parameter sga
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
lock_sga
boolean FALSE
pre_page_sga
boolean FALSE
sga_max_size
big integer 372M
sga_target
big integer 0
gyj@OCM> show parameter db_cache
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_cache_advice
stringON
db_cache_size
big integer 100M
gyj@OCM> show parameter mttr
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target
integer 0
_db_writer_coalesce_area_size
Size of memory allocated to dbwriter for coalescin 1048576
gyj@OCM>create table gyj100 (id int,name varchar2(2000));
gyj@OCM> declare
2 cnumber :=0 ;
3begin
4for i in 1 .. 5000000 loop
5insert into gyj100 values(i,'gyj'||i);
6c := c+1;
7if mod(c,5000)=0 then
8commit;
9 end if;
10end loop;
11end;
12/
PL/SQL procedure successfully completed.
gyj@OCM> select bytes/1024/1024 sz from user_segments where segment_name='GYJ100';
SZ
----------
120
gyj@OCM> select sid from v$mystat where rownum=1;
SID
----------
125
gyj@OCM> update gyj100 set id=id+0 where rownum select * from V$SESSION_wait where sid=125;
SID SEQ# EVENT
P1TEXT
P1 P1RAW P2TEXT
P2 P2RAW
P3TEXT
P3 P3RAW
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
WAIT_TIME SECONDS_IN_WAIT STATE
WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- --------------------------
12545323 log buffer space
0 00
0 00
0 00 3290255840 2 Configuration
-1
0 WAITED SHORT TIME
4608
7446
gyj@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file parallel write';
no rows selected
gyj@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT like 'db file%';
EVENT
TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
db file sequential read
91162
37712572
db file scattered read
1830 8543678
db file single write
372 1934286
db file async I/O submit
553
84199251
db file parallel read
67 1863758
始终没看到db file parallel write等待。。。。
在吕大师的指点下：
1.jpg (10.75 KB, 下载次数: 74)
下载附件
2013-10-26 20:34 上传
2.jpg (15.2 KB, 下载次数: 59)
下载附件
2013-10-26 20:34 上传
3.jpg (50.84 KB, 下载次数: 54)
下载附件
2013-10-26 20:34 上传
select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';
MOS上有篇文章
'db file async I/O submit' when FILESYSTEMIO_OPTIONS=NONE [ID 1274737.1]
'db file async I/O submit' should be treated as 'db file parallel write' in previous releases.
***************************************************************
开始测试：
**************************************************************
第一把：
参数：_db_writer_coalesce_area_size=1048576
sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';
EVENT
TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
db file async I/O submit
1944
88188121
gyj@OCM> set timing on;
gyj@OCM> update gyj100 set id=id+0 where rownum select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';
EVENT
TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
db file async I/O submit
2222 128007271
sys@OCM> select (2222-1944)/46||'次/秒' from dual;
(2222-1944)/46
--------------
6.04347826次/秒
第二把测试，修改参数：
sys@OCM> alter system set "_db_writer_coalesce_area_size"=8048576 scope=spfile;
System altered.
sys@OCM> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@OCM> startup
ORACLE instance started.
Total System Global Area388354048 bytes
Fixed Size
2228584 bytes
Variable Size
276827800 bytes
Database Buffers
104857600 bytes
Redo Buffers
4440064 bytes
Database mounted.
Database opened.
sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';
no rows selected
gyj@OCM> update gyj100 set id=id+0 where rownum select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';
EVENT
TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
db file async I/O submit
31
15256544
sys@OCM> select 31/16||'次/秒' from dual;
31/16
----------
1.9375次/秒
再测一把，把参数改回去：_db_writer_coalesce_area_size=1048576
sys@OCM> alter system set "_db_writer_coalesce_area_size"=1048576 scope=spfile;
System altered.
sys@OCM> startup force;
ORACLE instance started.
Total System Global Area388354048 bytes
Fixed Size
2228584 bytes
Variable Size
276827800 bytes
Database Buffers
104857600 bytes
Redo Buffers
4440064 bytes
Database mounted.
Database opened.
sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';
no rows selected
gyj@OCM> update gyj100 set id=id+0 where rownum sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';
EVENT
TOTAL_WAITS TIME_WAITED_MICRO
---------------------------------------------------------------- ----------- -----------------
db file async I/O submit
282
40353435
sys@OCM> select 282/48||'次/秒' from dual;
282/48
----------
5.875次/秒
总结：
参数_db_writer_coalesce_area_size=1048576， 产生的等待6次/秒
参数：_db_writer_coalesce_area_size=8048576，产生的等待2次/秒
期待《Oracle核心揭密》早日上市。。。。
|
