GRID 11g| User-Defined SQL Metric alert if a query TOPs CPU Time

It often happens that DBMS statistics  though do  good for most queries can turn a best performing query into worse performing piece of SQL.
Here is a process I used to raise an Grid alert when a good query turns BAD.

You need to locate SQL_ID first before raising an SQL UDM for it. To identify culprit you will have to sample system number of  times to pick up right candidate SQL_ID.

SELECT SQL_ID
 , Round ( elapsed_time )
 FROM ( SELECT sql_id
 , elapsed_time / 60000000 elapsed_time       -- CPU_TIME/EXECUTIONS,
 , disk_reads
 , executions
 , first_load_time
 , last_load_time
 FROM v$sql
 ORDER BY elapsed_time DESC )
 WHERE ROWNUM < 5;

#    SQL_ID    ROUND(ELAPSED_TIME)
1    6hhbs09sb16j2    1006
2    7x3utw1gc9bqn    219
3    9y7yvrq53ju75    113
4    cr988d50t86za    106

Elapsed_Time : Minutes spent
SQL_ID I need to monitor is “6hhbs09sb16j2″

select 'The culprit SQL with SQL_ID 6hhbs09sb16j2 has topped CPU time'
 from ( SELECT SQL_ID
 , Round ( elapsed_time / executions )
 , executions
 FROM ( SELECT sql_id
 , elapsed_time / 1000 elapsed_time
 , disk_reads
 , executions
 , first_load_time
 , last_load_time
 FROM v$sql
 ORDER BY elapsed_time DESC )
 WHERE ROWNUM < 5 )
 where SQL_ID = '6hhbs09sb16j2';

Navigate to Targets -> Databases -> select “Grid Database”

Scroll down at this page and select “User-Defined Metrics” under “Related Links”.  Add entry for UDM  with above SQL.

Alter schedule and Frequency per your need

Click TEST button on right hand corner to test UDM. You shoudl see response as below.

Once SQL UDM is in place you will see an alert when above SQL_ID tops up CPU time.

If you want to be notified when UDM alert raised, don’t forget to add a Notification Rule.