Re: Performance problem of query over SQL*Net.

Checked out the waits in the database but there is nothing obvious. I also have statspack running every hour.

Biggest waits are for "SQL*Net message from client" and "rdbms ipc message" which, I'm told, are to be expected.

However there are a lot of hits over dblinks to this database. Is it possible that there is contention for some listener resource? The odd thing is that I can execute a query over the link for a number of times with virtually instantaneous response and then it just hangs for a large number of seconds, as if some other process has robbed a resource, but I cannot track it down. What determines how many requests the listener can deal with. ie is it limited by a parameter that can be set?

Thanks for you help.

Regards

Ray

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:aodrdb$ll1$1$8300dec7_at_news.demon.co.uk...
>
> 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.
> >
> >
> >
>
>
Received on Mon Oct 14 2002 - 04:11:37 CDT