系统跑了下ADDM发现以下语句特别影响性能，请各位给个调优路径，本人新手不知道该如何调，谢谢
select schedule.ID AS scheduleId,
schedule.TTB_DATE AS scheduleDate,
staff.STAFF_NAME AS staffName,
staff.STAFF_ID AS staffId,
staff.NODE_ID AS nodeId,
staff.AGENTNUM AS staffAgentNum,
TO_CHAR(schedule.TTB_DATE, 'YYYY-MM-DD') AS isoDate,
schedule.PLAN_START_TIME AS startWorkTime,
schedule.PLAN_END_TIME AS endWorkTime,
(case
when ((schedule.PLAN_END_TIME - checkout.check_time) * 24 * 60 * 60 > :1) AND
((SELECT ATTENDANCE_ID
FROM CMN_DAILY_ATTENDANCE appeal
WHERE appeal.ATTENDANCE_DATE = schedule.TTB_DATE
AND appeal.STAFF_ID = staff.STAFF_ID
AND appeal.PROCESS_STATUS = 1
AND appeal.TYPE_ITEM_ID = :2) IS NULL) then
1
else
0
end) AS isLeaveEarly,
(case
when ((checkout.check_time IS NULL) OR (checkin.check_time IS NULL)) AND
((SELECT ATTENDANCE_ID
FROM CMN_DAILY_ATTENDANCE appeal
WHERE appeal.ATTENDANCE_DATE = schedule.TTB_DATE
AND appeal.STAFF_ID = staff.STAFF_ID
AND appeal.PROCESS_STATUS = 1
AND appeal.TYPE_ITEM_ID = :3) IS NULL) then
1
else
0
end) isAbsenteeism,
(case
when ((checkin.check_time - schedule.PLAN_START_TIME) * 24 * 60 * 60 > :4) AND
((SELECT ATTENDANCE_ID
FROM CMN_DAILY_ATTENDANCE appeal
WHERE appeal.ATTENDANCE_DATE = schedule.TTB_DATE
AND appeal.STAFF_ID = staff.STAFF_ID
AND appeal.PROCESS_STATUS = 1
AND appeal.TYPE_ITEM_ID = :5) IS NULL) then
1
else
0
end) AS isLate,
checkin.check_timeas realStartWorkTime,
checkout.check_time as realEndWorkTime,
schedule.WORK_FLAGas workFlag,
schedule.PLAN_WORK_TIME as planWorkTime
from ttb_work_result schedule
LEFT OUTER JOIN HRM_STAFF staff
ON schedule.STAFF_AGENTNUM = staff.AGENTNUM
left outer join (select id, check_time
from (select staff_agentnum,
id,
ttb_date,
check_time,
rank() over(partition by staff_agentnum, ttb_date order by difference asc) as rnk
from (select schdule.id,
schdule.staff_agentnum,
schdule.ttb_date,
checkin.check_time,
abs(schdule.plan_start_time -
checkin.check_time) as difference
from ttb_work_result schdule
left outer join middle_cmn_check_time checkin
on checkin.check_type = 0
left outer join INF_RAS_USERS agentInfo
on checkin.staff_num = agentInfo.din
and schdule.staff_agentnum =
to_char(agentInfo.pin))
where difference = :7
AND schedule.TTB_DATE <= :8
ORDER BY isoDate desc, staffAgentNum
|
