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

> The queries are fine (not pulling whole tables)

Are you sure? 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? 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.

> 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?

> 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.

> log file sync 94,051 5 67,230 7 0.5
> db file parallel write 31,395 0 44,485 14 0.2
>
> 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.

> log file sync's can't be tuned either, and they don't bother me much,
> redo is running on dedicated disks, striping didn't help.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"