Oracle Database - Queries doing Full Table Scan - Last 15 minutes
In this blog, I am writing down a query to find out queries doing Full Table scan in last 15 minutes in Oracle Database.
It can be extrapolated to do queries doing full table scan in 30 / 60 or x minutes
Below is the query
I am taking out queries from v$active_session_history (hoping the queries were sampled doing a full table scan)
and then joining the table with V$sql_plan.
I am making double sure using sql plan that the query is doing a full table scan using V$sql_plan view.
set pages 500 lines 500
with sql_queries as
(
select distinct sql_id from V$active_session_history
where sample_time > sysdate - 1/96 -- 1/24 for 1 hour
and sql_plan_options = 'FULL'
)
select distinct sp.sql_id from V$sql_plan sp, sql_queries sq
where sp.sql_id = sq.sql_id
and sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL';
and sp.object_owner not in ('SYS','XDB','SYSTEM','DBSNMP')
;
I have also put a where clause to restrict sys,system,xdb tables, generally dbsnmp will not have any ownership of tables.
This will give distinct sql ids with table access full operation in last 15 minutes
