I am trying to find concurrent request that executed a SQL ID to investigate a potential tuning issue.
How to do this? Below is the information from ASH report,
Top user events:
Event Event Class %Event Avg Active Sessions
db file sequential read User I/O 39.22 2.14
CPU + Wait for CPU CPU 36.32 1.98
Top SQLs with top events:
26ysqsawx3yuc CPU + Wait for CPU
6dgnpwwquhhxz db file sequential read
0g9xcs1jrad8a CPU + Wait for CPU
8zkza2pfjb95c db file sequential read
0tw0agw9wcmp7 db file sequential read
Top DB Objects:
Object ID %Activity Event %Event Object Name (Type) Tablespace
119167 8.83 db file sequential read 8.73 GL.GL_JE_LINES (TABLE) APPS_TS_TX_DATA
117043 3.02 db file sequential read 2.08 AP.AP_INVOICES_ALL (TABLE) APPS_TS_TX_DATA
119288 2.84 db file sequential read 2.17 AR.RA_CUSTOMER_TRX_ALL (TABLE) APPS_TS_TX_DATA
119157 2.82 direct path read temp 0.96 GL.GL_JE_HEADERS (TABLE) APPS_TS_TX_DATA
118263 2.65 db file sequential read 2.39 AR.AR_RECEIVABLE_APPLICATIONS_ALL (TABLE) APPS_TS_TX_DATA
So my question is, how to trace back the concurrent request that executed the SQL ID?
The problem with what you are trying to do is that any number of requests could have executed that SQL ID. One way though is to use
v$active_session_history to find any sessions that have executed that piece of SQL. Then go through fnd_logins and onto fnd_concurrent_requests.