[20180316]异步IO和共享服务模式.txt

--//在共享服务器模式下,不会使用异步IO,通过例子证明.

1.环境:
SYS@book> @ &r/ver1
PORT_STRING          VERSION    BANNER
-------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx  11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> alter system set filesystemio_options=asynch scope=spfile;
System altered.

--//重启数据库.

SCOTT@book> show sga
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes

SCOTT@book> show parameter disk_asynch_io
NAME                 TYPE    VALUE
-------------------- ------- ------
disk_asynch_io       boolean TRUE

SCOTT@book> show parameter filesystemio_options
NAME                 TYPE    VALUE
-------------------- ------- -------
filesystemio_options string  ASYNCH

SCOTT@book> create table t as select rownum id from dual connect by level<=2;
Table created.

SCOTT@book> ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
--//这样可以实现每块2条记录.

SCOTT@book> insert into t select rownum+2 from dual connect by level <=8e4-2;
79998 rows created.

SCOTT@book> commit ;
Commit complete.

--//分析表略.

SCOTT@book> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS from dba_segments where owner=user and segment_name='T';
OWNER  SEGMENT_NAME         SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS
------ -------------------- ------------------ ----------- ------------ ---------- ----------
SCOTT  T                    TABLE                        4          546  333447168      40704

--//占用 333447168/1024/1024 = 318M

SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
     90467          90467

2.测试使用专用模式连接:
SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> @ &r/spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       274          5 49118                    DEDICATED 49119       21          3 alter system kill session '274,5' immediate;;

--//SERVER=DEDICATED,进程号=49119.启动$ strace -f -p 49119  -c,然后执行:
SCOTT@book> select count(*) from t;
  COUNT(*)
----------
     80000

$ strace -f -p 49119  -c
Process 49119 attached - interrupt to quit
^CProcess 49119 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
99.62    0.028456          87       326           io_submit
  0.16    0.000045           1        86           pread
  0.13    0.000036           0       164           io_getevents
  0.09    0.000027           7         4           mmap
  0.00    0.000000           0         2           read
  0.00    0.000000           0         2           write
  0.00    0.000000           0        82           getrusage
  0.00    0.000000           0        32           times
------ ----------- ----------- --------- --------- ----------------
100.00    0.028564                   698           total

--//可以发现调用io_submit,io_getevents.说明启动异步IO.

3.测试使用共享模式连接:
$ rlsql scott/book@127.0.0.1:1521/book
..
SCOTT@127.0.0.1:1521/book> alter system flush buffer_cache;
System altered.

SCOTT@127.0.0.1:1521/book> alter session set statistics_level=all;
Session altered.

SCOTT@127.0.0.1:1521/book> @ &r/spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       261          1 49156                    SHARED    49063       20          1 alter system kill session '261,1' immediate;

$ ps -ef | grep 4906[3]
oracle   49063     1  0 15:33 ?        00:00:00 ora_s000_book

--//SERVER=SHARED,进程号=49063.查询进程号49063对应ora_s000_book,说明使用共享服务模式.启动 strace -f -p 49063  -c,然后执行:
SCOTT@book> select count(*) from t;
  COUNT(*)
----------
     80000

$ strace -f -p 49063  -c
Process 49063 attached - interrupt to quit
^CProcess 49063 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
99.84    0.011918          35       336           pread
  0.16    0.000019           1        27           getrusage
  0.00    0.000000           0         2           read
  0.00    0.000000           0         2           poll
  0.00    0.000000           0         2           sendto
  0.00    0.000000           0        25           times
  0.00    0.000000           0         1           restart_syscall
------ ----------- ----------- --------- --------- ----------------
100.00    0.011937                   395           total

--//使用pread,read函数.不存在调用io_submit,io_getevents函数.说明在共享模式下不会使用异步IO.
--//加上上午的测试,说明在共享模式下,不会使用异步IO以及直接路径读特性.
--//以后工作中遇到这类问题主要.
--//补充启用DRCP连接的情况,好像使用很少有人使用这个特性.

4.测试drcp:

SYS@book> exec dbms_connection_pool.start_pool()
PL/SQL procedure successfully completed.

$ rlsql scott/book@127.0.0.1:1521/book:POOLED

SCOTT@127.0.0.1:1521/book:POOLED> alter session set statistics_level=all;
Session altered.

SCOTT@127.0.0.1:1521/book:POOLED> alter system flush buffer_cache;
System altered.

SCOTT@127.0.0.1:1521/book:POOLED> @ &r/spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        94          1 49221                    POOLED    49204       31          1 alter system kill session '94,1' immediate;

--//SERVER=POOLED,进程号=49204.启动 strace -f -p 49204  -c,然后执行:

SCOTT@127.0.0.1:1521/book:POOLED> @ &r/viewsess "physical reads direct"
NAME                                                                   STATISTIC#      VALUE        SID
---------------------------------------------------------------------- ---------- ---------- ----------
physical reads direct                                                          97          0         94
physical reads direct temporary tablespace                                    110          0         94
physical reads direct (lob)                                                   176          0         94

$ strace -f -p 49204  -c
...//等
.
SCOTT@127.0.0.1:1521/book:POOLED> select count(*) from t;
  COUNT(*)
----------
     80000

SCOTT@127.0.0.1:1521/book:POOLED> @ &r/viewsess "physical reads direct"
NAME                                                                   STATISTIC#      VALUE        SID
---------------------------------------------------------------------- ---------- ---------- ----------
physical reads direct                                                          97      40217         94
physical reads direct temporary tablespace                                    110          0         94
physical reads direct (lob)                                                   176          0         94

$ strace -f -p 49204  -c
Process 49204 attached - interrupt to quit
^CProcess 49204 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
98.17    0.019632          60       326           io_submit
  1.11    0.000221          74         3           munmap
  0.57    0.000113          28         4           write
  0.16    0.000031           2        13           mmap
  0.00    0.000000           0         4           read
  0.00    0.000000           0         1           poll
  0.00    0.000000           0         4           pread
  0.00    0.000000           0        39           getrusage
  0.00    0.000000           0        30           times
  0.00    0.000000           0       164           io_getevents
------ ----------- ----------- --------- --------- ----------------
100.00    0.019997                   588           total

--//可以发现启动drcp连接调用io_submit,io_getevents,也就是会使用异步IO以及直接路径读特性.