| Change in explain plan due to Oracle upgrade [message #486150] |
Tue, 14 December 2010 00:12  |
 |
chowdhhx
Messages: 2
Registered: December 2010
Location: Sydney, Australia
|
Junior Member |
|
|
Hi everyone. First post here, which I'm quite excited about. Also, I need to warn readers that I am not a DBA but am heavily involved in application development. Whatever I know about database tuning is whatever I've managed to pick up via self-learning, and I must admit that the sum total of my knowledge isn't a lot.
Anyway, our "DBAs" recently did an upgrade to our 10g database, going from version 10.2.0.2.0 to 10.2.0.4.0. Immediately after the upgrade, a particular query has started to under-perform. The query itself was not altered in any way during the upgrade.
We have two explain plans for the query, a before and an after plan. The two plans are similar but not identical. The plans are too massive to post here, so I hope the following synopsis of the differences will do.
The 10.2.0.2.0 plan:
- shows a HASH GROUP BY
- has a TempSpc column in the explain plan
- shows a particular table (EMP_HISTORY) as having ~1700 rows
The 10.2.0.4.0 plan:
- shows SORT GROUP BY instead of HASH GROUP BY
- does not show a TempSpc column in the explain plan
- shows the EMP_HISTORY table as having only 25 rows
Other than these points, no other discernible differences can be noted. I'm wondering what would cause HASH to change to SORT. I'm told that stats are up-to-date.
What sort of things should we be checking?
Thanks in advance.
|
|
|
|
|
|
|
|
| Re: Change in explain plan due to Oracle upgrade [message #486237 is a reply to message #486150] |
Tue, 14 December 2010 06:59  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The optimizer's behaviour does change between patchsets, usually for the better but it does sometimes get it wrong - even if the statistics are correct.
What licence do you have? If you have Enterprise Edition, plus the Tuning and Diagnostics packs, then you can use the SQL Tuning Advisor to look at the statement. This will check the statistics and perhaps build a profile that will improve the execution plan. The Enterprise Manager interface is quite easy to use.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
