Detailed Management and Maintenance of Oracle AWR, oracleawr

Detailed Management and Maintenance of Oracle AWR, oracleawr

Preface

Zookeeper AWR is short for Automatic Workload Repository. AWR management is mainly for snapshot and baseline management. For example, set the snapshot interval, delete the snapshot, and set the snapshot retention time. The baseline is created, deleted, and automatically created. This article mainly describes these two parts.

I. snapshot Management

  By default, the Oracle database generates a snapshot every hour and retains the 8-day statistical information in the workload database. If necessary, you can use the dbms_workload_repository program to manually create, delete, or modify snapshots. You can use the OEM and dbms_workload_repository packages to manage snapshots.

1. manually create snapshots

Zookeeper uses the following process to manually create snapshots. After creation, you can view all snapshot information in the data dictionary DBA_HIST_SNAPSHOT.

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();END;/SELECT snap_id,   dbid,   begin_interval_time,   end_interval_timeFROM  dba_hist_snapshotORDER BY end_interval_time DESC; SNAP_ID  DBID BEGIN_INTERVAL_TIME   END_INTERVAL_TIME---------- ---------- ---------------------------- --------------------------  164 41924548 14-APR-17 09.38.19.467 AM 14-APR-17 10.51.21.886 AM  163 41924548 14-APR-17 09.00.10.470 AM 14-APR-17 09.38.19.467 AM  162 41924548 14-APR-17 08.00.07.242 AM 14-APR-17 09.00.10.470 AM  161 41924548 14-APR-17 07.00.04.120 AM 14-APR-17 08.00.07.242 AM

2. delete a snapshot

The following procedure deletes snapshots of snap_id from 162 to 164. If 41924548 is not specified, the default database is used.

BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 162,       high_snap_id => 164, dbid => 41924548);END;/

3. Modify the snapshot retention Interval

-- View the current retention policy, as shown below, which is the default value. The collection interval is 1 hour, and the data is retained for 8 days. SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL retention topnsql ---------- -------------------- ------------------ ---------- 41924548 + 00000 01:00:00. 0 + 00008 00:00:00. 0 DEFAULT -- change it to keep for 5 days, the collection interval is 30 minutes, and topnsql is 50 BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (retention => 10*24*60, interval => 30, topnsql => 50, dbid => 41924548); END;/-- view the modified result SQL> select * from orders; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- ------------------ ---------------------- 41924548 + 00000 00:30:00. 0 + 00010 00:00:00. 0 50

Ii. Baseline Management

  AWR baselines can be created and deleted manually or automatically. To create a baseline automatically, you must first create a baseline sample. The main description is as follows.

1. Create a baseline

