db file sequential read
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: db file sequential read

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    I am running 2 Oracle 7.3.4 databases on AIX (seperate databases but having database links to each other. Users have been complaining of slow response times. On doing some investigation I have found that there are 2 database files in each database which have the majority of I/O reads performed on them.
    Looking at v$system_event the avg. figure for the db file sequential read is around 200 centisecs on both databases
    I know what disk the filesystem containing the db files is on. Is there any further anlysis I could do to narrow the problem down further?

    Thanks in advance

    Fraze

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    Yes find out hot objects (tables/indexes), how much sorts are on , find out if buffer cache latches are enough.
    u can use v$waitstat,v$session_wait, v$latch.

  3. #3
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    I've had a look at v$waitstat and nearly all of the waits are on data blocks. v$latch shows the most sleeps on shared pool / library cache / cache buffer chains.

    How can I find out what the hot tables/indexes are (I'm getting a crash course in tuning today!!)?

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    Ok

    v$waitstat:

    data block wait is highest
    In v$sysstat, do u see high dirty buffers inspected , increase DBWR processes.

    v$system_event : db_file_sequential_read is highest, did u see high ebough buffer busy waits and buffer deadlocks.
    Increase Freelists .
    Also in V$latch_children , check for cache buffer chains , are sleeps divide properly on all chains, if no get ADDR of that chain.


    select dbarfil,dbablk,count(*)
    from x$bh
    where hladdr='838E85EC'
    group by dbarfil,dbablk

    Get the block(s) with high counts
    and from dba_extents , get the object name.

    Increase Freelist or change sql code.

    Take Care
    GP


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