|Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
|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
reason we know this can somehow be done is because
we have received a report from a vendor like the
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 ABC TABLEONE 335 DEF TABLETWO 5551
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