Oracle Manual generation of AWR reporting method Records __oracle

The AWR(Automatic workload Repository) Report is an important tool for our daily database performance Assessment and problem SQL discovery. Proficiency in the AWR report, is to do a good job of development, operation and maintenance of the DBA important basic skills.

the principle of awr reporting is based on the timing mirroring function of the Oracle database. By default, the Oracle database daemon collects the current state image of the system at a certain interval (one hour) and is saved in the database. When you generate a AWR report, you can generate profiling for that time period by simply specifying the time period for the analysis (starting mirror number and ending mirror number). The AWR mirror is kept in the database for a period of about one months.

Current Oracle after 10g , the AWR report replaced the original Statspack report as a mainstream performance analysis report. View AWR reports can typically be generated from the OEM (Oracle Enterprise managerconsole) platform. In OEMs, it is easier to use a graphical approach. This article describes the method of using manual scripting to generate AWR, leaving out OEM limitations.

1. Run the script

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

[Oracle@bspdev/]$ ls-l | Greptest

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

[Oracle@bspdev/]$ Cdtest

start Sqlplus, etc. development tool to invoke the build script. Program scripts are generally stored in the rdbms/admin under $oracle_home, and the name is Awrrpt.sql.

[Oracle@bspdev test]$ Sqlplus/nolog

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

Copyright (c) 1982, 2009,oracle. Allrights 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.

U input generate report type, currently AWR provides TXT and HTML two formats. You need to confirm the build format, which is HTML format by default.

Current Instance

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

DBId dbname Instnum Instance

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

4143510747 ORA 11G 1ora11g

Specify the The Type of the

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

Would as an HTML, or a plaintext?

Enter ' HTML ' for a htmlreport, or ' text ' for plain text

Defaults to ' HTML '

• The number of days covered by the report

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

By default, AWR retains the mirror information for one months. When manually generated, you need to confirm the time range that generated the AWR report. Under normal circumstances, especially in the production environment, we usually set 1-7 days is enough.

Instances in this workload Repositoryschema

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

DBId instnum DB Name Instance Host

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

* 4143510747 1ora11g ora11g bspdev.local

Domain

Using 4143510747 for DatabaseId

Using 1for Instance Number

Specify the number of days of snapshots Tochoose from

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

Entering the number of days (n) would resultin the most recent

(n) Days of snapshots beinglisted. Pressingwithout

Specifying a number lists all completedsnapshots.

Enter value for Num_days:3

U Enter start and end snapshot numbers

After you enter the number of days, the AWR generation code lists the snapshot mirror points in the range of days for input selections.

Listing the last 3 days of completedsnapshots

Snap

Instance dbname Snapid snapstarted level

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

ora11g ora11g 178920 June 2011 13:01 1

179020 June 2011 14:00 1

179120 June 2011 15:00 1

179220 June 2011 16:00 1

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

181121 June 2011 11:00 1

181221 June 2011 12:00 1

181321 June 2011 13:00 1

Specify the Begin and end Snapshotids

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

After that, we need to enter the start and End snap numbers based on the time range listed.

Specify the Begin and end Snapshotids

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

Enter value for begin_snap:1796

Begin Snapshot Id specified:1796

Enter value for end_snap:1813

U Determine report Name

The last is to determine the name of the build report. Generally use the default name on it.

Specify the "the" Name

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

The default file name isawrrpt_1_1796_1813.html. To use Thisname,

Press to continue, otherwise enter analternative.

Enter value Forreport_name:

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

Toawrrpt_1_1796_1813.html written

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

[Oracle@bspdev test]$ Ls-l

Total 508

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

3, to illustrate two issues

First, the HTML-formatted report is generated here. If you want to generate a txt format, select the text Format report during the build process.

Specify the

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

Would as an HTML, or a plaintext?

Enter ' HTML ' for a HTML, or ' text ' for plain text

Defaults to ' HTML '

Enter value for Report_type:text

Type Specified:text

End of the

Toawrrpt_1_1789_1800.txt written

[Oracle@bspdev test]$ Ls-l

Total 692

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

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

the second is the question of how to invoke the script. The Sqlplus client used on the call can be on the Oracle server Native (telnet) or on the client machine local. The author recommends that you build on the client computer so that you can avoid the work of copying the report files back and forth. However, it is best to ensure that the client version matches the server version.

 

4. Conclusion

The manual generation of AWR reports can avoid OEM constraints and high degree of flexibility. In this document, the right to be a memo.