DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: statspack report

  1. #1
    Join Date
    Jun 2006
    Posts
    101

    statspack report

    Are there any performance issue in this report ?

    Code:
    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
                   Buffer Cache:       500M      Std Block Size:         8K
               Shared Pool Size:       108M          Log Buffer:       512K
    
    Load Profile
    ~~~~~~~~~~~~                            Per Second       Per Transaction
                                       ---------------       ---------------
                      Redo size:             21,511.13              7,472.74
                  Logical reads:              1,263.32                438.86
                  Block changes:                135.43                 47.05
                 Physical reads:                  3.05                  1.06
                Physical writes:                 24.90                  8.65
                     User calls:                114.00                 39.60
                         Parses:                 91.83                 31.90
                    Hard parses:                 10.95                  3.80
                          Sorts:                 47.66                 16.56
                         Logons:                  0.09                  0.03
                       Executes:                275.94                 95.86
                   Transactions:                  2.88
    
      % Blocks changed per Read:   10.72    Recursive Call %:    97.05
     Rollback per transaction %:   10.06       Rows per Sort:    21.55
    
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                Buffer  Hit   %:   99.93    In-memory Sort %:   99.97
                Library Hit   %:   93.05        Soft Parse %:   88.08
             Execute to Parse %:   66.72         Latch Hit %:   99.85
    Parse CPU to Parse Elapsd %:   93.84     % Non-Parse CPU:   70.19
    
     Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   85.21   86.70
        % SQL with executions>1:   61.27   66.46
      % Memory for SQL w/exec>1:   72.19   73.26
    
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                      % Total
    Event                                               Waits    Time (s) Call Time
    -------------------------------------------- ------------ ----------- ---------
    CPU time                                                          227     57.48
    class slave wait                                       13          65     16.48
    Queue Monitor Task Wait                                13          65     16.47
    log file sync                                       9,825          13      3.38
    SQL*Net more data to client                        58,272          11      2.76
              -------------------------------------------------------------
    Wait Events  DB/Inst: PKAHEP01/PKAHEP01  Snaps: 71-111
    -> 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
    ---------------------------- ------------ ---------- ---------- ------ --------
    class slave wait                       13         13         65   5006      0.0
    Queue Monitor Task Wait                13         10         65   5005      0.0
    log file sync                       9,825          0         13      1      0.9
    SQL*Net more data to client        58,272          0         11      0      5.6
    db file sequential read             3,010          0          8      3      0.3
    library cache pin                     208          0          2     11      0.0
    control file parallel write         1,358          0          1      1      0.1
    process startup                        15          0          1     45      0.0
    db file scattered read                 29          0          0      7      0.0
    log buffer space                       11          0          0     17      0.0
    latch: shared pool                  1,451          0          0      0      0.1
    enq: TX - row lock contentio            7          0          0     26      0.0
    library cache load lock                58          0          0      2      0.0
    log file switch completion              7          0          0     12      0.0
    latch free                              5          0          0     12      0.0
    row cache lock                        290          0          0      0      0.0
    latch: library cache                  286          0          0      0      0.0
    control file sequential read        2,945          0          0      0      0.3
    control file single write              59          0          0      0      0.0
    SQL*Net break/reset to clien            6          0          0      3      0.0
    kksfbc child completion                 1          1          0     17      0.0
    log file parallel write            10,460          0          0      0      1.0
    latch: session allocation              39          0          0      0      0.0
    latch: row cache objects               29          0          0      0      0.0
    enq: TX - index contention              1          0          0      6      0.0
    log file single write                  16          0          0      0      0.0
    LGWR wait for redo copy               131          0          0      0      0.0
    latch: library cache pin                5          0          0      0      0.0
    latch: library cache lock               8          0          0      0      0.0
    direct path read temp               1,582          0          0      0      0.2
    direct path write temp              3,000          0          0      0      0.3
    buffer busy waits                       7          0          0      0      0.0
    log file sequential read               16          0          0      0      0.0
    rdbms ipc reply                         1          0          0      0      0.0
    direct path read                       40          0          0      0      0.0
    latch: In memory undo latch             2          0          0      0      0.0
    latch: redo writing                     2          0          0      0      0.0
    direct path write                      44          0          0      0      0.0
    SQL*Net message from client       279,209          0    108,927    390     26.9
    Queue Monitor Wait                    115        115      3,430  29828      0.0
    wakeup time manager                    12          0      3,095 ######      0.0
    Queue Monitor Slave Wait                5          5        300  60008      0.0
    SQL*Net more data from clien       22,230          0          7      0      2.1
    SQL*Net message to client         279,214          0          0      0     26.9
              -------------------------------------------------------------
    Background Wait Events  DB/Inst: PKAHEP01/PKAHEP01  Snaps: 71-111
    -> ordered by wait time desc, waits desc (idle events last)
    
                                                                       Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    Queue Monitor Task Wait                13         10         65   5005      0.0
    class slave wait                        1          1          5   5010      0.0
    control file parallel write         1,358          0          1      1      0.1
    process startup                        15          0          1     45      0.0
    control file single write              59          0          0      0      0.0
    log file parallel write            10,460          0          0      0      1.0
    control file sequential read          260          0          0      0      0.0
    log file single write                  16          0          0      0      0.0
    LGWR wait for redo copy               131          0          0      0      0.0
    latch: shared pool                     23          0          0      0      0.0
    latch: library cache                    4          0          0      0      0.0
    Wait Event Histogram  DB/Inst: PKAHEP01/PKAHEP01  Snaps: 71-111
    -> ordered by event (idle events last)
    
    SQL ordered by Gets  DB/Inst: PKAHEP01/PKAHEP01  Snaps: 71-111
    -> Resources reported for PL/SQL code includes the resources used by all SQL
       statements called by the code.
    -> End Buffer Gets Threshold:     10000 Total Buffer Gets:       4,547,944
    -> Captured SQL accounts for   50.3% of Total Buffer Gets
    -> SQL reported below exceeded  1.0% of Total Buffer Gets
    
                                                         CPU      Elapsd     Old
      Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
            941,500      315,510            3.0   20.7     7.35      7.38 4274598960
    select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
    estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
    density, col#, spare1, spare2, avgcln from hist_head$ where obj#
    =:1 and intcol#=:2
    Last edited by gandolf989; 05-31-2007 at 04:49 PM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you tell us, are you suffering performance problems? and is there a reason you cant post using the code tahe to at least make it barely readable?

  3. #3
    Join Date
    Jun 2006
    Posts
    101
    what is code tahe?
    im doing some testing, there isnt major performance issue as far as we are aware but ned to know if there is anything I could do as this db has never been tuned / looked after,

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by khoshtip_uk
    what is code tahe?
    im doing some testing, there isnt major performance issue as far as we are aware but ned to know if there is anything I could do as this db has never been tuned / looked after,
    You could put the database into sql_trace mode, then look for the queries that take the most time, resources, etc, and tune them to use less resources, look at fewer rows, etc. There are many books dedicated to the topic of tuning. No one is going to be able to give you an answer to your question based on a single statspack report.

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