statspack top 5 wait event
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: statspack top 5 wait event

  1. #1
    Join Date
    Mar 2005
    Posts
    26

    statspack top 5 wait event

    iam looking in statspack report in oracle 8.1.7.4, can anybody tell me in top 5 wait event, how to know, anything is abnormal..
    some time i find different top 5 wait event every time in statpack report... well any body can tell me, what are thing i need to consider in top 5 wait event.... and how to analyze, whether is there any default percent in total wt time to consider as normal..for the these events
    db file scattered read,db file sequential read,db file parallel write,log file parallel write,log file sync,latchfree, enqueues and soon..... ... please reply me i will be very grateful...

    Code:
    Top 5 Wait Events 
    ~~~~~~~~~~~~~~~~                                             Wait     % Total 
    Event                                               Waits  Time (cs)   Wt Time 
    -------------------------------------------- ------------ ------------ ------- 
    db file scattered read                            686,446      841,612   28.68 
    db file sequential read                           560,899      540,532   18.42 
    db file parallel write                             26,270      277,926    9.47 
    log file parallel write                           289,226      262,024    8.93 
    log file sync                                     184,512      240,604    8.20 
              -------------------------------------------------------------
    Last edited by marist89; 05-12-2005 at 12:18 PM.

  2. #2
    Join Date
    Jan 2005
    Posts
    221
    I think the info you gave us is incomplete but you have FTS, disk i/O. You may want to give us more information.

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    How long did you run statspack for? Are you looking for something in particular that is running slowly? THere are no "silver bullet" percentages to look out for. It is totally application specific.
    Assistance is Futile...

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    This may be perfectly fine or horrible. Please post the header information for statspack
    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."

  5. #5
    Join Date
    Jan 2005
    Posts
    221
    Jeff,

    could you please let us know in what case it's perfectly fine and in what case it's horrible.

    I would like to know speically guys like Jeff


    thanks so much

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by hannah00
    could you please let us know in what case it's perfectly fine and in what case it's horrible.

    If this were an OLAP system or a OLTP during a nightly reporting phase, a high number of "db file scattered read" waits might be perfectly reasonable. In an OLTP system during normal transactional operation, I would think this is a high number of "db file scattered read" waits and some query probablyneeds to be optimized.
    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."

  7. #7
    Join Date
    Mar 2005
    Posts
    26
    statspack detail report...report will generate daily 2 time, one is morning 7AM and another evening 7PM..
    Code:
    DB Name         DB Id    Instance     Inst Num Release     OPS Host 
    ------------ ----------- ------------ -------- ----------- --- ------------ 
    xxxxxxxxx     130952321 xxxxxxxxx            1 8.1.7.4.0   NO  xxxxxxxx 
                                                                   1 
    
                    Snap Id     Snap Time      Sessions 
                    ------- ------------------ -------- 
     Begin Snap:      26152 25-Apr-05 06:00:14      167 
       End Snap:      26164 25-Apr-05 18:00:17      167 
        Elapsed:                 720.05 (mins) 
    
    Cache Sizes 
    ~~~~~~~~~~ 
               db_block_buffers:     294912          log_buffer:     491520 
                  db_block_size:      16384    shared_pool_size:  419430400 
    
    Load Profile 
    ~~~~~~~~~~~                            Per Second       Per Transaction 
                                       ---------------       --------------- 
                      Redo size:            266,846.91             61,006.21 
                  Logical reads:              8,035.94              1,837.17 
                  Block changes:              1,418.57                324.31 
                 Physical reads:                269.16                 61.54 
                Physical writes:                 88.84                 20.31 
                     User calls:                408.26                 93.33 
                         Parses:                172.62                 39.47 
                    Hard parses:                  1.10                  0.25 
                          Sorts:                  2.67                  0.61 
                         Logons:                  0.20                  0.05 
                       Executes:                271.14                 61.99 
                   Transactions:                  4.37 
    
      % Blocks changed per Read:   17.65    Recursive Call %:   50.83 
     Rollback per transaction %:    0.19       Rows per Sort:  624.25 
    
    Instance Efficiency Percentages (Target 100%) 
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
                Buffer Nowait %:   99.97       Redo NoWait %:  100.00 
                Buffer  Hit   %:   96.65    In-memory Sort %:   99.66 
                Library Hit   %:   99.58        Soft Parse %:   99.36 
             Execute to Parse %:   36.33         Latch Hit %:   99.80 
    Parse CPU to Parse Elapsd %:   81.24     % Non-Parse CPU:   99.97 
    
     Shared Pool Statistics        Begin   End 
                                   ------  ------ 
                 Memory Usage %:   91.85   91.70 
        % SQL with executions>1:   48.13   55.18 
      % Memory for SQL w/exec>1:    4.65   41.82 
    
    Top 5 Wait Events 
    ~~~~~~~~~~~~~~~~                                             Wait     % Total 
    Event                                               Waits  Time (cs)   Wt Time 
    -------------------------------------------- ------------ ------------ ------- 
    db file scattered read                            686,446      841,612   28.68 
    db file sequential read                           560,899      540,532   18.42 
    db file parallel write                             26,270      277,926    9.47 
    log file parallel write                           289,226      262,024    8.93 
    log file sync                                     184,512      240,604    8.20 
              ------------------------------------------------------------- 
    Wait Events for DB: xxxxxxxx  Instance: xxxxxx  Snaps:   26152 -  26164 
    -> cs - centisecond -  100th of a second 
    -> ms - millisecond - 1000th of a second 
    -> ordered by wait time desc, waits desc (idle events last) 
    
      
    
      Buffer Gets    Executions  Gets per Exec  % Total  Hash Value 
    --------------- ------------ -------------- ------- ------------ 
         31,931,732          175      182,467.0     9.2     65569463
    Last edited by marist89; 05-12-2005 at 10:47 PM.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    This window is way too big. It is impossible to tell anything. Try 15 minute intervals.
    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."

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    This window is way too big. It is impossible to tell anything. Try 15 minute intervals.
    Sounds good.

    Also, the average wait on the db file parallel write looks a little high, which means your i/o is a little slow. What o/s is this running on?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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