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

Hi!

My first question would be, what's the problem? Is your application slow or are you just tuning everything what appears in statspack top-5 list? (In other words - CTD is a disease probably even worse than SARS, thousands of DBAs have left their lives and spend all their time tuning until they die (or the sama happens to all of their databases) ;)

Note that when you post a statspack top-5 output taken in some timeframe, you should also post the information how much CPU was used during this timeframe, this way we can see the ratio between service & wait times and recommend whether it'd be appropriate to concentrate on waits at all. There should be statistic called CPU used ... or something in SP report as well.

But anyway, your stats report shows that during this hour all of your sessions were spending totally 12,5% on waiting for data from dblink. You have about 7 waits per transaction and average wait lasts for 3ms. You have two options here, either reduce number of waits (thus reduce number of roundtrips to remote server by tuning sql etc or reduce the average duration of a wait).
I can't help you with sql, but wait time "more data from dblink" generally breaks to two: 1) network latency(wait time) 2) query execution&fetching (service time).

1. network latency can be tuned using TCP parameters, using faster protocol (named pipes in windows maybe), using dedicated network, using faster network, etc..
2. query execution & fetching - this is normal query response time tuning, just in remote server.

> The queries are fine (not pulling whole tables)

When latency is a problem, it is wiser to pull more data with less requests. This is design & SQL issue.

> The network is fine (running at about half the capacity)
> Db's are not direct-connected, traffic goes over a switch, 100Mbps
> ethernet.
> When looking at trace files, it looks like oracle does some kind of
> checking over the link to make sure the objects are valid, that might
> be a contributor to these waits, any way to get rid of this?

If you're joining tables in remote location, then create views with required join conditions there and select from them instead of drectly from tables. That way you can save sqlnet roundtrips required for checking validity (or whatever) of referenced objects. That way you only evaluate your view, not any underlying objects. Also, note that selecting the first time over database link in a session does more roundtrips than subsequent operations. Thus if you got a web application which always spawns a new session, the overhead could come there..

Tanel.

> Some queries have already been 'tuned' to use snapshots, but some
> other queries require instanteneous updates to local tables, so
> snapshots won't work.
>
> My last statspack for an hour shows:
> 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
> db file sequential read 588,295 0 440,978 7 2.9
> 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.
> 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.
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email
Received on Wed Sep 10 2003 - 14:09:56 CDT