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

"Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message news:MPG.1c9a3db6778c7c3b989dbc_at_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.

What *I* would do.

Modify the stored procedure to start extended sql tracing (probably based on the remote clients client info if you can) as you are going to generate a *lot* of small logs it is probably a good idea to set the trace file identifier as well. www.petefinnigan.com/ramblings has a page on setting trace if you need it.

Run the output through tkprof or the HOTSOS profiler. this should show you pretty much where the problem lies for that sp.

What I would *guess*

You have an edge case - if you are submitting different parameters and the execution plan for a particular piece of sql is fine for most values but a real PITA for some subset.

You have some contention on a particular object (the audit table even).

You are extending database objects in DMTs

I'd be surprised if it were redo or general slow IO related because we are talking about minutes of extra time here.

What I would *conclude*

Real users probably get the same issue.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com