DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Database Time Spent Waiting (%): Wait Class Other

  1. #11
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by muzammil.dba
    some time is going hung & I have to restart the db.
    What do you mean by "going hung"?
    Can users connect to the database?
    Can you see active sessions on v$session?
    What do you see in alert log?
    What do you see on the box?... are pmon/smon in good shape?
    Last edited by PAVB; 09-29-2008 at 04:53 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  2. #12
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Why do not you analyze the AWR report when the performance issue was there? IF you have some difficulty with that you can share the report. We'll help you.
    http://www.perf-engg.com
    A performance engineering forum

  3. #13
    Join Date
    May 2008
    Posts
    95

    Exclamation

    Every day I am facing deadlock issue. this is the problem problem for the same??

    -------------------------------------------------------------------
    Oct 6, 2008 1:18:02 PM Oct 6, 2008 1:19:05 PM 1.05 Metrics "Database Time Spent Waiting (%)" is at 32.7185 for event class "Other"
    Oct 6, 2008 1:16:02 PM Oct 6, 2008 1:17:02 PM 1 Metrics "Database Time Spent Waiting (%)" is at 39.59369 for event class "Concurrency"
    Oct 6, 2008 1:16:02 PM Oct 6, 2008 1:17:02 PM 1 Metrics "Database Time Spent Waiting (%)" is at 76.37071 for event class "Other"
    Oct 6, 2008 1:13:02 PM Oct 6, 2008 1:14:02 PM 1 Metrics "Database Time Spent Waiting (%)" is at 40.04823 for event class "Other"
    Oct 6, 2008 1:09:59 PM Oct 6, 2008 1:10:59 PM 1 Metrics "Database Time Spent Waiting (%)" is at 68.52271 for event class "Other"
    Oct 6, 2008 1:08:59 PM Oct 6, 2008 1:09:59 PM 1 Metrics "Database Time Spent Waiting (%)" is at 50.2031 for event class "Concurrency"
    Oct 6, 2008 1:04:50 PM Oct 6, 2008 1:06:53 PM 2.05 Metrics "Database Time Spent Waiting (%)" is at 46.4171 for event class "Other"
    Oct 6, 2008 12:00:23 PM Oct 6, 2008 12:01:23 PM 1 Metrics "Database Time Spent Waiting (%)" is at 36.67834 for event class "Other"
    Oct 6, 2008 11:55:17 AM Oct 6, 2008 11:57:20 AM 2.05 Metrics "Database Time Spent Waiting (%)" is at 42.73943 for event class "Other"
    Oct 6, 2008 11:50:08 AM Oct 6, 2008 11:51:08 AM 1 Metrics "Database Time Spent Waiting (%)" is at 83.0844 for event class "Other"
    Oct 6, 2008 10:14:28 AM Oct 6, 2008 10:15:28 AM 1 Metrics "Database Time Spent Waiting (%)" is at 32.30657 for event class "Other"
    Oct 6, 2008 9:29:49 AM Oct 6, 2008 9:30:49 AM 1 Metrics "Database Time Spent Waiting (%)" is at 33.01647 for event class "Concurrency"
    Oct 6, 2008 9:29:49 AM Oct 6, 2008 9:30:49 AM 1 Metrics "Database Time Spent Waiting (%)" is at 75.57205 for event class "Other"
    Oct 6, 2008 9:16:19 AM Oct 6, 2008 9:17:19 AM 1 Metrics "Database Time Spent Waiting (%)" is at 79.5333 for event class "Commit"
    Oct 6, 2008 9:16:19 AM Oct 6, 2008 9:17:19 AM 1 Metrics "Database Time Spent Waiting (%)" is at 82.38806 for event class "Other"
    Oct 6, 2008 8:50:25 AM Oct 6, 2008 8:51:25 AM 1 Metrics "Database Time Spent Waiting (%)" is at 57.47044 for event class "Other"
    Oct 6, 2008 8:20:19 AM Oct 6, 2008 8:22:19 AM 2 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
    Oct 6, 2008 8:20:19 AM Oct 6, 2008 8:21:19 AM 1 Metrics "Database Time Spent Waiting (%)" is at 45.7133 for event class "Concurrency"
    Oct 6, 2008 7:39:46 AM Oct 6, 2008 7:40:49 AM 1.05 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
    Oct 6, 2008 7:34:37 AM Oct 6, 2008 7:35:37 AM 1 Metrics "Database Time Spent Waiting (%)" is at 52.15656 for event class "Concurrency"
    Oct 6, 2008 7:34:37 AM Oct 6, 2008 7:35:37 AM 1 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
    Oct 6, 2008 6:36:27 AM Oct 6, 2008 6:37:27 AM 1 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
    Oct 6, 2008 5:04:48 AM Oct 6, 2008 5:06:48 AM 2 Metrics "Database Time Spent Waiting (%)" is at 39.85522 for event class "Other"
    Oct 6, 2008 4:50:12 AM Oct 6, 2008 4:51:15 AM 1.05 Metrics "Database Time Spent Waiting (%)" is at 39.58819 for event class "Other"
    Oct 6, 2008 4:46:06 AM Oct 6, 2008 4:47:06 AM 1 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"


    -------------------------------------------------------------------

    Regards,
    dba

  4. #14
    Join Date
    May 2008
    Posts
    95

    Exclamation

    AWR Report:

    DB cpu (seconds) 0.00 0.00 0.00
    DB time (seconds) 10793.85 2.99 0.63
    db block changes 306559.00 84.87 17.94
    execute count 99084.00 27.43 5.80
    global cache cr block receive time (seconds) 0.00 0.00 0.00
    global cache cr blocks received 0.00 0.00 0.00
    global cache current block receive time (seconds) 0.00 0.00 0.00
    global cache current blocks received 0.00 0.00 0.00
    global cache get time (seconds) 0.00 0.00 0.00
    global cache gets 0.00 0.00 0.00
    opened cursors cumulative 15820.00 4.38 0.93
    parse count (total) 16720.00 4.63 0.98
    parse time cpu (seconds) 0.09 0.00 0.00
    parse time elapsed (seconds) 0.26 0.00 0.00
    physical reads 14.00 0.00 0.00
    physical writes 6446.00 1.78 0.38
    redo size (KB) 50233.90 13.91 2.94
    session cursor cache hits 8794.00 2.43 0.51
    session logical reads 1077166.00 298.22 63.03
    sql execute cpu time (seconds) 0.00 0.00 0.00
    sql execute elapsed time (seconds) 0.00 0.00 0.00
    user calls 20412.00 5.65 1.19
    user commits 13039.00 3.61 0.76
    user rollbacks 4051.00 1.12 0.24
    workarea executions - multipass 0.00 0.00 0.00
    workarea executions - onepass 0.00 0.00 0.00
    workarea executions - optimal 2697.00 0.75 0.16
    Last edited by muzammil.dba; 10-06-2008 at 05:51 AM.

  5. #15
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    It seems you have lot of Writing activities going on, can you uploadthe enture AWR report?
    http://www.perf-engg.com
    A performance engineering forum

  6. #16
    Join Date
    May 2008
    Posts
    95

    Exclamation

    How I will get AWR report?

  7. #17
    Join Date
    May 2008
    Posts
    95

    Exclamation

    Please find attached zip file of SP Report.
    Attached Files Attached Files

  8. #18
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    1st question is this the statspack report during problematic period, as there are only 25 sessions during that 48 min period.

    Anyway Your Buffer Cache is not big enough and this is the query which needs to be tuned. This is in both top Buffer gets and Disk read.
    You check explain plan of this query and try to tune it and if that's not suffiecient you may need to increase your Buffer cache.
    select count(alertvo0_.ALERT_ID) as col_0_0_ from ALERT alertvo0
    _ where alertvo0_.EH_CODE=:1 and alertvo0_.DELETE_FLAG=:2 and al
    ertvo0_.ALERT_TO_USER=:3 and alertvo0_.STATUS=:4
    http://www.perf-engg.com
    A performance engineering forum

  9. #19
    Join Date
    May 2008
    Posts
    95

    Exclamation

    Dear Malay,

    Thanks for your help.

    When I am trying to run ur SQL I got following message :

    SQL> select count(alertvo0_.ALERT_ID) as col_0_0_ from ALERT alertvo0 where aler
    tvo0_.EH_CODE=:1 and alertvo0_.DELETE_FLAG=:2 and alertvo0_.ALERT_TO_USER=:3 and
    alertvo0_.STATUS=:4;
    SP2-0552: Bind variable "4" not declared.
    =

    AND MY DB BUFFER size is 1gb

    SQL> show sga

    Total System Global Area 2298478592 bytes
    Fixed Size 2074512 bytes
    Variable Size 1207961712 bytes
    Database Buffers 1073741824 bytes
    Redo Buffers 14700544 bytes


    Regards,
    dba

  10. #20
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Yeah Buffer cache size is in statspack report. You can't execute that SQL just like that, you need to provide the bind varibales.
    http://www.perf-engg.com
    A performance engineering forum

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