RE: Can we trust these numbers ?
- From: robson@xxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 25 Aug 2004 15:44:04 -0300
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@hotso To: <oracle-l@freelis=
ts.org> =20
s.com> cc: =
=20
Sent by: Subject: RE: Can we t=
rust these numbers ? =20
oracle-l-bounce@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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxx=
rg]
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=
=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@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
-----------------------------------------------------------------
=
----------------------------------------------------------------
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:
