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

1. What is AWR? AWR (Automatic Workload Repository) is an information base for storing historical performance data of oracle database, including a large number of statistical data such as system, session, SQL, etc., stored in SYSAUX table space. The information base contains many tables, all of which start with "WR": WR metadata (WRM$), historical and variable data (WRH$, WRR$ and WRI$), AWR tables (WRI$) related to consultant (advisor) functions. In addition, AWR data can be accessed through the view at the beginning of the DBA_HIST.

The latest AWR information is stored in SGA, and the default MMON process starts the slave process m00x every other hour to create an AWR snapshot and save the data in the SYSAUX tablespace.

two。 How to manage AWR?

2.1 enable AWR

Set STATISTICS_LEVEL to TYPICAL or ALL.

The feature is not enabled if STATISTICS_LEVEL is BASIC,AWR, but AWR statistics can be crawled manually by using the DBMS_WORKLOAD_REPOSITORY package.

2.2 Managing snapshot

2.2.1 about snapshot

The data of AWR is obtained through snapshot. A snapshot is the state of the system at a certain time. By default, the database collects snapshot once an hour and keeps it in WR (Workload Repository) for 8 days (after 11g). Of course, you can also create and delete snapshot manually, mainly using the DBMS_WORKLOAD_REPOSITORY package.

2.2.2 create snapshot

Execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT () to generate a new snapshot, and you can view the existing snapshot through the DBA_HIST_SNAPSHOT view.

BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END;/2.2.3 delete snapshot by default, the snapshot in AWR will be deleted automatically after 8 days, but it can also be deleted manually, as follows:

BEGINDBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id = > 22 graded snapshots = > 32 dbid = > 3310949047); END;/2.2.4 modifies the snapshot setting to modify the time interval for producing snapshots, retention period, and the number of Top Sql (interval, retention, topnsql). At the same time, note that the modification will affect the accuracy of the oracle diagnostic tool. If you want to set a retention of 30 days (43200 minutes), an interval of 30 minutes, and a topsql of the first 100, as follows:

BEGINDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (retention = > 43200 Magnum interval = > 30 topnsql = > 100 Magi dbid = > 3310949047); END;/ can view the modified results through the DBA_HIST_WR_CONTROL view.

SQL > select snap_interval, retention from DBA_HIST_WR_CONTROL

SNAP_INTERVAL RETENTION

-

+ 00000 01 snapshot 00.0 + 00008 00 snapshot 00.02.3 Management snapshot about baselinebaseline is a collection of snapshot for a specific period of time, when an exception occurs, use the snapshot and baseline of the exception period for comparison. Snapshot in baseline is not restricted by AWR retention. 2.3.2 three types of baseline

1.fixed baselines

A fixed baseline is a baseline generated by snapshots in a continuous and fixed period of time, so the selected time period should be the optimal period of the system, representing the optimal level of the system, as a benchmark for performance comparison.

2. The baseline of the moving window baselines mobile window contains all the AWR data within the AWR retention, that is, it is generated by all snapshots during the retention period. This is useful for adaptive thresholds because the database can use all the data during the AWR retention period to calculate the threshold. Oracle automatically maintains mobile window baselines, and the default window size is the current AWR retention period. The window size can be reset, but it must be less than or equal to the AWR retention period, and if you want to increase the moving window, the AWR retention period must increase accordingly. Therefore, when using the adaptive threshold, it can be set to 30 days to get a more accurate threshold.

3. Baseline templates's so-called baseline template means that future baselines are generated according to the configuration of the template, such as specifying a future time period, or 10:00-11:00 every Monday in a future period. The baseline template is divided into a single baseline template (generating one baseline) and a repeated baseline template (generating multiple baselines). Example of a single baseline template: do a test from 09:00 to 11:00 next Saturday, create a single baseline template, and then automatically collect AWR data. An example of repeating the baseline template: create a baseline from 09:00 to 11:00 every Monday for the next three months. 2.3.3 create baseline by default, oracle automatically maintains mobile window baselines, and we can create fixed baselines manually. As follows. Snap_id can be selected from the DBA_HIST_SNAPSHOT view.

BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id = > 270 endpoints snapshots); END;/2.3.4 deletes baseline in order to save disk space, you can delete baseline manually, and the generated baseline can be viewed through the DBA_HIST_BASELINE view. In the following example, delete the peak baseline, keep the corresponding snapshot, and if you want to delete the related snapshot, set cascade to true.

BEGINDBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name = > 'peak baseline',cascade = > FALSE,dbid = > 3310949047); END;/2.3.5 renamed baseline

BEGINDBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (old_baseline_name = > 'peak baseline',new_baseline_name = >' peak mondays',dbid = > 3310949047); END;/2.3.6 resets mobile window baseline window size mobile window baseline default window size is the same as the AWR retention period, but can also be modified, but cannot exceed the AWR retention period. The following example sets the window to 30 days.

BEGINDBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (window_size = > 30 dbid = > 3310949047); the example below for END;/2.3.7 to create a single baseline template is simple and unexplained.

BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (start_time = > to_date), end_time = > to_date ('2018-07-02 17mm hh34:mi:ss'), end_time = > to_date), baseline_name = >' baseline_180702',template_name = > 'template_180702',expiration = > 3310949047); END / 2.3.8 create a duplicate baseline template the following example starts 17:00 on July 2, 2018, creates a baseline from 17:00 to 20:00 (duration=3) on Monday, ends 20:00 template on December 2, 2018, no longer creates a baseline using this as a template, and creates a baseline with an expiration period of 30 days.

BEGINDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (day_of_week = > 'monday',hour_in_day = > 17Grammer = > 3, expiration = > 30Grad hh34:mi:ss'), expiration = > to_date (' 2018-07-02 1700purse 00lyric 00lanyylue hh34:mi:ss'), end_time = > to_date ('2018-12-02 20lance 00lance 00lemagery hh34:mi:ss'), baseline_name_prefix = >' baseline_2018_mondays_' Template_name = > 'template_2018_mondays',dbid = > 3310949047) END;/2.3.9 Delete baseline template baseline template name can be selected in the DBA_HIST_BASELINE_TEMPLATE view.

BEGINDBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (template_name = > 'template_180702',dbid = > 3310949047); what is the END;/3. AWR report? The AWR report is a database performance report generated by two snapshot, which contains the statistics between the two snapshot. When there is a performance problem in the database, you can use the AWR report to analyze and locate. Scripts to generate different AWR reports: this example AWR report

@? / rdbms/admin/awrrpt

Select the instance number in RAC

@? / rdbms/admin/awrrpti

AWR comparison report

@? / rdbms/admin/awrddrpt

RAC Global AWR report

@? / rdbms/admin/awrgrpt

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/gathering-database-statistics.html#GUID-CBB1716F-2B20-4575-ADCE-94E33BEA53EF

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.