Re: oracle 11g uses more CPU than 10g
- From: Yong Huang <yong321@xxxxxxxxx>
- To: eagle.f@xxxxxxxxx
- Date: Fri, 8 Mar 2013 10:36:17 -0800 (PST)
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
