Find the sql and tables which are performing full table scan (FTS)

Performance Tuning

1) Below query give number of full table scan of tables in last one day along with owner.

SELECT
object_owner,
object_name ,COUNT(*) FTS_NO
FROM
dba_hist_sql_plan
WHERE
object_owner NOT LIKE ‘%SYS%’ AND
operation = ‘TABLE ACCESS’
AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND
options = ‘FULL’ GROUP BY object_name,object_owner ORDER BY 3 DESC

OBJECT_OWNER
OBJECT_NAME
FTS_NO
User1
T1
931
User1
T2
288

2) Below query helps to find sql id and objects name along owner which has accessed using FTS in last one day

SELECT DISTINCT
sql_id stid,
object_owner owner,
object_name NAME
FROM
dba_hist_sql_plan
WHERE
object_owner NOT LIKE ‘%SYS%’ AND
operation = ‘TABLE ACCESS’
AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND
options = ‘FULL’ ORDER BY 1

STID
OWNER
NAME
00hk65r3g82u4
User1
T1
00wh32sp1z8j8
User1
T2

3) Using above SQL_ID you can find the sql text.

SELECT sql_text FROM v$sqltext WHERE sql_id=’0601k3shzwrdj’ ORDER BY piece

4) By combining the above queries you can find the tables accesed by FTS and related sql statements

SELECT sql_id,sql_text FROM v$sqltext WHERE sql_id IN ( SELECT DISTINCT
sql_id
FROM
dba_hist_sql_plan
WHERE
object_owner NOT LIKE ‘%SYS%’ AND
operation = ‘TABLE ACCESS’
AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND
options = ‘FULL’ ) ORDER BY 1,piece