生产环境一日常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 <<<<<<<<<<,37.57 ms
...
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
...
ANLYS_DSD_HOUR_201606 18 0 27.22 1.00 69 0.00 28 0 0 0.00 <<<<,,27.22ms
IO performance issue caused the SQL was slow.
contact the OS admin or vendor to check and fixed the IO issues?
同样使用iostat -x命令查看命令输出的最有一列，超过50一般IO都有问题。
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 <<<<<<<<<<,37.57 ms
...
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
...
ANLYS_DSD_HOUR_201606 18 0 27.22 1.00 69 0.00 28 0 0 0.00 <<<<,,27.22ms
IO performance issue caused the SQL was slow.
contact the OS admin or vendor to check and fixed the IO issues?
同样使用iostat -x命令查看命令输出的最有一列，超过50一般IO都有问题。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29135257/viewspace-1972656/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
2016年新年愿望
下一篇：
iostat -x命令诊断
请登录后发表评论
登录
全部评论