Creating a snapshot baseline depends on a snapshot. Therefore, you should obtain the snap_id of the required time range before creating the baseline. You can obtain it by querying the data dictionary DBA_HIST_SNAPSHOT, after the baseline is created, you can query the baseline information from the data dictionary DBA_HIST_BASELINE.

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 160, end_snap_id => 161, baseline_name => 'peak _ baseline ', dbid => 41924548, expiration => 10); END; /SQL> SELECT baseline_id, 2 baseline_name, 3 baseline_type, 4 expiration, 5 creation_time 6 FROM dba_hist_baseline; BASELINE_ID BASELINE_NAME BASELINE_TYPE expiration creation _ ----------- hour ------------- ---------- --------- 1 peak_baseline STATIC 10 14-APR-17 0 hour MOVING_WINDOW 07-APR-17 -- create a baseline at a specific time, the following example shows BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_time => TO_DATE ('2017-04-14 6:00:00 ', 'yyyy-mm-dd hh24: mi: ss '), end_time => TO_DATE ('2017-04-14 8:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), baseline_name => 'peak _ baseline2 ', expiration => 10); END ;/

In the preceding example, a baseline with a range of 160-161 is created, and the baseline name is peak_baseline. The retention period is 10 days. After more than 10 days, the baseline will be deleted and the corresponding snapshots will also be deleted. If the expiration time is specified, the baseline and corresponding snapshots are permanently retained.

2. delete a baseline

BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak_baseline',     cascade => FALSE, dbid => 41924548);END;/

In the preceding example, the baseline named peak_baseline is deleted and the cascade value is set to false. That is to say, the corresponding snapshots are not deleted when the baseline is deleted.

3. rename a baseline

BEGIN commit (old_baseline_name => 'peak _ baseline ', new_baseline_name => 'peak _ mondays', dbid => 41924548); END;/-- Test Result SQL> SELECT baseline_id, 2 baseline_name, 3 baseline_type, 4 expiration, 5 creation_time 6 from expiration; your BASELINE_NAME limit expiration CREATION _ ----------- begin certificate ---------- --------- 1 hour STATIC 10 14-APR-17 0 hour MOVING_WINDOW limit

In the preceding example, modify the baseline name from peak_baseline to peak_mondays.

4. Modify the baseline Retention Value of the default mobile window.

-- Check the default parameter baseline_name, baseline_type, baseline_name = 'System _ MOVING_WINDOW ', BASELINE_NAME BASELINE_TYPE, baseline_type, BASELINE_TYPE, baseline_window, baseline_window, dbid => 41924548); END;/-- window_size is a day, can only be less than or equal to the current snapshot retention time, otherwise the ERROR is reported, as follows: ERROR at line 1: ORA-13541: system moving window baseline size (864000) greater than retention (691200) ORA-06512: at "SYS. DBMS_WORKLOAD_REPOSITORY ", line 686ORA-06512: at line 2

5. Manage baseline samples

