You wont believe it how useful ASH (Active Session History) can be.
Step 1: Find the sql_id of the long_running query (the following lists the top 100 most -busy queries. I.e., queries that are in the most ASH samples either on cpu or actively waiting on something) :
[Update]: In this newer post we describe how one can use ASH and SQL Monitoring to tune a complex query that does not even finish!
Step 1: Find the sql_id of the long_running query (the following lists the top 100 most -busy queries. I.e., queries that are in the most ASH samples either on cpu or actively waiting on something) :
[Update]: In this newer post we describe how one can use ASH and SQL Monitoring to tune a complex query that does not even finish!
