Re: High CPU Query

"Kumar" <vinodky2000_at_yahoo.com> wrote in message news:1143782631.960225.159240_at_e56g2000cwe.googlegroups.com...
> Hi,
>
> Following query is talking high CPU, using Oracle9i on windows:

>
> 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?
>

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