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

Thread: v$session_events - time_waited excessively larger than runtime!

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    v$session_events - time_waited excessively larger than runtime!

    All,

    I've just been looking at the 'time_waited' values for particular events on user sessions that have been suffering major performance problems (down to poor third-party application code).

    I found it curious that for a 'db file scattered read' event, the returning value was apparently in the region of 285,636,472 (which to my understanding is hundredths of a second), which should therefore equate to:

    285,636,472/100 (2856364.72) seconds = 2856364.72/3600 (793) hours.

    I'm surprised at this amount of apparent wait time for 1 event, when the session itself has only been connected for approximately 36 hours.

    There are no concurrent table reads taking place within any of the PL/SQL loops of the program. Each stage/loop only runs a table scan vs a single target.

    Can anyone shed any light on this?

    - Tony.

  2. #2
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi Tony,

    the wait event 'db file scattered read' occurs during full table scan or full index scan.
    To trace the problematic query,i advise you to install STATSPACK ,with interval of half an hour. Let it run during the massive activity in your database.
    I believe that after you'll generate the statspack report,you'll catch the massive query.
    You can also catch it by using TKPROF and analyze the execution plan.


    Regards,
    Nir

  3. #3
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hi Nir,

    I'm aware of the SQL query, and I know exactly what components and sql syntax are generating the full table scans. What I can't understand is why the v$session_event accumulated wait times for that particular session and that specific event are supposedly bringing back a wait time in excess of the runtime of the query.

    I.e. How can I have a larger waiting time for disk reads in excess of the length of time the query has been running?

    To me, something appears to be wrong with the accumulated statistics made available within the V$ view.

    Comments?

    - Tony.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    How about go to Metalink and search bugs with your version?

  5. #5
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hi Pando,

    I did find something on Metalink, eventually, after having outlined incident on here.

    However, the document only referred to someone else outlining my own findings... and the response from Oracle consultants replying to the thread suggested they hadn't come across any associated bugs.

    The thread then veered in an alternate direction, and no further comments focussed on the apparent anomaly in statistics reports.

    Of course, I am still looking, and come tomorrow morning (when I arrive back in work), I'll maybe raise a TAR - if no-one else flags up anything from their experiences!

    :-)

    Best regards,

    - Tony.

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