Monitoring Full tablescans
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Monitoring Full tablescans

  1. #1
    Join Date
    Sep 2006
    Posts
    114

    Monitoring Full tablescans

    SQL> select name,value
    2 from v$sysstat
    3 where name like 'table scans%'
    4 ;

    NAME VALUE
    ----------------------------------------------------------------
    table scans (short tables) 6599797
    table scans (long tables) 14003
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0

    SQL> show parameter db_file_multiblock_read_count

    NAME TYPE VALUE
    ------------------------------------ ------- --------------
    db_file_multiblock_read_count integer 32

    should i increase db_file_multiblock_read_count and how much it is needed to be increased?

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    What you are expecting by increasing db_file_multiblock_read_count? Do you face any problem?
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Feb 2003
    Posts
    63
    What are you looking for exactly do you know that FTS are causing you a problem?

  4. #4
    Join Date
    Jun 2005
    Posts
    31
    Quote Originally Posted by M.Shakeel Azeem
    SQL>

    NAME VALUE
    ----------------------------------------------------------------
    table scans (long tables) 14003

    NAME TYPE VALUE
    ------------------------------------ ------- --------------
    db_file_multiblock_read_count integer 32

    should i increase db_file_multiblock_read_count and how much it is needed to be increased?
    (1) I agree with the previous two responses.

    ==> You really need to find out if those table scans (long tables) are required or caused by a wrong execution plan.

    ==> Read your statspack-report, watch V$SQLAREA.

    (The "table scans (short tables)" are usually OK (typically on tiny lookup-tables, eg. 100 rows of country-codes in 1 Oracle-Block...)

    (2) !!! Warning: !!! Increasing the parameter db_file_multiblock_read_count integer from 32 (that's in case of 16 KB Oracle Blocksize 512 KB) can cause even NEGATIVE impact, as MORE execution plans might switch to full-table-scans.

    For this reason some vendors of Oracle-based applications recommend on OLTP-systems "db_file_multiblock_read_count = 4" to reduce the problem with unwanted full-table-scans.

    (On a reporting-system that's of course completely different!)

    ==> You really need to find out if those table scans are required.

    In case of reports where a full-table-scan is the best execution plan you still can change that parameter at session-level!

    alter session set db_file_multiblock_read_count = 128;


    Good Luck,

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The value of db_file_multiblock_read_count ought to reflect the size of a multiblock read on your system divided by the size of the system tablespace blocks. That's all you really need to know, IMHO.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    full table scan != bad
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jun 2006
    Posts
    259
    full table scan != good
    full table scan != bad

    Ok so which one is it really? The answer is it depends.
    Depends on your application type and sql statement that is being executed.
    Last edited by ixion; 09-19-2006 at 02:57 PM.

  8. #8
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by M.Shakeel Azeem
    SQL>
    should i increase db_file_multiblock_read_count and how much it is needed to be increased?
    Do you know optimizer would choose a full table scan over an index if you set this value high?
    "What is past is PROLOGUE"

  9. #9
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    hi friends,

    How "high" is high and how "low" is low (the value of db_file_multiblock_read_count) for the optimizer to
    use index or full table scans?
    Last edited by yxez; 09-19-2006 at 07:35 PM.
    Behind The Success And Failure Of A Man Is A Woman

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by yxez
    hi friends,

    How "high" is high and how "low" is low (the value of db_file_multiblock_read_count) for the optimizer to
    use index or full table scans?
    Making the count smaller increases the number of i/o requests (from Oracle) for a full scan to be completed, therefore the optimizer would tend to favour index access paths. Other than that it depends on far too many variables to describe here. An excellent reference would be Jonathan Lewis' new book.

    What problem are you trying to solve?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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