Re: High CPU Query

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,

Jonathan Lewis wrote:
> "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
Received on Mon Apr 03 2006 - 23:01:48 CDT