Whats wrong with this query?
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,
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,
0