Search <book_title>...

Veritas InfoScale™ 8.0.2 Storage and Availability Management for Oracle Databases - AIX, Linux, Solaris

Last Published: 2023-06-12

Product(s): InfoScale & Storage Foundation (8.0.2)

Platform: AIX,Linux,Solaris

Section I. Storage Foundation High Availability (SFHA) management solutions for Oracle databases
1. Overview of Storage Foundation for Databases
Section II. Deploying Oracle with Veritas InfoScale products
Section III. Configuring Storage Foundation for Database (SFDB) tools
1. Configuring and managing the Storage Foundation for Databases repository database
2. Configuring authentication for Storage Foundation for Databases (SFDB) tools
Section IV. Improving Oracle database performance
Section V. Using point-in-time copies
Section VI. Optimizing storage costs for Oracle
Section VII. Managing Oracle disaster recovery
1. Using Storage Foundation for Databases (SFDB) tools in a replicated environment
2. Using volume snapshots in a replicated environment
Section VIII. Storage Foundation for Databases administrative reference
Section IX. Reference

Using AWR statistics to identify sub-file objects for potential relocation

You can use Oracle's Automatic Workload Repository (AWR) statistics to identify sub-file objects for potential relocation by identifying key properties such as activity levels. The Oracle database collects these statistics for a variety of purposes, including making self-management decisions. See the guidelines below for enabling AWR and generating AWR reports.

To enable AWR statistics collection

Specify the level of collection for database and operating system statistics by setting the initialization parameter: STATISTICS_LEVEL:
- TYPICAL: is the default setting. This setting ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.
- ALL: additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
- BASIC: disables the collection of many of the important statistics required by Oracle database features and functionality.

Veritas recommends setting STATISTICS_LEVEL = typical .This parameter can be dynamically changed when database is online.

To change the STATICS_LEVEL

Use the following:

SQL> alter system set statistics_level=typical;

An AWR report shows data captured between two snapshots (or two points in time). The AWR reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains the workload profile of the system for the selected range of snapshots.

To generate an HTML or text report for a range of snapshot IDs

Run the awrrpt.sql script at the SQL prompt:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify whether you want an HTML or a text report.
```
Enter value for report_type: text
```
Specify the number of days for which you want to list snapshot IDs.
```
Enter value for num_days: 2
```
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
```
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: Sample segments by physical read

Owner	Tablespace name	Object Name	Object type	Physical reads	%Total
TPCC	USERS	ITEM_ID	INDEX	13,551	43.59
TPCC	USERS	ITEM	TABLE	7,718	24.83
TPCC	USERS	CUSTOMER	TABLE	4,403	14.16
TPCC	USERS	STOCK	TABLE	3,692	11.88
TPCC	USERS	ORDERS	TABLE	352	1.13

ITEM_ID and ITEM from the sample segments reported above are the two most likely candidates for relocation to a higher performance tier. The commands to relocate them are:

$ dbdst_obj_move -S $ORACLE_SID -H $ORACLE_HOME \
-i ITEM_ID -c SSD_TIER

$ dbdst_obj_move -S $ORACLE_SID -H $ORACLE_HOME \
-t ITEM -c SSD_TIER

For more on using the dbdst_obj_move command:

See Optimizing sub-file database object placement using SmartTier for Oracle.