9i to 10g query performance

[philthee]

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?

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
/

[stecal]

What is the execution plan on 9i and on 10g? How do they differ? You have gathered statistics in 10g?

[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.

[LKBrwn_DBA]

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.

[philthee]

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.

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.

[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.

[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.

[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.

[PAVB]

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.