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

The output in Listing 4-1 shows that we have a number of tables that are full-scanned. Now, add criteria to only
display tables that are full-scanned with a size greater than 2% the size of a specified buffer cache size, as provided in
Listing 4-2:
Listing 4-2. lst04-02-segstat-scans-large.sql
SQL> select * from (
select tab.owner,tab.table_name,count(ss.table_scans_delta) cnt
from dba_tables tab, dba_hist_seg_stat ss, dba_objects obj
where ss.obj#=obj.object_id
and obj.object_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
and tab.table_name=obj.object_name
and tab.blocks >=
( select .02 * ((&&cache_size_gb*1024*1024*1024)/&&db_blk_size) from dual )
group by tab.owner,tab.table_name
order by 3 desc)
where rownum < &&display_rows
/
Enter value for cache_size_gb: 4
Enter value for db_blk_size: 8192
old 12: select .02 * ((&&cache_size_gb*1024*1024*1024)/&&db_blk_size)
new 12: select .02 * ((4*1024*1024*1024)/8192)
Enter value for display_rows: 6
old 17: where rownum < &&display_rows
new 17: where rownum < 6
Owner Table Table Scans
------------ ------------------------------ ------------
APPLSYS FND_CONCURRENT_REQUESTS 859
APPLSYS WF_ITEM_ATTRIBUTE_VALUES 839
APPLSYS WF_ITEM_ACTIVITY_STATUSES 804
APPLSYS WF_COMMENTS 757
WSH WSH_EXCEPTIONS 745
5 rows selected.
SQL>
Expanding on this example, you can try different combinations of your target buffer cache size to see what the
impact will be for potentially larger SGA sizes. Oracle Exadata X2-2 compute nodes have 96 GB of physical memory
and with hugepages configured, you can have relatively large SGA sizes. Re-run the query with different values for
&&cache_size_gb to compare the differences.
Let's expand our example in Listing 4-3 to only include full table scans on tables without BLOB, CLOB, BFILE, or
LONG data types, as these data types are not Smart Scan eligible:
Listing 4-3. lst04-03-segstat-scans-large-supp.sql
SQL> select *
{ ... code from lst04-02-segstat-scans-large-supp.sql ... }
and not exists
( select 'x' from dba_tab_columns c
where tab.owner=c.owner