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

I agree with Ron. You need to post the entire file. Under 8i the top 5 events don't mean much unless you can see the CPU time used later in the report.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Ron" <support_at_dbainfopower.com> wrote in message news:<zK-dnXbuzLJUW9XdRVn-vg_at_comcast.com>...
> 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 - 18:26:19 CST