Hello, how to add custom metric to EM with “bad” sql and how-to diagnose results:
usefull links first:
Creating an Information Publisher Report based on a Custom Metric Extension

whats_changed.sql

based on this material add custom metric with query from
query with performance degradation running more than 5 sec

after all
Enjoy the message like this one:

Host=xxxxxxxxx.local
Target type=Database Instance
Target name=SOME_DATABASE
Message=The value of NORM_STDDEV is 16.668
Severity=Critical
Event reported time=Mar 29, 2016 3:00:55 AM MSK
Operating System=Linux
Platform=x86_64
Associated Incident Id=19287
Associated Incident Status=New
Associated Incident Owner=
Associated Incident Acknowledged By Owner=No
Associated Incident Priority=None
Associated Incident Escalation Level=0
Event Type=Metric Alert
Event name=ME$SQL_PERFOMANCE_CHANGE:NORM_STDDEV
Metric Group=ME$SQL_PERFOMANCE_CHANGE
Metric=NORM_STDDEV
Metric value=16.66792070888098375763394835218401326307
Key Value=
Rule Name=all alarms by type:db and cluster,abushmelev
Rule Owner=ABUSHMELEV
Update Details:
The value of NORM_STDDEV is 16.668
Incident created by rule (Name = Incident management rule set for all targets, Create incident for critical metric alerts [System generated rule]).

the metric is clickable and have historic information:

based on this email with modification of Kerry Osborne’s script we can analyse what changed with sql_id=’0crfdgc01xcha’:

09:21:24 (1)[p00esia]system@p00esia> @plan_hist2 0crfdgc01xcha
old   8: where sql_id = '&1'
new   8: where sql_id = '0crfdgc01xcha'

   SNAP_ID |   NODE | BEGIN_INTERVAL_TIME             | SQL_ID        | PLAN_HASH_VALUE |      EXECS |    AVG_ETIME |        AVG_LIO |    AVG_PIO
---------- | ------ | ------------------------------- | ------------- | --------------- | ---------- | ------------ | -------------- | ----------
     48097 |      1 | 16-MAR-16 11.40.46.963 PM       | 0crfdgc01xcha |      3594529397 |          1 |         .817 |          228.0 |          0
     48960 |      1 | 29-MAR-16 12.51.12.708 AM       | 0crfdgc01xcha |      3695145680 |          1 |        1.441 |          471.0 |         52
     48960 |      2 | 29-MAR-16 12.51.12.745 AM       | 0crfdgc01xcha                   |          1 |        1.299 |          233.0 |         27
     49010 |      1 | 29-MAR-16 05.40.02.717 PM       | 0crfdgc01xcha                   |          1 |        6.077 |      138,653.0 |       2073
     49053 |      1 | 30-MAR-16 08.00.11.253 AM       | 0crfdgc01xcha                   |          1 |        4.628 |       79,942.0 |       1199
     49054 |      2 | 30-MAR-16 08.20.13.680 AM       | 0crfdgc01xcha                   |          1 |        5.473 |      103,279.0 |       2643

so, it’s clear that plan has changed and it run slower
quick way to fix this – add baseline ( how-to )