本文共 6298 字，大约阅读时间需要 20 分钟。
[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 ProductionSYS@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 bytesSCOTT@book> show parameter disk_asynch_io
NAME TYPE VALUE -------------------- ------- ------ disk_asynch_io boolean TRUESCOTT@book> show parameter filesystemio_options
NAME TYPE VALUE -------------------- ------- ------- filesystemio_options string ASYNCHSCOTT@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 904672.测试使用专用模式连接:
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(*) ---------- 80000SCOTT@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以及直接路径读特性.
转载地址：http://oqzda.baihongyu.com/