Re: High CPU Query

From: oracle_doc <nilendu_at_nilendu.com>
Date: 31 Mar 2006 11:05:21 -0800
Message-ID: <1143831921.117446.18100@z34g2000cwc.googlegroups.com>

   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' (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. Received on Fri Mar 31 2006 - 13:05:21 CST