Why the Elapsed Time of one query in AWR report is longer than Elapsed Time of report
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Why the Elapsed Time of one query in AWR report is longer than Elapsed Time of report

  1. #1
    Join Date
    Apr 2001
    Posts
    125

    Why the Elapsed Time of one query in AWR report is longer than Elapsed Time of report

    Hi,

    I have an AWR report based on two snapshots one an one hour time window, under the "SQL ordered by Elapsed Time" section, there is a query having Elapsed Time = 7012 seconds, which is almost two hours, how is this possible?

    Thanks

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    because it aggregates data from all sessions

  3. #3
    Join Date
    Apr 2001
    Posts
    125
    Thanks for your kind answer. I am working on a RAC performance issue, basically we have an application running extremly slow on an Oracle 10g RAC on AIX, sometime it runs more than 10 hours. Based on the AWR reports, I found that:

    1. This application heavily use one node, lightly use the other node
    2. There is no query runs more than 1 second per Exec (s), but more of the queries are called tens of thousands of times.

    Is there any best practice on how to tune the DB in this scenario?

    Thanks
    Oracle 8, 8i, 9i OCP DBA
    Oracle 6/6i OCP DEV
    Sun Solaris8 SCSA
    MCDBA 2000

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Queries running thousands of times? Queries are most likely called by the application, you might have to take a look at it.

    How about Wait Events? where and what is Oracle waiting for?

    How about topas?... how the system looks?
    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.

  5. #5
    Join Date
    Apr 2001
    Posts
    125
    Yes, these queries are called by the application.

    Wait Events (For one hour window)

    Event Waits Timeouts Total Wait (s)
    latch: cache buffers chains 70,524 70,522 703
    db file sequential read 38,155 0 388
    enq: TX - row lock contention 1,183 182 385
    Oracle 8, 8i, 9i OCP DBA
    Oracle 6/6i OCP DEV
    Sun Solaris8 SCSA
    MCDBA 2000

  6. #6
    Join Date
    Apr 2001
    Posts
    125
    Wait Events (For another hour window)

    Event Waits Timeouts Total Wait Time (s) Avg wait (ms) Waits /txn
    db file scattered read 390,480 0 4,075 10 6.48
    db file sequential read 326,598 0 3,947 12 5.42
    log file parallel write 123,212 0 363 3 2.05
    db file parallel read 12,613 0 305 24 0.21
    gc cr multi block request 330,968 0 211 1 5.50
    enq: TX - row lock contention 382 197 137 360 0.01
    gc current grant 2-way 82,816 0 122 1 1.38
    gc cr grant 2-way 87,626 0 114 1 1.46

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    1) You do not need one window, or another window, you need a window, when the application performs bad. Cache buffer chains means lots of concurrent selects/DMLs. enq:tx row lock contention means concurent DMLs.
    That, along with the queries running tenths thousands of times, meas that the DBA cannot do much about it. It's just bad application.
    Maybe increasing the buffer cache can lower the cache buffer chains latch but the enq ... there is nothing i can do about that.

  8. #8
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    It could be number of session times as explained above or number of CPU's acn make a difference.
    How many CPU you have. If you have 10 CPU, during 1 hour time you have 3600*10 seconds CPU time. So 7000 sec is possible.
    You said the query is executing 1000 times, is it a batch program?

    In your 2nd window, the problem is in FTS as the DB FILE SCATTERED read is the top wait event.
    Can you post the query with it's stats from ADR ( numbber of executions, buffer gets, disk reads etc).
    http://www.perf-engg.com
    A performance engineering forum

  9. #9
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Quote Originally Posted by malay_biswal
    It could be number of session times as explained above or number of CPU's acn make a difference.
    How many CPU you have. If you have 10 CPU, during 1 hour time you have 3600*10 seconds CPU time. So 7000 sec is possible.
    You said the query is executing 1000 times, is it a batch program?

    In your 2nd window, the problem is in FTS as the DB FILE SCATTERED read is the top wait event.
    Can you post the query with it's stats from ADR ( numbber of executions, buffer gets, disk reads etc).
    WHy don't you use ADDM which uses data from AWR and gives recommendations ALso !!!

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