Full table scan Sql statements

select
sql_text,
table_owner,
table_name,
table_type,
size_kb,
–statement_count,
–reference_count,
executions–,
–executions * reference_count total_scans
from
(select
c.sql_text sql_text,
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions) executions–,
–count(distinct a.hash_value) statement_count,
–count(*) reference_count
from
sys.v_$sql_plan a,
dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and
a.object_name (+) = b.segment_name
and
b.segment_type in (‘TABLE’, ‘TABLE_PARTITION’)
and
a.operation like ‘%TABLE%’
and
a.options = ‘FULL’
and
a.hash_value = c.hash_value
and
b.bytes / 1024 > 1024
and
a.object_owner in (‘&&v_AppOwner’)
group by
c.sql_text,
a.object_owner,
a.object_name,
a.operation,
b.bytes / 1024,
b.segment_type
order by
5 desc,
2,
3);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: