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

Since Oracle 10g began to support AWR, compared with Statspack, in addition to more new content for reference, HTML save format also provides a lot of convenience for viewing documents.

The AWR infrastructure consists of two main parts:

1. An in-memory statistics collection tool that is used by Oracle Database 11g components to collect statistics. For performance reasons, these statistics are stored in memory. Statistics stored in memory can be accessed through the dynamic performance (V$) view.

2. An AWR snapshot representing the persistent part of the tool. AWR snapshots can be accessed through the data dictionary view and EnterpriseManager Database Control.

It provides services to internal Oracle server components to collect, process, maintain, and use performance statistics for problem detection and self-optimization.

The AWR contains hundreds of tables, all of which belong to the SYSMAN schema and are stored in the SYSAUX tablespace.

1. The full name of AWR is Automatic Workload Repository, and the content is based on the data stored in the AWR database, provided that the corresponding license has been purchased.

2. AWR collects statistics once every 60 minutes by default, saves them for a week, and then deletes them. Statistics are saved in the database.

3. In order to collect statistics correctly, STATISTICS_LEVEL is set to TYPICAL (default) or ALL.

4. AWR consists of many tables that belong to the SYS schema and are usually saved in the SYSAUX tablespace. All AWR table names start with the identifier "WR": metadata (WRM$), historical / mutable data (WRH$, WRR$, and WRI$), and AWR tables (WRI$) associated with the consultant (advisor) function. The DBA view that can be queried against the AWR repository, starting with DBA_HIST.

5. You can use the DBMS_WORKLOAD_REPOSITORY package to modify the snapshot collection interval.

Exec dbms_workload_repository.modify_snapshot_settings-

(retention= > 20160, interval= > 15)

Use the modify_snapshot_settings process of the dbms_workload_repository package to modify the snapshot collection parameters, that is, to modify the collection every 15 minutes, with a retention time of 20160 minutes (14 days).

Setting the interval to 0 stops the collection of all statistics (here I think the translation is ambiguous, which should stop the collection of AWR snapshots, not statistics).

6. Check the current retention time and interval settings of AWR:

Select * from dba_hist_wr_control

7. Create or delete a snapshot:

Exec dbms_workload_repository.create_snapshot

Exec dbms_workload_repository.drop_snapshot_range (low_snap_id= > 1107, high_snap_id= > 1108)

8. View all snapshots:

Select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1

11g collects AWR statistics using a scheduling job named GATHER_STATS_JOB. When the Oracle database is created, the job is automatically created and activated.

10. To view the job, please refer to the view:

Select a.job_name, a.enabled, c.window_name, c.repeat_interval

From dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c

Where job_name='GATHER_STATS_JOB'

And a.schedule_name=b.window_group_name

And b.window_name=c.window_name

JOB_NAME ENABL WINDOW_NAME REPEAT_INTERVAL

-

GATHER_STATS_JOB TRUE WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

GATHER_STATS_JOB TRUE WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

Indicates that there are two windows performing a job that collects statistics. WEEKEND_WINDOW is executed at 00:00 every Saturday. WEEKNIGHT_WINDOW is executed every Monday to Friday at 22:00.

11. How to disable and start jobs:

Exec dbms_scheduler.disable ('GATHER_STATS_JOB')

Exec dbms_scheduler.enable ('GATHER_STATS_JOB')

12. You can run AWR snapshots using the following script:

$ORACLE_HOME/rdbms/admin/awrrpt.sql or awrrpti.sql.

13. A baseline is created in AWR, which is defined as a snapshot within a certain range, which can be used to compare with other snapshots.

Create a baseline:

Exec dbms_workload_repository.create_baseline (start_snap_id= > 1109, end_snap_id= > 1111, baseline_name= > 'EOM Baseline')

View the baseline:

Select baseline_id, baseline_name, start_snap_id, end_snap_id from dba_hist_baseline

Delete the baseline:

Exec dbms_workload_repository.drop_baseline (baseline_name= > 'EOM Baseline', Cascade= > FALSE)

If the parameter Cascade is set to true, all related snapshots will be deleted, and here the 1109 and 1111 related snapshots will be deleted. Otherwise, the AWR automatic process will automatically clear these snapshots

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.