Hello,
On oracle 12.1.0.2 runing on oracle linux 6 connect to oracle ZFS3 storage we are constatly see "local write wait" on the top wait evet.
We have also notice that the whis wait event is come togther with truncate operations.
Awr report shows :
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|
| rdbms ipc reply | 287 | 2414.6 | 8413.07 | 26.1 | Other |
| free buffer waits | 167 | 2176 | 13030.16 | 23.6 | Configuration |
| local write wait | 113 | 2033.8 | 17997.86 | 22.0 | User I/O |
| write complete waits | 25 | 942.9 | 37716.82 | 10.2 | Configuration |
| enq: RO - contention | 7 | 635.6 | 90800.07 | 6.9 | Application |
| row cache lock | 2 | 507.2 | 253622.16 | 5.5 | Concurrency |
| DB CPU | | 285.8 | | 3.1 | |
| log file sync | 1,045 | 83.9 | 80.31 | .9 | Commit |
| log buffer space | 158 | 76.1 | 481.74 | .8 | Configuration |
| db file sequential read | 15,135 | 34 | 2.25 | .4 | |
Also ASH report shows that the event "local write wait" is due to truncate operations:
Top SQL with Top Events
| SQL ID | Plan Hash | Executions | % Activity | Event | % Event | Top Row Source | % Row Source | SQL Text |
|---|
| 8jdnp4xqz46jv | 75433467 | 3 | 4.94 | local write wait | 3.05 | DDL STATEMENT | 3.05 | truncate table TMP_SP_REFRESHC... |
8jdnp4xqz46jv | 75433467 | 3 | 4.94423791821561338289962825278810408922 | rdbms ipc reply | 1.82 | DDL STATEMENT | 1.82 | |
| 0ajcttwqv0q5h | 25184387 | 4 | 4.65 | local write wait | 2.49 | DDL STATEMENT | 2.49 | TRUNCATE TABLE TMP_CDRS_FULL |
0ajcttwqv0q5h | 25184387 | 4 | 4.64684014869888475836431226765799256506 | rdbms ipc reply | 2.12 | DDL STATEMENT | 2.12 | |
| 0kbpwn4f4pngq | 700989480 | 2 | 4.35 | free buffer waits | 4.31 | LOAD TABLE CONVENTIONAL | 4.31 | INSERT INTO TMP_IMPORT_PLIST T... |
| 89wa9tmwk3unf | 3837413856 | 4 | 3.35 | enq: RO - contention | 2.01 | DDL STATEMENT | 2.01 | truncate table D$IMPORT_DIALCO... |
89wa9tmwk3unf | 3837413856 | 4 | 3.34572490706319702602230483271375464684 | rdbms ipc reply | 1.15 | DDL STATEMENT | 1.15 | |
| 7qdt98x5jqp8n | 1704899393 | 1 | 2.97 | local write wait | 1.97 | DDL STATEMENT | 1.97 | ** SQL Text Not Available ** |
I have found some information here :
https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_local_writ…
which explain that :
"The wait event can be caused by truncate operations. Truncate operations cause the DBWR to be posted to flush out the space header."
I have found that in 10g there was a bug related to truncate operation.
I have tried the suggested work around set the hidden parameter : _db_fast_obj_truncate=FALSE . It helped to reduce the wait on event "enq: RO - fast object reuse" , but we still see high "local write wait"
Please advise how to handle such performance issue.
Thanks
Yoav