Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Important: Oracle processes taking lots of CPU

RE: Important: Oracle processes taking lots of CPU

From: New DBA <>
Date: Wed, 24 Nov 2004 06:47:42 -0800 (PST)
Message-ID: <>

Hi John,

> If this is indeed the case, you will need to look at
> disabling bind-peeking
> using the hidden parm '_optim_peek_user_binds' -
> obviously you need to
> consult with Support for this.

I understand your concern about Bind Variable in Oracle Apps environment.

But, The processes don't show excessive LIOs. Looking at the trace file, everything seems normal. Only a tiny fraction of the CPU actually used by the server (found through top or ps) process is accounted for in the trace files.

As I understand, the CPU is used while performing LIOs, doing calculation, sorts or while spinning for the latches. My understanding might be wrong! Since there aren't enough LIOs or complex calculations involved I thought that spinning for latches might be causing excessive CPU.

But _spin_count is at its default value (2000).

My concern is to find out why is so much CPU being consumed while it does not show up in the trace files.

Is the CPU used while spinning (waiting for the latch) accounted for in trace or v$sesstat?

> Btw, 'CPU used by this session' is not upadated
> until the call completes.

What do you mean by the call? Do you mean the entire SQL query? Or just one particular OS operation. But I have waited long enough before looking at the stat again without any significant change in the value (despite the fact that CPU utilized by the process was 100% during that time)

> As
> well, it seems that you have a background in 7.x and
> lower, as STATSPACK is
> the default 8i and above....

I have never used statspack before since didn't get much exposure to performance tuning. There is a learning curve involved, but right now don't have enough time to learn!

New DBA                 

Do you Yahoo!?
All your favorites on one personal page Try My Yahoo!
Received on Wed Nov 24 2004 - 08:44:54 CST

Original text of this message