Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to manually generate the AWR run-time comparison report, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

AWR (Automatic Workload Repository) report is an important performance diagnostic tool launched after Oracle 10g. AWR is a powerful extension of the Statspack report. With the help of AWR, we can easily analyze and evaluate the performance of the Oracle database during a specific working period, and finally achieve the goal of finding bottlenecks and tuning.

As a DBA, manual generation of AWR reports is one of the basic skills of the job. In the previous "manually generating AWR report Records" (http://space.itpub.net/17203031/viewspace-700471)), the author has described in detail how to generate AWR reports under the sqlplus tool.

1. From AWR to AWR Compare Period Report

AWR is a product system, and we can actually do more according to the system operation data collected on a regular basis. In the mainstream process of operation and maintenance management and tuning, the concept of baseline baseline runs through all the time. The system runs load Baseline, key SQL execution plan Baseline, and so on, which appear repeatedly. In short, the so-called Baseline is simply a set of running load indicators or execution plan indicators. When you think there is a problem with the system, you can compare Baseline as a standard to find out where the problem lies. Furthermore, Baseline can be used as an emergency means to force the use of coping production (SQL Profile, SPM).

We do need such gadgets in our general tuning process. For example: what are the significant differences between peak and trough periods in the database? How do the performance indicators of the system change after tuning? At this time, generate two AWR, look at the comparison is indeed a way. However, Oracle has provided us with AWR Compare Period Report to facilitate the implementation of such functionality.

In principle, AWR Compare Period Report is not difficult to understand. Oracle saves the performance data of snap at different times in a series of data dictionary tables in the SYSAUX tablespace. As long as it is reasonably organized, compare report is not a difficult thing.

2. Generate the report manually

This article is a record article, which still uses the manual sqlplus method to generate the report.

Log in to sqlplus and run the script

Log in to the specified system using sqlplus.

D:\ > sqlplus / nolog

SQL*Plus: Release 11.2.0.1.0 Production on Monday March 5 09:02:42 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL > (login process is slightly … )

-- call the script

SQL > @? / rdbms/admin/awrddrpt.sql

Note: in sqlplus,? Represents the $ORACLE_HOME path. The specified awrddrpt.sql script is also true that the script is executed on the SQL local to the client. If you use a development tool such as PL/SQL Developer, specify a direct path.

Enter the basic report type

First of all, like AWR reports, Compare Period Report also supports both TEXT and HTML formats. During the creation process, we need to make a selection.

Current Instance

~

DB Id DB Id DB Name Inst Num Inst Num Instance

--

2351142467 2351142467 NBSTEST 11 NBSTEST

Specify the Report Type

~ ~ ~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or' text' for plain text

Defaults to 'html'

Enter a value for report_type:

Type Specified: html

Enter the project of the first comparison period

Since you want to make a comparison, you first need to specify a time range for comparison. This process is the same as the general AWR report, and the process is not repeated.

Instances in this Workload Repository schema

~

DB Id Inst Num DB Name Instance Host

--

* 2351142467 1 NBSTEST NBSTEST P550_05_LC

Database Id and Instance Number for the First Pair of Snapshots

~ ~ ~

Using 2351142467 for Database Id for the first pair of snapshots

Using 1 for Instance Number for the first pair of snapshots

Specify the number of days of snapshots to choose from

~ ~ ~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing without

Specifying a number lists all completed snapshots.

-- enter the comparison time range

Enter a value for num_days: 3

After that, Oracle lists the snap information for the three-day range.

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

--

NBSTEST NBSTEST 10251 03 March 2012 00:00 1

10252 03 March 2012 01:00 1

(the reason for the length of the space, omit the part. )

10305 05 March 2012 06:00 1

Snap

Instance DB Name Snap Id Snap Started Level

--

NBSTEST NBSTEST 10306 05 March 2012 07:00 1

10307 05 March 2012 08:00 1

10308 05 March 2012 09:00 1

Specify the First Pair of Begin and End Snapshot Ids

~ ~ ~

Enter a value for begin_snap:

-- specify a time slice

Specify the First Pair of Begin and End Snapshot Ids

~ ~ ~

Enter the value of begin_snap: 10296

First Begin Snapshot Id specified: 10296

Enter the value of end_snap: 10298

The start and end times snap are set as the first time.

Enter the project of the second comparison period

The second report time snap information is basically the same and will not be repeated.

Instances in this Workload Repository schema

~

DB Id Inst Num DB Name Instance Host

--

* 2351142467 1 NBSTEST NBSTEST P550_05_LC

Database Id and Instance Number for the Second Pair of Snapshots

~ ~ ~

Using 2351142467 for Database Id for the second pair of snapshots

Using 1 for Instance Number for the second pair of snapshots

Specify the number of days of snapshots to choose from

~ ~ ~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing without

Specifying a number lists all completed snapshots.

Enter a value for num_days2:

Enter a value for num_days2: 1

Listing the last day's Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

--

NBSTEST NBSTEST 10299 05 March 2012 00:00 1

(for reasons of space, omit. )

10306 05 March 2012 07:00 1

10307 05 March 2012 08:00 1

10308 05 March 2012 09:00 1

Specify the Second Pair of Begin and End Snapshot Ids

~ ~ ~

Enter a value for begin_snap2:

-- enter a time slice

Specify the Second Pair of Begin and End Snapshot Ids

~ ~ ~

Enter the value of begin_snap2: 10306

Second Begin Snapshot Id specified: 10306

Enter the value of end_snap2: 10308

Specify the name of the generated report and generate the report

Specify the Report Name

~ ~ ~

The default report file name is awrdiff_1_10296_1_10306.html To use this name

Press to continue, otherwise enter an alternative.

Enter the value of report_name:-- use the default name and leave it blank

Using the report name awrdiff_1_10296_1_10306.html

Back to Top

Report written to awrdiff_1_10296_1_10306.html

In the directory, we can find the report file we generated.

3. an overview of the content of the report

Unlike the AWR report, Compare Period Report emphasizes contrast. In all projects, the first and second are compared, and the differences are listed. This has a lot of benefits for us to do operations such as load comparison.

It is convenient for us to make performance comparison and comparison before and after tuning.

4. Conclusion

AWR reports and their derivatives are important tools for performance tuning and problem discovery. Making good use of these tools is an important prerequisite for us to complete our work, fulfill our responsibilities and move forward. AWR Compare Period Report is a very convenient tool for comparing baselines and tuning results.

On how to manually generate AWR runtime comparison report to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.