Thnx for reply
New Hardware is 3 times faster than old
Current os : IBM AIX 7.1
And yes same data volume .
We used EXPDP/IMPDP for upgrade
We analyzed AWR reports but it's showing that database is totally idle and smooth(also the same is replied by ORACLE )
We have 12 core and As we have observed that only 1 core is being utilized.
There should be some parameters setting regarding PT that will force to use more than 1 cpu
Edited by: Santosh Pradhan on Jun 6, 2013 6:06 PM
With everything else being equal, most queries should run faster on 11gR2 than on 10gR2. There will be some queries that may run slower - these will need to be tuned (or rewritten) individually. It is unusual to state that performance in general is worse than before - this likely indicates a missed step in the upgrade process. Pl see if these can help
Things to Consider Before Upgrading to 126.96.36.199 to Avoid Poor Performance or Wrong Results [ID 1320966.1]
TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance [ID 160089.1]
Using SQL Performance Analyzer to Test SQL Performance Impact of an Upgrade [ID 562899.1]
Best Practices: Proactively Avoiding Database and Query Performance Issues [ID 1482811.1]
Best Practices: Proactive Data Collection for Performance Issues [ID 1477599.1]
Upgrade to 10g had more changes/features than upgrade to 11g.
1) How much is the buffer cache and SGA size on 11g instance? how much was it on 10g? 11g parameter memory_max_target ?
2) Before running queries (i.e. reports) on new 11g instance and after you loaded data, have you done the stats (at least on important/big tables)?
3) When you say, report is running slow, that means effectively, some SQL is running slow. Did you check the plan of the slow running SQLs? Investigating the slow running SQLs might/will highlight a generic issue with the whole database setup .
4) In AWR report, which are the top 5 wait events ?
First things first - are the execution plans for reports the same comparing 10g and 11g? You may consider use plan stability features in 11g.
The cpu count is determined by oracle on startup (unless it set manually). Check with your UNIX team if they set any CPU usage restrictions on the LPAR.
11gR2 has a new parameter: db_fast
By default, this is set to 'disabled, because we hate you'. In 11gR2, there are two other values which can be used: 'sort of enabled (10g edition)' and 'enabled, you're welcome'. There's also the undocumented value of 'Oracle ACS' which allows for amazing performance but at a large consulting cost.
Seriously, I would hazard a guess that your stats are all messed up. Did you backup your statistics before the upgrade? Have you tried running your '10g statistics' on the upgraded database (preferably on a copy first)?
What is your 'optimizer_features_enable' parameter set to? What about your 'compatible' parameter?
Did you check out the 'Things to Consider Before an Upgrade' document?
You did volumetric testing before you upgrading, right? What's different between that database and this one?
11gR2 has a new parameter: db_fast
Oh come on Mark, you are not supposed to tell the secrets so easily on a public forum and moreover, the parameter is wrong . The actual one is __make_my_db_fast(notice the double underscores) and it's only set if the DBA sets one more parameter _i_read_documentation=NEVER explicitly using ALTER SYSTEM! .
>We have 12 core and As we have observed that only 1 core is being utilized
That is interesting. As if you have only 1 session using CPU at any time. How many active database sessions do you have ? Are they waiting on I/O and not running on CPU ?
Hemant K Chitale
I'd also want to know how that observation is made. It could be everything is running fast on many processors, but only one is running the slow process (or process that is slowing everyone down). It could be AIX has been told to limit Oracle to 1 core. It could be the log writer bogging. Who knows?