SQL performing extremely slow after setting filesystemio_options=setall and mounting file system wit
One of the SQL statement started perfoming extremely slow after changing the SQL performing extremely slow after setting filesystemio_options=setall and mounting file system with direct option.
One of the table is a partition table having approx 4 mn records. Buffer cache advisory does not show any performance gain even if we increase the cache size.
Top wait events have sequential and scattered reads.
Explain Plan:
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2357K| 294M| | 111K (1)| 00:22:19 | | |
| 1 | HASH GROUP BY | | 2357K| 294M| 334M| 111K (1)| 00:22:19 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 2357K| 294M| | 42373 (1)| 00:08:29 | | |
| 3 | TABLE ACCESS FULL | W_WHSL_CLS | 19627 | 536K| | 123 (1)| 00:00:02 | | |