First you will try to check how far the query has gone (if in v$session_longops for example, or READING some session statistics) and if there is contention with another session (such as a lock for example) because it talks about a 'concurrent request'.

Method 1: Trace the session USING dbms_system.set_sql_trace_in_session (or) if u r using 10g, using dbms_monitor and look at the trace file to identify where the ISSUE is. You can get the explain plan and tune the query.

Method 2:

1. Is this a new query (or) an existing query
2. Look for the wait event associated to this query.
3. Based on the sql hash value, look at the sql_text from v$sql
4. Run an explain plan (or) use oradebug and trace the query if what BIND variables are using and how the data distribution is.
5. Based on the above information, look for when statistics was collected

Once u understand where the problem is, then solutions can be defined ranging from creating sql profiles / stored outlines (or) adding hints to the query and thoroughly test your changes.

Always REMEMBER to make one change and test for performance change. If you make a series of changes, it will be very difficult to isolate which has caused the performance improvement.

First you will try to check how far the query has gone (if in v$session_longops for example, or reading some session statistics) and if there is contention with another session (such as a lock for example) because it talks about a 'concurrent request'.

Method 1: Trace the session using dbms_system.set_sql_trace_in_session (or) if u r using 10g, using dbms_monitor and look at the trace file to identify where the issue is. You can get the explain plan and tune the query.

Method 2:

Once u understand where the problem is, then solutions can be defined ranging from creating sql profiles / stored outlines (or) adding hints to the query and thoroughly test your changes.

Always remember to make one change and test for performance change. If you make a series of changes, it will be very difficult to isolate which has caused the performance improvement.