V$session_wait help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: V$session_wait help

  1. #1
    Join Date
    Nov 1999
    Posts
    226

    V$session_wait help

    It says in the Oracle documentataion that to find the waits:

    To see the file and block numbers in more detail:

    SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS FROM v$session_wait WHERE event = 'buffer busy waits' GROUP BY p1, p2, p3


    NUM_WAITERS FILE# BLK# CLASS
    ------------ ----- ------- ------
    92 2 13487 1016
    73 2 27762 1016
    32 1 29697 1016

    This shows that during the execution of the above query, there were 92 waiters for file 2, block 13487.

    When I run, I am getting this


    NUM_WAITERS FILE# BLK# CLASS
    ----------- ---------- ---------- ----------
    1 0 0 0
    1 4 0 0
    2 10 0 0
    1 287 0 0
    1 299 0 0
    3 300 0 0
    5 1000 0 0
    1 25195 0 0
    1 25845 0 0
    1 180000 0 0
    94 1413697536 1 0
    125 1650815232 1 0

    I have no file with these File# above 300. What do I do



    Moreover this is what I get from my system

    SQL> SELECT time, count, class
    2 FROM V$WAITSTAT
    3 ORDER BY time,count
    4 ;

    TIME COUNT CLASS
    ---------- ---------- ------------------
    0 0 sort block
    0 0 save undo block
    0 0 save undo header
    0 0 free list
    0 0 bitmap block
    0 0 unused
    0 0 system undo block
    0 0 system undo header
    0 0 bitmap index block
    0 0 extent map
    418 4695 segment header
    448 6850 undo block
    1281 8787 undo header
    145448 333731 data block

    Any advise please

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    The File# is the 'Absolute File Number' if you have the temp files in your database.

    Check this out.

    ---**** From Documentation-------------------

    Absolute File Number

    In Oracle7 all file# values can be treated as the absolute file number for the queries in this note .
    In Oracle8 onwards each datafile has a relative file number and an absolute file number. The relative file number is relative to the tablespace which owns the datafile. The following query will show the absolute and relative file numbers for datafiles in the database:

    SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
    FROM dba_data_files
    ;

    In Oracle8i onwards a database can also contain TEMPFILES. The following query will show the absolute and relative file numbers for tempfiles in the database:
    SELECT tablespace_name, file_id+value "AFN", relative_fno "RFN"
    FROM dba_temp_files, v$parameter
    WHERE name='db_files';

    It is important to use the correct (absolute) file number in the queries in this note.


    Badrinath
    There is always a better way to do the things.

  3. #3
    Join Date
    Nov 1999
    Posts
    226
    I do not have any tempfiles in my Database and the AFN start from
    1 to 322 and the AFN =RFN in my case

    When I run the

    SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS FROM v$session_wait WHERE event = 'buffer busy waits' GROUP BY p1, p2, p3


    The File# I am getting are in millions . I am wondering why?

  4. #4
    Join Date
    Nov 1999
    Posts
    226
    Okie got It!

    Thats not file ID , It is P1 for sql*Net message

    Thanks once again

    Puneet

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