SQL statements related to full table Scan

1. Based on the specified applicationProgramQuery

Select T. SQL _text, T. disk_reads, T. first_load_time, T. module, U. username, T. hash_value
From v $ sqlarea T, V $ SQL _plan P, all_users u
Where T. hash_value = P. hash_value and P. Operation = 'table access' and U. user_id = T. parsing_user_id and
P. Options = 'full' and T. module = 'xxx.exe 'and disk_reads <> 0
Order by disk_reads DESC, SQL _text

Select SQL _text from V $ sqltext where hash_value = 190441126 order by piece

2. display the read data volume (number of rows and blocks)
select sp. object_owner, SP. object_name,
(select SQL _text from V $ sqlarea SA where Sa. address = sp. address and SA. hash_value = sp. hash_value) sqltext,
(select executions from V $ sqlarea SA where Sa. address = sp. address and SA. hash_value = sp. hash_value) no_of_full_scans,
(select lpad (nvl (TRIM (to_char (num_rows), ''), 15 ,'') | '|
lpad (nvl (TRIM (to_char (blocks), ''), 15 ,'') | '| buffer_pool
from dba_tables
where table_name = sp. object_name and owner = sp. object_owner) "rows | blocks | pool"
from V $ SQL _plan sp
where Operation = 'table access' and Options = 'full' and object_owner in ('zlhis ')
order by 1, 2;

3. display the read data volume (bytes)
Select to_char (sysdate, 'yyyymm') as tjyf, A. object_owner, A. object_name, C. Bytes/1024/1024, sum (B. Executions)
From
(Select object_owner, object_name, hash_value
From v $ SQL _plan
Where object_owner not in ('sys ', 'system', 'dbsnmp', 'outln ', 'perfstat', 'public', 'sqlab', 'wmsys ') and Options = 'full'
Group by object_owner, object_name, hash_value),
V $ sqlarea B, dba_segments C
Where a. hash_value = B. hash_value
And a. object_owner = C. Owner
And a. object_name = C. segment_name
And C. segment_type = 'table'
Group by to_char (sysdate, 'yyyymmm '), A. object_owner, A. object_name, C. Bytes/1024/1024
Order by sum (B. Executions );