High CPU Query
Kumar
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
FROM hrm_employee t1, hrm_curr_career_v t0
WHERE ( ( ( ( (t0.job_level_code IN
('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?
frank.va...@gmail.com
You might want to rewrite the employee IN statements to where exists
Jonathan Lewis
news:1143782631.9...@e56g2000cwe.googlegroups.com...
> Hi,
>
> Following query is talking high CPU, using Oracle9i on windows:
>
It's almost certain that this execution pan is not true
(you've printed the Execution plan, not the Rowsource
Operation).
The giveaway lines are:
> 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)
You cannot get rowids from an index and then
get 40,000,000 rows from a table with them.
You need to find the real execution plan before
you can pin down exactly what the problem is
(although that 40,000,000 is probably identifying
the problem - when you can attach the right table
or operation to it).
Since you are on 9i, pick up the hash value and
address from the trace file (hv= and ad= values
from the PARSING IN CURSOR #n line),
then query v$sql_plan.
--
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
DA Morgan
> Hi,
>
> Following query is talking high CPU, using Oracle9i on windows:
Try selecting the relevant records from hrm_curr_career_v and then
join them to hrm_employee.
But I have a feeling hrm_curr_career_v is a view ... you might want
to look at querying the underlying table(s) directly.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)
oracle_doc
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'
(1) Looks like this is the source of the problem. I have seen large
number of values supplied to IN-list cause performance issues when
complex views are also present in the query.
(2) Since the table HRM_CAREER is not listed in the SQL - I assume the
table is part of the view hrm_curr_career_v.
(3) Since I see " VIEW OF 'VW_NSO_1' " in the explain plan I think your
view hrm_curr_career_v is non-mergeable, i.e., has one or more of
DISTINCT, ORDER BY, GROUP BY, UNION etc. operators that make a view
"complex". I see UNION ALL, so I presume that's what you have in the
view.
(4) This in
t0.job_level_code IN
('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' )
goes against the view, and ideally should filter data on in indexed
"job_level_code" field. Check your view to see this column is in all
UNION ALL parts. Is JOB_LEVEL_CODE an indexed column from table
HRM_CAREER? If yes, then HRM_CAREER has > 40 million rows and probably
just a handful of JOB_LEVEL_CODEs.
Please attach the view for further analysis.
steved...@gmail.com
beasts is to break it up into manageable pieces. When you do that, you
usually find the problem child very quickly. Split the unions into two
statements and run each separately, break out the IN's, etc., to see
what falls out.
Regards,
Steve
Jonathan Lewis
Daniel,
Can you contact me offline, please.
Kumar
Execution plan is correct. I suspect there is CARTESIAN. Anyway,
although I do not like too many indexes but here after creating a index
on employee_id+supervisor_3 query took less than 2 sec. there are no
40m rows now.
Regards,
Kumar
true, source of the problem is 40m row. and after creating index on
hrm_career(employee_id+supervisor_3) query takes less than 2 sec.
Thanks.
Jonathan Lewis
"Kumar" <vinod...@yahoo.com> wrote in message
news:1144123308.1...@g10g2000cwb.googlegroups.com...
> Hi,
>
> Execution plan is correct. I suspect there is CARTESIAN. Anyway,
> although I do not like too many indexes but here after creating a index
> on employee_id+supervisor_3 query took less than 2 sec. there are no
> 40m rows now.
>
> Regards,
>
How are you actually checking that the
execution plan you see in the tkprof output
is the execution plan that actually ran ?
I maintain that the following cannot happen
40928544 NESTED LOOPS
13428 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_CAREER_PK' (UNIQUE)
40928544 TABLE ACCESS GOAL: ANALYZED (BYINDEX ROWID) OF
'HRM_CAREER'
3048 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_CAREER_PK' (UNIQUE)
Specifically, you cannot get 3,048 rowids from
an index, and then get 4,000,000 million rows
from the table using those rowids.
Here's an example of a forced Cartesian join (though
not labelled as Cartesian)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9000000 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T2'
9003001 NESTED LOOPS
3000 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T1'
9000000 INDEX GOAL: ANALYZED (FULL SCAN) OF 'T_I2' (NON-UNIQUE)
Note - the visits to the table cannot (for a b-tree index)
exceed the visits to the index.