opinions on statspack
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: opinions on statspack

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    but db file sequential read was only 22% of the total wait.

    CPU time was your biggest problem

  3. #3
    Join Date
    Jan 2005
    Posts
    221
    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,

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jan 2005
    Posts
    221
    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???

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    unless you know the query plan, I don't know how you would diagnose it.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    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.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
  •  



Click Here to Expand Forum to Full Width