About the only way to know which tables are undergoing full table scans most frequently is to query V$SQL and V$SQL_PLAN. The V$SQL_PLAN view is new for Oracle 9i. From V$SQL_PLAN, you can find out what the execution plan is for each SQL statement. V$SQL will show you the SQL statement as well as the number of times it executed.
Dig deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.