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

and tab.table_name=c.table_name
and c.data_type in ('BLOB','CLOB','BFILE','LONG')
)
{ ... code from lst04-02-segstat-scans-large-supp.sql ... }
/
Enter value for cache_size_gb: 10
Enter value for db_blk_size: 8192
old 12: select .02 * ((&&cache_size_gb*1024*1024*1024)/&&db_blk_size)
new 12: select .02 * ((10*1024*1024*1024)/8192)
Enter value for display_rows: 6
old 23: where rownum < &&display_rows
new 23: where rownum < 6
Owner Table Table Scans
------------ ------------------------------- ------------
APPLSYS FND_CONCURRENT_REQUESTS 860
APPLSYS WF_ITEM_ATTRIBUTE_VALUES 840
APPLSYS WF_ITEM_ACTIVITY_STATUSES 805
APPLSYS WF_COMMENTS 757
WSH WSH_EXCEPTIONS 746
5 rows selected.
Thus far, we've shown some interesting information that may be helpful in your Exadata Smart Scan analysis, but
unless you're intimately familiar with your application code, a simple listing of table scans on a large table may not
necessarily allow you to make intelligent decisions when planning for Exadata. So let's expand our analysis to examine
data in Oracle's historical Active Session History view, DBA_HIST_ACTIVE_SESS_HISTORY . In the query in Listing 4-4,
we will search for full scan SQL plan operations:
Listing 4-4. lst04-04-scans-ash.sql
SQL> select sql_id,sql_child_number,sql_plan_operation||' '||sql_plan_options sqlop,
owner||'.'||object_name seg,
sum(sql_secs_per_snap)/60 dbt from (
SELECT ash.snap_id,ash.sample_id,ash.sql_id, ash.sql_child_number,
ash.sql_plan_operation, ash.sql_plan_options, obj.owner, obj.object_name,
10*(count(sample_id) over (partition by ash.sql_id,ash.sql_child_number,ash.snap_id))
sql_secs_per_snap
FROM dba_hist_active_sess_history ash,
dba_objects obj
WHERE ash.wait_class = 'User I/O'
AND ( ( ash.sql_plan_operation = 'TABLE ACCESS' AND ash.sql_plan_options LIKE '%FULL%')
OR ( ash.sql_plan_operation = 'INDEX' AND ash.sql_plan_options LIKE '%FAST%FULL%'))
and obj.object_id=ash.current_obj#
and obj.owner not in ('SYS','SYSTEM')
and obj.object_type in ('TABLE','INDEX')
)
group by sql_id,sql_child_number,sql_plan_operation, sql_plan_options,owner, object_name
order by 5 desc
/