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

Thread: statspack oppinion

  1. #1
    Join Date
    Dec 2003
    Posts
    21

    statspack oppinion

    I have gathered statspack report. A this is a part of it:

    Code:
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                     % Total
    Event                                               Waits    Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    log file sync                                     192,878       4,564    18.72
    db file scattered read                          3,478,398       4,071    16.70
    CPU time                                                        3,867    15.87
    log file parallel write                           218,733       3,580    14.69
    latch free                                         44,897       3,480    14.28
              
                                                                       Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log file sync                     192,878        468      4,564     24      1.0
    db file scattered read          3,478,398          0      4,071      1     18.0
    log file parallel write           218,733          0      3,580     16      1.1
    latch free                         44,897     16,452      3,480     78      0.2
    db file sequential read         2,719,216          0      2,160      1     14.1
    control file parallel write        13,277          0      1,021     77      0.1
    db file parallel read              29,206          0        559     19      0.2
    buffer busy waits                  32,484         83        449     14      0.2
    log file sequential read            1,097          0        171    156      0.0
    process startup                       502        118        145    289      0.0
    enqueue                               176         33        141    799      0.0
    log file switch completion             75         54         62    828      0.0
    log buffer space                      107          6         25    236      0.0
    switch logfile command                  7          3         18   2570      0.0
    control file sequential read       13,252          0         12      1      0.1
    row cache lock                         31          1         12    395      0.0
    library cache load lock                12          2         11    906      0.0
    SQL*Net more data to client           113          0          7     66      0.0
    library cache pin                       7          0          5    707      0.0
    SQL*Net break/reset to clien          198          0          3     15      0.0
    LGWR wait for redo copy               662        250          3      4      0.0
    db file single write                   33          0          1     17      0.0
    log file single write                  12          0          0     12      0.0
    async disk IO                      47,491          0          0      0      0.2
    db file parallel write             10,318          0          0      0      0.1
    control file single write              15          0          0      1      0.0
    undo segment extension              5,760      5,760          0      0      0.0
    direct path read                      100          0          0      0      0.0
    direct path write                     101          0          0      0      0.0
    buffer deadlock                         7          7          0      0      0.0
    SQL*Net message from client     1,161,156          0  3,137,645   2702      6.0
    wakeup time manager                 1,298      1,297     37,303  28739      0.0
    jobq slave wait                     7,963      7,863     23,232   2918      0.0
    SQL*Net message to client       1,161,239          0          9      0      6.0
    SQL*Net more data from clien            6          0          0      0      0.0
    
    log file parallel write           218,720          0      3,580     16      1.1
    control file parallel write        12,993          0      1,008     78      0.1
    latch free                          2,701         41        218     81      0.0
    log file sequential read            1,016          0        156    154      0.0
    db file scattered read                534          0         16     30      0.0
    control file sequential read        4,271          0         11      3      0.0
    enqueue                                 8          1          6    766      0.0
    db file sequential read               148          0          5     36      0.0
    rdbms ipc reply                       156          0          3     21      0.0
    LGWR wait for redo copy               662        250          3      4      0.0
    buffer busy waits                       8          0          0     23      0.0
    log file single write                  12          0          0     12      0.0
    db file parallel write             10,318          0          0      0      0.1
    async disk IO                       1,972          0          0      0      0.0
    direct path read                      100          0          0      0      0.0
    direct path write                     100          0          0      0      0.0
    rdbms ipc message                 264,617     52,829    215,838    816      1.4
    pmon timer                         16,732     16,363     38,732   2315      0.1
    smon timer                            137        126     37,181 ######      0.0
              -------------------------------------------------------------
    
    and this:
    
    session pga memory                        60,568,108        1,521.5        313.7
    session pga memory max                    48,536,592        1,219.3        251.4
    session uga memory                   554,074,333,236   13,919,017.6  2,869,840.3
    session uga memory max                   151,716,720        3,811.3        785.8
    What do you think my problem could be?

    ThanksI have gathered statspack report. A this is a part of it: I have gathered statspack report. A this is a part of it:
    Last edited by marist89; 05-07-2005 at 06:18 AM.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    What do you think my problem could be?
    let me guess:
    A) you didn't post this using code tags, so it's unreadable (actually that's my problem)
    B) your husband/wife/boyfriend/girlfriend has/hasn't left you
    C) you didn't say anything about your system, size, use etc etc
    D) your windows need painting (hate that job)
    E) we have no idea how long and under what circumstances the statspack was run
    F) your dog/cat is sick
    G) your end-users are complaining
    H) you've been fired
    J) all of the above


    High "log file sync" would suggest that the commit of updates may be delayed - users might complain of slow updates. Is that happening?

    If it's a problem (i.e. your system is update intensive), then putting the log files on fast disks, separate from all other activity is the probably answer.

    High "db file scattered read" suggests FTS - this may or may not be a problem.
    "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

  3. #3
    Join Date
    Dec 2003
    Posts
    21
    sorry

    this is the daily output. system has 2GB RAM and SGA is 800MB. I have lots of swaping.

    system is update intensive.



    Code:
    Event                                               Waits    Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    log file sync                                     192,878       4,564    18.72
    db file scattered read                          3,478,398       4,071    16.70
    CPU time                                                        3,867    15.87
    log file parallel write                           218,733       3,580    14.69
    latch free                                         44,897       3,480    14.28
              -------------------------------------------------------------
    
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log file sync                     192,878        468      4,564     24      1.0
    db file scattered read          3,478,398          0      4,071      1     18.0
    log file parallel write           218,733          0      3,580     16      1.1
    latch free                         44,897     16,452      3,480     78      0.2
    db file sequential read         2,719,216          0      2,160      1     14.1
    control file parallel write        13,277          0      1,021     77      0.1
    db file parallel read              29,206          0        559     19      0.2
    buffer busy waits                  32,484         83        449     14      0.2
    log file sequential read            1,097          0        171    156      0.0
    process startup                       502        118        145    289      0.0
    enqueue                               176         33        141    799      0.0
    log file switch completion             75         54         62    828      0.0
    log buffer space                      107          6         25    236      0.0
    switch logfile command                  7          3         18   2570      0.0
    control file sequential read       13,252          0         12      1      0.1
    row cache lock                         31          1         12    395      0.0
    library cache load lock                12          2         11    906      0.0
    SQL*Net more data to client           113          0          7     66      0.0
    library cache pin                       7          0          5    707      0.0
    SQL*Net break/reset to clien          198          0          3     15      0.0
    LGWR wait for redo copy               662        250          3      4      0.0
    db file single write                   33          0          1     17      0.0
    log file single write                  12          0          0     12      0.0
    async disk IO                      47,491          0          0      0      0.2
    db file parallel write             10,318          0          0      0      0.1
    control file single write              15          0          0      1      0.0
    undo segment extension              5,760      5,760          0      0      0.0
    direct path read                      100          0          0      0      0.0
    direct path write                     101          0          0      0      0.0
    buffer deadlock                         7          7          0      0      0.0
    SQL*Net message from client     1,161,156          0  3,137,645   2702      6.0
    wakeup time manager                 1,298      1,297     37,303  28739      0.0
    jobq slave wait                     7,963      7,863     23,232   2918      0.0
    SQL*Net message to client       1,161,239          0          9      0      6.0
    SQL*Net more data from clien            6          0          0      0      0.0
              -------------------------------------------------------------
    
    
                                                                       Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log file parallel write           218,720          0      3,580     16      1.1
    control file parallel write        12,993          0      1,008     78      0.1
    latch free                          2,701         41        218     81      0.0
    log file sequential read            1,016          0        156    154      0.0
    db file scattered read                534          0         16     30      0.0
    control file sequential read        4,271          0         11      3      0.0
    enqueue                                 8          1          6    766      0.0
    db file sequential read               148          0          5     36      0.0
    rdbms ipc reply                       156          0          3     21      0.0
    LGWR wait for redo copy               662        250          3      4      0.0
    buffer busy waits                       8          0          0     23      0.0
    log file single write                  12          0          0     12      0.0
    db file parallel write             10,318          0          0      0      0.1
    async disk IO                       1,972          0          0      0      0.0
    direct path read                      100          0          0      0      0.0
    direct path write                     100          0          0      0      0.0
    rdbms ipc message                 264,617     52,829    215,838    816      1.4
    pmon timer                         16,732     16,363     38,732   2315      0.1
    smon timer                            137        126     37,181 ######      0.0
              -------------------------------------------------------------
    
    
    session pga memory                        60,568,108        1,521.5        313.7
    session pga memory max                    48,536,592        1,219.3        251.4
    session uga memory                   554,074,333,236   13,919,017.6  2,869,840.3
    session uga memory max                   151,716,720        3,811.3        785.8

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    this is the daily output
    If you mean it's over many hours, then it's far too long. Tom Kyte can explain better than I can:
    http://asktom.oracle.com/pls/ask/f?p...:7641015793792

    The average wait times are tens of miliseconds - do end-users actually see slow performance? (could indicate big peak values).

    Code:
    I have lots of swaping
    
    session pga memory                        60,568,108        1,521.5        313.7
    session pga memory max                    48,536,592        1,219.3        251.4
    session uga memory                   554,074,333,236   13,919,017.6  2,869,840.3
    session uga memory max                   151,716,720        3,811.3        785.8
    But I don't think that is meaningful for "daily" statspack.
    "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

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Do you have a lot of hard parses? (you didn't post that part of the output). They can contribute to CPU and latch waits.
    "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

  6. #6
    Join Date
    Dec 2003
    Posts
    21
    what do you think about that ?
    Code:
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:   99.97       Redo NoWait %:     99.99
                Buffer  Hit   %:   86.22    In-memory Sort %:    100.00
                Library Hit   %:   99.78        Soft Parse %:     98.50
             Execute to Parse %:   93.10         Latch Hit %:     99.97
    Parse CPU to Parse Elapsd %:    2.59     % Non-Parse CPU:     99.71
    
     Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   95.24   96.28
        % SQL with executions>1:   62.19   77.55
      % Memory for SQL w/exec>1:   54.38   59.81

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by DaPi
    do end-users actually see slow performance?
    Yeah, this is a good question. I see some things I don't like for my systems, but for yours it may be OK. Also, this snapshot may include some batch jobs that skew the numbers. Lets see a snapshot of 5-10 minutes during a time the users are complaining that things are slow.
    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
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by D.D.
    what do you think about that ?
    Parse CPU to Parse Elapsd %: 2.59
    How often are you parsing? If it's rare, it may not be a problem.

    If you're parsing frequently:
    - that's bad by itself
    - parsing is serialised, so 97% of the elapsed may be due to waiting on other parses!
    "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

  9. #9
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi Hunies,

    Im just curious about DDs SGA, its 800Mb and he got only
    2Gb RAM. I guess its quite big dont u think?
    You know dearest, here in our system, we got 3Gb RAM and
    i only alot 150Mb for shared pool, coz when i increase it
    more, I felt the server perfomance is getting slower. I dont
    know if im only hallucinating

    By the way DD dear, how did u came up with that 800Mb SGA?

    Thanks a lot

    Ms C3

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by kris123
    You know dearest, here in our system, we got 3Gb RAM and
    i only alot 150Mb for shared pool, coz when i increase it
    more,....
    I'm not sure you realy understand what SGA is, what shared pool is and what the relation between the two is. Because the above statement about your RAM and your shared pool sizing is totaly meaningles....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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