sql

我每隔半个小时抽一次数,发现:
运行正常时:应该在30s内完成。如果超过两分钟就需要处理了。如果没有新增数据在10s完成。
处理过程:使用apps/apps

  1. 查询死锁对象
    --查询表死锁:
    select b.username,b.sid,b.serial#,logon_time,c.object_name,a.session_id,a.locked_mode,
    'alter system kill session '''||b.sid||','||b.serial#||''';',b.status,b.state,b.*
    from v$locked_object a,v$session b,dba_objects c
    where a.session_id = b.sid
    and c.object_id = a.object_id
    and c.owner in ('XSR')
    order by b.logon_time;

--包,等的死锁:
Select b.logon_time,b.SID,b.SERIAL#,b.status,b.state,
a.owner,a.name,a.type,a.session_id,'alter system kill session ''' || b.sid || ',' || b.serial# || ''';',b.*
From dba_ddl_locks a, v$session b
where a.session_id = b.SID
and a.owner = 'XSR'
and a.name like 'XSR_ADT_%'
--and b.status = 'INACTIVE'
order by b.LOGON_TIME;

  1. 杀掉active死锁
    查询死锁,将Active状态的进程杀掉,active状态的进程用alter system kill session;一般执行1分钟后提示,执行不成功。此时再查看死锁,active状态会变成killed状态。
    此时:删除抽数锁定记录
  2. 删除抽数锁定记录
    --锁定记录表:
    select * from xsr_adt_etl_lock t for update;
  3. 重新抽数
    重新打开报表生成页面:点EBS抽数,正常运行。
    --查询执行的sql
    select sql_text
    from v$sql
    where hash_value in
    (select sql_hash_value
    from v$session
    where sid in (select session_id from v$locked_object));

select sql_text
from v$sql
where hash_value in
(select sql_hash_value
from v$session
where sid in (845));

SELECT T.ID, T.MEMBER
FROM XSR_APP_ACCOUNT_D T
WHERE T.APP_ID = 52156
AND EXISTS (SELECT 1
FROM CUX_GL_CODE_V@xsr_to_erp A
WHERE T.MEMBER = A.PARENT_FLEX_VALUE_LOW
AND A.FLEX_VALUE_SET_NAME = 'CPI_COA_SUBACC'
AND a.id_flex_num = :p_coa_id
AND A.FLEX_VALUE <> 'T'
AND A.FLEX_VALUE <> '0')
ORDER BY T.MEMBER

推荐阅读更多精彩内容