query to find all the select statement which is using Full Table Scan

query to find all the select statement which is using Full Table Scan

SELECT SQL_ID,OPERATION,OPTIONS,OBJECT_NAME,OBJECT_OWNER
FROM V$SQL_PLAN
WHERE OPTION like ‘%FULL%’ AND OPERATION like ‘%TABLE%’;

–Then find the Query Text :
SELECT SQL_ID, SQL_TEXT
  FROM V$SQL
 WHERE SQL_ID IN (SELECT SQL_ID
                    FROM V$SQL_PLAN
                   WHERE OPTIONS LIKE ‘%FULL%’ AND OPERATION LIKE ‘%TABLE%’);

  • January 19, 2019 | 17 views
  • Comments