The Oracle (tm) Users' Co-Operative FAQ

How do I find out which tables are accessed with a full table scan, and the number of times this occurred ?

Author's name: Jonathan Lewis

Author's Email:

Date written: 18th July 2002

Oracle version(s): 7.3 onwards

We have found a doc on metalink which shows a query on the x$bh table/view to determine which tables are being accessed by full table scans. We are interested in a count of number of times each table is accessed by a full table scan so we can find the most heavily accessed table by full table scan and possibly tune to reduce the number of full table scanning. The reason we know this can somehow be done is because we have received a report from a vendor like the following:
		There have been 15,402 full table scans since the start of the database. 
	Listed below are the tables that have had over 10 full table scans.
	Table Owner Table Name Number of Full Table Scans

Because it mentions how many occurred since startup of the database, it implies that there is/are v$ tablesor x$bh tables that were queried to get this type of information. I have checked metalink documents without success.

Back to index of questions

There is no such view or X$ object as far as I know.

I assume the script that you mention regarding using X$BH uses the flag value to check if the buffer header was used for scan read, with a clause like:

	select distinct obj 
	from x$bh
	where	bitand(flag,power(2,19)) = power(2,19):
Perhaps joining to obj$ on the dataobj# column.

This identifies objects that have been scanned (which may include index fast full scans), but doesn't tell you how often.

To get an idea of how often an object has been scanned, you could dump v$sql or v$sqlarea from time to time and use explain plan on the queries involving the guilty objects to identify the SQL that includes a full scan on the object. If you find plans with full scans, you can use the executions column to tell you how many times the query was executed. This still doesn't tell you how often the scan occured, of course, as the scan may have taken place many times in one exection of the query.

In Oracle 9.0 onwards, you have the extra view v$sql_plan so you don't need to dump the sql and explain it, and in 9.2 you have a view called v$sql_plan_statistics, which includes information about the number of times each line of each plan was executed, and this can allow you to get a much better estimate of the actual number of scans.

Of course, under all Oracle versions, using v$sql as the source of such information is only an approximation - some executions may have been aged out of the SGA before you get to see them, and sometimes invalidations and reloads can confuse the statistics.


Further reading: N/A

Back to top

Back to index of questions