opinions on statspack

[hannah00]

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:

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.

[davey23uk]

but db file sequential read was only 22% of the total wait.

CPU time was your biggest problem

[hannah00]

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,

[anandkl]

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

[marist89]

With high "CPU time" and high "db file sequential read" I'd probably start looking for queries that had excessive LIOs from misused indexes.

[hannah00]

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???

[marist89]

unless you know the query plan, I don't know how you would diagnose it.

[hrishy]

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

[DaPi]

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).

[tamilselvan]

First you should not post incomplete statspack report, and start asking questions.

Tamil