This content has been marked as final. Show 6 replies
The probable cause of error is you didn't set up a test database to test the application under 11g, and 'assumed' nothing would change.
However, usually in every major release the CBO is revised, resulting in different execution paths.
Also you, if you migrated the database by dumps, the clustering factor of many indexes can have changed, resulting in different execution plans.
Senior Oracle DBA
Kapil wrote:First guess would be inefficient execution plans.
As part of maintainace, DB is migrated from 10g to 11g.
On 10g the tempsace is 10G and on 11g it is 20G. But still some of the bulk reports are failing with unable to extend temp segment on 11g. But running on same data volume on 10g the report executes fine. There is no change in data or objects as both dbs are connected with golden gate.
The data base parameter on 10g and 11g has kept same by DBA. Please advise what could be the probable cause of errors ?
Pick a specific SQL you're having a problem with and compare what it did on the pre-upgrade instance (hopefully you have one) with what it's doing now.
Thanks to both of you...
Yes, as you correctly directed.. the query plan on 11g and 10g is different.
Please advise how this can be fixed ? Is there anything that can be done at configuration level which DBA can fix. Or the complex queries has to be reopend and fixed on 11g env by dev ?
Your adivse is valuable as this will help to estimate cost.
It is impossible to tell without specifics. In general, most queries will run faster on later versions of the database. Pl see if these MOS Docs can help
Query Performance Degradation - Upgrade Related - Recommended Actions [ID 745216.1]
Things to Consider Before Upgrading to 18.104.22.168 to Avoid Poor Performance or Wrong Results [ID 1320966.1]
Using SQL Performance Analyzer to Test SQL Performance Impact of an Upgrade [ID 562899.1]
Testing SQL performance impact of upgrade from 10g to 11g using SQL Performance Analyzer [ID 1363104.1]
How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g [ID 465787.1]
Tips for avoiding upgrade related query problems [ID 167086.1]
Recording Access Path Information Prior to an upgrade to 10g or 11g [ID 466350.1]
Have you applied the latest PSU patch for 22.214.171.124 ? How was the database upgraded ?
I would run STATS on all the tables and indexes with "AUTO_SAMPLE_SIZE" option.
Also have a look
Please close the thread if you feel you have the answer and keep the Oracle forum clean.