generate pivot script:

select listagg( ''''||sql_id||'''',',') within group (order by 1 )from (select distinct (sql_id) from  dba_hist_sql_plan where object_name='OPN_HIS' ) ;

use info in awr to build a graph

select  * from (
select s.sql_id,
sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
-- 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 s.sql_id in  ( select p.sql_id from dba_hist_sql_plan p where p.object_name='OPN_HIS') 
and ss.begin_interval_time > sysdate-7
group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id )
pivot ( sum(execs)  for sql_id in (
'04chua8g507qc','0ppqkga09s858','13nb5fgqggy5y','2y61btbkgbt99','3akp3rn03dw0m','4hwvc5b6kg9s9','5btym214ffk95','5fjnq328z8cvm','5haf8w0xm1qrz','5yrbchx0n2ww9','70qkbhpxdyhmd',
'7459pp1x54x2g','7hap4j3ar5fu8','9ym4u1trvzcq8','aabhxfrznx8ty','b4ynhjjwwdjf9','bp0sfgzv774u4','btm5kxhrc34qy','bxfhw0z4cnmxp','by4vbkw71nrax','dpnr7csjy3657','f3n5pm0ck9yuw','f452wsrxch6js' )
  );

result :

https://bushmelev-aa.ru/owncloud/index.php/s/vdqNlAqNqIPLxil materials

12c SPM

col "Parameter" for a50
col "Session Value" for a10
col "Instance Value" for a10
col "Default value" for a5
col SESSMOD for a7
col SYSMOD for a9
col "Description" for a95

select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" ,b.ksppstdf "Default value", decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,a.ksppdesc "Description" from sys.x$ksppi a, sys.x$ksppcv b , sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and upper(ksppinm) like upper( '_%&1%') order by a.ksppinm;

generate logminer list:

select completion_time,'exec DBMS_LOGMNR.ADD_LOGFILE('''||name||''');' from v$archived_log where completion_time > trunc(sysdate) +9/24 and  completion_time < trunc(sysdate) +11/24 and dest_id=1;

build catalog

EXEC DBMS_LOGMNR.START_LOGMNR(   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

find out transaction content:

select global_tran_id,local_tran_id,l.* from (
 (
select global_tran_id,local_tran_id,
from DBA_2PC_PENDING) T
JOIN  V$LOGMNR_CONTENTS L ON (l.xidusn||'.'||l.xidslot||'.'||l.xidsqn)=t.local_tran_id
)

commit\rollback:

select ' commit force '''||local_tran_id||''';' from dba_2pc_pending ;
--select ' rolback force '''||local_tran_id||''';' from dba_2pc_pending ;
select 'exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||local_tran_id||'''); commit;' from dba_2pc_pending ;

if archivelogs are on tape:

select sum(blocks*block_size)/1024/1024/1024 gb_size, THREAD#, min(sequence#),max(sequence#) from gv$archived_log where dest_id=1 and first_time > trunc(sysdate) +9/24
and first_time < trunc(sysdate) +11/24 group by THREAD# ;

run {
restore archivelog thread 1 from sequence 3925 until sequence 3938;
}