查找全表扫描比较严重的SQL方法1

分为两步，这样查询比较快些

1.首先定位SQL_id

select  SQL_ID

  from v$sql_plan v
 where v.operation = 'TABLE ACCESS'
   and v.OPTIONS = 'FULL'
   and length(object_owner)=4
   and object_owner like 'XX%'
   and io_cost>10000

   and exists (select 1 from v$session_longops r where r.sql_id=v.sql_id)

2.然后查询相关信息（重要的参考，执行次数，程序端）

select sql_text,
       sql_id,
       executions,
       parse_calls,
       disk_reads,
       buffer_gets,
       SHARABLE_MEM,
       cput_time,
       module,
       parsing_schema_name
  from v$sqlarea
 where sql_id in ('3z0hqrbqb8344',
                  '3bq1vgtcwc73h',
                  'cjrgdu5qpwbym',
                  '865n3jzy70d3s'')