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

On Wed, 10 Sep 2003 22:09:56 +0300, "Tanel Poder" <change_to_my_first_name_at_integrid.info> wrote:

>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) ;)

I don't like wasting my time on tuning things that don't need to be tuned :) The problem is that during peak times the load on the server jumps to levels which I don't feel are acceptable (25 on a 10 CPU machine), sometimes to a point where clients (web app) cannot connect to the db anymore. I don't know how much an hour of wait time contributes to load, but all that context switching probably has to contribute some significant cpu usage.

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

Here it is:
CPU used by this session 909,953 252.8

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

That's what I am trying to do :)

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

More data would probably indicate pulling lots of data, such stats is low:
SQL*Net more data from dblin 2,554 0 480 2 0.0

Queries are tuned, see my other post.

>1) network latency can be tuned using TCP parameters, using faster protocol
>(named pipes in windows maybe), using dedicated network, using faster
>network, etc..

The databases are on different physical machines. I'll be happy to use any other protocol that can go over the ethernet and work faster than TCP.
The latency doesn't seem to be an issue: 41 packets transmitted, 41 packets received, 0% packet loss round-trip (ms) min/avg/max = 0/0/2
Bandwidth is not an issue, we monitor it via MRTG. Not sure if GigE is going to help..

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

Interesting suggestion.. Haven't thought about it (about creating views on joined-remote queries). Unforrtunately this one would require some application changes, but I'll definetely take it into consideration.
As for your second point, yes, it is our problem a little. Although our web application doesn't connect on every request, it does disconnect and reconnect after N page requests, something we are working on to eliminate.
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Sep 11 2003 - 11:35:35 CDT