我每隔半個(gè)小時(shí)抽一次數(shù)葛圃，發(fā)現(xiàn)：
運(yùn)行正常時(shí)：應(yīng)該在30s內(nèi)完成桶唐。如果超過兩分鐘就需要處理了缰儿。如果沒有新增數(shù)據(jù)在10s完成元镀。
處理過程：使用apps/apps
- 查詢死鎖對(duì)象
--查詢表死鎖：
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;
- 殺掉active死鎖
查詢死鎖掌测，將Active狀態(tài)的進(jìn)程殺掉，active狀態(tài)的進(jìn)程用alter system kill session;一般執(zhí)行1分鐘后提示产园，執(zhí)行不成功汞斧。此時(shí)再查看死鎖，active狀態(tài)會(huì)變成killed狀態(tài)什燕。
此時(shí)：刪除抽數(shù)鎖定記錄 - 刪除抽數(shù)鎖定記錄
--鎖定記錄表：
select * from xsr_adt_etl_lock t for update; - 重新抽數(shù)
重新打開報(bào)表生成頁面：點(diǎn)EBS抽數(shù)粘勒，正常運(yùn)行。
--查詢執(zhí)行的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
