Re: select statement uses 100% CPU

Are there statistics on the tables owned by SYS? select last_analyzed from dba_tables where owner = 'SYS';

If yes .. then delete statistics and see if it makes a difference. If no ... then gather stats and see if it makes a difference. What optimizer goal are you using?

Alternatively .. run a 10046 trace level 12 .... and see if that tells you anything.

Anurag

"Vince Laurent" <eAddict_at_yahoo.com> wrote in message news:gpkqdvcthkschvu9p88ur29n03mtmssens_at_4ax.com...
> I am trying to debug a system that is not acting right. I have 2
> systems running Oracle 9.2.0.3 on HP-UX11i. BDE has 4 G of RAM, BQA
> has 2G of RAM. The systems are patched to the same or equivilant OS
> level patches and running the same version of Oracle. Both were
> upgraded from an 8.1.7.3 environment.
>
> On BDE if I run the command
>
> select * from dba_extents
>
> it takes just over 2 hours and the CPU is pegged! If I run it on BQA
> it takes seconds and the CPU doesn't even notice it. Both are also 2
> CPU systems.
>
> I have looked at the initSID.ora files (and even dumped the
> v$parameters) and the only parameters that are noticeably different
> are the ones that involve memory allocation. BDE has more so the
> numbers were larger.
>
> I even did a test and made the BDE paramters the same as BQA, bounced
> oracle, and reran the test. Same results.
>
> Any ideas? Sorry for the cross post too.
> Vince
Received on Wed Jun 04 2003 - 07:29:08 CDT