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

What is the method of manually generating AWR reports? for this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

This article is to share with you what the method of manually generating AWR report is, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

AWR (Automatic Workload Repository) report is an important means for our daily database performance evaluation and problem SQL discovery. Proficient in AWR reporting is an important basic skill for development, operation and maintenance DBA work.

The principle of AWR reporting is based on the timing mirroring function of the Oracle database. By default, the Oracle database background process collects images of the current state of the system at regular intervals (one hour) and saves them in the database. When you generate an AWR report, you only need to specify the time period for the analysis (the start mirror number and the end mirror number) to generate the performance analysis for that period. The AWR image is kept in the database for about a month.

At present, after Oracle10g, AWR report has replaced the original Statspack report to become a mainstream performance analysis report. You can usually generate and view AWR reports from the OEM (Oracle Enterprise Manager Console) platform. In OEM, it is easier to use the graphical method. This article introduces the method of generating AWR by manual script, which is free from the limitation of OEM.

1. Run the script

First, prepare a directory as the path for AWR to generate the report.

[oracle@bspdev /] $ls-l | grep test

Drwxr-xr-x. 2 oracle oinstall 4096 Jun 21 13:01 test

[oracle@bspdev /] $cd test

Start development tools such as sqlplus and call the generation script. Program scripts are generally stored in rdbms/admin under $ORACLE_HOME and are called awrrpt.sql.

[oracle@bspdev test] $sqlplus / nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 21 13:04:44 2011

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

SQL > conn / as sysdba

Connected.

-- call the script to generate the file

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

Then enter the report parameter input module.

2. Enter report parameters

After that, continue to enter a series of report parameters.

U input to generate report type, currently AWR provides both txt and html formats. You need to confirm the generated format. The default is html format.

Current Instance

~

DB Id DB Name Inst Num Instance

--

4143510747 ORA11G 1 ora11g

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'

The report covers the range of days

After you start the report, basic information such as the name of the generated instance is displayed.

By default, AWR retains the mirror information for one month. When generating manually, you need to confirm the time frame within which the AWR report is generated. In general, especially in the production environment, we usually set 1-7 days is enough.

Instances in this Workload Repository schema

~

DB Id Inst Num DB Name Instance Host

--

* 4143510747 1 ORA11G ora11g bspdev.local

Domain

Using 4143510747 for database Id

Using 1 for instance number

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 value for num_days: 3

Enter the snapshot number for the start and end

After entering the number of days information, the AWR generation code lists the snapshot mirror points within the range of days for input.

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

--

Ora11g ORA11G 1789 20 Jun 2011 13:01 1

1790 20 Jun 2011 14:00 1

1791 20 Jun 2011 15:00 1

1792 20 Jun 2011 16:00 1

(for reasons of space, there are omissions. )

1811 21 Jun 2011 11:00 1

1812 21 Jun 2011 12:00 1

1813 21 Jun 2011 13:00 1

Specify the Begin and End Snapshot Ids

~ ~

After that, we need to enter the start and end snap numbers according to the listed time range.

Specify the Begin and End Snapshot Ids

~ ~

Enter value for begin_snap: 1796

Begin Snapshot Id specified: 1796

Enter value for end_snap: 1813

ü determine the name of the report

Finally, determine the name of the generated report. Generally speaking, the default name is fine.

Specify the Report Name

~ ~ ~

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

Press to continue, otherwise enter an alternative.

Enter value for report_name:

After that, there is a lot of output, which will not be repeated here. Finally, it is prompted that the report was generated successfully.

Report written to awrrpt_1_1796_1813.html

Therefore, the corresponding report file can be seen on the specified directory.

[oracle@bspdev test] $ls-l

Total 508

-rw-r--r--. 1 oracle oinstall 515262 Jun 21 13:10 awrrpt_1_1796_1813.html

3. Explain two problems

First, the report in html format generated here. If you want to generate the txt format, select the text format report during the generation process.

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 value for report_type: text

Type Specified: text

End of Report

Report written to awrrpt_1_1789_1800.txt

[oracle@bspdev test] $ls-l

Total 692

-rw-r--r--. 1 oracle oinstall 180601 Jun 21 13:27 awrrpt_1_1789_1800.txt

-rw-r--r--. 1 oracle oinstall 515262 Jun 21 13:10 awrrpt_1_1796_1813.html

The second is the way in which scripts are called. The sqlplus client used in the call can be on the local Oracle server (remote login) or on the client machine. The author suggests that it is generated on the client machine, so that the work of copying report files back and forth can be avoided. However, it is best to make sure that the client version matches the server version.

Manual generation of AWR reports avoids the limitations of OEM and is highly flexible.

This is the answer to the question about what is the method of manually generating AWR report. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.