Friday, March 14, 2008

How to determine full scan sqls?

Here is a sql script to determine full scan sqls:

SELECT /*+ ordered */
DISTINCT s.hash_value, s.buffer_gets, s.disk_reads, s.executions,
s.sql_text,
'SELECT * FROM V$SQL_PLAN WHERE ADDRESS='''
|| s.address
|| ''' AND HASH_VALUE='
|| s.hash_value
FROM v$sql_plan p, v$sqlarea s
WHERE p.options LIKE '%FULL%'
AND s.address = p.address
AND s.hash_value = p.hash_value
AND object_name <> 'DUAL'
-- AND EXECUTIONS > 10
ORDER BY s.buffer_gets DESC

No comments: