RE: Can we trust these numbers ?
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Aug 2004 12:30:39 -0500
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
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of robson@xxxxxxxxxxxxxxx
Sent: Wednesday, August 25, 2004 11:54 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
