select statement uses 100% CPU

From: Vince Laurent <eAddict_at_yahoo.com>
Date: Tue, 03 Jun 2003 21:07:58 -0500
Message-ID: <gpkqdvcthkschvu9p88ur29n03mtmssens@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 Tue Jun 03 2003 - 21:07:58 CDT