Re: Any way to reduce waits over the link? (SQL*Net message from dblink)

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Thu, 11 Sep 2003 16:22:37 GMT
Message-ID: <3f6099ec.2080316547@nyc.news.speakeasy.net>

On Wed, 10 Sep 2003 18:40:15 GMT, Brian Peasland <dba_at_remove_spam.peasland.com> wrote:

>> The queries are fine (not pulling whole tables)
>
>Are you sure?

I wouldn't say it if I wasn't :)
The tables that the distributed queries are joined with are large (millions or 10's of millions of rows), and are executed very, very frequently. The explain plans always show the 'remote' query to be using a where clause on an indexed column. Such queries on the 'master' database have been looked at, and require no further tuning.

> Have you taken the query that is sent to the remote
>database and tuned it on that remote database? What percentage of your
>data are you pulling across your database link as opposed to the amount
>of data you actually need on the local database?

Are you talking about a percentage in a query, or in a percentage of overall data?
Some queries are executed completely on a remote (master) site, therefore 100% of data is pulled, some queries are 50-50, some are 70-30 (local-remote). As for overall amount of data, I would guess about 10-15 percent of data is pulled across the link. This is an "OLTP" server, 99% of queries take less than .1 seconds to execute.

> Maybe its a good idea
>to do a full table scan instead of an index lookup! One never knows for
>sure until they tune the query on the remote database.

I don't think pulling any of the tables across the link is going to be beneficial. In rear cases when such queries crawl into production, they cause havoc on the 'master' db. Distributed queries are very difficult to tune, sometimes they require a subquery to use the index, sometimes a join works better.

>
>> The network is fine (running at about half the capacity)
>
>Just because a network runs at 50% capacity or 10% capacity does not
>necessarily mean that data is moving fast across the network. Granted,
>if the network is at 100% capacity, data will move slower....
>
>> Avg
>> Time TotalWait wait Waits
>> Event Waits outs Time(cs) (ms) /txn
>> --------------------------- -------- ---- -------- ---- ------
>> SQL*Net message from dblink 1,428,648 55 454,719 3 7.1
>
>This one event contributes approximately 50% of your total wait time.
>This event will only occur if you are waiting on communication from a
>remote database, initiated at your local database. i.e. a database link
>is involved here between two databases. Have you tuned your distributed
>query?

Yes, database link is obviously involved. See notes above. Queries are fine.

>> db file sequential read 588,295 0 440,978 7 2.9
>
>You might want to see if this event can be tuned too since it accounts
>for nearly the other 50% of your total wait time.
>>
>> 454,719cs/100/60=75mins of wait time on a 10cpu machine
>> the file reads cannot really be tuned, unless i increase the buffer
>> cache a bit, but it's running at 99% hit rate.
>
>Why can't the file reads be tuned? You may be requesting more logical
>reads than you really need, which *may* translate to more physical reads
>than you need. The BCHR at 99% doesn't mean that this wait event is a
>waste of time to tune. This one event contributes 44% of your total wait
>time. It may be a good idea to investigate this further. Is there I/O
>contention on the disks or in the controllers? Are these disks fast
>enough? Your BCHR of 99% does not mean that you don't have I/O tuning
>(logical and/or physical) to do. That type of reasoning is a myth.
>

Unfortunately, increasing the buffer cache would marginally bring any positive results. There is only a small set of data that is shared between users, and stays fairly static most of the day (and is cached on front ends anyway), the rest of data belongs to individual web users, or possibly small groups of users (groups of 10-20, out of 400K). I can try increasing the buffer cache, but we have no io wait problem (disks are not overloaded, that same statspack shows:

Physical reads:                185.34 persecond, on a 10disk

stripe-mirror set(Sun A5200, via fibre hubs) there are 2 more databases on the same disks, but they currently have very little activity). The block size is 4K. That said, I'll try increasing it anyway :), but i doubt this problem is going away until we upgrade the disks (currently 10K, but a software raid (Volume Manager).

As for having too many 'logical' reads, that _might_ be the case for some queries, but all queries are scrutinized all the time, and there are no 'bad-performers' currently (well, there is one, but that does lots of memory reads--20K or so, which don't translate into physical reads, the rest are multi-joins, and have no more than 20-50 logical reads, with few execeptions that look at a few hundred rows). .......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Sep 11 2003 - 11:22:37 CDT