Problem solve Get help with specific problems with your technologies, process and projects.

Finding full table scans on 9i

Full table scans are usually not welcome in an OLTP environment. This SQL script will help identify them for you.

Traditionally speaking, full table scans are not welcome in an OLTP environment. This SQL script will help identify full table scans very easily as there is a 9i view that stores all executed statement plans. Prior to 9i, it would have been a bit more difficult to do this.

Please note that the output will also include something as simple as

SELECT * FROM DUAL

The script can be easily modified to pick out the more resource-intensive FTS by filtering and/or ordering by one of the selected columns or other columns in V$SQLAREA.

set pages 1000 
set feedback off 
set lines 150 
spool fts.lst 
Column sql_text Format A50 Heading 'Full Table Scans' Wrap 
break on sql_text skip 1 
Select Executions, Sorts, Disk_Reads, Buffer_Gets, CPU_Time, 
Elapsed_Time, sql_text 
  from v$sqlarea 
 where (address, hash_value) in (Select address, hash_value from 
v$sql_plan   where options   like '%FULL%' and operation like '%TABLE%') 
Order by Elapsed_Time 
/ 
spool off 
set feedback on 

This was last published in May 2005

Dig Deeper on Oracle database performance problems and tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close