9i to 10g query performance

Printable View

12-19-2011, 12:08 PM
philthee

9i to 10g query performance

Hi Folks, the following query runs in 25 mins on a 9i database. (9.2.0.1.0)
On 10g it takes 19 hours.

When you run tuning advisor on 10g it says
'ORA-00979 not a GROUP BY expression'
It doesn't fall over though, it keeps running.

The query was created by someone ages ago through Business Objects.
Can anyone see anything glaringly obvious that 10g wouldn't like about the following but 9i is perfectly happy with?

Code:

SELECT WO_BACKLOG_TREND.WOPRIORITY, WO_BACKLOG_TREND.DATADATE, SUM(WO_BACKLOG_TREND.REMHRS), WO_BACKLOG_TREND.LOCATION, NVL(WO_BACKLOG_TREND.CREWID,'BLAH'), NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH'), NVL(WO_BACKLOG_TREND.LEADCRAFT,'BLAH'), NVL(WO_BACKLOG_TREND.SUPERVISOR,'BLAH'), SUM(WO_BACKLOG_TREND.PLANHRS), SUM(WO_BACKLOG_TREND.ACTHRS), MTR_LOCN_ANC.LH2||' : '||MTR_LOCN_ANC.LD2, MTR_LOCN_ANC.LH3, MTR_LOCN_ANC.LH3||' : '||MTR_LOCN_ANC.LD3, MTR_LOCN_ANC.LH4||' : '||MTR_LOCN_ANC.LD4, MTR_LOCN_ANC.LH1, MTR_LOCN_ANC.LH2, MTR_LOCN_ANC.LH4, MTR_LOCN_ANC.LH5, MTR_LOCN_ANC.LH6 FROM V_MTR_WO_BACKLOG_TREND WO_BACKLOG_TREND, V_MTR_LOCN_ANC MTR_LOCN_ANC, LOCATIONS LOCATIONS3 WHERE ( WO_BACKLOG_TREND.LOCATION=MTR_LOCN_ANC.LOCATION and WO_BACKLOG_TREND.SITEID=MTR_LOCN_ANC.SITEID ) AND ( LOCATIONS3.LOCATION=MTR_LOCN_ANC.LOCATION and LOCATIONS3.SITEID=MTR_LOCN_ANC.SITEID ) AND ( (WO_BACKLOG_TREND.WOPRIORITY IS NULL OR WO_BACKLOG_TREND.WOPRIORITY IN (1,2,3,4)) AND (NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH') IS NULL OR NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH') IN ('PM','CM','PMA')) AND MTR_LOCN_ANC.LH1 = ('VENTURE') AND MTR_LOCN_ANC.LH2 LIKE ('KW%') AND LOCATIONS3.LO2 IS NOT NULL ) GROUP BY WO_BACKLOG_TREND.WOPRIORITY, WO_BACKLOG_TREND.DATADATE, WO_BACKLOG_TREND.LOCATION, NVL(WO_BACKLOG_TREND.CREWID,'BLAH'), NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH'), NVL(WO_BACKLOG_TREND.LEADCRAFT,'BLAH'), NVL(WO_BACKLOG_TREND.SUPERVISOR,'BLAH'), MTR_LOCN_ANC.LH2||' : '||MTR_LOCN_ANC.LD2, MTR_LOCN_ANC.LH3, MTR_LOCN_ANC.LH3||' : '||MTR_LOCN_ANC.LD3, MTR_LOCN_ANC.LH4||' : '||MTR_LOCN_ANC.LD4, MTR_LOCN_ANC.LH1, MTR_LOCN_ANC.LH2, MTR_LOCN_ANC.LH4, MTR_LOCN_ANC.LH5, MTR_LOCN_ANC.LH6 /
12-19-2011, 12:18 PM
stecal

What is the execution plan on 9i and on 10g? How do they differ? You have gathered statistics in 10g?
12-19-2011, 01:08 PM
philthee

yeah, statistics have been gathered.
I'll run execution plan on 9i. (i joined company just a couple months ago when they were in the middle of the project)
The 10g plan is full of full table scans on the biggest table.
12-19-2011, 06:22 PM
LKBrwn_DBA

Quote:

Originally Posted by philthee

yeah, statistics have been gathered.
... Etc...
The 10g plan is full of full table scans on the biggest table.

Seems you are missing indexes and/or statistics (on indexes also)....
Check if the indexes on 9i are the same as on 10g in number and columns indexed.
:p
12-20-2011, 04:19 AM
philthee

Quote:

Originally Posted by LKBrwn_DBA

Seems you are missing indexes and/or statistics (on indexes also)....
Check if the indexes on 9i are the same as on 10g in number and columns indexed.
:p

The number of indexes is the same (for this schema) on 9i and 10g.
The indexes all have statistics (as do the tables).

While the above would explain slower performance (even though this is not the case) what would explain the sudden complaint about GROUP BY clause. Note that it is not enough for it to fall over but it is grumbling about it while executing the query. With the Enterprise manager console thick client thingy, if you look at the details of the session it is still processing but if you click on the SQL tab it says a database error has occured
'ORA-00979 not a GROUP BY expression'.

Annoyingly this is one report our users didn't test and of course this is the one that is not working.
12-20-2011, 08:21 AM
balajiyes

Can you please send the execution plan on 9i and on 10g.

Are you sure the number of records in the source tables in 9i and 10 are same?
Same type index?
Same partition strategy?
Create table is same in both 9i and 10?
Try to gather stats again on 10g.
12-20-2011, 08:55 AM
PAVB

You may have bumped into bug #6530141 or some variation of it.

Open an SR with Oracle - there are work arounds but since they involve either patching or the use of hidden init.ora parameters better for you to work with Oracle face-to-face.
01-03-2012, 01:56 PM
philthee

Well, having just come back from holiday i decided to try out something.
In our test system i set optimizer_features_enable=9.2.0 and for the one query that i have been given it is now flying in at 20 minutes rather than 20 hours.
01-04-2012, 08:37 AM
PAVB

Quote:

Originally Posted by philthee

Well, having just come back from holiday i decided to try out something.
In our test system i set optimizer_features_enable=9.2.0 and for the one query that i have been given it is now flying in at 20 minutes rather than 20 hours.

Are you aware you are turning off Ora10g optimizer features?

Why don't hint the offending query to follow the proven execution plan and let the system to the default optimizer setting.