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;

2. 杀掉active死锁
   查询死锁，将Active状态的进程杀掉，active状态的进程用alter system kill session;一般执行1分钟后提示，执行不成功。此时再查看死锁，active状态会变成killed状态。
   此时：删除抽数锁定记录
3. 删除抽数锁定记录
   --锁定记录表：
   select * from xsr_adt_etl_lock t for update;
4. 重新抽数
   重新打开报表生成页面：点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