A very interesting problem - frustrating for me but a challenge for you? 9i / mod_plsql .....

From: Jeremy <newspostings_at_hazelweb.co.uk>
Date: Thu, 10 Mar 2005 11:20:06 -0000
Message-ID: <MPG.1c9a3db6778c7c3b989dbc@news.individual.net>

Environment:
9iR2 / Solaris 8 / Oracle HTTP server / mod_plsql

A strange thing... an application runs in the above environment. Every minute a remote server tests for the availability of the system by submitting a URL which will execute a standard (i.e. part of the app) pl/sql packaged procedure.

Usually this proc will take an elapsed time of < 1 sec but just occasionally it takes *much* longer.

For example last night's logs (this is an audit table in the database which is inserted into at the start of execution of each procedure and updated upon completion to record start & end times) show an execution each minute taking elapsed 0 or 1 secs then suddenly there is one at 438 seconds then the next are all back to normal

10-mar 03:42:41      0
10-mar 03:41:40      0
10-mar 03:40:39    438
10-mar 03:39:38      0
10-mar 03:38:37      1

.
.
.

09-mar 21:52:34 695
.
.
.

09-mar 09:47:13 227
.
.

Remember we are taking a reading here every minute and they are all 0 or 1 sec elapsed

There is no pattern to these and, from this same audit table, we can see exactly what other application processes were working at the time - and we see none so it would appear that it is not any kind of conflict caused by the application (if it were we would expect to see many more examples during application busier times).

We have also been tracking the server load with a reading taken every minute and it would appear that the system was virtually idle at the time of these anomalies..

Finally, the procedure that is being executed is a commonly-executed procedure (albeit with different parameters) which is run thousands of times a day by users out on the internet and in a couple of the cases where these spikes have been detected we have seen that the procedure is started (via the internet monitor) and before it has completed the same procedure has been started and completed in 0 or 1 seconds by other users out in internet land.

So the question (which is of course obvious): why do we see this behaviour - what might be the cause?

Is it possible in any way to be related to any delays in the internet - I have discounted this becase the procedure once started does not (as far as I am aware) have any dependency on the internet to complete - the procedure does I believe run in the database generating its output and then mod_plsql is responsible for pumping the output back to the originator of the request - and that therefore it is only after the pl/sql procedure has completed (and done its updates etc in the db) that the performance of the internet might come into play.

So.... what do you think?

Cheers!

-- 

jeremy

Received on Thu Mar 10 2005 - 05:20:06 CST