-
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
-
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
-
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
-
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.
