Re: High Cpu Usage After 9i Migration

From: Eberhard Niendorf <eberhard.niendorf_schrott_at_t-online.de>
Date: Wed, 24 Nov 2004 00:01:28 +0100
Message-ID: <co0fg9$mei$05$1@news.t-online.com>

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 Received on Tue Nov 23 2004 - 17:01:28 CST