-
statspack oppinion
I have gathered statspack report. A this is a part of it:
Code:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
log file sync 192,878 4,564 18.72
db file scattered read 3,478,398 4,071 16.70
CPU time 3,867 15.87
log file parallel write 218,733 3,580 14.69
latch free 44,897 3,480 14.28
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 192,878 468 4,564 24 1.0
db file scattered read 3,478,398 0 4,071 1 18.0
log file parallel write 218,733 0 3,580 16 1.1
latch free 44,897 16,452 3,480 78 0.2
db file sequential read 2,719,216 0 2,160 1 14.1
control file parallel write 13,277 0 1,021 77 0.1
db file parallel read 29,206 0 559 19 0.2
buffer busy waits 32,484 83 449 14 0.2
log file sequential read 1,097 0 171 156 0.0
process startup 502 118 145 289 0.0
enqueue 176 33 141 799 0.0
log file switch completion 75 54 62 828 0.0
log buffer space 107 6 25 236 0.0
switch logfile command 7 3 18 2570 0.0
control file sequential read 13,252 0 12 1 0.1
row cache lock 31 1 12 395 0.0
library cache load lock 12 2 11 906 0.0
SQL*Net more data to client 113 0 7 66 0.0
library cache pin 7 0 5 707 0.0
SQL*Net break/reset to clien 198 0 3 15 0.0
LGWR wait for redo copy 662 250 3 4 0.0
db file single write 33 0 1 17 0.0
log file single write 12 0 0 12 0.0
async disk IO 47,491 0 0 0 0.2
db file parallel write 10,318 0 0 0 0.1
control file single write 15 0 0 1 0.0
undo segment extension 5,760 5,760 0 0 0.0
direct path read 100 0 0 0 0.0
direct path write 101 0 0 0 0.0
buffer deadlock 7 7 0 0 0.0
SQL*Net message from client 1,161,156 0 3,137,645 2702 6.0
wakeup time manager 1,298 1,297 37,303 28739 0.0
jobq slave wait 7,963 7,863 23,232 2918 0.0
SQL*Net message to client 1,161,239 0 9 0 6.0
SQL*Net more data from clien 6 0 0 0 0.0
log file parallel write 218,720 0 3,580 16 1.1
control file parallel write 12,993 0 1,008 78 0.1
latch free 2,701 41 218 81 0.0
log file sequential read 1,016 0 156 154 0.0
db file scattered read 534 0 16 30 0.0
control file sequential read 4,271 0 11 3 0.0
enqueue 8 1 6 766 0.0
db file sequential read 148 0 5 36 0.0
rdbms ipc reply 156 0 3 21 0.0
LGWR wait for redo copy 662 250 3 4 0.0
buffer busy waits 8 0 0 23 0.0
log file single write 12 0 0 12 0.0
db file parallel write 10,318 0 0 0 0.1
async disk IO 1,972 0 0 0 0.0
direct path read 100 0 0 0 0.0
direct path write 100 0 0 0 0.0
rdbms ipc message 264,617 52,829 215,838 816 1.4
pmon timer 16,732 16,363 38,732 2315 0.1
smon timer 137 126 37,181 ###### 0.0
-------------------------------------------------------------
and this:
session pga memory 60,568,108 1,521.5 313.7
session pga memory max 48,536,592 1,219.3 251.4
session uga memory 554,074,333,236 13,919,017.6 2,869,840.3
session uga memory max 151,716,720 3,811.3 785.8
What do you think my problem could be?
ThanksI have gathered statspack report. A this is a part of it: I have gathered statspack report. A this is a part of it:
Last edited by marist89; 05-07-2005 at 06:18 AM.
-
What do you think my problem could be?
let me guess:
A) you didn't post this using code tags, so it's unreadable (actually that's my problem)
B) your husband/wife/boyfriend/girlfriend has/hasn't left you
C) you didn't say anything about your system, size, use etc etc
D) your windows need painting (hate that job)
E) we have no idea how long and under what circumstances the statspack was run
F) your dog/cat is sick
G) your end-users are complaining
H) you've been fired
J) all of the above
High "log file sync" would suggest that the commit of updates may be delayed - users might complain of slow updates. Is that happening?
If it's a problem (i.e. your system is update intensive), then putting the log files on fast disks, separate from all other activity is the probably answer.
High "db file scattered read" suggests FTS - this may or may not be a problem.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
sorry
this is the daily output. system has 2GB RAM and SGA is 800MB. I have lots of swaping.
system is update intensive.
Code:
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
log file sync 192,878 4,564 18.72
db file scattered read 3,478,398 4,071 16.70
CPU time 3,867 15.87
log file parallel write 218,733 3,580 14.69
latch free 44,897 3,480 14.28
-------------------------------------------------------------
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 192,878 468 4,564 24 1.0
db file scattered read 3,478,398 0 4,071 1 18.0
log file parallel write 218,733 0 3,580 16 1.1
latch free 44,897 16,452 3,480 78 0.2
db file sequential read 2,719,216 0 2,160 1 14.1
control file parallel write 13,277 0 1,021 77 0.1
db file parallel read 29,206 0 559 19 0.2
buffer busy waits 32,484 83 449 14 0.2
log file sequential read 1,097 0 171 156 0.0
process startup 502 118 145 289 0.0
enqueue 176 33 141 799 0.0
log file switch completion 75 54 62 828 0.0
log buffer space 107 6 25 236 0.0
switch logfile command 7 3 18 2570 0.0
control file sequential read 13,252 0 12 1 0.1
row cache lock 31 1 12 395 0.0
library cache load lock 12 2 11 906 0.0
SQL*Net more data to client 113 0 7 66 0.0
library cache pin 7 0 5 707 0.0
SQL*Net break/reset to clien 198 0 3 15 0.0
LGWR wait for redo copy 662 250 3 4 0.0
db file single write 33 0 1 17 0.0
log file single write 12 0 0 12 0.0
async disk IO 47,491 0 0 0 0.2
db file parallel write 10,318 0 0 0 0.1
control file single write 15 0 0 1 0.0
undo segment extension 5,760 5,760 0 0 0.0
direct path read 100 0 0 0 0.0
direct path write 101 0 0 0 0.0
buffer deadlock 7 7 0 0 0.0
SQL*Net message from client 1,161,156 0 3,137,645 2702 6.0
wakeup time manager 1,298 1,297 37,303 28739 0.0
jobq slave wait 7,963 7,863 23,232 2918 0.0
SQL*Net message to client 1,161,239 0 9 0 6.0
SQL*Net more data from clien 6 0 0 0 0.0
-------------------------------------------------------------
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 218,720 0 3,580 16 1.1
control file parallel write 12,993 0 1,008 78 0.1
latch free 2,701 41 218 81 0.0
log file sequential read 1,016 0 156 154 0.0
db file scattered read 534 0 16 30 0.0
control file sequential read 4,271 0 11 3 0.0
enqueue 8 1 6 766 0.0
db file sequential read 148 0 5 36 0.0
rdbms ipc reply 156 0 3 21 0.0
LGWR wait for redo copy 662 250 3 4 0.0
buffer busy waits 8 0 0 23 0.0
log file single write 12 0 0 12 0.0
db file parallel write 10,318 0 0 0 0.1
async disk IO 1,972 0 0 0 0.0
direct path read 100 0 0 0 0.0
direct path write 100 0 0 0 0.0
rdbms ipc message 264,617 52,829 215,838 816 1.4
pmon timer 16,732 16,363 38,732 2315 0.1
smon timer 137 126 37,181 ###### 0.0
-------------------------------------------------------------
session pga memory 60,568,108 1,521.5 313.7
session pga memory max 48,536,592 1,219.3 251.4
session uga memory 554,074,333,236 13,919,017.6 2,869,840.3
session uga memory max 151,716,720 3,811.3 785.8
-
If you mean it's over many hours, then it's far too long. Tom Kyte can explain better than I can:
http://asktom.oracle.com/pls/ask/f?p...:7641015793792
The average wait times are tens of miliseconds - do end-users actually see slow performance? (could indicate big peak values).
Code:
I have lots of swaping
session pga memory 60,568,108 1,521.5 313.7
session pga memory max 48,536,592 1,219.3 251.4
session uga memory 554,074,333,236 13,919,017.6 2,869,840.3
session uga memory max 151,716,720 3,811.3 785.8
But I don't think that is meaningful for "daily" statspack.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Do you have a lot of hard parses? (you didn't post that part of the output). They can contribute to CPU and latch waits.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
what do you think about that ?
Code:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.97 Redo NoWait %: 99.99
Buffer Hit %: 86.22 In-memory Sort %: 100.00
Library Hit %: 99.78 Soft Parse %: 98.50
Execute to Parse %: 93.10 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 2.59 % Non-Parse CPU: 99.71
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.24 96.28
% SQL with executions>1: 62.19 77.55
% Memory for SQL w/exec>1: 54.38 59.81
-
Originally posted by DaPi
do end-users actually see slow performance?
Yeah, this is a good question. I see some things I don't like for my systems, but for yours it may be OK. Also, this snapshot may include some batch jobs that skew the numbers. Lets see a snapshot of 5-10 minutes during a time the users are complaining that things are slow.
Jeff Hunter
-
Originally posted by D.D.
what do you think about that ?
Parse CPU to Parse Elapsd %: 2.59
How often are you parsing? If it's rare, it may not be a problem.
If you're parsing frequently:
- that's bad by itself
- parsing is serialised, so 97% of the elapsed may be due to waiting on other parses!
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Hi Hunies,
Im just curious about DDs SGA, its 800Mb and he got only
2Gb RAM. I guess its quite big dont u think?
You know dearest, here in our system, we got 3Gb RAM and
i only alot 150Mb for shared pool, coz when i increase it
more, I felt the server perfomance is getting slower. I dont
know if im only hallucinating
By the way DD dear, how did u came up with that 800Mb SGA?
Thanks a lot
Ms C3
-
Originally posted by kris123
You know dearest, here in our system, we got 3Gb RAM and
i only alot 150Mb for shared pool, coz when i increase it
more,....
I'm not sure you realy understand what SGA is, what shared pool is and what the relation between the two is. Because the above statement about your RAM and your shared pool sizing is totaly meaningles....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|