SQL Language (MOSC)

MOSC Banner

Whats wrong with this query?

edited Feb 17, 2012 2:07AM in SQL Language (MOSC) 4 commentsAnswered
Hi,
we have this query that takes 1.5h in PROD and 2 minutes in TEST. I have gathers new statistics on all objects in PROD. It did not help.

They get different plans in PROD & TEST.

version: 11.2.0.1 (PROD) 11.2.0.2 (TEST) we are hitting a bug in TEST so we have not upgraded PROD yet.

Query:

SELECT x.audit_year,
         SUM(DECODE(x.min_score, 2, 1, 0)) score_2,
         SUM(DECODE(x.min_score, 1, 1, 0)) score_1,
         SUM(DECODE(x.min_score, 0, 1, 0)) score_0
    FROM (  SELECT larpy.virtual_audit_year audit_year, arh.production_unit_id, MIN(sco.score_num) min_score
              FROM auditapp.audit_score sco,
                   auditapp.audit_report_detail ard,

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center