Shulou(Shulou.com)05/31 Report--

This article mainly introduces "analyzing Oracle AWR report". In daily operation, I believe many people have doubts about analyzing Oracle AWR report. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "analyzing Oracle AWR report". Next, please follow the editor to study!

After ORACLE10G, awr is an internal component of oracle

The report cannot include database start and stop actions

1. Control the parameters of the awr mechanism, statistics level

SYS@orcl11g > show parameter statistics_level

NAME TYPE VALUE

Statistics_level string TYPICAL

Statistics_level:

Basic-close awr

Typical-typical

All-collect more detailed information

SYS@orcl11g > select STATISTICS_NAME, ACTIVATION_LEVEL from V$STATISTICS_LEVEL

STATISTICS_NAME ACTIVAT

-

Buffer Cache Advice TYPICAL

MTTR Advice TYPICAL

Timed Statistics TYPICAL

Timed OS Statistics ALL

Segment Level Statistics TYPICAL

PGA Advice TYPICAL

Plan Execution Statistics ALL

Shared Pool Advice TYPICAL

Modification Monitoring TYPICAL

Longops Statistics TYPICAL

Bind Data Capture TYPICAL

Ultrafast Latch Statistics TYPICAL

Threshold-based Alerts TYPICAL

Global Cache Statistics TYPICAL

Global Cache CPU Statistics ALL

Active Session History TYPICAL

Undo Advisor, Alerts and Fast Ramp up TYPICAL

Streams Pool Advice TYPICAL

Time Model Events TYPICAL

Plan Execution Sampling TYPICAL

Automated Maintenance Tasks TYPICAL

SQL Monitoring TYPICAL

Adaptive Thresholds Enabled TYPICAL

Visual IOSTATIONS * statistics TYPICAL

24 rows selected.

How long 2.awr information is retained, and how long awr snapshots are collected

The default awr information retention time is 8 days. The time interval for snapshot collection is: 1 hour. Snapshots are saved in the sysaux tablespace.

-- modified command:

Begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (12960 and 30); end

-- indicates that the retention period is set to: 6002409 = 12960, and the snapshot collection interval is set to 30 minutes.

SYS@orcl11g > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

971282091 + 00000 00000 DEFAULT 30mm 00.0 + 00009 0000lv 00.0

The saving time of automatic adjustment is 8 days. The above modification orders can only be executed in more than 8 days, and 7 days in 10g.

3. Generate awr report

[oracle@memory admin] $pwd

/ u01/app/oracle/product/11.2.0/db_home1/rdbms/admin

[oracle@db253 admin] $ls awr*

Awrblmig.sql awrextr.sql awrginp.sql awrinpnm.sql awrrpt.sql

Awrddinp.sql awrgdinp.sql awrgrpti.sql awrinput.sql awrsqrpi.sql

Awrddrpi.sql awrgdrpi.sql awrgrpt.sql awrload.sql awrsqrpt.sql

Awrddrpt.sql awrgdrpt.sql awrinfo.sql awrrpti.sql

SYS@orcl11g > @? / rdbms/admin/awrrpt.sql

Current Instance

~

DB Id DB Name Inst Num Instance

--

915341431 ORCL11G 1 orcl11g 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

.

Instances in this Workload Repository schema

~

DB Id Inst Num DB Name Instance Host

* 915341431 1 ORCL11G orcl11g g11252.neves.com

Using 915341431 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: 2-displays the snapshot information of the last 2 days. The snapshot used to generate the report cannot cross the database start and stop action.

Listing the last 2 days of Completed Snapshots

Instance DB Name Snap Id Snap Started Snap Level

--

Orcl11g ORCL11G 2 27 Mar 2013 09:52 1

3 27 Mar 2013 11:00 1

4 27 Mar 2013 12:00 1

5 27 Mar 2013 13:00 1

6 27 Mar 2013 14:00 1

7 27 Mar 2013 15:00 1

8 27 Mar 2013 16:00 1

9 27 Mar 2013 17:00 1

10 28 Mar 2013 09:17 1

11 28 Mar 2013 10:00 1

Specify the Begin and End Snapshot Ids

~ ~

Enter value for begin_snap: 3

Begin Snapshot Id specified: 3

Enter value for end_snap: 5-the longer you span the time, the easier it is to cover up some problems, and some problems will be diluted because of the long time.

Specify the Report Name

~ ~ ~

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

Press to continue, otherwise enter an alternative.

Enter value for report_name: / home/oracle/awr_3_5.html

*

View the current AWR (automatic workload repository) save policy:

Col SNAP_INTERVAL format a20

Col RETENTION format a20

Select * from dba_hist_wr_control

View a snapshot of AWR ID

SELECT SNAP_ID

To_char (BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh34:mi:ss') BEGIN_INTERVAL_TIME

To_char (STARTUP_TIME,'yyyy-mm-dd hh34:mi:ss') STARTUP_TIME

FROM dba_hist_snapshot ORDER BY 1

Create an AWR report with a user other than sys (SCOTT):

CONNECT / AS SYSDBA

GRANT ADVISOR TO SCOTT

GRANT SELECT_CATALOG_ROLE TO SCOTT

GRANT EXECUTE ON sys.dbms_workload_repository TO SCOTT

Create a snapshot manually:

SYS@prod > exec dbms_workload_repository.create_snapshot

At this point, the study of "analyzing the Oracle AWR report" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.