遭遇direct path sync等待,不知道是不是异步IO没有开启的原因
本帖最后由 不了峰 于 2014-1-21 20:27 编辑环境说明:
RHEL 4 64位 Oracle 10.2.0.5 64bit 单节点
采用文件系统来存放数据文件,没有使用ASM
(这个数据库一个OLTP的系统,用来频繁录入日志,每秒的log file sync 有100次左右,每秒的user commit有80次左右)
在awr中Top 5 time event中发现不常见的direct path sync等待事件,
想请教是一下,这是不由于没有启用Oracle的异步IO而导致的?
# egrep "kioctx|kiocb" /proc/slabinfo
kioctx 87 180 320 12 1 : tunables 54 27 8 : slabdata 15 15 0
kiocb 0 0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0
下面是一些输出,并附上一个小时的awr报告.
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
disk_asynch_io boolean TRUE
tape_asynch_io boolean TRUE
------------------------------------ --------------------------------- ------------------------------
filesystemio_options string none
每小时awr中的direct path sync次数
SNAP_ID EVENT_NAME END_TIME TOTAL_WAITS Avg Wait(ms)
---------- ------------------ -------------------- ----------- ----------
1584 direct path sync 2014-01-21 06:00:19 2634 1.69
1585 direct path sync 2014-01-21 07:00:21 4366 1.66
1586 direct path sync 2014-01-21 08:00:24 9580 1.66
1587 direct path sync 2014-01-21 09:00:26 16333 1.7
1588 direct path sync 2014-01-21 10:00:29 20117 1.68
1589 direct path sync 2014-01-21 11:00:31 22082 1.97
1590 direct path sync 2014-01-21 12:00:34 22961 1.88
1591 direct path sync 2014-01-21 13:00:37 21733 4.17
1592 direct path sync 2014-01-21 14:00:39 13238 59.64
1593 direct path sync 2014-01-21 15:00:42 11053 54.96
1594 direct path sync 2014-01-21 16:00:44 10480 57.81
1595 direct path sync 2014-01-21 17:00:47 49437 7.16
1596 direct path sync 2014-01-21 18:00:49 40491 1.65
1597 direct path sync 2014-01-21 19:00:52 21831 1.69
每小时awr中的db time的时长
DAY name DB_TIME_MIN BACKGROUND_ELAPSED_TIMES_MIN
------------------------------------------------------------- ----------- ----------------------------
2014-01-21 06:00:19 DB time 4.41 2.8
2014-01-21 07:00:21 DB time 6.92 4.05
2014-01-21 08:00:24 DB time 15.53 8.44
2014-01-21 09:00:26 DB time 29.15 15.4
2014-01-21 10:00:29 DB time 35.21 17.7
2014-01-21 11:00:31 DB time 46.91 20.07
2014-01-21 12:00:34 DB time 56.64 22.29
2014-01-21 13:00:37 DB time 60.27 24.4
2014-01-21 14:00:39 DB time 89.16 49.08
2014-01-21 15:00:42 DB time 89.37 51.63
2014-01-21 16:00:44 DB time 88.37 53.34
2014-01-21 17:00:47 DB time 68.11 39.57
2014-01-21 18:00:49 DB time 41.78 20.59
2014-01-21 19:00:52 DB time 39.37 19.47
每小时awr中的log file sync 的次数
SNAP_ID EVENT_NAME END_TIME TOTAL_WAITS TIME_WAITED_MICRO
---------- ------------------ -------------------- ----------- ----------
1584 log file sync 2014-01-21 06:00:19 50627 2.58
1585 log file sync 2014-01-21 07:00:21 78587 2.55
1586 log file sync 2014-01-21 08:00:24 174096 2.56
1587 log file sync 2014-01-21 09:00:26 327957 2.55
1588 log file sync 2014-01-21 10:00:29 377661 2.58
1589 log file sync 2014-01-21 11:00:31 418432 2.63
1590 log file sync 2014-01-21 12:00:34 402134 3.11
1591 log file sync 2014-01-21 13:00:37 372513 3.73
1592 log file sync 2014-01-21 14:00:39 375711 3.5
1593 log file sync 2014-01-21 15:00:42 406071 3.45
1594 log file sync 2014-01-21 16:00:44 423585 3.04
1595 log file sync 2014-01-21 17:00:47 448699 3.12
1596 log file sync 2014-01-21 18:00:49 426349 2.67
1597 log file sync 2014-01-21 19:00:52 413578 2.59
每小时awr中的user commits 的次数
DAY USER_COMMITS USER_ROLLBACKS TRANSACTION_ROLLBACKS
--------------------------------------------------------- ------------ -------------- ---------------------
2014-01-21 07:00:21 61376 0 17320
2014-01-21 08:00:24 143999 0 30001
2014-01-21 09:00:26 251056 11 76915
2014-01-21 10:00:29 279529 0 98242
2014-01-21 11:00:31 293315 0 124954
2014-01-21 12:00:34 280153 2 122093
2014-01-21 13:00:37 253628 2 117780
2014-01-21 14:00:39 252391 1 122356
2014-01-21 15:00:42 266954 2 138316
2014-01-21 16:00:44 268500 9 155135
2014-01-21 17:00:47 299836 11 148843
2014-01-21 18:00:49 295688 0 130706
2014-01-21 19:00:52 271254 0 142343
谢谢~
当然这个系统不是很优化,比如没有使用绑定变量等 11.2的官方文档：
direct path sync
During Direct Path write operations the data is asynchronously written to the database files. At some point the session needs to ensure that all outstanding asynchronous I/O have been completed to disk. On Unix the fsync command, which synchronizes data to disk, is issued to confirm that all the writes have completed and the data is all on disk.
Wait Time: The time taken for the fsync operation to complete, which normally is the time taken to complete the outstanding I/Os.
Parameters Description
file# See "file#"
flags Flags used for debugging purposes
http://docs.oracle.com/cd/E11882_01/server.112/e25513/waitevents003.htm#REFRN00539
harryzhang 发表于 2014-1-21 21:21 static/image/common/back.gif
11.2的官方文档：
direct path sync
direct path write 一般是由什么命令 ?
sqlldr 的直接路径写,insert /*+ append */
exp 中的direct算吗 这个问题和 High "direct path sync" Waits on Unix Due to Oracle Not Using Direct I/O to Interact with the Filesystem 有些类似，当前参数FILESYSTEMIO_OPTIONS 是 none, 物理读最大的对象也是 LOB
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
FJDXGPS FJSATCOMNOR SYS_LOB0000053815C00015$$ LOB 762,143 79.62
FJDXGPS FJSATCOMNOR SYS_LOB0000053819C00015$$ LOB 163,815 17.11
FJDXGPS FJSATCOMNOR GJ_T_VIDICONTRACK_1 TABLE 13,216 1.38
FJDXGPS FJSATCOMNOR GJ_T_VIDICONTRACK_2 TABLE 5,285 0.55
FJDXGPS FJSATCOMNOR SYS_LOB0000053811C00015$$ LOB 620 0.06
尝试下设置
ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
看看能否生效 ODM FINDING:
High "direct path sync" Waits on Unix Due to Oracle Not Using Direct I/O to Interact with the Filesystem (Doc ID 1492706.1)
Import Slow For LOB Objects (Doc ID 950350.1)
CAUSE
The AWR reports the wait event 'direct path sync' spending a lot of time doing a fsync system calls which is an OS issue. The performance problem is due to the filesystemio_options database parameter is not to SETALL which enables direct I/O as well as asynchronous I/O.
I/O operations in UNIX and Linux systems typically go through the file system cache. This extra processing does require resources. Thus reducing the I/O performance. The default value for filesystemio_options is none. And setting the value to SETALL enables both direct I/O and asynchronous I/O where possible.
SOLUTION
Setting the parameter for FILESYSTEMIO_OPTIONS to SETALL or DIRECTIO improves the import performance dramatically. Restarting the instance is needed as this parameter is static parameter.
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE; 具体用了什么文件系统？ 具体用了什么文件系统？ Liu Maclean(刘相兵 发表于 2014-1-23 15:32 static/image/common/back.gif
具体用了什么文件系统？
ext3 格式
insert 的表没有 大字段类型的列
页:
[1]