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.