Re: oracle 11g uses more CPU than 10g
Date: Fri, 8 Mar 2013 10:36:17 -0800 (PST)
Message-ID: <1362767777.56570.YahooMailClassic_at_web184801.mail.gq1.yahoo.com>
> The databases was upgraded from 10204 to 11203 version. After upgrade,
> we found that the CPU increased more than 30%.
> But buffer gets don't have much difference.
> These SQLs are very simple PK lookup & Nest Loop SQLs.
What if you run the same SQL with optimizer_features_enable set to 10.2.0.4 in 11g and measure the CPU usage? Since you mentioned Nested Loop, could it be due to the new 11g NLJ mechanism? http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#BABFCIAI
You can test the effect of it by setting _nlj_batching_enabled to 0 and compare. The new NLJ batching is supposed to *save* CPU compared to 10g prefetching, especially on non-unique index access ( http://oracle-randolf.blogspot.com/2011/08/logical-io-evolution-part-3-11g.html). Not sure where the *more* CPU is used in your case. While changing the parameter value back and forth in your session, check more statistics, 'consistent gets - examination', 'buffer is pinned count', etc and of course SQL elapsed time as well as CPU time. If the SQL actually runs faster, a little more CPU usage may be worth it.
Yong Huang
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 08 2013 - 19:36:17 CET