 |
|
| Oracle Tips: |
|
|
|
ORACLE DATABASE ADMINISTRATOR
Finding full table scans on 9i
Vasan Srinivasan
05.13.2005
Rating: -4.16- (out of 5)
|
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
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
