ITPub博客

首页 > 数据库 > Oracle > direct path write

direct path write

原创 Oracle 作者:dbs101 时间:2011-07-24 16:11:54 0 删除 编辑

当一个进程直接写PGA buffer中的数据到磁盘中,而不通过DBWR写SGA的buffer cache。进程
等待写完的次数。这些操作有磁盘排序,并行DML操作,direct-path insert,create table
as select, 和一些lob操作。

direct-path insert在表为nologging和使用append模式load insert语句时是最快的。

环境:
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>

创建40g的表空间,可以放下所有的数据。
DROP TABLESPACE "SOEDATA" INCLUDING CONTENTS AND DATAFILES;

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

创建表为nologging,分别是testwrite1, testwrite2, testwrite3, testwrite4。
 
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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     31.52     526.57          0      15715    1263977     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     31.53     526.58          0      15715    1263977     1000000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  (cr=39197 pr=0 pw=1000000 time=83135877 us)
   1000000    1000000    1000000   COUNT  (cr=0 pr=0 pw=0 time=5080416 us)
   1000000    1000000    1000000    VIEW  (cr=0 pr=0 pw=0 time=4129817 us cost=2 size=0 card=1)
   1000000    1000000    1000000     COUNT  (cr=0 pr=0 pw=0 time=3190483 us)
   1000000    1000000    1000000      CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=2103834 us)
         1          1          1       FAST DUAL  (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  buffer busy waits                             327        0.02          0.05
  Disk file operations I/O                        2        0.00          0.00
  CSS initialization                              1        0.01          0.01
  CSS operation: query                            6        0.00          0.00
  CSS operation: action                           1        0.00          0.00
  direct path write                           17920        0.26        491.60
  KSV master wait                                 2        0.00          0.00
  ASM file metadata operation                     1        0.00          0.00
  log file switch (private strand flush incomplete)
                                                  1        0.08          0.08
  latch free                                      1        0.00          0.00
  log file switch completion                      1        0.10          0.10
********************************************************************************


SQL> select * from v$sysstat where name like '%physical%' and statistic# in (46,47,50,51,52,53);

STATISTIC# NAME                                                                 CLASS      VALUE    STAT_ID  
---------- ---------------------------------------------------------------- ---------- ---------- ----------
        46 physical read total IO requests                                           8     439231 3343375620
        47 physical read total multi block requests                                  8     125978 2007302071
        50 physical read total bytes                                                 8 7.0978E+10 2572010804
        51 physical write total IO requests                                          8     783515 1315894329
        52 physical write total multi block requests                                 8     546011 3540174003
        53 physical write total bytes                                                8 2.5221E+11 2495644835

6 rows selected.

SQL> select * from v$sysstat where name like '%physical%' and statistic# in (46,47,50,51,52,53);

STATISTIC# NAME                                                                 CLASS      VALUE    STAT_ID  
---------- ---------------------------------------------------------------- ---------- ---------- ----------
        46 physical read total IO requests                                           8     440277 3343375620
        47 physical read total multi block requests                                  8     125978 2007302071
        50 physical read total bytes                                                 8 7.0995E+10 2572010804
        51 physical write total IO requests                                          8     929647 1315894329
        52 physical write total multi block requests                                 8     671234 3540174003
        53 physical write total bytes                                                8 2.8509E+11 2495644835

6 rows selected.


统计信息physical write total multi block requests记录了多块写的次数。

10046 trace文件的数据结果:
EXEC #182927868896:c=0,e=200,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1512486435,tim=1311495126233233
FETCH #182927868896:c=0,e=173,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=1512486435,tim=1311495126233453
FETCH #182927868896:c=0,e=14,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=1512486435,tim=1311495126233520
CLOSE #182927868896:c=0,e=12,dep=2,type=3,tim=1311495126233587
WAIT #182927391528: nam='direct path write' ela= 17227 file number=5 first dba=8030656 block cnt=32 obj#=78388 tim=1311495126251904
WAIT #182927391528: nam='direct path write' ela= 32413 file number=5 first dba=8031232 block cnt=32 obj#=78388 tim=1311495126285676
PARSE #182927868896:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1512486435,tim=1311495126287145
BINDS #182927868896:
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2a977aea68  bln=22  avl=02  flg=05

从中可以看出,每次direct write 32blocks。

671,234 - 546,011 = 125,223 (physical write total multi block requests)

125,223 * 32(blocks) * 8k / 1024 = 31,305 mb

整个脚本运行了8m49s

31,305mb / (8m * 60s + 49s) = 31,305mb / (529s) = 59m/s (平均)

iostat的输出:
Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda         55.73  11.67 10.06 48.09  532.80 30189.13   266.40 15094.57   528.33     1.59   27.01  15.22  88.53
sda1         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2         0.00   0.00  0.00  0.40    0.00    1.61     0.00     0.80     4.00     0.01   35.00  33.50   1.35
sda3        55.73   0.00  9.86  0.00  526.36    0.00   263.18     0.00    53.39     0.22   21.08  21.18  20.89
sda4         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda5         0.00  11.67  0.20 47.48    6.44 30184.31     3.22 15092.15   633.11     1.34   28.13  18.01  85.90
sda6         0.00   0.00  0.00  0.20    0.00    3.22     0.00     1.61    16.00     0.01   36.00  70.00   1.41
sda7         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb          0.00  28.17  0.20 72.64    6.44 30404.83     3.22 15202.41   417.52     3.13   42.80  13.79 100.42
sdb1         0.00  28.17  0.20 72.64    6.44 30404.83     3.22 15202.41   417.52     3.13   42.80  13.79 100.42
sdb2         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb3         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdc          0.00  31.79  0.20 47.08    6.44 30430.58     3.22 15215.29   643.71     1.39   29.64  16.23  76.72
sdc1         0.00  15.69  0.20 44.06    6.44 30277.67     3.22 15138.83   684.15     1.09   24.86  17.33  76.70
sdc2         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdc3         0.00  16.10  0.00  3.02    0.00  152.92     0.00    76.46    50.67     0.30   99.73  21.67   6.54
sdd          0.00  14.69  0.40 45.07   12.88 30396.78     6.44 15198.39   668.74     1.15   25.16  17.19  78.19
sdd1         0.00  14.69  0.40 45.07   12.88 30396.78     6.44 15198.39   668.74     1.15   25.16  17.19  78.19
sdd2         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdd3         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdd4         0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

从这里可以看出,一个asm磁盘的写是15m/s。那么四个asm磁盘的写60m/s。这和统计信息的结果接近。

注释:磁盘在线扩展需要额外的操作,比如磁盘头的争用,控制文件的更新等等,将磁盘初始加大到需要的值会加快
insert的速度。

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

请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    437893