Re: High elapsed to CPU ratio for parses
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: paul.baumgartel@xxxxxxxxx
- Date: Tue, 23 Aug 2005 12:35:23 -0400
On 8/22/05, Paul Baumgartel <paul.baumgartel@xxxxxxxxx> 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@xxxxxxxxxxxx
> --
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
