oracle io 慢,io问题导致的insert缓慢

最新推荐文章于 2022-12-13 11:24:15 发布

于人士心

最新推荐文章于 2022-12-13 11:24:15 发布

阅读量586

点赞数

文章标签： oracle io 慢

这篇博客讨论了一旦生产环境中SQL执行速度显著下降，尤其是插入操作变得缓慢时如何进行性能排查。通过AWR报告和10046追踪，发现主要的等待事件为磁盘顺序读取，其响应时间远超正常阈值20毫秒，部分表空间的平均读取时间甚至高达数十毫秒。博主建议，当发现I/O响应时间过长时，应联系操作系统管理员或供应商检查并修复I/O问题。

摘要由CSDN通过智能技术生成

生产环境一日常insert在业务量加倍之后非常缓慢，对有问题sql做awr报告和10046trace得出以下结论：

In the 10046 trace, the execution of sql elapsed 27.85 secs, the waiting event held 12.41 secs. it is main contributor of slow sql.

when checking the raw 10046 trace, we can see db file sequential read elapsed more than 50ms even reached up to 156ms for reading one block.

How to Tell if the I/O of the Database is Slow ( Doc ID 1275596.1 )

==>

Expected thresholds for response timeA typical multi-block synchronous read of 64 x 8k blocks (512kB total) should have an average of at most 20 milliseconds before worrying about 'slow IO'.

In the AWR report, many tablespace avg RD(ms) were over 20 ms

Tablespace Reads Av Rds/s Av Rd(ms) Av Blks/Rd 1-bk Rds/s Av 1-bk Rd(ms) Writes Writes avg/s Buffer Waits Av Buf Wt(ms)

HIATMPTS_DETECT_201601 656,884 91 64.92 1.00 793,608 91.00 65 110 44,029 82.77 <<<<<<<<<64.92ms

...

HIATMPTS_ILLEGAL_201502 8,170 1 37.57 1.03 105 1.13 38 0 9,054 16.02 <<<<<<<<<

...

HIATMPTS_DETECT_201511 2,358 0 89.72 1.00 1,689 0.33 90 0 0 0.00 <<<<<<<<<89.72

HIATMPTS_ILLEGAL_201601 1,438 0 63.39 1.01 1,259 0.20 63 0 299 33.31 <<<<<<<<<63.39

SYSTEM 1,557 0 65.36 1.66 512 0.21 64 0 1,383 277.48 <<<<<<<<<65.36 ms

...

ITSOM 169 0 44.73 26.07 26 0.02 1 0 0 0.00 <<<<<<<<<<<44.73ms

...