用户反映员工离职处理效率很差!反映强烈。

查看系统中存在的等待情况:

select s.sid,s.LAST_CALL_ET,q.SQL_TEXT,q.HASH_VALUE, w.event
from v$session_wait w, v$session s, v$sql q
where w.SID = s.SID
and q.HASH_VALUE = s.SQL_HASH_VALUE
and w.event not like '%message from client%'
and s.sid >= 6
order by last_call_et desc;

根据hash_value查看具体sql:
SELECT d.sql_text
FROM v$sqltext d
WHERE d.HASH_VALUE = '669053422'
order by d.address, d.piece;

主要是如下得两个更新wa_data和bm_data得sql得语句中关于wa_data和bm_data得限制条件没有提到括弧外导致得。
原有sql:
UPDATE WA_DATA
SET TS = '2007-09-18 16:24:32',
(PSNCLID, DEPTID) = (SELECT DIMISSION.PSNCLAFTER,
DIMISSION.PKDEPTAFTER
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB =
'1548V41000000000111F'
AND DIMISSION.DR = 0
AND WA_DATA.PSNID = '1548V4100000000004OD'
AND WA_DATA.IPARTFLAG = 0
AND WA_DATA.DR = 0
AND EXISTS
(SELECT 1
FROM V_RPT_WA_PERIOD
WHERE CLASSID = WA_DATA.CLASSID
AND CYEAR = WA_DATA.CYEAR
AND CPERIOD = WA_DATA.CPERIOD
AND IACCOUNTMARK = 0));
WHERE EXISTS (SELECT 1
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB = '1548V41000000000111F'
AND DIMISSION.DR = 0
AND WA_DATA.PSNID = '1548V4100000000004OD'
AND WA_DATA.IPARTFLAG = 0
AND WA_DATA.DR = 0
AND EXISTS (SELECT 1
FROM V_RPT_WA_PERIOD
WHERE CLASSID = WA_DATA.CLASSID
AND CYEAR = WA_DATA.CYEAR
AND CPERIOD = WA_DATA.CPERIOD
AND IACCOUNTMARK = 0));

UPDATE BM_DATA
SET TS = '2007-09-18 16:24:32',
(PSNCLID, DEPTID) = (SELECT DIMISSION.PSNCLAFTER,
DIMISSION.PKDEPTAFTER
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB =
'1548V41000000000111F'
AND DIMISSION.DR = 0
AND BM_DATA.PSNID = '1548V4100000000004OD'
AND BM_DATA.IPARTFLAG = 0
AND BM_DATA.DR = 0
AND EXISTS
(SELECT 1
FROM BM_PERIOD
WHERE BMCLASSID = BM_DATA.BMCLASSID
AND CYEAR = BM_DATA.CFUNDYEAR
AND CPERIOD = BM_DATA.CFUNDMONTH
AND IACCOUNTMARK = 0))
WHERE EXISTS (SELECT 1
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB = '1548V41000000000111F'
AND DIMISSION.DR = 0
AND BM_DATA.PSNID = '1548V4100000000004OD'
AND BM_DATA.IPARTFLAG = 0
AND BM_DATA.DR = 0
AND EXISTS (SELECT 1
FROM BM_PERIOD
WHERE BMCLASSID = BM_DATA.BMCLASSID
AND CYEAR = BM_DATA.CFUNDYEAR
AND CPERIOD = BM_DATA.CFUNDMONTH
AND IACCOUNTMARK = 0));
需要修改为:
UPDATE WA_DATA
SET TS = '2007-09-18 16:24:32',
(PSNCLID, DEPTID) = (SELECT DIMISSION.PSNCLAFTER,
DIMISSION.PKDEPTAFTER
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB =
'1548V41000000000111F'
AND DIMISSION.DR = 0
AND WA_DATA.PSNID = '1548V4100000000004OD'
AND WA_DATA.IPARTFLAG = 0
AND WA_DATA.DR = 0
AND EXISTS
(SELECT 1
FROM V_RPT_WA_PERIOD
WHERE CLASSID = WA_DATA.CLASSID
AND CYEAR = WA_DATA.CYEAR
AND CPERIOD = WA_DATA.CPERIOD
AND IACCOUNTMARK = 0));
WHERE EXISTS (SELECT 1
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB = '1548V41000000000111F'
AND DIMISSION.DR = 0
AND EXISTS (SELECT 1
FROM V_RPT_WA_PERIOD
WHERE CLASSID = WA_DATA.CLASSID
AND CYEAR = WA_DATA.CYEAR
AND CPERIOD = WA_DATA.CPERIOD
AND IACCOUNTMARK = 0))
AND WA_DATA.PSNID = '1548V4100000000004OD'
AND WA_DATA.IPARTFLAG = 0
AND WA_DATA.DR = 0;

UPDATE BM_DATA
SET TS = '2007-09-18 16:24:32',
(PSNCLID, DEPTID) = (SELECT DIMISSION.PSNCLAFTER,
DIMISSION.PKDEPTAFTER
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB =
'1548V41000000000111F'
AND DIMISSION.DR = 0
AND BM_DATA.PSNID = '1548V4100000000004OD'
AND BM_DATA.IPARTFLAG = 0
AND BM_DATA.DR = 0
AND EXISTS
(SELECT 1
FROM BM_PERIOD
WHERE BMCLASSID = BM_DATA.BMCLASSID
AND CYEAR = BM_DATA.CFUNDYEAR
AND CPERIOD = BM_DATA.CFUNDMONTH
AND IACCOUNTMARK = 0))
WHERE EXISTS (SELECT 1
FROM HI_PSNDOC_DIMISSION DIMISSION
WHERE DIMISSION.PK_PSNDOC_SUB = '1548V41000000000111F'
AND DIMISSION.DR = 0
AND EXISTS (SELECT 1
FROM BM_PERIOD
WHERE BMCLASSID = BM_DATA.BMCLASSID
AND CYEAR = BM_DATA.CFUNDYEAR
AND CPERIOD = BM_DATA.CFUNDMONTH
AND IACCOUNTMARK = 0))
AND BM_DATA.PSNID = '1548V4100000000004OD'
AND BM_DATA.IPARTFLAG = 0
AND BM_DATA.DR = 0;