We are encountering intermittent performance problems in our ECC system and have found that there are occassionally ver high I/O response times. In particular, we identified high 'direct path read' for a session analysed.
A user reported a problem scrolling in transaction MIRO, so we performed a trace in ST12:
It took on average ~30 seconds to scroll one page. The above was the trace for that period.
The DB performed a FTS on RBKP:
Number of rows:
Shouldn’t be too big of a deal, right?
I identified the Oracle session and extracted the ASH data. The poor response times appear to be related to the wait event ‘direct path read’. I plotted the session against the wait event with 'Time Waited' (filtered on object RBKP):
From this (for me), there is clearly an I/O bottleneck. However, why is the optimizer choosing a ‘direct path read’ to retrieve this data? It would also be good to know how reliable the ASH stats are in relation to these events?