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

Automatic workload knowledge Base (AWR)

Oracle collects a large amount of statistical information about performance and activity, which is accumulated in memory and periodically written to a table called the automatic workload knowledge Base (AWR). AWR exists as a set of tables and other objects in the database SYSAUX tablespace and exists in the SYSMAN schema.

The collection level of statistics is controlled by the instance parameter statistics_level, which can be set to BASIC, TYPICAL (default), ALL:

Show parameter statistics_level

NAME TYPE VALUE

-

Statistics_level string TYPICAL

TYPICAL collects all the statistics needed for normal tuning and does not collect statistics that adversely affect performance. BASIC almost disables the collection of statistics, and there are no assessable performance benefits. The ALL level, which collects extremely detailed statistics related to the execution of SQL statements, can be used if advanced SQL statement adjustments are made, but has a slight impact on performance when collecting statistics.

Statistics are accumulated in the data structure of SGA memory and are written to disk periodically (by default once per hour), that is, to AWR, which is called an AWR snapshot. Sampling AWR snapshots and writing statistics to disk are done by the background process MMON (manageability monitor process). 11g snapshot is stored for 8 days by default, and 10g is kept for 7 days by default.

You can view the components that reside in the sysaux table space through the view v$sysaux_occupants, and you can see the amount of space occupied by AWR:

Col occupant_name for a30

Select occupant_name, occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name='SM/AWR'

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES

SM/AWR Server Manageability-Automatic Workload Repository 101120

The information collected by AWR is presented through a series of views that can be queried to obtain samples of information from the database, starting with dba_hist_. The underlying tables for these views roughly fall into several categories:

WRM$-headed tables store metadata for AWR

Tables that start with WRH$ store historical data for sampled snapshots

WRI$-led tables store data related to database recommendation functions

The table that starts with WRR$ represents information about the new Oracle 11g features Workload Capture and Workload Replay.

The historical data table of AWR is mainly stored in the SYSAUX table space in the form of partition table, and can be queried through the dba_tab_partitions view.

When the MMON process saves an AWR snapshot, it automatically generates a large number of metrics based on statistics. The creation of the benchmark must be done by DBA. A base is one or more pairs of snapshots that are saved until they are specifically deleted. You can compare metrics derived from the benchmark with those derived from the current activity level to help identify changes in activities and behaviors. You can create benchmarks for specific events and normal operations.

Database Control needs to call procedures in the PL/ SQL package DBMS _ WORKLOAD_REPOSITORY when performing operations, which can adjust the frequency and persistence of snapshots, generate ad hoc snapshots, create and manipulate benchmarks, and generate activity reports between any two snapshots.

Create an ad hoc snapshot:

Exec dbms_workload_repository.create_snapshot

Set the retention time for snapshots and the time interval for collection (in minutes), save for 30 days, and collect every half hour:

Exec dbms_workload_repository.modify_snapshot_settings (retention = > 43200, interval = > 30)

View the interval of snapshot collection (default is 1 hour) and retention time (8 days by default for 11g and 7 days by default for 10g):

Col snap_interval for a30

Col retention for a30

Select dbid, snap_interval, retention from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION

-

2001528686 + 00000 01VOUR 00RU 00.0 + 00008 00UR 00RU 00.0

You can create a baseline for a well-functioning period of time to compare with other reports, which are not deleted because they are out of date:

Exec dbms_workload_repository.create_baseline (start_snap_id= > 487, end_snap_id= > 488, baseline_name= > 'FridayPM')

View the AWR baseline:

Col baseline_name for a30

Select dbid

Baseline_id

Baseline_name

Start_snap_id

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

End_snap_id

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

Creation_time

From dba_hist_baseline

DBID BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME CREATION_TIME

-

2001528686 1 baseline_214_215 2017-02-04 15:00:36 215 2017-02-04 15:41:41 2017-02-04 16:49:19

2001528686 0 SYSTEM_MOVING_WINDOW 2017-01-27 18:00:18 216 2017-02-04 17:00:03 2016-07-23 10:05:47

