Thursday, 2 January 2014

direct path reads

To resolve the direct path reads wait event :-

Please check the below views :-

V$SESSION_EVENT ->  to identify sessions with high numbers of waits
V$SESSTAT ->  to identify sessions with high "physical reads direct" (statistic only present in newer Oracle releases)
V$FILESTAT ->  to see where the IO is occurring
V$SQLAREA ->  for statements with SORTS and high DISK_READS (which may or may not be due to direct reads)

Solution approcah :-

If the file indicates a temporary tablespace check for unexpected disk sort operations.
Ensure Parameter DISK_ASYNCH_IO -> is TRUE .
Ensure the OS asynchronous IO is configured correctly.
Check for IO heavy sessions / SQL and see if the amount of IO can be reduced.Better approach will be to look at the sql query that need to be tuned and need to run in parallel .
Please check with ASH reports to see the all the queries which have this particular wait event .
Ensure no disks are IO bound.

And in the last , Please tune the disk_asynch_io and filesystemio_options parameters .

No comments:

Post a Comment