Hi people,
consider this :
- IBM AIX 7.1
- Oracle EE 12.1.0.2
Complex query (plan) runs in about 8 minutes. I notice that the IO wait times are not impressive and look for cause/solution. I come up with parameter filesystemio_options which is set on ASYNCH. I switch it to SETALL to start using DIRECT IO but the response time goes through the roof, it needs now 18 hours !!
What happened here?
I have AWR and AWR SQL Reports and it looks like the avg IO wait time has indeed significantly been reduced (which is a good thing). And overall query runtime stats are improved. Only CPU time and elapse time have increased dramatically. Here are some numbers:
- I can see that the avg IO wait has dramatically reduced from 12ms to 1.85ms – that’s must be because of the change setting of filesystemio_options.
- What’s also improved: the runtime stats of the query have been significantly reduced
- Buffer gets from 855K to 663K
- Disk reads from 1694K to 1279K
- User IO wait time from 198K to 78K
I do see a change in query plan however. The query plan has changed slightly...would that be because of the parameter change? Please note that the query is running on a isolated system with hardly any activity and queries only 'old' data (which is never changed).
I am uploading relevant AWR reports.
Thanks a lot.
Kind regards,
Benny Derous
· I can see that the avg IO wait has dramatically reduced from 12ms to 1.85ms – that’s because of the change setting of filesystemio_options.
· What’s also improved: the runtime stats of the query have been significantly reduced
o Buffer gets from 855K to 663K
o Disk reads from 1694K to 1279K
o User IO wait time from 198K to 78K