Query history snapshot:

Select dbid

Instance_number

Snap_id

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

From dba_hist_snapshot

Order by begin_interval_time desc

DBID INSTANCE_NUMBER SNAP_ID BEGIN_INTERVAL_TIME

--

1903404692 1 31179 2017-02-04 15:00:22

1903404692 1 31178 2017-02-04 14:00:09

1903404692 1 31177 2017-02-04 13:00:56

1903404692 1 31176 2017-02-04 12:00:43

1903404692 1 31175 2017-02-04 11:00:30

1903404692 1 31174 2017-02-04 10:00:17

1903404692 1 31173 2017-02-04 09:00:04

1903404692 1 31172 2017-02-04 08:00:51

1903404692 1 31171 2017-02-04 07:00:38

You can see that the snapshot is saved every hour by default.

Query the number of AWR historical snapshots and the time range involved:

Select dbid

Instance_number

To_char (min (begin_interval_time), 'yyyy-mm-dd hh34:mi:ss') begin_time

Min (snap_id) begin_id

To_char (max (begin_interval_time), 'yyyy-mm-dd hh34:mi:ss') end_time

Max (snap_id) end_id

Count (snap_id) amount

From dba_hist_snapshot

Group by dbid, instance_number

DBID INSTANCE_NUMBER BEGIN_TIME BEGIN_ID END_TIME END_ID AMOUNT

1903404692 1 2017-01-27 22:00:21 30994 2017-02-04 15:00:22 31179

Delete the baseline, along with its snapshot:

Exec dbms_workload_repository.drop_baseline (baseline_name = > 'FridayPM', cascade = > true)

Delete a snapshot:

Exec dbms_workload_repository.drop_snapshot_range (low_snap_id = > 487, high_snap_id = > 488)

The command line generates an AWR report:

@?\ rdbms\ admin\ awrrpt.sql

Specify the snapshots to display in recent days, the ID of the front and back snapshots selected for analysis, the generated AWR report file format (default is html), the path and file name of the report, and the default report file generation path is the current path where the SQL*Plus is executed.

The awrrpt.sql script actually calls the DBMS_WORKLOAD_REPOSITORY package to generate the report, which has two main functions for generating the report:

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT, used to generate reports in TEXT format

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML, used to generate reports in HTML format.

"for example, an AWR report can also be generated with the following query parameters: database ID, instance number, start snapshot ID, and end snapshot ID:"

Select * from table (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT (2896903393, 1230232))

You can also generate AWR comparison reports for two periods to make it easy to compare performance differences between two different periods:

@?\ rdbms\ admin\ awrddrpt.sql

Oracle allows AWR data to be exported and migrated to other databases for later analysis. Dbms_swrf_internal.awr_extract can be used to export data, and the awrextr.sql script is used to do this, while the import can be done through the dbms_swrf_internal.awr_load and dbms_swrf_internal.move_to_awr processes, and the awrload.sql script is used to do this.

There is also a report on the storage and distribution of AWR data, showing the usage information of AWR itself, including snapshot information, SYSAUX space usage, AWR components, ASH, and so on:

@?\ rdbms\ admin\ awrinfo.sql

You can also generate an AWR report for a specified SQL statement, and you need to provide SQL ID for execution:

@?\ rdbms\ admin\ awrsqrpi.sql

II. Active session History (ASH)

As a supplement to AWR, there is also an ASH (Active Session History), that is, active session history. ASH is based on V$SESSION and is sampled once a second to record historical events that active sessions are waiting for. The inactive session will not sample, and the sampling work will be completed by the newly introduced background process MMNL, and after the ASH Buffers is full, the ASH information will be filtered and filtered by the process and written to disk through direct path insertion, thus minimizing the impact on database performance.

Whether MMNL writes ASH information to disk is controlled by an implicit parameter: _ ash_disk_write_enable, which defaults to True. On the other hand, the proportion of ASH information written by MMNL is controlled by another implicit parameter: _ ash_disk_filter_ratio, which is filtered and written out by default.

