AWR报告的使用

最新推荐文章于 2021-04-04 19:35:46 发布

最新推荐文章于 2021-04-04 19:35:46 发布 · 210 阅读

文章标签：

本文介绍如何使用Oracle的AWR和ADDM报告功能，通过SQL命令生成数据库运行状态报告，并分析报告以优化数据库性能。AWR报告提供了数据库整体运行情况的概览，包括执行时间长的SQL查询、参数文件等；而ADDM报告则更深入地分析了数据库性能，提供优化建议。

[oracle@localhost admin]$ pwd

/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin

[oracle@localhost admin]$ sqlplus sys/lubinsu as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 19 15:12:52 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/awrrpt.sql

Current Instance

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

DB Id DB Name Inst Num Instance

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

1326384392 ORCL 1 orclsid

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: html（也是缺省值）

Type Specified: html

Instances in this Workload Repository schema

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

DB Id Inst Num DB Name Instance Host

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

* 1326384392 1 ORCL orclsid localhost.lo

caldomain

Using 1326384392 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 <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

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

orclsid ORCL 220 19 May 2013 10:18 1

221 19 May 2013 11:00 1

222 19 May 2013 11:42 1

223 19 May 2013 12:00 1

224 19 May 2013 12:30 1

225 19 May 2013 13:00 1

226 19 May 2013 13:30 1

227 19 May 2013 14:00 1

228 19 May 2013 14:30 1

229 19 May 2013 15:00 1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 228

Begin Snapshot Id specified: 228

Enter value for end_snap: 229

End Snapshot Id specified: 229

Specify the Report Name

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

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

press <return> to continue, otherwise enter an alternative.

可以设置文件名，报告分析之后：我们可以在浏览器中查看报告（这里列出了数据库的整体运行情况，执行时间长的前几条SQL，参数文件，等等，注意AWR只是一个报告，并不会帮助我们分析该如何优化，在后面的ADDM报告则会为我们提供优化的建议）：

--======================================================================

另外有一种addm报告会帮助我们分析，并且提供必要的调优建议：

SQL> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/addmrpt.sql

Current Instance

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

DB Id DB Name Inst Num Instance

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

1326384392 ORCL 1 orclsid

Instances in this Workload Repository schema

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

DB Id Inst Num DB Name Instance Host

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

* 1326384392 1 ORCL orclsid localhost.lo

caldomain

Using 1326384392 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 <return> without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

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

orclsid ORCL 220 19 May 2013 10:18 1

221 19 May 2013 11:00 1

222 19 May 2013 11:42 1

223 19 May 2013 12:00 1

224 19 May 2013 12:30 1

225 19 May 2013 13:00 1

226 19 May 2013 13:30 1

227 19 May 2013 14:00 1

228 19 May 2013 14:30 1

229 19 May 2013 15:00 1

230 19 May 2013 15:31 1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap:228

Enter value for end_snap: 229

End Snapshot Id specified: 229

Specify the Report Name

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

The default report file name is addmrpt_1_228_229.txt. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 可以使用默认文件名

DETAILED ADDM REPORT FOR TASK 'TASK_1814' WITH ID 1814

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

Analysis Period: 19-MAY-2013 from 13:30:50 to 14:00:57

Database ID/Instance: 1326384392/1

Database/Instance Names: ORCL/orclsid

Host Name: localhost.localdomain

Database Version: 10.2.0.1.0

Snapshot Range: from 226 to 227

Database Time: 11 seconds

Average Database Load: 0 active sessions

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

THERE WAS NOT ENOUGH DATABASE TIME FOR ADDM ANALYSIS.

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

ADDITIONAL INFORMATION

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

There was no significant database activity to run the ADDM.

The database's maintenance windows were active during 99% of the analysis

period.

The analysis of I/O performance is based on the default assumption that the

average read time for one database block is 10000 micro-seconds.

An explanation of the terminology used in this report is available when you

run the report with the 'ALL' level of detail.