查找全表扫描比较严重的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'')