buffer busy wait - file header block

buffer busy wait这个事件表明多个进程正在尝试访问同样的cache buffer中的buffers。

class为file header block的buffer busy wait的等待事件表明文件头的块出现争用。

环境：
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 
SQL>

创建表为nologging，分别是testwrite1, testwrite2, testwrite3, testwrite4。
 
DROP TABLESPACE "SOEDATA" INCLUDING CONTENTS AND DATAFILES;

CREATE BIGFILE TABLESPACE "SOEDATA" DATAFILE
  '+DATA/dbs101/datafile/soedata01.dbf' SIZE 10485760
  AUTOEXTEND ON NEXT 65536 MAXSIZE 33554431M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1048576 DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

create table testwrite1 (tid number, tcode varchar2(40), name char(2000), name1 char(2000), name2 char(2000), name3 char(1000)) nologging;

create table testwrite1 (tid number, tcode varchar2(40), name char(2000), name1 char(2000), name2 char(2000), name3 char(1000)) nologging;
create table testwrite2 (tid number, tcode varchar2(40), name char(2000), name1 char(2000), name2 char(2000), name3 char(1000)) nologging;
create table testwrite3 (tid number, tcode varchar2(40), name char(2000), name1 char(2000), name2 char(2000), name3 char(1000)) nologging;
create table testwrite4 (tid number, tcode varchar2(40), name char(2000), name1 char(2000), name2 char(2000), name3 char(1000)) nologging;

shell脚本iotest_write.sh：运行iotest_write.sql。
#!/bin/bash
n=1
while (( $n <= 4 ))
do
        sqlplus " soe/soe@dbs101" @iotest_write.sql $n &
        n=$(( n+1 ))
done

脚本iotest_write.sql：direct load数据到testwrite四张表中，每张表load 1,000,000条记录。
set timing on

set echo on

alter session set events '10046 trace name context forever, level 12';

declare
 l_sql varchar2(4000);
begin
 l_sql := 'insert /*+ append */ into testwrite&1 ' || 'select rownum, to_char(rownum), to_char(rownum), to_char(rownum), to_char(rownum), to_char(rownum)'
  || 'from (select rownum from dual connect by rownum<=1000000)';
 execute immediate l_sql;
 commit;
end;
/

select 1 from dual;

运行前先truncate testwrite四张表
truncate table testwrite1 ;
truncate table testwrite2 ;
truncate table testwrite3 ;
truncate table testwrite4 ;

运行shell脚本iotest_write.sh。

