Oracle: How to identify SQL doing TABLE ACCESS FULL on a given partition

In my last post, I described a quick show case for Oracle 12c Automatic Big Table Caching. But ABTC is just a crock, nevertheless a useful one. The biggest help from Automatic Big Table Caching for “sustainable” database performance management is the temperature of segments, heated up by TABLE ACCESS FULL. In my case, after enabling ABTC the hottest segment was one I did not expect: A sink containing lots of passive data, a LIST partition holding the passive status. (Not) nice, and now the next question is, who does that?

Finding SQL_IDs doing Full Table Scans on a given table is no magic, V$SQL_PLAN allows it in a very simple way:

select distinct sql_id, plan_hash_value
 from v$sql_plan
 where
 object_owner='ME'
 and operation='TABLE ACCESS'
 and options like '%FULL%'
 and object_name='MYTABLE'
 group by sql_id,plan_hash_value
 order by sql_id,plan_hash_value;

But the challenge is, to find every TABLE ACCESS FULL that comes from a PARTITION LIST operation. Thus, we need a query that allows looking back one row in V$SQL_PLAN, to decide if the TABLE ACCESS FULL is relevant:

How to identify SQL doing TABLE ACCESS FULL on a given partition

select sp_complete.*,
 sa.disk_reads,
 sa.buffer_gets,
 sa.sql_fulltext
 from v$sqlarea sa,(
  select distinct sql_id, plan_hash_value
  from (
   select sql_id,
   plan_hash_value, 
   child_number, 
   id, 
   operation, 
   options,
   object_name,
   object_type,
   partition_start,
   partition_stop,
   lag (operation,1) over (order by sql_id,child_number,id) as prev_operation,
   lag (options,1) over (order by sql_id,child_number,id) as prev_options
   from v$sql_plan sp
   order by sql_id,child_number,id
  ) sph
 where operation='TABLE ACCESS'
  and options like '%FULL%'
  and object_name='MYTABLE'
  and prev_operation='PARTITION LIST'
  and (partition_start='1' or partition_stop='1')
 ) sp_complete
 where sp_complete.sql_id=sa.sql_id
 order by sa.disk_reads desc;

How does the statement work?

1. The innermost SELECT gives us all TABLE ACCESS FULL along with the operation before. Those prev_% fields are generated with lag() over (order by).
2. The next layer does the conditional filtering on the first, limiting to PARTITION LIST and the partition numbers (adapt to your own needs).
3. On the outside, the data is enriched with data from v$sqlarea, but maybe you want something different here – wherever the SQL_ID leads to… 🙂

Be careful, as usual
Martin Klier