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 )

find query with performance degradation running more than 5 sec :

select * from (
select sql_id, execs, before_avg_etime, after_avg_etime, norm_stddev,
       case when to_number(before_avg_etime) < to_number(after_avg_etime) then 'Slower' else 'Faster' end result
from (
select sql_id, sum(execs) execs, sum(before_execs) before_execs, sum(after_execs) after_execs,
       sum(before_avg_etime) before_avg_etime, sum(after_avg_etime) after_avg_etime,
       min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev,
       case when sum(before_avg_etime) > sum(after_avg_etime) then 'Slower' else 'Faster' end better_or_worse
from (
select sql_id,
       period_flag,
       execs,
       avg_etime,
       stddev_etime,
       case when period_flag = 'Before' then execs else 0 end before_execs,
       case when period_flag = 'Before' then avg_etime else 0 end before_avg_etime,
       case when period_flag = 'After' then execs else 0 end after_execs,
       case when period_flag = 'After' then avg_etime else 0 end after_avg_etime
from (
select sql_id, period_flag, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, period_flag, sum(execs) execs, sum(etime)/sum(decode(execs,0,1,execs)) avg_etime from (
select s.sql_id, 'Before' period_flag,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta)/1000000 etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
   and st.sql_id=s.sql_id
   and st.sql_text not like '/* SQL Analyze%'
and ss.begin_interval_time <= sysdate-1
union
select s.sql_id, 'After' period_flag,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta)/1000000 etime
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS ,dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and s.executions_delta > 0
and s.elapsed_time_delta > 0
   and st.sql_id=s.sql_id
   and st.sql_text not like '/* SQL Analyze%'
and ss.begin_interval_time > sysdate-1
)
group by sql_id, period_flag
)
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('1'),2)
and max_etime > nvl(to_number('5'),.1)
)
where result = 'Slower'
order by norm_stddev;

original by Kerry Osborne

while trying to commit distibuted transaction faced with “free global transaction table entry”
solution foud at mos 549307.1
define local_tran_id:

var numtransact varchar2(100)

begin
:numtransact:='5.31.1099960';
end;
/

use mos note to force commit transaction:

set transaction use rollback segment SYSTEM;

delete from sys.pending_trans$
where local_tran_id = :numtransact;
delete from sys.pending_sessions$ where local_tran_id = :numtransact;
delete from sys.pending_sub_sessions$ where local_tran_id = :numtransact;

commit;

insert into pending_trans$ (
LOCAL_TRAN_ID,
GLOBAL_TRAN_FMT,
GLOBAL_ORACLE_ID,
STATE,
STATUS,
SESSION_VECTOR,
RECO_VECTOR,
TYPE#,
FAIL_TIME,
RECO_TIME)
values( :numtransact, 
306206,
'XXXXXXX.12345.1.2.3', 
'prepared','P', 
hextoraw( '00000001' ),
hextoraw( '00000000' ), 
0, sysdate, sysdate );

insert into pending_sessions$
values( :numtransact,
1, hextoraw('05004F003A1500000104'),
'C', 0, 30258592, '',
146
);
commit;

begin
DBMS_TRANSACTION.COMMIT_FORCE (:numtransact);
commit;
end;
/

transaction state changed to forced commit ( or commit forced =) ), after that purge transcation entry

exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.31.1099960'); --local tran id

*copy passwd file from primary
scp $ORACLE_HOME/dbs/passwd$ORACLE_SID{instance_number}
cp [pwd file from primary] orapw$ORACLE_SID

*create static listener

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = u41testdb.rt.ru)
     (ORACLE_HOME = /oracle/app/base/db/12.1.0.2)
     (SID_NAME = s41testdb)
    )
   )

create audit directory:

mkdir -p /oracle/app/base/admin/s41testdb

create asm directory:

ASMCMD [+] > mkdir +DATA/u41testdb

*startup dummy instance:

echo db_name='db_name' > pfile.ora
startup nomount pfile='pfile.ora'

*connect auxiliary instance +connect target instance

rman target sys/xxxxx@u41testdb auxiliary sys/xxxxx@s41testdb
RMAN> duplicate target database for standby from active database spfile set "db_unique_name"="s41testdb" set "cluster_database"="false" set "audit_file_dest"="/oracle/app/base/admin/s41testdb";

DGMGRL> create configuration u41dg as primary database is u41testdb connect identifier is u41testdb	;
Configuration "u41dg" created with primary database "u41testdb"
DGMGRL> add database s41testdb as connect identifier is s41testdb maintained as physical;
Database "s41testdb" added
DGMGRL> enable configuration;
Enabled.
DGMGRL>

ORA-15025, KFSG-00312, ORA-15081
Database Creation on 11.2 Grid Infrastructure with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 ) (Doc ID 1084186.1)
su – grid
$ORACLE_HOME/bin/setasmgidwrap o=/oracle/app/base/db/12.1.0.2/bin/oracle