AWR report输出结果
Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):                1.6              215.0       0.12      11.70
       DB CPU(s):                0.1                7.0       0.00       0.38
       Redo size:           21,315.0        2,834,705.7
   Logical reads:            2,051.8          272,875.0
   Block changes:              214.9           28,584.8
  Physical reads:                0.7               97.5
 Physical writes:            1,429.4          190,101.7

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
buffer busy waits                     2,966       2,191    739   53.6 Concurrenc
direct path write                    64,291       1,760     27   43.1 User I/O
DB CPU                                              134           3.3
db file sequential read                 874          15     17     .4 User I/O
Data file init write                    265           6     22     .1 User I/O

                                                             Avg
                                        %Time Total Wait    wait    Waits   % DB
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
buffer busy waits                 2,966     0      2,191     739    156.1   53.6
direct path write                64,291     0      1,760      27  3,383.7   43.1
db file sequential read             874     0         15      17     46.0     .4
Data file init write                265     0          6      22     13.9     .1
db file scattered read              156     0          4      24      8.2     .1
control file sequential re          219     0          2       9     11.5     .1
log file switch (private s            4     0          0     105      0.2     .0
log file sync                        13     0          0      23      0.7     .0
control file parallel writ           18     0          0      11      0.9     .0

SQL ordered by Elapsed Time            DB/Inst: dbs101/dbs101  Snaps: 802-803
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
-> %Total - Elapsed Time  as a percentage of Total DB time
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   98.8% of Total DB Time (s):           4,084
-> Captured PL/SQL account for   98.4% of Total DB Time (s):           4,084

        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
         1,006.2              1      1,006.16   24.6    3.2   44.1 dcmcu7kww1184
Module: SQL*Plus
declare l_sql varchar2(4000); begin l_sql := 'insert /*+ append */ into testwr
ite4 ' || 'select rownum, to_char(rownum), to_char(rownum), to_char(rownum), to_
char(rownum), to_char(rownum)' || 'from (select rownum from dual connect by ro
wnum<=1000000)'; execute immediate l_sql; commit; end;

Tablespace
------------------------------
                 Av       Av     Av                       Av     Buffer  Av Buf
         Reads Reads/s  Rd(ms) Blks/Rd       Writes Writes/s      Waits  Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
SOEDATA
             1       0     0.0     1.0      119,437       47      5,539   395.7
SYSAUX
           591       0    22.5     2.7          290        0          0     0.0
SYSTEM
           519       0    15.7     1.0           46        0          8     0.0

Tablespace               Filename
------------------------ ----------------------------------------------------
                 Av       Av     Av                       Av     Buffer  Av Buf
         Reads Reads/s  Rd(ms) Blks/Rd       Writes Writes/s      Waits  Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
PERFSTAT_DATA            +DATA/dbs101/datafile/perfstat_data.296.751306195
             1       0     0.0     1.0            1        0          0     0.0
SOEDATA                  +DATA/dbs101/datafile/soedata.294.751909089
             1       0     0.0     1.0      119,437       47      5,539   395.7
SYSAUX                   +DATA/dbs101/sysaux01.dbf
           591       0    22.5     2.7          290        0          0     0.0
SYSTEM                   +DATA/dbs101/system01.dbf

Segments by Physical Writes             DB/Inst: dbs101/dbs101  Snaps: 802-803
-> Total Physical Writes:       3,611,933
-> Captured Segments account for  100.0% of Total

           Tablespace                      Subobject  Obj.      Physical
Owner         Name    Object Name            Name     Type        Writes  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SOE        SOEDATA    TESTWRITE1                      TABLE      904,274   25.04
SOE        SOEDATA    TESTWRITE4                      TABLE      903,377   25.01
SOE        SOEDATA    TESTWRITE2                      TABLE      903,126   25.00
SOE        SOEDATA    TESTWRITE3                      TABLE      900,950   24.94
SYS        SYSAUX     WRH$_SQL_PLAN                   TABLE           42     .00
          -------------------------------------------------------------

Segments by Physical Write Requests     DB/Inst: dbs101/dbs101  Snaps: 802-803
-> Total Physical Write Requestss:         119,915
-> Captured Segments account for   99.7% of Total

           Tablespace                      Subobject  Obj.    Phys Write
Owner         Name    Object Name            Name     Type      Requests  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SOE        SOEDATA    TESTWRITE1                      TABLE       29,903   24.94
SOE        SOEDATA    TESTWRITE4                      TABLE       29,873   24.91
SOE        SOEDATA    TESTWRITE2                      TABLE       29,870   24.91
SOE        SOEDATA    TESTWRITE3                      TABLE       29,800   24.85
SYS        SYSAUX     WRH$_SQL_PLAN                   TABLE           37     .03
          -------------------------------------------------------------

Segments by Direct Physical Writes      DB/Inst: dbs101/dbs101  Snaps: 802-803
-> Total Direct Physical Writes:       3,603,256
-> Captured Segments account for  100.0% of Total

           Tablespace                      Subobject  Obj.        Direct
Owner         Name    Object Name            Name     Type        Writes  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SOE        SOEDATA    TESTWRITE1                      TABLE      902,596   25.05
SOE        SOEDATA    TESTWRITE4                      TABLE      901,701   25.02
SOE        SOEDATA    TESTWRITE2                      TABLE      901,445   25.02
SOE        SOEDATA    TESTWRITE3                      TABLE      899,271   24.96
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 814636_796 TABLE           24     .00
          -------------------------------------------------------------

Buffer Wait Statistics                  DB/Inst: dbs101/dbs101  Snaps: 802-803
-> ordered by wait time desc, waits desc

Class                    Waits Total Wait Time (s)  Avg Time (ms)
------------------ ----------- ------------------- --------------
file header block        5,521               2,186            396
data block                   8                   0              0
segment header               2                   0              0

从arw报表中可以看出，最大的等待事件时buffer busy waits，class为file header block。

ASH Report输出结果：
Top User Events                DB/Inst: dbs101/dbs101  (Jul 24 16:13 to 16:33)

                                                               Avg Active
Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
buffer busy waits                   Concurrency          39.84       1.68
direct path write                   User I/O             33.75       1.43
CPU + Wait for CPU                  CPU                   3.20       0.14
          -------------------------------------------------------------

Top Event P1/P2/P3 Values      DB/Inst: dbs101/dbs101  (Jul 24 16:13 to 16:33)

Event                          % Event  P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1                Parameter 2                Parameter 3
-------------------------- -------------------------- --------------------------
buffer busy waits                39.84                  "5","2","13"      39.84
file#                      block#                     class#

direct path write                33.75            "5","8431712","32"       0.02
file number                first dba                  block cnt

log file parallel write           8.95                   "1","4","1"       0.85
files                      blocks                     requests

Data file init write              8.19        "1","256","2147483647"       7.97
count                      intr                       timeout

db file parallel write            1.93          "1","0","2147483647"       1.93
requests                   interrupt                  timeout

 

Top Sessions                  DB/Inst: dbs101/dbs101  (Jul 24 16:13 to 16:33)
-> '# Samples Active' shows the number of ASH samples in which the session
      was found waiting for that particular event. The percentage shown
      in this column is calculated with respect to wall clock time
      and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
      when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
      the PQ slave activity into the session issuing the PQ. Refer to
      the 'Top Sessions running PQs' section for such statistics.

   Sid, Serial# % Activity Event                             % Event
--------------- ---------- ------------------------------ ----------
User                 Program                          # Samples Active     XIDs
-------------------- ------------------------------ ------------------ --------
      578,    1      19.27 buffer busy waits                    9.94
SOE                  sqlplus@dbs.dbs...m (TNS V1-V3)   504/1,200 [ 42%]        1

                           direct path write                    8.58
                                                      435/1,200 [ 36%]        1

      770,    5      19.27 buffer busy waits                    9.98
SOE                  sqlplus@dbs.dbs...m (TNS V1-V3)   506/1,200 [ 42%]        1

                           direct path write                    8.36
                                                      424/1,200 [ 35%]        1

      195,    3      19.21 buffer busy waits                    9.96
SOE                  sqlplus@dbs.dbs...m (TNS V1-V3)   505/1,200 [ 42%]        1

                           direct path write                    8.50
                                                      431/1,200 [ 36%]        1

      388,    1      19.21 buffer busy waits                    9.96
SOE                  sqlplus@dbs.dbs...m (TNS V1-V3)   505/1,200 [ 42%]        1

                           direct path write                    8.30
                                                      421/1,200 [ 35%]        1

      575,    1       9.01 log file parallel write              8.95
SYS                  oracle@dbs101.dbs.com (LGWR)    454/1,200 [ 38%]        0

 

Top Blocking Sessions          DB/Inst: dbs101/dbs101  (Jul 24 16:13 to 16:33)
-> Blocking session activity percentages are calculated with respect to
      waits on enqueues, latches and "buffer busy" only
-> '% Activity' represents the load on the database caused by
      a particular blocking session
-> '# Samples Active' shows the number of ASH samples in which the
      blocking session was found active.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
      when the blocking session was found active.

 Blocking Sid (Inst) % Activity Event Caused                      % Event
-------------------- ---------- ------------------------------ ----------
User                 Program                          # Samples Active     XIDs
-------------------- ------------------------------ ------------------ --------
   960,    3(     1)      16.06 buffer busy waits                   16.06
SYS                  oracle@dbs101.dbs.com (W000)    206/1,200 [ 17%]        0

   390,    9(     1)      12.64 buffer busy waits                   12.64
SYS                  oracle@dbs101.dbs.com (W004)    165/1,200 [ 14%]        0

   580,    5(     1)       5.88 buffer busy waits                    5.88
SYS                  oracle@dbs101.dbs.com (W003)     77/1,200 [  6%]        0

  1149,    5(     1)       4.75 buffer busy waits                    4.75
SYS                  oracle@dbs101.dbs.com (W001)     63/1,200 [  5%]        0

从ash报表中可以看出，最大的等待事件也是buffer busy waits，等待的块是file id是5。
块号是2。

QUERY得到buffer busy wait的对象：

set linesize 120
col block_type for a20
col objn for a25
col otype for a15
col filen for 9999
col blockn for 9999999
col obj for a20
col tbs for a10
select
bbw.cnt,
bbw.obj,
bbw.otype,
bbw.sql_id,
bbw.block_type,
nvl(tbs.name,to_char(bbw.p1)) TBS,
tbs_defs.assm ASSM
from (
select
count(*) cnt,
nvl(object_name,CURRENT_OBJ#) obj,
o.object_type otype,
ash.SQL_ID sql_id,
nvl(w.class,'usn '||to_char(ceil((ash.p3-18)/2))||' '||
decode(mod(ash.p3,2),
1,'header',
0,'block')) block_type,
--nvl(w.class,to_char(ash.p3)) block_type,
ash.p1 p1
from v$active_session_history ash,
( select rownum class#, class from v$waitstat ) w,
all_objects o
where event='buffer busy waits'
and w.class#(+)=ash.p3
and o.object_id (+)= ash.CURRENT_OBJ#
and ash.session_state='WAITING'
and ash.sample_time > sysdate - &minutes/(60*24)
--and w.class# > 18
group by o.object_name, ash.current_obj#, o.object_type,
ash.sql_id, w.class, ash.p3, ash.p1
) bbw,
(select file_id,
tablespace_name name
from dba_data_files
) tbs,
(select
tablespace_name NAME,
extent_management LOCAL,
allocation_type EXTENTS,
segment_space_management ASSM,
initial_extent
from dba_tablespaces
) tbs_defs
where tbs.file_id(+) = bbw.p1
and tbs.name=tbs_defs.name
Order by bbw.cnt

 
       CNT OBJ            OTYPE     SQL_ID        BLOCK_TYPE           TBS       ASSM
---------- -------------- --------- ------------- -------------------- --------- ------
       560 TESTWRITE2     TABLE     73uqrqxgbu6hu file header block    SOEDATA   AUTO
       560 TESTWRITE4     TABLE     g83bdwkq7vvd7 file header block    SOEDATA   AUTO
       561 TESTWRITE3     TABLE     2ktanngh5bazp file header block    SOEDATA   AUTO
       563 TESTWRITE1     TABLE     71c8yxa0z57xr file header block    SOEDATA   AUTO

从这里看出是class为file header block的buffer busy wait。

再从10046 trace文件中看到全部等待在一个块上。可以猜到这是ASSM的bmp管理块。
WAIT #182927391528: nam='buffer busy waits' ela= 1151204 file#=5 block#=2 class#=13 obj#=1 tim=1311496182929374
WAIT #182927391528: nam='buffer busy waits' ela= 2609 file#=5 block#=2 class#=13 obj#=1 tim=1311496182932216
WAIT #182927391528: nam='buffer busy waits' ela= 103 file#=5 block#=2 class#=13 obj#=1 tim=1311496185277262
WAIT #182927391528: nam='buffer busy waits' ela= 79 file#=5 block#=2 class#=13 obj#=1 tim=1311496185915601
WAIT #182927391528: nam='buffer busy waits' ela= 111 file#=5 block#=2 class#=13 obj#=1 tim=1311496186546257
WAIT #182927391528: nam='buffer busy waits' ela= 90 file#=5 block#=2 class#=13 obj#=1 tim=1311496187681644
WAIT #182927391528: nam='buffer busy waits' ela= 97 file#=5 block#=2 class#=13 obj#=1 tim=1311496188014463
WAIT #182927391528: nam='buffer busy waits' ela= 107 file#=5 block#=2 class#=13 obj#=1 tim=1311496189215838
WAIT #182927391528: nam='buffer busy waits' ela= 155 file#=5 block#=2 class#=13 obj#=1 tim=1311496189853705
WAIT #182927391528: nam='buffer busy waits' ela= 36 file#=5 block#=2 class#=13 obj#=1 tim=1311496190052796
WAIT #182927391528: nam='buffer busy waits' ela= 86 file#=5 block#=2 class#=13 obj#=1 tim=1311496192331624
WAIT #182927391528: nam='buffer busy waits' ela= 700 file#=5 block#=2 class#=13 obj#=1 tim=1311496193045278
WAIT #182927391528: nam='buffer busy waits' ela= 80 file#=5 block#=2 class#=13 obj#=1 tim=1311496194926218
WAIT #182927391528: nam='buffer busy waits' ela= 86 file#=5 block#=2 class#=13 obj#=1 tim=1311496198071027
WAIT #182927391528: nam='buffer busy waits' ela= 82 file#=5 block#=2 class#=13 obj#=1 tim=1311496198936753
WAIT #182927391528: nam='buffer busy waits' ela= 87 file#=5 block#=2 class#=13 obj#=1 tim=1311496200432685
WAIT #182927391528: nam='buffer busy waits' ela= 85 file#=5 block#=2 class#=13 obj#=1 tim=1311496201468397
WAIT #182927391528: nam='buffer busy waits' ela= 110 file#=5 block#=2 class#=13 obj#=1 tim=1311496202320686
WAIT #182927391528: nam='buffer busy waits' ela= 302 file#=5 block#=2 class#=13 obj#=1 tim=1311496208721118
WAIT #182927391528: nam='buffer busy waits' ela= 28 file#=5 block#=2 class#=13 obj#=1 tim=1311496208791422
WAIT #182927391528: nam='buffer busy waits' ela= 132 file#=5 block#=2 class#=13 obj#=1 tim=1311496209009280
WAIT #182927391528: nam='buffer busy waits' ela= 72 file#=5 block#=2 class#=13 obj#=1 tim=1311496209206937

dump文件的输出结果：
alter system dump datafile 5 block min 0 block max 2;

Start dump data blocks tsn: 9 file#:5 minblk 2 maxblk 2
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9, rdba=2
BH (0xb8f95938) file#: 5 rdba: 0x00000002 (1024/2) class: 13 ba: 0xb84ce000
  set: 19 pool: 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 129,19
  dbwrid: 0 obj: -1 objn: 1 tsn: 9 afn: 5 hint: f
  hash: [0xdb450b18,0x9bf8b148] lru: [0xb8f95b50,0xb8f958f0]
  ckptq: [NULL] fileq: [NULL] objq: [0xd0157700,0xb8f95918] objaq: [0xd01576f0,0xb8f95928]
  st: XCURRENT md: NULL tch: 144
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [3]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 9 rdba: 0x00000002 (1024/2)
scn: 0x0000.01ad97fe seq: 0x02 flg: 0x04 tail: 0x97fe1d02
frmt: 0x02 chkval: 0x390b type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000002A96DE6600 to 0x0000002A96DE8600
2A96DE6600 0000A21D 00000002 01AD97FE 04020000  [................]
2A96DE6610 0000390B 00000400 00000080 00BCC000  [.9..............]
2A96DE6620 00000009 00002000 FFFFFFFD 0000007E  [..... ......~...]
2A96DE6630 00BCBFFF 00016EF4 00000A8B 01ABC751  [.....n......Q...]
2A96DE6640 00000000 00000000 00000000 00000000  [................]
2A96DE6650 00B77A00 00000080 00000000 00000000  [.z..............]
2A96DE6660 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
2A96DE85F0 00000000 00000000 00000000 97FE1D02  [................]
File Space Header Block:
Header Control:
RelFno: 1024, Unit: 128, Size: 12369920, Flag: 9
AutoExtend: YES, Increment: 8192, MaxSize: 4294967293
Initial Area: 126, Tail: 12369919, First: 93940, Free: 2699
Deallocation scn: 28034897.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 9 file#: 5 minblk 2 maxblk 2

根据网站的解释
http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-buffer-busy-wait

class为"file header block"的buffer busy waits等待事件
Most likely extent allocation problems, look at extent size on tablespace and increase
the extent size to there are few extent allocations and less contention on the File
Header Block.

then try increasing the "next extent" size in the tablespace.
This wait can happen when lots of extents are being allocated in the tablespace.

尝试不同的方法解决这个等待：
当tablespace的next默认64k，buffer busy waits用了132s。

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                     1447        0.00          0.49
  db file sequential read                       963        0.02          3.57
  CSS initialization                              1        0.00          0.00
  CSS operation: query                            6        0.00          0.00
  CSS operation: action                           1        0.00          0.00
  direct path write                           21987        0.16        199.99
  control file sequential read                 9620        0.18         19.98
  KSV master wait                              6195        0.08          9.88
  ASM file metadata operation                  2136        0.03          0.27
  kfk: async disk IO                            481        0.01          2.04
  Data file init write                          481        0.06         13.28
  db file single write                          481        0.01          2.62
  control file parallel write                  1443        0.05         10.88
  rdbms ipc reply                               481        0.04          4.78
  enq: CF - contention                            2        0.01          0.01
  os thread startup                               1        0.03          0.03
  buffer busy waits                             119        2.35        132.67
********************************************************************************

将tablespace的next从默认64k改到1m，buffer busy waits用了138s。
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                      715        0.00          0.25
  db file sequential read                       475        0.01          1.29
  CSS initialization                              1        0.00          0.00
  CSS operation: query                            6        0.00          0.00
  CSS operation: action                           1        0.00          0.00
  direct path write                           22599        0.15        197.94
  control file sequential read                 4740        0.05          9.12
  KSV master wait                              3042        0.06          5.04
  ASM file metadata operation                  1047        0.00          0.11
  kfk: async disk IO                            237        0.01          1.33
  Data file init write                          237        0.04          6.36
  db file single write                          237        0.01          1.25
  control file parallel write                   711        0.07          5.44
  rdbms ipc reply                               237        0.07          1.98
  enq: TX - contention                            1        0.00          0.00
  enq: CF - contention                            2        0.13          0.17
  buffer busy waits                             140        2.32        138.60
********************************************************************************

将tablespace的初始大小改到10g。buffer busy waits没有了。
DROP TABLESPACE "SOEDATA" INCLUDING CONTENTS AND DATAFILES;

CREATE BIGFILE TABLESPACE "SOEDATA" DATAFILE
  '+DATA/dbs101/datafile/soedata01.dbf' SIZE 10g
  AUTOEXTEND ON NEXT 1048576 MAXSIZE 33554431M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1048576 DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  CSS initialization                              1        0.00          0.00
  CSS operation: query                            6        0.00          0.00
  CSS operation: action                           1        0.00          0.00
  direct path write                           22554        0.14        196.62
  KSV master wait                                 4        0.00          0.00
  ASM file metadata operation                     2        0.00          0.00
********************************************************************************

可以看到关于文件扩展的消耗已经没有了。主要的事件是direct path write。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-703110/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-703110/