statspack wait events
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: statspack wait events

  1. #1
    Join Date
    Jun 2006
    Posts
    1

    statspack wait events

    i Guy,
    My database is 10g and and my database is running in auto tuneing,i.e I kept in sga_target,
    and my application is hanging ,can any body help me in finding why cpu is high and
    what is parse cpu to parsed in statspack.
    Code:
    STATSPACK report for
    
    DB Name         DB Id    Instance     Inst Num Release     RAC Host
    ------------ ----------- ------------ -------- ----------- --- ----------------
    q         xxxxxx q               1 10.1.0.4.0  NO  q
    
                  Snap Id     Snap Time      Sessions Curs/Sess Comment
                --------- ------------------ -------- --------- -------------------
    Begin Snap:      1580 20-Jun-06 17:42:03      109     384.0
      End Snap:      1582 20-Jun-06 18:02:05       97     436.7
       Elapsed:               20.03 (mins)
    
    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
                   Buffer Cache:     1,472M      Std Block Size:         8K
               Shared Pool Size:       416M          Log Buffer:       256K
    
    Load Profile
    ~~~~~~~~~~~~                            Per Second       Per Transaction
                                       ---------------       ---------------
                      Redo size:              4,074.00             15,160.82
                  Logical reads:                 74.22                276.19
                  Block changes:                 13.37                 49.74
                 Physical reads:                  0.68                  2.53
                Physical writes:                  0.97                  3.61
                     User calls:                 39.07                145.41
                         Parses:                  9.41                 35.01
                    Hard parses:                  0.04                  0.17
                          Sorts:                  1.81                  6.73
                         Logons:                  0.12                  0.43
                       Executes:                 14.69                 54.67
                   Transactions:                  0.27
    
      % Blocks changed per Read:   18.01    Recursive Call %:    31.96
     Rollback per transaction %:   36.53       Rows per Sort:    62.95
    
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                Buffer  Hit   %:   99.08    In-memory Sort %:  100.00
                Library Hit   %:   99.68        Soft Parse %:   99.52
             Execute to Parse %:   35.96         Latch Hit %:  100.00
    Parse CPU to Parse Elapsd %:   86.49     % Non-Parse CPU:   97.00
    
     Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   93.61   93.66
        % SQL with executions>1:   35.62   35.76
      % Memory for SQL w/exec>1:   61.91   61.94
    
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                      % Total
    Event                                               Waits    Time (s) Call Time
    -------------------------------------------- ------------ ----------- ---------
    CPU time                                                           21     83.14
    db file sequential read                               724           2      9.42
    process startup                                        12           1      1.95
    log file parallel write                               336           0      1.82
    control file parallel write                           400           0      1.32
              -------------------------------------------------------------
    Wait Events  DB/Inst: q/q  Snaps: 1580-1582
    -> 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               724          0          2      3      2.2
    process startup                        12          0          1     42      0.0
    log file parallel write               336          0          0      1      1.0
    control file parallel write           400          0          0      1      1.2
    SQL*Net more data to client         1,635          0          0      0      5.1
    log buffer space                        2          0          0     62      0.0
    log file sync                         115          0          0      1      0.4
    db file parallel read                  11          0          0      8      0.0
    change tracking file synchro           19          0          0      1      0.1
    control file sequential read        2,020          0          0      0      6.3
    latch: library cache                   68          0          0      0      0.2
    SQL*Net break/reset to clien            6          0          0      0      0.0
    rdbms ipc reply                         4          0          0      1      0.0
    db file parallel write                712          0          0      0      2.2
    change tracking file synchro           19          0          0      0      0.1
    buffer busy waits                       1          0          0      0      0.0
    db file scattered read                  1          0          0      0      0.0
    latch: shared pool                      2          0          0      0      0.0
    latch: redo allocation                  1          0          0      0      0.0
    virtual circuit status             47,614         37      2,292     48    147.4
    SQL*Net message from client        49,039          0      1,584     32    151.8
    Queue Monitor Wait                     40         40      1,149  28726      0.1
    jobq slave wait                       375        368      1,087   2898      1.2
    wakeup time manager                     4          0        915 ######      0.0
    SQL*Net message to client          49,039          0          0      0    151.8
    SQL*Net more data from clien           22          0          0      0      0.1
              -------------------------------------------------------------
    Background Wait Events  DB/Inst: q/q  Snaps: 1580-1582
    Last edited by davey23uk; 06-20-2006 at 08:28 AM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Have you ever heard of bind variables?
    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."

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    Short of rewriting app to utilize bind variables: (Bind variables would be the best)

    Review the following parameters:
    Cursor_sharing
    Cursor_space_for_time
    Last edited by ixion; 06-20-2006 at 12:06 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Those are bandaids to the symtoms. Fix the problem.
    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
    Jun 2006
    Posts
    259
    Indeed... Short term bandaid. Long term solution.

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    I don't know if I'm being stupid here, but have you looked at the STATSPACK report that the poster attached? All the SQL seems to use bind variables and % Non-Parse CPU = 99.88. I don't think bind variables are the issue here, although I could be wrong?
    Last edited by hacketta1; 06-21-2006 at 08:56 AM. Reason: Bad Grammar!

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    First the report in the .txt file spans 2 hours - you should try to target a bad period of 10-15 mins to simplify things.

    Secondly what is happening here?
    Code:
    Instance Activity Stats  DB/Inst: KTPRO/ktpro  Snaps: 1552-1564
    
    Statistic                                      Total     per Second    per Trans
    --------------------------------- ------------------ -------------- ------------
    
    bytes sent via SQL*Net to client     292,477,608,057   40,599,334.8 ############
    "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

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    292Gb in 2 hours! wow, 40Mb a second

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . also your two reports are very different: the "in-line" one shows 21 seconds CPU in 20 minutes, the .txt file shows 8'798 second in 120 minutes and only one CPU ! ! ! (probably an effect of a long transaction finishing in the time slot).
    "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
    Jun 2006
    Posts
    259
    Time Model System Stats DB/Inst: KTPRO/ktpro Snaps: 1552-1564
    -> Total Time in Database calls 9967.6s (or 9967640228us)

    Statistic Time (s) % of DB Time
    ----------------------------------- -------------------- ------------
    DB CPU 6,858.6 68.8
    DB time 9,967.6
    PL/SQL compilation elapsed time 4.9 .0
    PL/SQL execution elapsed time 9,218.9 92.5
    background cpu time 15.0 .2
    background elapsed time 126.6 1.3
    connection management call elapsed 11.4 .1
    failed parse elapsed time 0.2 .0
    hard parse (bind mismatch) elapsed 0.0 .0
    hard parse (sharing criteria) elaps 0.1 .0
    hard parse elapsed time 3.8 .0
    inbound PL/SQL rpc elapsed time 9,244.5 92.7
    parse time elapsed 17.4 .2
    sequence load elapsed time 0.3 .0
    sql execute elapsed time 1,796.7 18.0
    -------------------------------------------------------------


    From the posted file 2 hour window. It looks like your app is transmitting most/ if not all of the PL/SQL to be executed into the server. Hence the high sql net activity.

    What type of system and How many CPU's?

    Can you modify the application to utilize named procedures/packages instead of anonymous procedures? Then use compiled PL/SQL and ping the packages.

    BAD SQL:
    SELECT ABS(:b1),ABS(:b2) FROM DUAL ----> Rewrite this, you don't need the DB to do this math work.
    There seems to be a lot of that type thing. Each one by itself is not a lot, but it adds up. If you don't have to select the data, don't!
    Instead:

    var2 = abs(var1) --- If this is embedded in the app. Then no round trip to the DB is required... 100 x faster.

    or if in PL/SQL
    var2 := abs(:var1) ; (this is a function call and requires less overhead)

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