RE: Can we trust these numbers ?
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Aug 2004 15:56:43 -0500
Robson,
You're right in your assessment of the max, except for one thing--a =
loophole
really. I'm not sure how statspack counts sessions. But if it computes =
the
number of sessions by counting them either at time t0 or time t1 (or =
even
max(count@t0, count@t1)), then there's no limit to the number of =
sessions it
can miss.
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 1:44 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Can we trust these numbers ?
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=3D
and
there were 1776 sessions, if all sessions
were all the time waiting it would give 60*1776 =3D3D 106560 minutes -> =
1=3D
776
hours -> 74 hours. So for that report
infinite would be 74 hours. So the numbers are really wrong. What =
makes=3D
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=3D
ning
but am still waiting for a chance on the budget.
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 =
exclu=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 =
divulga=3D
da sem o
consentimento expresso da Medidata Informatica S/A.. Em caso de =
recep=3DE7=3D
=3DE3o
por pessoa diversa do destinat=3DE1rio, a mensagem e seus respectivos =
ane=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
=3D20
"Cary Millsap" =
=3D
=3D20
<cary.millsap@hotso To: =
<oracle-l@freelis=3D
ts.org> =3D20
s.com> cc: =
=3D
=3D20
Sent by: Subject: RE: Can we =
t=3D
rust these numbers ? =3D20
oracle-l-bounce@fre =
=3D
=3D20
elists.org =
=3D
=3D20
=
=3D
=3D20
=
=3D
=3D20
25/08/04 14:30 =
=3D
=3D20
Please respond to =
=3D
=3D20
oracle-l =
=3D
=3D20
=
=3D
=3D20
=
=3D
=3D20
Any system has an infinite capacity for waiting, even in a finite =
perio=3D
d of
time. There's some more detail on the issue on pp215-216 of the =
Optimiz=3D
ing
Oracle Performance book.
But I think in your case, I may get to save this as one more example =
of=3D
"statspack messes up." I can't tell from the line wrapping. Is it =
sayin=3D
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 =3D3D 566,296,748.49 seconds
=3D3D 157,304.65 hours
=3D3D 6,554.36 days
=3D3D 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=3D
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=3D
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
=3D3D
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
=3D3D
----
XPTO 1234567890 xptoinst 1 8.1.7.4.0 NO =
XPTOHOST=3D
=3D3D
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
=3D3D
db_block_size: 8192 shared_pool_size: =
1825361100=3D
=3D3D
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
=3D3D
19
Rollback per transaction %: 0.05 Rows per Sort: =
15.=3D
=3D3D
17
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: =
100.=3D
=3D3D
00
Buffer Hit %: 97.98 In-memory Sort %: =
99.=3D
=3D3D
99
Library Hit %: 99.46 Soft Parse %: =
98.=3D
=3D3D
34
Execute to Parse %: 62.12 Latch Hit %: =
99.=3D
=3D3D
71
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: =
99.=3D
=3D3D
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
=3D3D
---
----------
latch free 549,918 =
56,629,674,=3D
=3D3D
849
67.75
log file sync 314,641 =
11,716,563,=3D
=3D3D
756
14.02
db file sequential read 7,527,206 =
11,331,652,=3D
=3D3D
411
13.56
rdbms ipc reply 2,192 =
1,952,998,=3D
=3D3D
207
2.34
db file scattered read 303,506 =
1,952,974,=3D
=3D3D
696
2.34
Robson Gomes
Medidata Inform=3D3DE1tica S.A
(21) 2546 3734
P Antes de imprimir pense em sua responsabilidade e compromisso com o =
M=3D
=3D3D
EIO
AMBIENTE!
O conte=3D3DFAdo desta mensagem (e o de seus eventuais anexos) =3D3DE9 =
de e=3D
xclu=3D3D
sivo
interesse do destinat=3D3DE1rio acima indicado e pode conter =
informa=3D3DE7=3D
=3D3DE3o=3D3D
confidencial e/ou propriet=3D3DE1ria, que n=3D3DE3o deve ser copiada ou =
div=3D
ulga=3D3D
da sem o
consentimento expresso da Medidata Informatica S/A.. Em caso de =
recep=3D3D=3D
E7=3D3D
=3D3DE3o
por pessoa diversa do destinat=3D3DE1rio, a mensagem e seus respectivos =
a=3D
ne=3D3D
xos
dever=3D3DE3o ser destru=3D3DEDdos imediatamente sob pena de =
responsabiliza=3D
=3D3DE7=3D3DE3=3D3D
o por uso
indevido.
The contents of this message (and of its eventual enclosures) are =
inten=3D
=3D3D
ded
to the exclusive benefit of the addressee indicated above and may =
conta=3D
=3D3D
in
confidential and/or proprietary information and may not be copied or
disseminated without express consent of Medidata Informatica S/A. In =
ca=3D
=3D3D
se
of unduly reception thereof by any other party than the addressee =
above=3D
=3D3D
,
this message and its enclosures shall be immediately destroyed. The =
use=3D
=3D3D
or
disclosure of the contents of this message and/or its enclosures will
become such other party liable for such action.=3D3D
----------------------------------------------------------------
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
-----------------------------------------------------------------
=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
-----------------------------------------------------------------
Other related posts:
