[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以及直接路径读特性.
