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: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 25 Aug 2004 12:30:39 -0500
Message-ID: <001001c48ac9$40441600$6701a8c0@CVMLAP02>


Any system has an infinite capacity for waiting, even in a finite period of time. There's some more detail on the issue on pp215-216 of the Optimizing 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 saying 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 = 566,296,748.49 seconds
                  = 157,304.65 hours
                  = 6,554.36 days
                  = 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 Orleans

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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= 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 ------------ ----------- ------------ -------- ----------- --- --------=



XPTO 1234567890 xptoinst 1 8.1.7.4.0 NO XPTOHOST=
                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=

              db_block_size: 8192 shared_pool_size: 1825361100=

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.=
19
 Rollback per transaction %:        0.05       Rows per Sort:       15.=
17

Instance Efficiency Percentages (Target 100%)


            Buffer Nowait %: 99.99 Redo NoWait %: 100.= 00

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

            Library Hit %: 99.46 Soft Parse %: 98.= 34

         Execute to Parse %: 62.12 Latch Hit %: 99.= 71
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 99.= 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
-------------------------------------------- ------------ -------------=
---
----------
latch free                                        549,918   56,629,674,=
849
67.75
log file sync                                     314,641   11,716,563,=
756
14.02
db file sequential read                         7,527,206   11,331,652,=
411
13.56
rdbms ipc reply                                     2,192    1,952,998,=
207
2.34
db file scattered read                            303,506    1,952,974,=
696
2.34



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.=


----------------------------------------------------------------
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 - 12:44:58 CDT

Original text of this message

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