How to find out full table scan Sql

Oracle How to find out full table scan Sql 

Full table scan Sql


This SQL script will help to identify sql .

set line 200 pagesize 200 
col SQLTEXT for a70 wrap
SELECT inst_id,sql_id,Disk_Reads DiskReads, Executions,PARSING_SCHEMA_NAME, SQL_Text SQLText
--,SQL_FullText SQLFullText 
FROM
 (SELECT inst_id,Disk_Reads, 
 Executions, 
 SQL_ID, LTRIM(SQL_Text) SQL_Text, 
 SQL_FullText, 
 Operation, 
 Options,
 PARSING_SCHEMA_NAME,
 Row_Number() OVER (Partition By sql_text ORDER BY Disk_Reads * Executions DESC) KeepHighSQL 
 FROM ( SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads, Max(Executions) OVER (Partition By sql_text) Executions,t.inst_id,t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options,t.PARSING_SCHEMA_NAME FROM gv$sql t, gv$sql_plan p
 WHERE t.hash_value=p.hash_value 
 AND p.operation ='TABLE ACCESS' 
 AND p.options ='FULL' 
 -- AND p.object_owner NOT IN ('SYS','SYSTEM')
 and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM','SYSDG','SYSBACKUP','GSMCATUSER','SYSKM' ,'OJVMSYS','DVSYS') 
 AND t.Executions > 1
 and t.inst_id=p.inst_id
 ) ORDER BY DISK_READS * EXECUTIONS DESC
 ) WHERE KeepHighSQL = 1
AND rownum <=5;




set line 200 pagesize 200 
col SQLTEXT for a70 wrap
SELECT inst_id,sql_id,Disk_Reads DiskReads, Executions,PARSING_SCHEMA_NAME, SQL_Text SQLText
--,SQL_FullText SQLFullText 
FROM
 (SELECT inst_id,Disk_Reads, 
 Executions, 
 SQL_ID, LTRIM(SQL_Text) SQL_Text, 
 SQL_FullText, 
 Operation, 
 Options,
 PARSING_SCHEMA_NAME,
 Row_Number() OVER (Partition By sql_text ORDER BY Disk_Reads * Executions DESC) KeepHighSQL 
 FROM ( SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads, Max(Executions) OVER (Partition By sql_text) Executions,t.inst_id,t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options,t.PARSING_SCHEMA_NAME FROM gv$sql t, gv$sql_plan p
 WHERE t.hash_value=p.hash_value 
 AND p.operation ='INDEX' 
 AND p.options ='FULL SCAN' 
 -- AND p.object_owner NOT IN ('SYS','SYSTEM')
 and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM','SYSDG','SYSBACKUP','GSMCATUSER','SYSKM' ,'OJVMSYS','DVSYS') 
 AND t.Executions > 1
 and t.inst_id=p.inst_id
 ) ORDER BY DISK_READS * EXECUTIONS DESC
 ) WHERE KeepHighSQL = 1
AND rownum <=5;

==

--CARTESIAN

set linesize 200
col sql_text format a100
col parsing_schema_name for a20
select con_id,LAST_ACTIVE_TIME,parsing_schema_name,sql_id,substr(sql_text,1,100) sql_text from gv$sql where hash_value in (select hash_value from gv$sql_plan
where options = 'CARTESIAN'
AND operation LIKE '%JOIN%'
-- and TIMESTAMP > sysdate -interval '10' minute 
)
and PARSING_SCHEMA_NAME not in
('ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'SYS',
'SYSTEM',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS'
)
and con_id=3
and  LAST_ACTIVE_TIME > sysdate -interval '10' minute 
order by hash_value

=====

SET PAGES 300 LINES 300 TRIMS ON
COL OPERATION FORMAT A15
COL OBJECT_NAME FORMAT A32
COL OBJECT_OWNER FORMAT A25
COL OPTIONS FORMAT A20
COL EXECUTIONS FORMAT 999,999,999


SELECT A.OBJECT_OWNER,
        b.sql_id,
         A.OBJECT_NAME,
         RTRIM (A.OPERATION)     OPERATION,
         A.OPTIONS,
         B.EXECUTIONS
    FROM gV$SQL_PLAN A, gV$SQLAREA B
   WHERE     A.SQL_ID = B.SQL_ID
     and A.inst_id = B.inst_id
         AND A.OPERATION IN ('TABLE ACCESS', 'INDEX')
         AND A.OPTIONS IN ('FULL',
                           'FULL SCAN',
                           'FAST FULL SCAN',
                           'SKIP SCAN',
                           'SAMPLE FAST FULL SCAN')
         AND A.OBJECT_OWNER NOT IN ('SYS', 'SYSTEM')
