Workload Qualification - Oracle Exadata Recipes: A Problem-Solution Approach - page 81

Time spent
SQL ID Child# Operation + Options Segment (Secs)
-------------- ------- ---------------------- --------------------------- ------------
c4g3860gd5adm 1 TABLE ACCESS FULL APPLSYS.WF_COMMENTS 1883794.33
c4g3860gd5adm 4 TABLE ACCESS FULL APPLSYS.WF_COMMENTS 603418.17
6v2ckrt9j1j6g 0 INDEX FAST FULL SCAN BOM.CST_ITEM_COST_DETAILS_N 50592.33
1qfhrwxmh4u9s 0 TABLE ACCESS FULL APPLSYS.WF_NOTIFICATION_OUT 29762.67
... Additional SQL operations omitted for brevity
Now you can add in criteria to our SQL statement to limit the output to SQL operations doing full scans on
segments that are large (in other words, those that will qualify for direct reads), as displayed in Listing 4-5:
Listing 4-5. lst04-05-scans-ash-large.sql
SQL> select
{ ... Code from lst04-04-scans-ash.sql ...}
and seg.blocks >
(select .02 * ((&&target_cache_size_gb*1024*1024*1024)/&&target_db_blk_size) from dual))
group by sql_id,sql_child_number,sql_plan_operation, sql_plan_options,
owner, object_name,blocks
order by 6 desc
/
old 25: select .02 * ((&&target_cache_size_gb*1024*1024*1024)/&&target_db_blk_size)
new 25: select .02 * ((10*1024*1024*1024)/8192)
SQL ID Child# Operation + Options Segment Blks Time (Secs)
------------- ------ ------------------- -------------------------- ------- ----------
c4g3860gd5adm 1 TABLE ACCESS FULL APPLSYS.WF_COMMENTS 70048 1883794.33
c4g3860gd5adm 4 TABLE ACCESS FULL APPLSYS.WF_COMMENTS 70048 603418.17
1qfhrwxmh4u9s 0 TABLE ACCESS FULL APPLSYS.WF_NOTIFICATION_OUT 15072 29762.67
26chjdn37vcb7 0 TABLE ACCESS FULL CN.CN_NOTIFY_LOG_ALL 0688 18969.67
... Additional output omitted
In Listing 4-5, we've queried our ASH data to show SQL statements that included a full table scan or fast full
index scan operations on segments larger than 2% the size of a 10 GB database buffer cache. What if we want to also
examine SQL with filter predicates not eligible for Smart Scan? AWR tables do not publish the FILTER_PREDICATES and
ACCESS_PREDICATES columns, unfortunately. But if your library cache happens to still have some of your “important”
cursors in it, you can revise the previous query on DBA_HIST_SQL_PLAN to use V$SQL_PLA N as displayed in Listing 4-6:
Listing 4-6. lst04-06-scans-sqlplans-large-supp.sql
SQL> select s.sql_id,s.object_owner||'.'||s.object_name obj,
s.operation||' '||s.options op
from v$sql_plan s, dba_segments seg
where s.options like '%FULL%'
and s.object_name=seg.segment_name
and s.object_owner=seg.owner
and seg.blocks >
( select .02 * ((&&cache_size_gb*1024*1024*1024)/&&db_blk_size) from dual
)
and not exists
( select 'x'