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

Hello Joe,

  Can you please post whole Statspack report. This would make analysis much easier.

Regards,

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer: http://www.dbainfopower.com/dbaip_advice_disclaimer.html

  "Joe" <yung103_at_netvigator.com> wrote in message news:c2ab5g$16i2039_at_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 - 12:35:35 CST