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;
