-
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
/
-
What is the execution plan on 9i and on 10g? How do they differ? You have gathered statistics in 10g?
-
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.
-
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.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
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.
-
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.
Cheers!
OraKid.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|