163 积分	0 好友	12 主题

发消息

遭遇direct path sync等待,不知道是不是异步IO没有开启的原因

1^#

发表于 2014-1-21 20:20:38 | 查看: 5830| 回复: 8

本帖最后由不了峰于 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

谢谢~

awrrpt_1_1593_1594.html (255.11 KB, 下载次数: 775)

分享0

收藏0 回复只看该作者道具举报

不了峰

9^#

发表于 2014-1-24 10:00:36

Liu Maclean(刘相兵发表于 2014-1-23 15:32
具体用了什么文件系统？

ext3 格式

insert 的表没有大字段类型的列

回复只看该作者道具举报

Liu Maclean(刘相兵

8^#

发表于 2014-1-23 15:32:51

具体用了什么文件系统？

回复只看该作者道具举报

Liu Maclean(刘相兵

7^#

发表于 2014-1-23 15:32:39

具体用了什么文件系统？

回复只看该作者道具举报

Liu Maclean(刘相兵

6^#

发表于 2014-1-23 15:32:24

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;

回复只看该作者道具举报

生生不息

5^#

发表于 2014-1-23 11:07:10

这个问题和 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;
看看能否生效

点评回复只看该作者道具举报

不了峰发表于 2014-1-24 10:00: 谢谢

不了峰

4^#

发表于 2014-1-22 08:56:52

harryzhang 发表于 2014-1-21 21:21
11.2的官方文档：

direct path sync

direct path write 一般是由什么命令 ?
sqlldr 的直接路径写,insert /*+ append */

exp 中的direct算吗

回复只看该作者道具举报

harryzhang

3^#

发表于 2014-1-21 21:21:19

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

回复只看该作者道具举报

不了峰

2^#

发表于 2014-1-21 20:21:35

当然这个系统不是很优化,比如没有使用绑定变量等

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册