GROUP BY OBJECT_OWNER,b.sql_id,  OBJECT_NAME,  OPERATION, OPTIONS,   B.EXECUTIONS
ORDER BY OBJECT_OWNER,  OPERATION,  OPTIONS,  OBJECT_NAME;
		

--with row_num and sql_id 

COL OPERATION FORMAT A15
COL OBJECT_NAME FORMAT A32
COL OBJECT_OWNER FORMAT A15
COL OPTIONS FORMAT A20
COL EXECUTIONS FORMAT 999,999,999
SET PAGES 55 LINES 132 TRIMS ON

SELECT A.OBJECT_OWNER,
       b.sql_id,
         A.OBJECT_NAME,
         RTRIM (A.OPERATION)     OPERATION,
         A.OPTIONS,
         B.EXECUTIONS,
		 NUM_ROWS
    FROM gV$SQL_PLAN A, gV$SQLAREA B,dba_tables d
   WHERE     A.SQL_ID = B.SQL_ID
        and a.inst_id=b.inst_id 
         AND A.OPERATION IN ('TABLE ACCESS', 'INDEX')
         AND A.OPTIONS IN ('FULL',
                           'FULL SCAN',
                           'FAST FULL SCAN',
                           'SKIP SCAN',
                           'SAMPLE FAST FULL SCAN')
         AND A.OBJECT_OWNER NOT IN ('SYS', 'SYSTEM', 'PERFSTAT','SYSMAN')
		 and A.OBJECT_NAME=d.table_name
		 and NUM_ROWS> 1000
GROUP BY OBJECT_OWNER,
         b.sql_id,
         OBJECT_NAME,
         OPERATION,
         OPTIONS,
         B.EXECUTIONS,
		 NUM_ROWS
ORDER BY OBJECT_OWNER,
         OPERATION,
         OPTIONS,
         OBJECT_NAME;

============

set echo off
set feedback on
 
set pages 999
column nbr_FTS  format 99,999
column num_rows format 999,999
column blocks   format 9,999
column owner    format a20
column name     format a30
column ch       format a1
column time     heading "Snapshot Time"        format a15
 
column object_owner heading "Owner"            format a12
column ct           heading "# of SQL selects" format 999,999
 
break on time
 
select
   object_owner,
   count(*)   ct
from   dba_hist_sql_plan
where   object_owner is not null
group by   object_owner
order by   ct desc
;




set pages 999
column nbr_FTS  format 99,999
column num_rows format 999,999
column blocks   format 9,999
column owner    format a20
column name     format a30
column ch       format a1
column time     heading "Snapshot Time"        format a15
 
column object_owner heading "Owner"            format a12
column ct           heading "#SQL " format 999,999
 
break on time
 
select
   con_id,
   object_owner,
   sql_id,
   count(*)   ct
from   dba_hist_sql_plan
where   object_owner is not null
group by   con_id,sql_id,object_owner
order by   ct desc
;



             CON_ID Owner        SQL_ID           #SQL
------------------- ------------ ------------- --------
                  0 SYS          6h1ysp5jm8h8m      762
                  0 SYS          7r3793f1a0x0m      749
                  0 SYS          34cd4y8mbqvsk      710
                 

 
 
set heading on feedback on
 


column owner    format a20

select
   to_char(sn.end_interval_time,'dd-mm-yy hh24') time,
   a.sql_id,
   p.owner,
   p.name,
   t.num_rows,
--   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions_delta) nbr_FTS
from
   dba_tables  t,
   dba_segments s,
   dba_hist_sqlstat a,
   dba_hist_snapshot sn,
   (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from      dba_hist_sql_plan pl
   where   operation = 'TABLE ACCESS'
      and  options = 'FULL') p
where
   a.snap_id = sn.snap_id
   and   a.sql_id = p.sql_id
   and   t.owner = s.owner
   and   t.table_name = s.segment_name
   and   t.table_name = p.name
   and   t.owner = p.owner
   and   t.owner not in ('SYS','SYSTEM')
   and sn.snap_id between :BEGIN_SNAP_ID and :END_SNAP_ID
