Re: statspack comment is needed

From: hopehope_123 <hopehope_123_at_yahoo.com>
Date: 14 Dec 2006 06:11:35 -0800
Message-ID: <1166105495.877048.182280@16g2000cwy.googlegroups.com>

Hi ,

Thanks for your responses. I just try to understand the values , sorry here is another statspack :

54.55 min.

Top 5 Timed Events

---

% Total

Event                                               Waits    Time (s)

Ela Time

-------------------------------------------- ------------ -----------
--------
log file sync                                     302,886      14,774
 32.54
CPU time                                                       12,714
 28.00
global cache busy                                   8,510       6,011
 13.24
latch free                                      1,201,647       4,713
 10.38
db file sequential read                            74,150       2,579
  5.68
          -------------------------------------------------------------

Based on the values shown above :

48msec. for single log file sync ( 14774/302886) 34msec for single db file seq read ( 2579/74150) 706 msec for single globacl cache busy (6011/8510)

Considering a good fibre channel , 2-20 msec. disk io times are good but these values are very high , so disk io system my be slow.

What do you think about this calculationan and comment ?

Kind Regards,
hope

hpuxrac wrote:
> hopehope_123 wrote:
> > Hi ,
> >
> > Thank you very much for your help.
> >
> > Based on the article ,
> >
> > the system has 8 cpus .
> > 5551 secs ( 92) min cpu time in 60 minutes : if i have 2 cpus utilized
> > 100 % then , i can get 120 min. cpu time , here only 92 min. cpu time
> > can be achieved by using only 2 cpus, i have 8 , so only %20 of total
> > cpu resources is used.
> >
> > Is it correct?
> >
> > The application is an oltp which has strict transaction limitations
> > such as every business procedure has 4 secs. time out value.
> >
> > This is another part of the report , which shows high numbers of av
> > rd(ms)
> >
> > Tablespace Filename
> > ------------------------
> > ----------------------------------------------------
> > Av Av Av Av Buffer
> > Av Buf
> > Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> > Wt(ms)
> > -------------- ------- ------ ------- ------------ -------- ----------
> > ------
> >
> > UNDO_TBS_FIRST /dev/vx/rdsk/datadg/UNDOFIRST
> > 1 0 110.0 1.0 1,021 0 3
> > 0.0
> > /dev/vx/rdsk/datadg/verde_raw_undo12_8g
> > 1 0 90.0 1.0 2,555 1 17
> > 0.0
> > /dev/vx/rdsk/datadg/verde_raw_undo1_1g
> > 1 0 90.0 1.0 850 0 6
> > 1.7
> >
> > UNDO_TBS_SECOND /dev/vx/rdsk/datadg/UNDOSECOND
> > 1 0 90.0 1.0 1 0 0
> > /dev/vx/rdsk/datadg/verde_raw_undo22_1g
> > 1 0 120.0 1.0 1 0 2
> > 0.0
> > /dev/vx/rdsk/datadg/verde_raw_undo2_1g
> > 1 0 90.0 1.0 1 0 0
> >
> >
> > Kind Regards,
> > hope

>

> Tuning by looking at statspack output for anything is tricky but
> especially just things like CPU or IO statistics is not exactly a
> recommended approach any longer. ( If you use it to identify SQL with
> bad LIO that's a somewhat better approach ).

>

> Try looking at Tom Kyte's site http://asktom.oracle.com and do a search
> on statspack ... you will probably start down a better path.
>
> Cary Millsap's book "Optimizing Oracle Performance" is recommended.
Received on Thu Dec 14 2006 - 08:11:35 CST