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?
- The innermost SELECT gives us all TABLE ACCESS FULL along with the operation before. Those prev_% fields are generated with lag() over (order by).
- The next layer does the conditional filtering on the first, limiting to PARTITION LIST and the partition numbers (adapt to your own needs).
- 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