| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can we trust these numbers ?
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_at_t0, count_at_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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of robson_at_medidata.com.br
Sent: Wednesday, August 25, 2004 1:44 PM
To: oracle-l_at_freelists.org
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_at_hotso To: =
<oracle-l_at_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_at_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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.o=3D
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
=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
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
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
--- ---------- latch free 549,918 = 56,629,674,=3DReceived on Wed Aug 25 2004 - 15:54:13 CDT
=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_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 -----------------------------------------------------------------
=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 -----------------------------------------------------------------
 |
 |