| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> High CPU Query
Hi,
Following query is talking high CPU, using Oracle9i on windows: SELECT t1.cessation_date, t1.commence_date, t1.confirm_date, t1.alias,
t1.birth_date, t1.emp_card_one, t1.employee_id, t1.employee_name,
t1.employment_type_code, t1.family_name, t1.gender, t1.given_name,
t1.language_spoke, t1.language_written, t1.mail_id,
t1.marital_stat_eff_date, t1.marital_stat_code,
t1.nationality_code,
t1.native_name, t1.original_commence_date,
t1.permit_expiry_date,
t1.permit_issue_date, t1.permit_type, t1.national_id,
t1.race_code,
t1.religion_code, t1.religion_born, t1.religion_convert_date,
t1.security_id, t1.service_reference_date, t1.status_code,
t1.title_code
('JL1-S0',
'JL1-SF',
'JL1-SG',
'JL1-SH',
'JL1-SI',
'JL1-SJ',
'JL1-SK',
'JL1-SL',
'JL1-SM',
'JL1-SN',
'JL1-SO',
'JL1-SP',
'JL1-SQ',
'JL1-SR',
'JL5-S0',
'JL8-S9',
'JL9-SC',
'JLA-S0',
'JL8EHR26',
'JL8EHR29'
)
)
AND (t0.leave_scheme_code = 'LSA')
)
AND (t0.employee_id = t1.employee_id)
)
AND t1.employee_id <> 'SG02514373'
AND t1.employee_id IN (
SELECT t1.employee_id
FROM sec_gpprofile t2,
sa_userprofile t1,
sec_profilegroupmap t0
WHERE (( (t2.object_id = 'Time.Attendance Adj')
AND ( (t0.GROUP_ID = t2.GROUP_ID)
AND (t1.user_id = t0.profile_id)
)
)
))
)
AND (t0.employee_id IN (
SELECT e.employee_id
FROM hrm_curr_career_v c, hrm_employee e
WHERE c.employee_id = e.employee_id
AND e.employee_id LIKE 'SG%'
AND (e.cessation_date IS NULL
OR e.cessation_date >= SYSDATE
)
AND ( c.employee_id = 'SG02514373'
OR c.supervisor_3 = ('SG02514373')
OR c.employee_id IN (
SELECT cc1.employee_id
FROM hrm_cost_centre cc1,
hrm_curr_career_v c
WHERE c.employee_id = ('SG02514373')
AND c.remarks LIKE
'%/' || cc1.cost_centre_code
|| '%'
AND cc1.employee_id LIKE 'SG%'
AND cc1.eff_date =
(SELECT MAX (cc2.eff_date)
FROM hrm_cost_centre cc2
WHERE cc2.employee_id =
cc1.employee_id
AND cc2.eff_date <=
SYSDATE))
)
UNION
(SELECT e.employee_id
FROM hrm_curr_career_v c,
hrm_employee e,
ihr_covering_officer r2
WHERE c.employee_id = e.employee_id
AND e.employee_id LIKE 'SG%'
AND ( e.cessation_date IS NULL
OR e.cessation_date >= SYSDATE
)
AND r2.covering_officer_id = ('SG02514373')
AND r2.start_date <= SYSDATE
AND r2.end_date >= SYSDATE
AND ( c.supervisor_3 = r2.officer_id
OR c.employee_id IN (
SELECT cc1.employee_id
FROM hrm_cost_centre cc1,
hrm_curr_career_v c2
WHERE c2.employee_id = r2.officer_id
AND c2.remarks LIKE
'%/' || cc1.cost_centre_code
|| '%'
AND cc1.employee_id LIKE 'SG%'
AND cc1.eff_date =
(SELECT MAX (cc2.eff_date)
FROM hrm_cost_centre cc2
WHERE cc2.employee_id =
cc1.employee_id
AND cc2.eff_date <=
SYSDATE))
)))
)
)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 8 0
0
Fetch 7 386.25 685.22 1 41983586 0
69
total 9 386.25 685.22 1 41983594 0
69
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
69 FILTER
69 NESTED LOOPS
69 NESTED LOOPS
69 NESTED LOOPS
354 VIEW OF 'VW_NSO_1'
354 SORT (UNIQUE)
354 UNION-ALL
354 FILTER
1675 SORT (GROUP BY)
8420 FILTER
53511 HASH JOIN
53511 NESTED LOOPS
40928544 NESTED LOOPS
13428 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_CAREER_PK' (UNIQUE)
40928544 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID) OF 'HRM_CAREER'
3048 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'HRM_CAREER_PK' (UNIQUE)
53511 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'HRM_JOB_PK' (UNIQUE)
53511 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_EMPLOYEE_IDX1' (NON-UNIQUE)
53511 FILTER
354 SORT (GROUP BY)
1290 FILTER
4909 NESTED LOOPS
4909 MERGE JOIN (CARTESIAN)
1292 NESTED LOOPS
3022 TABLE ACCESS GOAL: ANALYZED
(BY INDEX ROWID) OF 'HRM_CAREER'
3022 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HRM_CAREER_PK' (UNIQUE)
3022 SORT (AGGREGATE)
1 FIRST ROW
1 INDEX GOAL: ANALYZED
(RANGE SCAN (MIN/MAX)) OF
'HRM_CAREER_PK'
(UNIQUE)
1 INDEX GOAL: ANALYZED (UNIQUE
SCAN) OF 'HRM_JOB_PK' (UNIQUE)
3022 BUFFER (SORT)
1292 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HRM_COST_CENTRE_PK' (UNIQUE)
4909 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'HRM_COST_CENTRE_PK' (UNIQUE)
1326 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
3022 NESTED LOOPS
3584 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'HRM_CAREER'
3584 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_CAREER_PK' (UNIQUE)
3584 SORT (AGGREGATE)
3609 FIRST ROW
3584 INDEX GOAL: ANALYZED (RANGE SCAN
(MIN/MAX)) OF 'HRM_CAREER_PK' (UNIQUE)
3584 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'HRM_JOB_PK' (UNIQUE)
3584 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'HRM_EMPLOYEE'
3022 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'HRM_EMPLOYEE_PK' (UNIQUE)
3584 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'IHR_COVERING_OFFICER'
0 FILTER
0 SORT (GROUP BY)
0 FILTER
0 NESTED LOOPS
0 MERGE JOIN (CARTESIAN)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID) OF 'HRM_CAREER'
0 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'HRM_CAREER_PK' (UNIQUE)
0 SORT (AGGREGATE)
0 FIRST ROW
0 INDEX GOAL: ANALYZED
(RANGE SCAN (MIN/MAX)) OF 'HRM_CAREER_PK'
(UNIQUE)
0 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'HRM_JOB_PK' (UNIQUE)
0 BUFFER (SORT)
0 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'HRM_COST_CENTRE_PK' (UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_COST_CENTRE_PK' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'HRM_CAREER'
69 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'HRM_CAREER_PK'
(UNIQUE)
354 SORT (AGGREGATE)
354 FIRST ROW
354 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'HRM_CAREER_PK' (UNIQUE)
354 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HRM_JOB_PK'
(UNIQUE)
69 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'HRM_EMPLOYEE'
69 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HRM_EMPLOYEE_PK'
(UNIQUE)
69 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SEC_GPPROFILE'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_SEC_PROFILEGROUPMAP26' (UNIQUE)
1 INDEX (UNIQUE SCAN) OF 'UK2_SA_TESTPROFILE' (UNIQUE)
Any idea to improve it? how to remove CARTESIAN which fetching 40M records? Received on Thu Mar 30 2006 - 23:23:52 CST
 |
 |