Veritas Storage Foundation 5.1 SP1: Storage and Availability Management for Oracle (5900-1504, April 2011)

To generate an HTML or text report for a range of snapshot IDs
1
Run the awrrpt.sql script at the SQL prompt:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
2
Specify whether you want an HTML or a text report.
Enter value for report_type: text
3
Specify the number of days for which you want to list snapshot IDs.
Enter value for num_days: 2
4
After the list displays, you are prompted for the beginning and ending
snapshot Id for the workload repository report.
Enter value for begin_snap: 150
Enter value for end_snap: 160
5
Accept the default report name or enter a report name. The default name is
accepted in the following example:
Enter value for report_name:
Using the report name awrrpt_1_150_160
The workload repository report is generated.
You can use the AWR reports to identify candidate tables or indexes for moving
to alternative storage. The AWR report has two sections which are useful for
sub-file SmartTier. For a directed move of an object to a desired tier, the Segment
Statistics portion of the AWR report can be used. Here is an example from TPCC
workload. Using the Segment Statistics hot table/index can be moved to SSD tier.
Table 15-1
Sample segments by physical read
%TotalPhysical
reads
Object typeObject
Name
Tablespace
name
Owner
43.5913,551INDEXITEM_IDUSERSTPCC
24.837,718TABLEITEMUSERSTPCC
14.164,403TABLECUSTOMERUSERSTPCC
11.883,692TABLESTOCKUSERSTPCC
1.13352TABLEORDERSUSERSTPCC
SmartTier for Oracle use cases
SmartTier for Oracle sub-file use cases
276