Re: New database is slower on large select queries.

Have you tried running the same query on both boxes and comparing the explain plans?

If they are wildly different, the optimiser may be taking a different path on the new box based on the statistics gathered for it. If this is the case, try running DBMS_STATS.DELETE_TABLE_STATS on the accounts table (for starters) to remove any junk stats from the data dictionary then DBMS_STATS.GATHER_TABLE_STATS on the accounts table and retrying the query and explain plan.

You can do the same for other tables after.

We had a similar problem, yesterday in fact, and re-analysing the tables after importing or copying data from one db to another helped.

HTH, Mark

ksinger_at_highspeed.com (Kyle Singer) wrote in message news:<e239071f.0201290859.136cb8b9_at_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 Wed Jan 30 2002 - 03:21:53 CST