ITPub博客

首页 > 数字化转型 > ERP > zjdx项目员工离职处理慢得跟踪过程

zjdx项目员工离职处理慢得跟踪过程

原创 ERP 作者:lgtu 时间:2007-09-20 13:02:29 0 删除 编辑
用户反映员工离职处理效率很差!反映强烈。[@more@]

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

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

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6897/viewspace-970394/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    5
  • 访问量
    27847