Re: Performance problem of query over SQL*Net.

The difficulty with this type of issue is that you need to know what the remote database is doing at the time of the slowdown. You also need to know what the network is doing, and how the query is being resolved at the far end.

At the local database - one important check is to look at v$session_wait (or use dbms_support to switch on tracing with WAITS) to see if the problem is (event names may be slightly wrong)

    SQL*Net send message to db link
    SQL*Net receive message from db block
    SQL*Net more data from db_link
    SQL*Net more data to db_link

Is the database link definitely open when the system slows down, or is the database link closed, and therefore slow to reconnect because of network traffic or other activity on the remote server ?

If the link is kept open, you can find which session is the target for the local session, and check its waits when the problem happens to find out if it is waiting for a message, of wasting time actually doing the job.

Are there lots of db-links around the place - is it possible that you have a problem where a db-link has to wait for another dblink to close before it can attach ?

Are you running shared server (MTS) at the remote site, with insufficient shared servers to support the level of access ?

Note - Oracle can have "network problems" when other processes don't appear to because Oracle tends to use lots of small synchronous dialogues - so any small network problem can become seriously exaggerated

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Michael Bourke wrote in message ...


>Version 8.1.6.  Solaris

>Three machines BOB, FRED, HARRY configured identically.

>

>

>Query is:

>"select count(*) from xx_at_BOB;".  Using SQL*Plus on machine FRED which has a

>db link to BOB.

>

>First execution can sometimes (although not always) takes thirty seconds,

>often more.  However a second execution of the same query returns in less

>than a tenth of a second.  I can usually continue to execute the same query

>with no problems for a number of times until eventually it will have the

>long pause again.  Seems very variable. Sometimes it just seems to hang and

>never come back.  Problem is reproducible on the other boxes ie the same

>delay is true between FRED and HARRY, HARRY and BOB etc. Other non-oracle

>processes using the netwok seem fine. Any ideas?

>

>--

>Michael Bourke.

>

>

>