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

Jeremy said,on 3/10/2005 4:50 PM:
> 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!
>
>

Have seen the same behaviour in my app also. Would be good to know the cause..

Thanks

-- 
Shanmu.