Lot of Full Table Scans

[anuragmin]

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

[sambavan]

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

[gpsingh]

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

[wjramsey]

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.