To proactively monitor long running queries, metric extensions can be created in Oracle Enterprise Manager 12c. I have created metric extension on Exadata platform but it can also be used on non Exadata platform.
Below are steps to create metric extension in Oracle Enterprise Manager 12c.
1. Navigate to Enterprise -> Monitoring -> Metric Extensions
2. Below Metric Extension click on Create
3. General properties tab: Select Target Type as Cluster Database, Specify name and display name, select Adapter as SQL and choose appropriate frequency based on your requirement.
4. Adapter: SQL Query select key,sql_id,username,sql_id,elapsed_time from gV$SQL_MONITOR where status=’EXECUTING’ and username is not null
Columns: Add following as Name, Display Name, Column Name, Value Type, Comparison Operator, Warning & Critical
key – Key – Key Column – Number
sql_id – sql_id – Data Column – String
username – username – Data Column – String
sql_id – sql_id – Data Column – String
elapsed_time – elapsed_time – Data Column – Number – > 900000000 – 3600000000
Elapsed_time is in micro seconds so choose appropriate values according to your environment. You can choose lower values for aggressive monitoring.
5. Credentials: Database Credentials Use Default Monitoring Credentials.
6. Test Targets: Here you can add couple of targets and test whether Metric Extension is working or not.
7. Review: You can review selections you made on previous screens.
8. Finish: Click on Finish to create Metric Extension.
9. Once the Metric Extension is created, click on action tab and convert template to deployable draft. Again choose Metric Extension and choose Deploy to target.
10. Provided you have setup email/pager notification in your Oracle Enterprise Manager 12c, you will get notified on long running queries.
Example: I got notification for one sql_id that my adhoc user was running on Exadata platform. As you can see from below there was space missing after full hint and query was running in serial mode with smart scan feature turned off. I notified user and he fixed query hint and query ran in 4 minutes instead of 90 plus minutes.
SELECT /*+ parallel(r,16),full(r)*/ MIN(from_dos),MAX(from_dos)
FROM CSUSER.REPOS_CLAIM_NEW r
WHERE r.proc_type ='C'
AND r.CHARge > 0.99
AND substr(r.error_flag, 17, 1) IN ('H', 'L', ' ')
AND nvl(trim(mdr_flag),'~') IN('~','D','C')
AND substr(error_flag,9,1) IN (' ','O')
PLAN hash value: 3370285159
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 983K(100)| | | |
| 1 | SORT AGGREGATE | | 1 | 83 | | | | |
| 2 | PARTITION RANGE ALL | | 3713M| 287G| 983K (1)| 03:16:45 | 1 | 119 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| REPOS_CLAIM_NEW | 3713M| 287G| 983K (1)| 03:16:45 | 1 | 119 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 5 | BITMAP AND | | | | | | | |
| 6 | BITMAP OR | | | | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | REPOS_CLM_CHRG_FLG_IDX | | | | | 1 | 119 |
|* 8 | BITMAP INDEX SINGLE VALUE | REPOS_CLM_CHRG_FLG_IDX | | | | | 1 | 119 |
|* 9 | BITMAP INDEX SINGLE VALUE | REPOS_CLM_CHRG_FLG_IDX | | | | | 1 | 119 |
|* 10 | BITMAP INDEX SINGLE VALUE | REPOS_CLAIM_PROC_TYPE_IDX | | | | | 1 | 119 |
---------------------------------------------------------------------------------------------------------------------------------
Vishal desai's Oracle Blog 
For parallel queries elapsed time will be different for QC and slaves. Change Adapter SQL to capture maximum elapsed_time as follows:
select * from
(select sql_id,sql_exec_id,round(max(elapsed_time)/1000000)
from gV$SQL_MONITOR where status=’EXECUTING’
group by sql_exec_id,sql_id);
Comment by vishaldesai — February 3, 2014 @ 3:40 pm