-
opinions on statspack
Hi all
below is results from my statspack and I would like get some opinion from yous guys to be sure that my analysis is correct:
Code:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,137.20 566,328.00
Logical reads: 5,048.43 2,514,118.00
Block changes: 2.22 1,105.00
Physical reads: 3,500.12 1,743,061.00
Physical writes: 9.47 4,718.00
User calls: 1.41 702.00
Parses: 1.20 600.00
Hard parses: 0.03 15.00
Sorts: 0.91 451.00
Logons: 0.00 0.00
Executes: 1.91 951.00
Transactions: 0.00
% Blocks changed per Read: 0.04 Recursive Call %: 89.55
Rollback per transaction %: 0.00 Rows per Sort: 640.81
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 30.84 In-memory Sort %: 99.11
Library Hit %: 88.40 Soft Parse %: 97.50
Execute to Parse %: 36.91 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 82.61 % Non-Parse CPU: 99.80
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.86 93.88
% SQL with executions>1: 60.98 62.99
% Memory for SQL w/exec>1: 32.95 35.39
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 97 75.94
db file sequential read 1,532,454 28 22.23
db file scattered read 23,868 2 1.61
control file parallel write 162 0 .15
db file parallel read 626 0 .03
-------------------------------------------------------------
Wait Events for DB: TEST Instance: TEST Snaps: 36 -37
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 1,532,454 0 28 0 ########
db file scattered read 23,868 0 2 0 ########
control file parallel write 162 0 0 1 162.0
db file parallel read 626 0 0 0 626.0
latch free 7 2 0 3 7.0
log file sync 2 0 0 8 2.0
SQL*Net more data to client 101 0 0 0 101.0
control file sequential read 122 0 0 0 122.0
direct path read 420 0 0 0 420.0
direct path write 260 0 0 0 260.0
log file parallel write 31 31 0 0 31.0
db file parallel write 6 0 0 0 6.0
LGWR wait for redo copy 2 0 0 0 2.0
SQL*Net message from client 641 0 1,733 2704 641.0
SQL*Net message to client 641 0 0 0 641.0
-------------------------------------------------------------
I think I have problem in I/O but there is nothing much I can do b/c this server only have one RAId controller and it runs on RAID 5. If I have more than RAID array that would give me option to balance out the loads. Basically, this report was captured only during the READ period from the map. Other things I want to ask is the buffer hit ratio is faily low. Any other comment you can see or advises????
thanks and have a great weekend everyone.
Last edited by hannah00; 04-24-2005 at 10:08 AM.
-
but db file sequential read was only 22% of the total wait.
CPU time was your biggest problem
-
Thanks Davey,
what could cause CPU high???? but when the user perform the operation from the map and I look at the the amount of CPU on the server it's used no more than 50% at the given time.
Please give me more input.
thanks,
-
Hi,
Check for queries doing high buffer read,disk reads. There must be queires doing lot of HOT BLOCK READS.
Your queries must be reading too much of data.
regards
anandkl
anandkl
-
With high "CPU time" and high "db file sequential read" I'd probably start looking for queries that had excessive LIOs from misused indexes.
Jeff Hunter
-
first of all, thanks for the value advises.
the SQL statement was issued directly from the third party tool (ESRI, if anyone is familiar with GIS), any way, on STATSPACK, it gave me the incomplete query so you think I should set the SQL trace so I can get the SQL statement???
-
unless you know the query plan, I don't know how you would diagnose it.
Jeff Hunter
-
Hi Hannah
Davey and Jeff are both correct in thier analysis..
However what type of quesries do you run..mostly read only ? then raid 5 is good elese you should be looking raid 1+0
Did you take the snap when the period of activity was high ?
You can upload your statspack repotrt here and have it analyzed for free
regards
Hrishy
Last edited by hrishy; 04-23-2005 at 02:52 AM.
-
Originally posted by hannah00
. . . . I look at the the amount of CPU on the server it's used no more than 50% at the given time.
Is this a dual-processor machine? That's pretty much what you'd expect if one processor was running near 100% most of the time.
(I'll join the others in saying you need at least an explain plan on the (or a typical) query - if you can't do that, set trace or event 10046 and tkprof or trace-analyze the output).
"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
-
First you should not post incomplete statspack report, and start asking questions.
Tamil
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
|