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

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:423135dd$0$2761$cc9e4d1f_at_news-text.dial.pipex.com...
> "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
>
>
>
>

What do you mean by DMT's? Received on Fri Mar 11 2005 - 16:34:40 CST