having   sum(a.executions_delta) > 1
group by   to_char(sn.end_interval_time,'dd-mm-yy hh24'),a.sql_id,p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by   1 asc;
 
 
 
 
 
column nbr_RID  format 999,999,999
column num_rows format 999,999,999
column owner    format a15
column name     format a25
 
ttitle 'Table access by ROWID and counts'
select
   to_char(sn.end_interval_time,'dd-mm-yy hh24') time,
   s.sql_id,
   p.owner,
   p.name,
   t.num_rows,
   sum(a.executions_delta) nbr_RID
from
   dba_tables t,
   dba_hist_sqlstat  a,
   dba_hist_snapshot sn,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from    dba_hist_sql_plan pl
   where
      operation = 'TABLE ACCESS'
      and  options = 'BY USER ROWID') p
where
   a.snap_id = sn.snap_id
   and   a.sql_id = p.sql_id
   and   t.table_name = p.name
   and   t.owner = p.owner
   and sn.snap_id between :BEGIN_SNAP_ID and :END_SNAP_ID
having   sum(a.executions_delta) > 9
group by   to_char(sn.end_interval_time,'dd-mm-yy hh24'),s.sql_id,p.owner, p.name, t.num_rows
order by   1 asc;




 
--*************************************************
--  Index Report Section
--*************************************************
 
column nbr_scans  format 999,999,999
column num_rows   format 999,999,999
column tbl_blocks format 999,999,999
column owner      format a20
column table_name format a27
column index_name format a20
 
--ttitle 'Index full scans and counts'
select
   to_char(sn.end_interval_time,'dd-mm-yy hh24') time,
   s.sql_id,
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions_delta) nbr_scans
from
   dba_segments seg,
   dba_indexes d,
   dba_hist_sqlstat   s,
   dba_hist_snapshot sn,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from      dba_hist_sql_plan pl
   where      operation = 'INDEX'
      and      options = 'FULL SCAN') p
where
   d.index_name = p.name
   and   s.snap_id = sn.snap_id
   and   s.sql_id = p.sql_id
   and   d.table_name = seg.segment_name
   and   seg.owner = p.owner
    and sn.snap_id between :BEGIN_SNAP_ID and :END_SNAP_ID
having   sum(s.executions_delta) > 9
group by   to_char(sn.end_interval_time,'dd-mm-yy hh24'),s.sql_id,p.owner, d.table_name, p.name, seg.blocks
order by   1 asc;
 
 
-- ttitle 'Index range scans and counts'
select
   to_char(sn.end_interval_time,'dd-mm-yy hh24') time,
   s.sql_id,
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions_delta) nbr_scans
from
   dba_segments seg,   dba_hist_sqlstat s,   dba_hist_snapshot sn,   dba_indexes d,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from
      dba_hist_sql_plan pl
   where
      operation = 'INDEX'
      and      options = 'RANGE SCAN') p
where
   d.index_name = p.name
   and   s.snap_id = sn.snap_id
   and   s.sql_id = p.sql_id
   and   d.table_name = seg.segment_name
   and   seg.owner = p.owner
    and sn.snap_id between :BEGIN_SNAP_ID and :END_SNAP_ID
having   sum(s.executions_delta) > 9
group by   to_char(sn.end_interval_time,'dd-mm-yy hh24'),s.sql_id,p.owner, d.table_name, p.name, seg.blocks
order by   1 asc;
 
 
 
ttitle 'Index unique scans and counts'
select
   to_char(sn.end_interval_time,'dd-mm-yy hh24') time,
   p.owner,
   d.table_name,
   p.name index_name,
   sum(s.executions_delta) nbr_scans
from
   dba_hist_sqlstat s,   dba_hist_snapshot sn,   dba_indexes d,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from      dba_hist_sql_plan pl
   where      operation = 'INDEX'
      and      options = 'UNIQUE SCAN') p
where
   d.index_name = p.name
   and   s.snap_id = sn.snap_id
   and   s.sql_id = p.sql_id
   and sn.snap_id between :BEGIN_SNAP_ID and :END_SNAP_ID
having
   sum(s.executions_delta) > 9
group by   to_char(sn.end_interval_time,'dd-mm-yy hh24'),p.owner, d.table_name, p.name
order by   1 asc;