Re: New database is slower on large select queries.

Kyle Singer wrote:
>
> 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

Is this server used exclusively for RDBMS? What is the disk configuration? Is the machine load the same?

For query performance, have you:

1. Used tkprof to get eprformance metrics (old server also)?
2. Recompiled your application to ensure it is optimized for the new machine (if applicable)?

You should do these things before you start messing with the init parameters, otherwise, this will be like trying to find a needle in a haystack.

-- 
Ron Reidy
Oracle DBA