Re: oracle 11g uses more CPU than 10g
Date: Mon, 11 Mar 2013 14:32:35 +0800
Message-ID: <CANHUaOroXHRQz_1V6S3S=msvJyS=bKH0jYAFHHqD948gc5sL3g_at_mail.gmail.com>
Thank you Yong.
I checked the TOP SQL's plan, it's same as 10g's SQL plan. It doesn't use the new NLJ mechanism.
ELAPSED_TIME is also increased in 11g. We suspect it has something related to the shared server mode we are using.
On Sat, Mar 9, 2013 at 2:36 AM, Yong Huang <yong321_at_yahoo.com> wrote:
> > 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
>
-- Eagle Fan (www.dbafan.com) -- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 11 2013 - 07:32:35 CET