Re: Performance problem of query over SQL*Net.

Database level snapshots are unlikely to help. The fact that the problem is intermittent does suggest the effect is highly local (in time) and related to contention. You need to see what a specific session is waiting for when it actually gets stuck.

Yes, it could be a simple resource limit problem. Take a look at v$resource_limit on the local and remote machines and see if there are any items listed which have reached their max value, this may give a clue.

Is the remote system using MTS - do you have long-running calls that attach via MTS, this could cause all shared servers to in-use when your query arrives. MTS and long-running calls do not mix.

There are many reasons why this could happen, but without a first hint from knowing the waits - you could spend ages guessing.

Can you simply emulate the problem from an SQL*Plus session - identify the relevant remote session , and switch on the WAIT trace ?

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



Ray Teale wrote in message <4Rvq9.15380$Sr6.495899_at_ozemail.com.au>...


>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

>