We have migrated from Oracle 10.2.0.4 to Oracle 11r1 for PeopleSoft HR app. "Everything" was tested by the users and performance was acceptable on the development box. However, after the upgrade - we are seeing most of the queries regress in performance. Some are running 10 times longer than they used to.
The queries run fine in 10g (we still have the dev instance on both versions.)
We'v tried gather new stats
Tried importing 10g stats
Applied patch set update: 9654987
We are still seeing very high consumption on CPU and some latch issues.
I find it interesting that in DBFlash shows me that the query is using "SYSTEM.DBF" and not the data files belonging to the actual tables. It is reading Data Dictionary info?
BTW - in case anyone is reading this, this is due to a bug in 11g. There is a recommended patch that ORACLE should of advised to us, but 2 days of 24 hour support and even though everyone understood the problem, they could not find the solution. Finally, an independent consultant - Dale Basemen, (who is awesome) gave us the fix to add hidden parameters or to apply the patch.