The minimum value of ASH buffers is 1MB, and the maximum value does not exceed 30MB. The data is recorded in SGA memory, and the expected value is recorded for one hour. You can see that this part of the memory is allocated in the shared pool:

Select * from v$sgastat where name = 'ASH buffers'

POOL NAME BYTES

Shared pool ASH buffers 16252928

Whether the ASH function is enabled or not is controlled by an internal hidden parameter: _ ash_enable, which can only be enabled if it is true. The default is true.

The sampling time of ASH is also controlled by an internal hidden parameter: _ ash_sampling_interval, which defaults to 1000 milliseconds, that is, sampling once a second.

The information recorded by the ASH can be accessed through the view v$active_session_history. By default, each active session is sampled once a second, and each sample records a row of information in this view. This part of the content is recorded in memory, and the expected value is to record the content for one hour. This information is refreshed to the AWR load library periodically (hourly) and retained by default for one week. The inner table wrh$_active_session_history is where the view v$active_session_history is stored in AWR. Dba_hist_active_sess_history is a joint presentation of wrh$_active_session_history and several other views, which can usually be used to access AWR historical data.

To generate an ASH report, execute the following script on the command line:

@?\ rdbms\ admin\ ashrpt.sql

The ASH report includes TOP waiting events, TOP SQL, TOP Sessions and other contents, clear and concise, concise and easy to understand. But ASH memory record data is always limited, in order to save historical data, we need AWR.

III. Database consultant framework

1. Automatic database diagnostic monitor (ADDM)

The Oracle database is preconfigured with a set of consultants, the first of which is Automatic Database Diagnostic Monitor (automatic Database Diagnostic Monitor, ADDM). ADDM reports are automatically generated when an AWR snapshot is saved, and whenever a snapshot is generated, the MMON process automatically runs ADDM. Automatically generated ADDM reports always include the time period between the current snapshot and the previous snapshot, so hourly ADDM reports can be accessed by default. You can also manually call ADDM to generate a report that includes the time period between any two snapshots. Both automatic snapshots and manual snapshot collection trigger ADDM.

The ADDM report is cleared after 30 days by default. The ADDM report takes DB time as a measure, and DB time includes CPU time spent on transaction computing and transaction waiting time, that is, the core of DBTime = DB CPU + Waiting Time,ADDM is to reduce DBTime and improve database system throughput. The ADDM report indicates the time consumption caused by all kinds of waiting in the system to the database, and puts forward some suggestions, which are more clear and intuitive.

The command line generates an ADDM report:

@?\ rdbms\ admin\ addmrpt.sql

Here you specify the ID of the front and back snapshots, the path and filename of the generated ADDM report, which is a text format file with the extension LST.

2. Other consultant procedures

In many cases, the ADDM report recommends that one or more other consultants be run. Compared with ADDM, these consultants can give more accurate diagnostic information and advice. These consultants include:

Memory consultant

SQL access, Adjustment, and repair Advisor

Automatically withdraw the consultant

Average recovery time consultant

Data recovery consultant

Duan consultant

3. Automatic maintenance operation

By default, Oracle11g configures three tasks in the AutoTask system after the database is created. These three automatic tasks are:

Collect optimizer statistics

Run Section Advisor

Run SQL tuning Advisor

AotoTask runs in the scheduler's maintenance window, which by default runs four hours from 22:00 on weekdays and 20 hours from 6 a.m. on Saturdays and Sundays. The scheduler is associated with the resource manager, and the resource manager plan activated during the maintenance window ensures by default that the computer resources allocated to the AutoTask job do not exceed 25% of the total to avoid negative impact on other work. The time frame and maximum resource usage of the maintenance window can be adjusted as needed.

For any AutoTask to run, the STATISTICS_LEVEL parameter must be set to TYPICAL (the default) or ALL, which will not run when set to BASIC.

The segment consultant task relies on collecting the history of object statistics built by the task through daily running optimizer statistics. SQL Sizing Advisor relies on AWR statistics collected by the MMON process.

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.