Hi,
Oracle version :- 12.1.0.2.0 two node RAC
OS - AIX 7
We have a batch which is running for 10 hours and is showing high 'DB file sequential read' in AWR.
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
db file sequential read 729,130 1552.1 2.13 41.7 User I/O
DB CPU 910.9 24.5 24.5
Found few huge tables(greater than 2GB size) causing this event from ASH report.The queries which caused DB file sequential read on huge tables were using index range scan of primary key as per plan,hence hash partitioned the tables with 16 partitions and global hash partitioned its PK.
We are using AMM and is given 96GB. Total.Total DB size is around 400GB. Advisory section of last batch's AWR doesn't show memory congestion. ADDM says 'Wait class "User I/O" was consuming significant database time.The throughput of the I/O subsystem was not significantly lower than expected.'
Even after partitioning the objects causing event and increasing memory_target and memory_max_target as per advisory ,still DB file sequential read is on top and is 42% .Kindly throw some light to reduce this event. If any more information is required ,kindly let me know