Lot of Full Table Scans
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Lot of Full Table Scans

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    I ran the utlbstat and utlestat scripts on my database and got the following output for table scans. This shows that I have a lot of Full Table Scans according to the formula

    Non Index Lookup ration = Table Scans (Long Tables) / Table Scans (Long Tables) + Table Scans (Short Tables) = 16%.

    What is the best approach I should take to tackle this problem.

    Thanks
    Anurag


    Statistic Total
    ---------------------------------------------------------------- ------------
    Per Transaction Per Logon Per Second
    --------------- ------------ ------------
    table scans (long tables) 3195
    19.97 59.17 .16

    table scans (short tables) 15666
    97.91 290.11 .79

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Find the sql statement(s) that does these tablescans form V$SQL, V$SQLAREA and then try to add index to then. If you have OEM installed, run the Oracle Spatial Index Advisor and that would give you a report on the tuning.

    Good luck,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Feb 2001
    Posts
    389
    Monitor the sql in buffer(v$sqlarea) , and find out the sessions/sql doing excessive disk_reads and even excessive buffer_gets , find out for those sessions whether it is better to have indexes(for high disk_reads , it would be).
    Also find out for those sessions/sql do u have lot of waits in v$session_wait for db_file_scattered_read.

    Tune those queries/ tables.

    Take Care
    GP

  4. #4
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    There are a number of initialization parameters influence the optimizer and can incorrectly promote a full table scan over an index including but not limited to:

    HASH_JOINED_ENABLED
    HASH_AREA_SIZE
    HASH_MULTIBLOCK_IO_COUNT
    CREATE_BITMAP_AREA_SIZE
    B_TREE_BITMAP_PLANS
    PARTITION_VIEW_ENABLED
    OPTIMIZER_PERCENT_PARALLEL
    ALWAYS_ANTI_JOIN

    You can read more about this in the Oracle performance tuning manual.
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

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