Because full table scans do multiblock reads, you can narrow the problem down to particular datafiles by looking at the ratio of blocks read to read requests in V$FILESTAT. The datafiles with the highest ratios are those that have had the most multiblock reads issued against them.

To find out exactly which tables are being scanned you have to catch the parameters for the db file scattered read waits, either by querying V$SESSION_WAIT repeatedly, or by using a script such as the trace_waits.sql script from the Ixora web site.

If we query V$SYSSTAT in that name column contain 'table scan (long tables)' the value of this row is very high in our database so Oracle recommends that value should be less. If it is more we need to create more indexes on these tables. So the question is how to check which tables need indexes, so that we can create indexes.