Re: statspack comment is needed

55.45 minutes is about 3,500 seconds.

db file sequential read is slow - and given you are apparently only doing about 25 per second you seem to have an I/O oddity to get times this slow. Of course, if you are running RAC the other instance may be crippling the I/O subsystem.

Global cache busy is the cross-instance RAC equivalent of buffer busy waits. This suggests there may be problems with moving popular blocks back and forth too much - but it could be exacerbated by slow I/Os ("read by other session (in other instance maybe)".

Log file sync suggest lots of commits - but since this is a RAC system, it may be a side-effect of excess global cache transfers - Oracle has to flush the buffer when it sends a block from one instance to another.

You need to check WHICH latches are getting most of the sleeps as it may be cache buffers chains - which would corroborate the global cache activity, but it might be something else.

The high CPU may be associated with excess local buffer activity or excess cross-instance chat.

Bottom line - you might be working too hard, your discs might be very slow, or you might be doing too much cross-instance block transfer.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"hopehope_123" <hopehope_123_at_yahoo.com> wrote in message 
news:1166105495.877048.182280_at_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.

>