Oracle manually generated AWR report method records

The AWR(Automatic Workload Repository) Report is an important tool for our daily database performance assessment and problem SQL discovery. Mastering the AWR report is an important skill in developing and operating DBA work.

The AWR reporting principle is based on the timed mirroring capabilities of the Oracle database. By default, theOracle Database Daemon collects the system's current state image at a certain interval (one hour) and is saved in the database. When generating an AWR report, you only need to specify the time period for the analysis (the starting mirror number and the end mirror number) to generate the profiling situation for that time period. The time that the AWR image is saved in the database is around one months.

Now Oracle 10g , theAWR report replaces the original Statspack report as a mainstream performance analysis report. You can typically generate view AWR reports from an OEM(Oracle Enterprise Manager Console) platform. In OEMs , it is much easier to use the graphical approach. This article describes ways to generate AWR using manual scripting, out of the limitations of OEMs .

1. Run the script

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

[Email protected]/]$ Ls-l | grep test

Drwxr-xr-x. 2 Oracle oinstall 4096 June 13:01 test

[[Email protected]/]$ CD test

Launch development tools such as Sqlplus and invoke build scripts. Program scripts are generally stored in the rdbms/admin under the $ORACLE _home , with the name awrrpt.sql.

[Email protected] test]$ Sqlplus/nolog

sql*plus:release11.2.0.1.0 Production on Tue June 21 13:04:44 2011

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

Sql> Conn/as SYSDBA

Connected.

- -Invoke script, generate file

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

Then enter the report parameter input module.

2. Input report Parameters

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

ü input Generate report type, currently AWR provides txt and HTML two formats. You need to confirm the build format, which is the default HTML format.

Current Instance

~~~~~~~~~~~~~~~~

DB Id db Name Inst Num Instance

----------- ------------ -------- ------------

4143510747 ora11g 1 ora11g

Specify the report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would the HTML report, or a plain the text report?

Enter ' HTML ' for the HTML report, or ' text ' for plain text

Defaults to ' HTML '

ü report involving days range

After the report is started, basic information such as the name of the build instance is displayed.

By default,AWR retains the image information for one months. When manually generated, you need to confirm the timeframe for generating the AWR report. In general, especially in the production environment, we usually set the 1-7 Day 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) would 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 start and end snapshot numbers

After entering the number of days, theAWR generated code will list the snapshot mirror points in the range of days for input selection.

Listing the last 3 days of completed snapshots

Snap

Instance DB Name snap Id snap Started level

------------ ------------ --------- ------------------ -----

ora11g ora11g 1789 June 2011 13:01 1

1790 June 2011 14:00 1

1791 June 2011 15:00 1

1792 June 2011 16:00 1

(space reasons, there are omitted ...) ）

1811 June 2011 11:00 1

1812 June 2011 12:00 1

1813 June 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 time range listed.

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 report name

The last is to determine the name of the generated report. The default name is generally used.

Specify the report Name

~~~~~~~~~~~~~~~~~~~~~~~

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

Press to continue, otherwise enter an alternative.

Enter value for Report_name:

After that, there is a lot of output, not to be described here. Finally, you are prompted to report a successful build.

Report written to awrrpt_1_1796_1813.html

The corresponding report file can then be seen on the specified directory.

[Email protected] test]$ ls-l

Total 508

-rw-r--r--. 1 Oracle oinstall 515262 June 13:10 awrrpt_1_1796_1813.html

3, explain two problems

First, the HTML -formatted report is generated here. If you require the txt format to be generated, select the text format Report during the build process.

Specify the report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would the HTML report, or a plain the text report?

Enter ' HTML ' for the 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

[Email protected] test]$ ls-l

Total 692

-rw-r--r--. 1 Oracle oinstall 180601 June 13:27 Awrrpt_1_1789_1800.txt

-rw-r--r--. 1 Oracle oinstall 515262 June 13:10 awrrpt_1_1796_1813.html

The second one is the way the script is called. The sqlplus client used at the time of invocation can be on the Oracle server natively (telnet) or on the client machine. The author suggests that the build should be made on the client's native computer, which avoids the work of reporting files back and forth. However, it is best to ensure that the client version matches the server version.

4. Conclusion

Manually generated awr reports that can be avoided by oem Constraints, and is highly flexible. This document, the right to be a memo.