Re: High Cpu Usage After 9i Migration

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 25 Nov 2004 11:45:36 +1100
Message-ID: <41a52b30$0$25116$afc38c87@news.optusnet.com.au>

Joel Garry wrote:
> ybalasub_at_gmail.com (Yegna Balasubramanian) wrote in message news:<21a9c364.0411231223.72e1e981_at_posting.google.com>...
>

>>All,
>>
>>   I have upgraded our production Oracle databse from 8.1.7.4 to
>>9.2.0.5 recentrly, the CPU usage is 35% normally when we run the DB in
>>8174 but after the migration CPU usage is 70%, sqls are doing lots of
>>buffer gets now than it used to before, we are working on tuning those
>>queries,have anybody experienced this before? inputs are welcome? My
>>DB is running on HP-UX 11.11 with 8CPUs and 16G memory.
>>
>>Regards
>>Yegna

>
>
> Did you change from 32 to 64 bits or blocksize? It is possible that
> you have made your SGA larger and the cpu is simply running around in
> there where before you did I/O.

It sounds a bit silly, and I know several people who would snort "Of course my SGA isn't larger than it used to be"... only then to discover that they, rather like the world's biggest Oracle author, can't size an SGA properly in 9i because they still think in 8i terms.

Specifically,

DB_BLOCK_SIZE=8192
DB_BLOCK_BUFFERS=20000
BUFFER_POOL_KEEP=(buffers=4000...)
BUFFER_POOL_RECYCLE=(buffers=8000...)

...all mean, in 8i, that your buffer cache is 160MB.

Translated directly into 9i:

DB_BLOCK_SIZE=8192
DB_CACHE_SIZE=160M
DB_KEEP_CACHE_SIZE=32M
DB_RECYCLE_CACHE_SIZE=64M

...all mean, in 9i, that your buffer cache is 256MB.

And never mind the non-standard block size caches on top of that.

So, Joel mentions a specific issue which sounds dismissable, but isn't, and could definitely affect your system accordingly.

Oh: and calculate system statistics if you haven't done so already.

Regards
HJR
>
> Are you using multiblock_read_count? That can suggest to the
> optimizer more full table scans, thrashing your buffers.
>
> Which init.ora parameters have you changed and how much? What
> specific changes have you made for the optimizer? What have you set
> for optimizer_index_caching and optimizer_index_cost_adj?
>
> Have you changed how you gather statistics?
>
> Are you using LMT's? (If not, combination of the above conceiveably
> may thrash dictionary tables more visibly than before).
>
> Is there a user noticeable performance issue? Maybe you are just
> using cpu more productively. How exactly did you migrate?
>
> jg
> --
> @home.com is bogus.
> The penguin inside your computer will now explode.
> http://story.news.yahoo.com/news?tmpl=story&u=/041123/480/wx10211231819
Received on Wed Nov 24 2004 - 18:45:36 CST