New database is slower on large select queries.

From: Kyle Singer <ksinger_at_highspeed.com>
Date: 29 Jan 2002 08:59:25 -0800
Message-ID: <e239071f.0201290859.136cb8b9@posting.google.com>

I'm no Oracle DBA so please be kind.

We are currently using Oracle 8.1.5 on a single CPU Sun machine with 512MB RAM. We are testing 8.1.7 on a 4 CPU Sun machine with 1.5GB RAM. We have exp/imp our data onto the new database with success and most of our processes and procedures are a lot faster than the old DB.

However, when we perform a query like 'select * from accounts' it is very slow. I may have tracked it down to queries with more than 16 columns that select all data from a table. If I perform a query like:   

  select accountid, name from accounts

the query is very fast, faster than the old db.

But even if I perform a query like:

  select name,name,name,name,name,name,name,name,

         name,name,name,name,name,name,name,name,
         name

  from accounts

it is incredibly slow. Much slower than the old DB.

When comparing the time it takes for a complete 'select * from accounts' between databases it takes 9 seconds on the old db and 80 seconds on the new db.

TOAD's database statistics screen is showing no warnings on the new DB and shows quite a few on the old DB.

We've played with a few init parameters like db_block_buffers and shared_pool_size with no luck.

The top command on the server indicated only 1% of CPU is used and a .1% iowait during the slow query. (I'm not sure that's what I should look at)

Any ideas would be greatly appreciated!

Thank you,
Kyle Singer
HighSpeed Communications Received on Tue Jan 29 2002 - 10:59:25 CST