Re: High elapsed to CPU ratio for parses

On 8/22/05, Paul Baumgartel <paul.baumgartel_at_gmail.com> wrote:
> I don't think I've seen this particular problem before. Oracle
> 10.1.0.4.0 on Windows.
>
> While database is under heavy load as part of a performance test
> (using Mercury LoadRunner), a number of SQL statements (with bind
> variables, no literals; have been part of the application for a long
> time) are exhibiting multi-second elapsed time for a single parse,
> with CPU time for the parse relatively high (0.35 sec) but still far
> below the elapsed (6.60 sec for this example).
>
> TKPROF output of 10046 trace does show some waits for library cache
> and shared pool, but not nearly adding up to the times shown above.
> I'm at a bit of a loss to know where to look for the consumer of 6
> seconds for a single parse of a single SQL. Any suggestions welcomed.
>
>
> --
> Paul Baumgartel
> paul.baumgartel_at_aya.yale.edu
> --

Paul,

We've hit a hang/spin issue twice on 10.1.0.4, both for w2k3 EE and w2k adv svr, both for standard edition and enterprise edition. Both times, it was not possible to connect via the console as sysdba, so a hanganalyze/SSD trace files were not obtained.

>From the rather limited information that was available, one hypothesis
was that scheduled jobs of statspack snapshots were to blame. It was never confirmed that this was indeed the root cause, but after breaking such jobs and dropping the statistics_level from typical to basic, the hang/spin condition did not result again. One clue was that a job that should have executed at the top of the hour did not start until after the instance was restarted.

Yes, I would have greatly preferred that we would have been able to generate hanganalyze and system state dump files and found the root blocker and work an iTAR through to a solution - meaning, get the fix into the 10.1.0.5 patchset or into a one-off patch.

I've reverted to leaving a console session up with a sysdba connection so that if this condition occurs again, that I'd be able to get the diagnostic info out.

Last week was a 4 iTAR week.
I just want to get to a patchset/patch combo that doesn't give us grief.

(Mladen, 10.1.0.4 ain't it)

Paul

-- 
#/etc/init.d/init.cssd stop
# f=ma, divide by 1, convert to moles.
--
http://www.freelists.org/webpage/oracle-l