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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can we trust these numbers ?

RE: Can we trust these numbers ?

From: <robson_at_medidata.com.br>
Date: Wed, 25 Aug 2004 15:44:04 -0300
Message-ID: <OF99D5FFD3.EE0B2A27-ON03256EFB.00655776@medidata.com.br>

Cary

I think there is an upper bound for the total wait time namely the max number of process times total observed time (elapsed time). Considering that the that statspack report was from a 60 minutes window=  and
there were 1776 sessions, if all sessions were all the time waiting it would give 60*1776 =3D 106560 minutes -> 1= 776
hours -> 74 hours. So for that report
infinite would be 74 hours. So the numbers are really wrong. What makes=  me
surprised is that they are not always that way. Sometimes they have reasonable values.
I don't know if Oracle messes up stats when running on multiprocessor boxes. The server from where this stats come from is a SunFire 15K with 24 CPU.
I have read chap. 1 of your book and told my boss about the hotsos trai= ning
but am still waiting for a chance on the budget.

Robson Gomes

Medidata Inform=E1tica S.A

(21) 2546 3734

P Antes de imprimir pense em sua responsabilidade e compromisso com o M= EIO
AMBIENTE! O conte=FAdo desta mensagem (e o de seus eventuais anexos) =E9 de exclu= sivo
interesse do destinat=E1rio acima indicado e pode conter informa=E7=E3o=

confidencial e/ou propriet=E1ria, que n=E3o deve ser copiada ou divulga= da sem o
consentimento expresso da Medidata Informatica S/A.. Em caso de recep=E7=
=E3o

por pessoa diversa do destinat=E1rio, a mensagem e seus respectivos ane= xos
dever=E3o ser destru=EDdos imediatamente sob pena de responsabiliza=E7=E3= o por uso
indevido.
The contents of this message (and of its eventual enclosures) are inten= ded
to the exclusive benefit of the addressee indicated above and may conta= in
confidential and/or proprietary information and may not be copied or disseminated without express consent of Medidata Informatica S/A. In ca= se
of unduly reception thereof by any other party than the addressee above= ,
this message and its enclosures shall be immediately destroyed. The use=  or
disclosure of the contents of this message and/or its enclosures will become such other party liable for such action.

                                                                       =
                                                =20
                    "Cary Millsap"                                     =
                                                =20
                    <cary.millsap_at_hotso       To:     <oracle-l_at_freelis=
ts.org>                                         =20
                    s.com>                    cc:                      =
                                                =20
                    Sent by:                  Subject:     RE: Can we t=
rust these numbers ?                            =20
                    oracle-l-bounce_at_fre                                =
                                                =20
                    elists.org                                         =
                                                =20
                                                                       =
                                                =20
                                                                       =
                                                =20
                    25/08/04 14:30                                     =
                                                =20
                    Please respond to                                  =
                                                =20
                    oracle-l                                           =
                                                =20
                                                                       =
                                                =20
                                                                       =
                                                =20




Any system has an infinite capacity for waiting, even in a finite perio= d of
time. There's some more detail on the issue on pp215-216 of the Optimiz= ing
Oracle Performance book.

But I think in your case, I may get to save this as one more example of=

"statspack messes up." I can't tell from the line wrapping. Is it sayin= g
that the total time waited for the 'latch free' timed event is 56,629,674,849 centiseconds? If so, then:

56,629,674,849 cs =3D 566,296,748.49 seconds
                  =3D 157,304.65 hours
                  =3D 6,554.36 days
                  =3D 17.94 years

Hmm.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26 Toronto
- SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New Orle= ans
- Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.o= rg]
On Behalf Of robson_at_medidata.com.br
Sent: Wednesday, August 25, 2004 11:54 AM To: oracle-l_at_freelists.org
Subject: Can we trust these numbers ?

Hi

I am intrigued that sometimes I get statspack reports that show very bi=
=3D

g
figures on the event wait
times.
Look the total wait time of the latch free: 56,629,674,849 cs. It gives one average time of 1,029,784 ms or 1030 seconds (17 hours). What can be wrong ?

