过账：单一分类账十分缓慢

select * from DBA_HIST_ACTIVE_SESS_HISTORY where event like '%enq: TX - row lock contention%';
DBA_HIST_ACTIVE_SESS_HISTORY 中的blocking_session字段关联DBA_HIST_ACTIVE_SESS_HISTORY中的session_id找到对应的sql_id从而得到回话信息。

select a.sql_id, count(*)
  from DBA_HIST_ACTIVE_SESS_HISTORY a,
       (select distinct BLOCKING_SESSION bid, BLOCKING_SESSION_SERIAL# bid#
          from DBA_HIST_ACTIVE_SESS_HISTORY
         where event = 'enq: TX - row lock contention'
         and module like '%GLPPOSS%'
           and sample_time >
               to_date('20230128 00:00:00', 'yyyymmdd hh24:mi:ss')
           and sample_time <
               to_date('20230204 00:00:00', 'yyyymmdd hh24:mi:ss')) b
 where a.session_id = b.bid
   and a.SESSION_SERIAL# = b.bid#
 group by a.sql_id order by 2 desc ;

select count(*)*10 exec_time,module,count(distinct sql_exec_id) count
from DBA_HIST_ACTIVE_SESS_HISTORY
where  
sql_id='b381z7azvq5km'
and sample_time>to_date('20230128 00:00:00','yyyymmdd hh24:mi:ss')
and sample_time<to_date('20230204 00:00:00','yyyymmdd hh24:mi:ss')
group by module;

select session_id sid,SESSION_SERIAL# sid#,sql_exec_id,module,min(sample_time),max(sample_time), count(*)*10 exec_time 
from DBA_HIST_ACTIVE_SESS_HISTORY
where  sql_id='b381z7azvq5km' 
and sample_time>to_date('20230128 00:00:00','yyyymmdd hh24:mi:ss')
and sample_time<to_date('20230204 00:00:00','yyyymmdd hh24:mi:ss')
group by session_id,SESSION_SERIAL#,module,sql_exec_id
order by exec_time desc;

dba 16:44:01
select 1
  from GL_CONCURRENCY_CONTROL C
 where ((C.concurrency_class = 'UPDATE_BALANCES' and
       C.concurrency_entity_name = 'LEDGER') and
       C.concurrency_entity_id = to_char(:b0))
   for update of concurrency_class;

看下是不是这条堵了
select 
     nvl(l.user_CONCURRENT_PROGRAM_NAME,'非报表程序') 运行程序,
      round(n.last_call_et / 60, 2) 运行时间_分钟,
       n.event 等待事件,
       n.CLIENT_IDENTIFIER 登录用户,
       round(p.pga_alloc_mem/1024/1204,2) 已经分配内存_M,
       n.terminal 登录终端,
       n.MODULE , 
       n.MACHINE,
       p.SPID,
       n.INST_ID,'alter  system  kill  session  ' || chr(39) ||
                             n.sid || ', ' || n.serial# || ', @' || n.inst_id ||
                             chr(39) || ' ' kill_sql,
       s.REQUEST_ID,
       n.username,
       n.sid,
       n.sql_id,
       a.sql_text,
       n.prev_sql_id,
       p.tracefile
  from gv$session n,gv$process p ,gV$sqlarea a,fnd_concurrent_requests s
  ,fnd_concurrent_programs_vl l
 where n.sql_id = a.sql_id(+)
   and p.ADDR=n.PADDR
   and n.inst_id = a.inst_id
   and n.AUDSID=s.ORACLE_SESSION_ID(+)
   and s.CONCURRENT_PROGRAM_ID=l.CONCURRENT_PROGRAM_ID(+)
   and type = 'USER'
   AND STATUS = 'ACTIVE'
   and round(n.last_call_et / 60, 2) > 5
   and schemaname <> 'SYS'
   and n.status='ACTIVE'
   order by 运行时间_分钟, 已经分配内存_M desc ;

1.	修改系统参数
Alter system set "_fix_control"='1704562:OFF';

2.	使用profile绑定正确的执行计划
Oracle sql Profile
select a.sql_id, a.event, count(*)
  from DBA_HIST_ACTIVE_SESS_HISTORY a,
       (select distinct BLOCKING_SESSION bid, BLOCKING_SESSION_SERIAL# bid#
          from DBA_HIST_ACTIVE_SESS_HISTORY
         where module like '%GLPPOSS%'
           and sample_time >
               to_date('20230120 00:00:00', 'yyyymmdd hh24:mi:ss')
           and sample_time <
               to_date('20230204 00:00:00', 'yyyymmdd hh24:mi:ss')) b
 where a.session_id = b.bid
   and a.SESSION_SERIAL# = b.bid#
 group by a.sql_id, a.event order by 3 desc ;