Introduction to Oracle 10 Gb AWR performance optimization tools

AWR (Automatic Workload Repository) is a new tool provided by Oracle 10 Gb to collect database statistics. It mainly includes the AWR memory area, historical data storage files, ASH and other components.

The main content of the AWR report includes the following parts:

Preface:This is the first section of the AWR report, which describes the environment, including the database name, database version, whether it is a RAC node, and the collection time of the snapshot report.

Summary:Including the waiting event segment, Load Profile segment, instance efficiency statistical segment, Shared Pool statistical segment, and Cache Size segment. The most important part is the waiting event segment, it tells us what performance bottlenecks the database encounters during the snapshot time. They will be the primary candidates for performance adjustment or problem diagnosis. The following Top Time Event data is taken from the AWR report generated by my database.

Event	Waits	Time (s)	Avg Wait (MS)	% Total Call Time	Wait Class
CPU time		17,306		99.6
Db file scattered read	19,469,037	4,448	0	25.6	User I/O
Log file parallel write	492,258	221	0	1.3	System I/O
Log file sync	253,519	143	1	. 8	Commit
Read by other session	421,942	73	0	. 4	User I/O

The report shows that "db file scattered read" is the most important wait event in the snapshot time, because the session is waiting for the completion of a "multiblock IO, this is usually caused by full table scan or quick index scan on a large table. Next, you can check which SQL statements execute these queries, which tables the query operations are concentrated on, and check the SQL PLAN to see if indexes are used.

SQL Section: Invalid SQL statements are the main cause of poor performance. This section classifies and sorts the SQL statements in the interval according to the execution time, logical read, disk read, and other indicators, unlike STATSPACK, the AWR report can directly find the SQL text and client process information sending requests, which greatly improves the SQL analysis efficiency.

Instance activity statistics: This part is the statistics on the activities of various internal modules of the instance and various resource usage during the snapshot period, mainly including CPU usage, SQL * Net message, the row access and PGA usage of the link.

Segment statistics: Tell which segments (including tables and indexes) have the highest disk read operations during the snapshot period. This information can help us decide whether to re-create the index, or partition the segments to reduce the I/O that occurs on these data files.

Some common operations of AWR include:

Modify the snapshot collection and storage time, create a Baseline, export and migrate AWR data. The following describes how to use these functions.

By default, AWR automatically collects a system load snapshot every hour, and this snapshot is saved for one week by default. However, Oracle 10 Gb provides the following commands to modify the snapshot collection interval and storage time.

execute dbms_workload_repository.modify_snapshot_settings(interval => 120, retention => 20160);

The preceding command sets the snapshot collection interval to 2 hours and the storage time to two weeks.

You can create a baseline for the AWR data and save the baseline for future analysis and comparison. The command is as follows,

execute dbms_workload_repository.create_baseline (start_snap_id =>1, end_snap_id =>100, baseline_name => 'baseline name', dbid => yourdbid);

AWR data can be exported and migrated to other databases for later analysis. Oracle10g Release2 provides two new tools for export and migration. AWR data work.

DBMS_SWRF_INTERNAL.AWR_EXTRACT is used to export data. The usage is as follows:

Begin DBMS_SWRF_INTERNAL.AWR_EXTRACT ( dmpfile   => 'awr_data.dmp', dmpdir    => 'TMP_DIR', bid       => 302, eid       => 305); end;

The dmpfile parameter specifies the name of the AWR data file to be exported. dmpdir specifies the directory path for storing the exported file, bid indicates the start snapshot number, and eid indicates the end snapshot number.

DBMS_SWRF_INTERNAL is used to migrate AWR data files to other databases. The process of importing AWR data is divided into two steps. First, use the DBMS_SWRF_INTERNAL.AWR_LOAD method to import the data to a temporary mode. In this example, AWR_TEST (you can also customize the name). The specific method is as follows:

begin DBMS_SWRF_INTERNAL.AWR_LOAD ( SCHNAME => 'AWR_TEST', dmpfile => 'awr_data', dmpdir =>  'TMP_DIR'); end;

Next, we need to transfer the AWR data to the SYS mode. The operation is as follows:

exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

In this way, the AWR data import has been completed. Now we can use this method to create a database dedicated to storing AWR data for centralized management and analysis of performance statistics of multiple database instances.

Related Articles]

• Simplified Oracle 10 Gb user management

• Update data rows using the Oracle 10g MERGE statement

• Use Hash Functions in Oracle 10 Gb to increase query speed