Thanks for any hint

STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host ------------ ----------- ------------ -------- ----------- --- --------=
=3D



XPTO 1234567890 xptoinst 1 8.1.7.4.0 NO XPTOHOST=
=3D
                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:      27474 23-Aug-04 09:58:58    1,776
   End Snap:      27475 23-Aug-04 10:59:10    1,776
    Elapsed:                  60.20 (mins)

Cache Sizes


           db_block_buffers: 2097152 log_buffer: 1572864=
=3D

              db_block_size: 8192 shared_pool_size: 1825361100=
=3D

Load Profile

~~~~~~~~~~~~                              Per Second         Per
Transaction
                                     ---------------
---------------
                  Redo size:              316,789.22
3,949.25
              Logical reads:              214,536.95
2,674.52
              Block changes:                1,972.78
24.59
             Physical reads:                4,325.08
53.92
            Physical writes:                  356.58
4.45
                 User calls:                2,351.87
29.32
                     Parses:                  630.83
7.86
                Hard parses:                   10.49
0.13
                      Sorts:                  719.67
8.97
                     Logons:                    0.95
0.01
                   Executes:                1,665.18
20.76
               Transactions:                   80.22

  % Blocks changed per Read:        0.92    Recursive Call %:       32.=

=3D

19
 Rollback per transaction %: 0.05 Rows per Sort: 15.=
=3D

17

Instance Efficiency Percentages (Target 100%)


            Buffer Nowait %: 99.99 Redo NoWait %: 100.=
=3D

00

            Buffer Hit %: 97.98 In-memory Sort %: 99.=
=3D

99

            Library Hit %: 99.46 Soft Parse %: 98.=
=3D

34

         Execute to Parse %: 62.12 Latch Hit %: 99.=
=3D

71
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 99.=
=3D

93

 Shared Pool Statistics           Begin       End
                                  ------      ------
             Memory Usage %:       86.70       84.48
    % SQL with executions>1:       76.18       74.04
  % Memory for SQL w/exec>1:       68.50       70.51

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                               Wait
% Total
Event                                               Waits    Time (cs)
Wt Time
-------------------------------------------- ------------ -------------=

=3D
---
----------
latch free                                        549,918   56,629,674,=

=3D
849 67.75 log file sync 314,641 11,716,563,=
=3D
756 14.02 db file sequential read 7,527,206 11,331,652,=
=3D
411 13.56 rdbms ipc reply 2,192 1,952,998,=
=3D
207 2.34 db file scattered read 303,506 1,952,974,=
=3D
696 2.34 Robson Gomes Medidata Inform=3DE1tica S.A (21) 2546 3734 P Antes de imprimir pense em sua responsabilidade e compromisso com o M=
=3D
EIO AMBIENTE! O conte=3DFAdo desta mensagem (e o de seus eventuais anexos) =3DE9 de e= xclu=3D sivo interesse do destinat=3DE1rio acima indicado e pode conter informa=3DE7=
=3DE3o=3D
confidencial e/ou propriet=3DE1ria, que n=3DE3o deve ser copiada ou div= ulga=3D da sem o consentimento expresso da Medidata Informatica S/A.. Em caso de recep=3D= E7=3D
=3DE3o
por pessoa diversa do destinat=3DE1rio, a mensagem e seus respectivos a= ne=3D xos dever=3DE3o ser destru=3DEDdos imediatamente sob pena de responsabiliza=
=3DE7=3DE3=3D
o por uso indevido. The contents of this message (and of its eventual enclosures) are inten=
=3D
ded to the exclusive benefit of the addressee indicated above and may conta=
=3D
in confidential and/or proprietary information and may not be copied or disseminated without express consent of Medidata Informatica S/A. In ca=
=3D
se of unduly reception thereof by any other party than the addressee above=
=3D
, this message and its enclosures shall be immediately destroyed. The use=
=3D
or disclosure of the contents of this message and/or its enclosures will become such other party liable for such action.=3D ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
=
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
Received on Wed Aug 25 2004 - 14:09:04 CDT

Original text of this message

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