Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: need help on statspack report, Parse CPU to Parse Elapsd %

Re: need help on statspack report, Parse CPU to Parse Elapsd %

From: Joe <yung103_at_netvigator.com>
Date: Sat, 6 Mar 2004 00:50:57 +0800
Message-ID: <c2ab5g$16i2039@imsp212.netvigator.com>


Hi Srivenu,

Thanks a lots for your reply.
Yes, I am using 8.1.7.4. and i am experiencing contention in the following area

Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     % Total
Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
enqueue                                            33,290   10,195,809   57.13
latch free                                      1,887,651    6,092,953   34.14
db file scattered read                          1,788,217      537,596    3.01
db file sequential read                         1,589,366      369,621    2.07
log file sync                                      32,896      164,777     .92
          -------------------------------------------------------------

I understand the 3-5 wait events, and able to handle them. However for the enqueue and latch free, I don't have much idea on how to improve it. I do read from the metalink that setting the spin_count may help to improve latch free wait. However, spin_count is a undocumented parameter, and i don't have much information of it. I want to reduce the value of this parameter, but I don't even know what is the current value of it. I can't find it in v$system_parameter view.

Can you give me some advise on latch free and enqueue wait? Any input is appreciated.
Thanks a lots.

Regards,
Joe

"srivenu" <srivenu_at_hotmail.com> ??? news:1a68177.0403050248.a6acf5_at_posting.google.com ???...
> Joe,
> The duration of your statspack (about 9 hrs) is high, normally
> Statspack reports are generated for a half-hour duration during peak
> load.
> Think you are running 8i.
> A low value for Parse CPU to Parse Elapsd % means that the during
> parsing, the server process is waiting for something, most likely
> contending for a latch (shared pool and library cache latches).
> But in your case this is not much of a disadvantage as you have a high
> % Non-Parse CPU (98.94 %).
> You should have also shown the Top 5 Wait Events (Top 5 Timed Events
> from 9.2) and the latch information from the statspack.
> You also have a high soft parse % - 98.98%.
> So most of your parse calls are resulting in soft parses.
> In such cases, the setting of SESSION_CACHED_CURSORS parameter helps
> (you can start with a value of 150).
> You may also want to look at the code, to see why there are so many
> parse calls.
> Ideally you should parse once and execute many times.
> regards
> Srivenu
Received on Fri Mar 05 2004 - 10:50:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US