Create a single baseline Template

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (start_time => TO_DATE ('2017-04-14 17:00:00 ', 'yyyy-mm-dd hh24: mi: ss '), end_time => TO_DATE ('1970-04-14 19:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), baseline_name => 'baseline _ 100 ', template_name => 'template _ 000000', expiration => 10, dbid => 140414); END;/-- if the baseline sample creation time is earlier than the current time, the following ERROR is received: ERROR at line 1: ORA-13537: invalid input for create baseline template (end_time, end_time is less than SYSDATE) ORA-06512: at "SYS. DBMS_WORKLOAD_REPOSITORY ", line 768ORA-06512: at" SYS. DBMS_WORKLOAD_REPOSITORY ", line 818ORA-06512: at line 2

In the preceding example, we create a single baseline sample and specify the corresponding time range, baseline name, and retention period. The corresponding snapshots within the time range will be retained, and the baseline can be used for subsequent comparison when performance problems are detected.

Create duplicate baseline samples

Repeated baseline samples in zookeeper indicate that Oracle will automatically create a baseline for US based on the set samples within a specific time range in the future. For example, you can create a duplicate baseline sample so that the baseline is automatically generated every Monday from to on January 1, 2017.

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; BEGIN Week (day_of_week => 'monday', hour_in_day => 9, duration => 2, expiration => 30, start_time => '2017-04-14 09:00:00 ', end_time => '2017-12-31 11:00:00', baseline_name_prefix => 'baseline _ pai_mondays _', template_name => 'template _ tem_mondays ', dbid => 41924548); END;/-- Author: Leshami -- Blog: http://blog.csdn.net/leshami -- QQ (Weixin ): 645746311 -- view the created baseline sample SQL> select t. template_name, 2 t. template_type, 3 t. start_time, 4 t. end_time, 5 t. day_of_week, 6 t. hour_in_day, 7 t. duration 8 from week t; TEMPLATE_NAME TEMPLATE _ START_TIME END_TIME hour DURATION period --------- hour ----------- --------- -------- hour SINGLE 17:00:00 19: 00: hour REPEATING 09:00:00 11:00:00 MONDAY 17 3

In the preceding example, we create a baseline that repeats every Monday (day_of_week) starting from January 1, April 14, 2017. The baseline starts at (hour_in_day ), the duration is 2 hours (duration) and is valid for 30 days (expiration). The start and end time of the entire baseline ranges from 09:00:00 to 11:00:00, the baseline sample name and baseline prefix name are also specified.

Baseline sample Deletion

BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (     template_name => 'template_140414',     dbid => 41924548);END;/

In the preceding example, we delete the baseline sample we created earlier and specify the sample name.

3. AWR space usage

All the data of the zookeeper AWR snapshot is filled in the SYSAUX tablespace. Therefore, the longer the snapshot is to be retained, the larger the SYSAUX space occupied. You can also use awrinfo. SQL to view the detailed information of each object that occupies SYSAUX.

SQL> @?/rdbms/admin/awrinfo.sqlThis script will report general AWR information~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Specify the Report File Name~~~~~~~~~~~~~~~~~~~~~~~~~~~~The default report file name is awrinfo.txt. To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name:Using the report name awrinfo.txtNo errors.     -- Author : LeshamiNo errors.     -- Blog : http://blog.csdn.net/leshami~~~~~~~~~~~~~~~   -- QQ  : 645746311 AWR INFO Report~~~~~~~~~~~~~~~Report generated at19:48:53 on Apr 14, 2017 ( Friday ) in Timezone +08:00Warning: Non Default AWR Setting!--------------------------------------------------------------------------------Snapshot interval is 60 minutes and Retention is 8 days  DB_ID DB Name HOST_PLATFORM   INST STARTUP_TIME  LAST_ASH_SID PAR------------ ------- ----------------------- ---- ----------------- ------------ ---* 41924548 ORA11G ydq - Linux x86 64-bit  1 15:18:26 (04/07)  617410 NO########################################################(I) AWR Snapshots Information########################################################*****************************************************(1a) SYSAUX usage - Schema breakdown (dba_segments)*****************************************************|| Total SYSAUX size      617.4 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )|| Schema SYS   occupies   206.6 MB ( 33.5% )| Schema XDB   occupies   157.7 MB ( 25.5% )| Schema APEX_030200 occupies    85.4 MB ( 13.8% )| Schema MDSYS  occupies    73.9 MB ( 12.0% )

#4. generate an AWR report

-- Generate an AWR report under a single instance

SQL> @?/rdbms/admin/awrrpt.sql

-- Generate an AWR report in the RAC Environment

SQL> @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

-- Specify a database instance to generate an AWR report

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

-- Generate an SQL statement AWR report

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

-- Specify an instance to generate an SQL statement AWR report

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

-- Generate a comparative AWR report

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

-- Generate a comparative AWR report in the RAC Environment

@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

5. Important views and data dictionaries related to AWR

V $ active_session_history: displays activity of Active Database sessions, sampling once per second

V $ metric and v $ metric_history:

  Provides metric data to track system performance. Views are organized into several groups, which are defined in the v $ metricgroup view.

DBA_HIST_ACTIVE_SESS_HISTORY

Zookeeper displays the history of active sessions in the memory.

DBA_HIST_BASELINE
  Displays the captured baseline information.

DBA_HIST_BASELINE_DETAILS

Detailed baseline information

DBA_HIST_BASELINE_TEMPLATE

Zookeeper baseline template information

DBA_HIST_DATABASE_INSTANCE

Zookeeper database environment

DBA_HIST_DB_CACHE_ADVICE

Physical reads under different cache sizes are predicted based on historical data

DBA_HIST_DISPATCHER

Zookeeper: information about the scheduling process in each snapshot

DBA_HIST_DYN_REMASTER_STATS

Zookeeper dynamic remastering process Statistics

DBA_HIST_IOSTAT_DETAIL
  Historical I/O information based on the type and function not seen

DBA_HIST_SHARED_SERVER_SUMMARY

Zookeeper Shared Server statistics

DBA_HIST_SNAPSHOT

  Snapshot Information

DBA_HIST_ SQL _PLAN

Zookeeper execution plan

DBA_HIST_WR_CONTROL

Zookeeper AWR control information

6. More references

• Oracle AWR feature description
• Linux/Unix shell automatically sends AWR report
• Linux/Unix shell automatically sends AWR report (2)

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.