Re: New database is slower on large select queries. (SOLVED)

From: Klaus Naumann <knaumann_at_gmx-ag.de>
Date: Wed, 30 Jan 2002 14:33:32 +0100
Message-ID: <7HS58.1$nu2.1175@news.ecrc.de>

On Wed, 30 Jan 2002 00:28:01 +0100, Kyle Singer wrote:

> Thanks to all who replied!
> 
> I think we (by we I mean our network operations team) have figured out
> the problem. It looks like at some point this or last week the network
> throughput from the new DB server to our workstations has been
> drastically cut. Our transfer rates on a large file over FTP used to be
> about 4 MB/s and now it's 260 KB/s. Not too good :)
> 
> Sounds like a problem with our switches and not the database.

Hi,

this might be related to the SUN. Try different settings of you switch port (like Hard-100 Hard-Full). We once had a problem with a test machine here because the switch and the network card didn't use the same parameters. (Like the switch being Full Duplex and the card Half).

          HTH, Klaus

> 
> Thanks again for the quick response!
> 
> Kyle Singer
> HighSpeed Communications
> 
> "Kyle Singer" <ksinger_at_highspeed.com> 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

-- 
Klaus Naumann
Database Administration
GMX  Aktiengesellschaft 
Riesstrasse 17, 80992 München 
Telefon +49.89.143 39-0
Telefax +49.89.143 39-100
mailto:knaumann_at_gmx-ag.de
http://www.gmx.net/

Received on Wed Jan 30 2002 - 07:33:32 CST