SEO

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

"Yegna Balasubramanian" <ybalasub@gmail.com> wrote in message
news:21a9c364.0411231223.72e1e981@posting.google.c om...
> 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
Yegna,

I think for anyone to offer any assistance you'll need to supply execution
plans for the 8.1.7 and 9i databases. That way you can tell what has
changed.

"Yegna Balasubramanian" <ybalasub@gmail.com> wrote in message
news:21a9c364.0411231223.72e1e981@posting.google.c om...
> 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 compare all the initialization parameters (pfile/spfile) between the
old and the new db ? Are the statistics of the new db gathered the same way
?

Matthias

Yegna Balasubramanian wrote:

> 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

It seems to me that in version 9i the gathering of statistics are more
important (dbms_stats). If there are missing statistics try also using
otimizer_dynamic_sampling = 2
We have good experiences with using optimizer_index_cost_adj and
optimizer_index_caching.
There are also hints about using the "NO_UNNEST" hint or
"_unnest_sub_query=false", but I've never used this hint/parameter.
Indeed on some instances I've seen the same behavior after migration to 9i.

Eberhard

Hi!

Our experience is opposite.
We can actually remove half of the CPUs.
It is really working great!

"Yegna Balasubramanian" <ybalasub@gmail.com> wrote in message
news:21a9c364.0411231223.72e1e981@posting.google.c om...
> 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

What are your settings for the optimizer_max_permutations and
optimizer_mode parameters? Are they very different now from before the
migration? If so, you might consider setting them back to the 8i
values. If you're having problems with some views, experiment with
setting _complex_view_merging=false. BTW, these parameters are session
modifiable. You can test them without having to change them at the
system level.

ybalasub@gmail.com (Yegna Balasubramanian) wrote in message news:<21a9c364.0411231223.72e1e981@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.

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?tmp.../wx10211231819

Joel Garry wrote:
> ybalasub@gmail.com (Yegna Balasubramanian) wrote in message news:<21a9c364.0411231223.72e1e981@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?tmp.../wx10211231819