Detailed explanation of the management and maintenance of Oracle AWR-Mysql Tutorial-php.cn

AWR is the abbreviation of Automatic Workload Repository, which is called automatic workload data archive in Chinese. The management of AWR is mainly for snapshot and baseline management. For example, set the snapshot interval, delete snapshots, and set the snapshot retention time. For baselines, it is the creation, deletion, automatic creation, etc. of baselines. This article mainly describes the contents of these two parts. This article mainly introduces to you the relevant information about the management and maintenance of Oracle AWR (Automatic Workload Data Archive). The article introduces it in great detail through sample code. It has certain reference learning value for everyone's study or work. Friends who need it Let’s learn with the editor below.

1. Snapshot Management

By default, Oracle database generates a snapshot every hour and retains 8 days of statistical information in the workload library. If necessary, you can use the dbms_workload_repository program to manually create, delete or modify snapshots, etc. Snapshots can be managed using OEM as well as the dbms_workload_repository package.

1. Manually create a snapshot

Use the following process to create a snapshot manually. After the creation is complete, you can view all snapshot-related information in the data dictionary DBA_HIST_SNAPSHOT.

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

SELECT snap_id,
   dbid,
   begin_interval_time,
   end_interval_time
FROM  dba_hist_snapshot
ORDER 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

Copy after login

2. Delete snapshots

The following process will delete the snapshots with snap_id from 162 to 164. When 41924548 is not specified, the current default database is used

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

Copy after login

3. Modify the snapshot retention interval

--查看当前的保留策略，如下，为缺省值，采集间隔为1小时，保留8天
SQL> select * from dba_hist_wr_control;

  DBID SNAP_INTERVAL  RETENTION   TOPNSQL
---------- -------------------- ------------------ ----------
 41924548 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

--下面将其修改为保留5天，采集间隔为30分钟,topnsql为50条
BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 10*24*60,
     interval => 30, topnsql => 50, dbid => 41924548);
END;
/

--查看修改后的结果
SQL> select * from dba_hist_wr_control;

  DBID SNAP_INTERVAL  RETENTION   TOPNSQL
---------- -------------------- ------------------ ----------
 41924548 +00000 00:30:00.0 +00010 00:00:00.0 50

Copy after login

2. Baseline Management

AWR baselines can be created and deleted manually or automatically. For automatic baseline creation, baseline samples must be created first to achieve automatic baseline creation. The main description is as follows.

1. Create a baseline

The creation of the baseline relies on snapshots, so the snap_id of the required time range should be obtained before creation, which can be obtained by querying the data dictionary DBA_HIST_SNAPSHOT, and after the baseline creation is completed Baseline related information can be queried 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_
----------- ----------------------- ------------- ---------- ---------
   1 peak_baseline   STATIC    10 14-APR-17
   0 SYSTEM_MOVING_WINDOW MOVING_WINDOW   07-APR-17

--基于特定时间创建基线，如下示例
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;
/

Copy after login

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

2. Delete baseline

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

Copy after login

In the above example, the baseline named peak_baseline will be deleted, and cascade is specified as false. That is to say, when the baseline is deleted, the corresponding snapshot is not deleted.

3. Rename the baseline

BEGIN
 DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
     old_baseline_name => 'peak_baseline',
     new_baseline_name => 'peak_mondays',
     dbid => 41924548);
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_
----------- ----------------------- ------------- ---------- ---------
   1 peak_mondays   STATIC    10 14-APR-17
   0 SYSTEM_MOVING_WINDOW MOVING_WINDOW   07-APR-17

Copy after login

In the above example, change the name of the baseline from peak_baseline to peak_mondays.

4. Modify the default moving window baseline retention value

--查看缺省的window_size
SELECT baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline
WHERE baseline_name = 'SYSTEM_MOVING_WINDOW';

BASELINE_NAME   BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW  MOVING_WINDOW     8

BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
     window_size => 7,
     dbid => 41924548);
END;
/

--window_size为天，只能够小于等于当前快照保留时间，否则报错，如下：

ERROR at line 1:
ORA-13541: system moving window baseline size (864000)
greater than retention (691200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 686
ORA-06512: at line 2

Copy after login

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 ('2017-04-14 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  baseline_name => 'baseline_140414',
  template_name => 'template_140414',
  expiration  => 10,
  dbid   => 41924548);
END;
/

--如果创建基线样本时间小于当前时间，则收到如下错误
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 768
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 818
ORA-06512: at line 2

Copy after login

In the above example, we created a single baseline sample and specified the corresponding time range, baseline name and retention Deadline etc. Then the corresponding snapshots within this time range will be retained, and this baseline can be used for subsequent comparison when performance problems are discovered.

Create repeated baseline samples

Repeated baseline samples refer to a specific time range in the future. Oracle will automatically create a baseline for us with reference to this set sample. For example, you can create a repeated baseline sample so that a baseline is automatically generated every Monday from 9:00 to 11:00 in 2017.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
  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_2017_mondays_',
  template_name   => 'template_2017_mondays',
  dbid     => 41924548);
END;
/

-- Author  : Leshami
-- Blog  : http://blog.csdn.net/leshami
-- QQ(Weixin) : 645746311

--查看已经创建的基线样本
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 dba_hist_baseline_template t;

TEMPLATE_NAME   TEMPLATE_ START_TIME   END_TIME   DAY_OF_WE HOUR_IN_DAY DURATION
--------------------- --------- ------------------- ------------------- --------- ----------- --------
template_140414  SINGLE 2017-04-14 17:00:00 2017-04-14 19:00:00
template_2017_mondays REPEATING 2017-04-14 09:00:00 2017-12-31 11:00:00 MONDAY    17  3

Copy after login

In the above example we created a repeat every Monday (day_of_week) starting from April 14, 2017, which will automatically generate a baseline. The start time is 9 o'clock (hour_in_day), its duration is 2 hours (duration), and its validity period is 30 days (expiration). The start and end time range of the entire baseline is: 2017-04-14 09:00:00 to 2017-12- 31 11:00:00, and also specifies the name of the baseline sample and the baseline prefix name.

Deletion of baseline samples

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

Copy after login

In the above example we delete the previously created baseline sample by specifying the sample name. Can.

3. AWR space occupation

AWR snapshot data is all filled in the SYSAUX table space. Therefore, if you want to keep the snapshot for a longer period of time, it means that the more SYSAUX space you need to occupy. At the same time, you can view the detailed information of each object occupying SYSAUX through awrinfo.sql.

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

This 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.txt
No errors.     -- Author : Leshami
No errors.     -- Blog : http://blog.csdn.net/leshami
~~~~~~~~~~~~~~~   -- QQ  : 645746311 
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
19:48:53 on Apr 14, 2017 ( Friday ) in Timezone +08:00


Warning: 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% )

Copy after login

## 4. Generate AWR report

--Generate AWR report under single instance

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

Copy after login

--Generate AWR report in RAC environment

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

Copy after login

--Specify database instance to generate AWR report

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

Copy after login

--Generate SQL statement AWR report

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

Copy after login

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

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

Copy after login

--Generate the comparative AWR report

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

Copy after login

--Generate comparative AWR report in RAC environment

@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

Copy after login

5. Important views related to AWR and data dictionary

v$active_session_history: Displays the activity of active database sessions, sampled once per second

v$metric and v$metric_history:

Provide 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

Display the active session history information in memory

DBA_HIST_BASELINE
Display the information of the captured baseline

DBA_HIST_BASELINE_DETAILS

Display the detailed information of a specific baseline

DBA_HIST_BASELINE_TEMPLATE

Baseline template related information

DBA_HIST_DATABASE_INSTANCE

Database environment

DBA_HIST_DB_CACHE_ADVICE

Based on historical data prediction under different cache sizes Physical read

DBA_HIST_DISPATCHER

Information about the scheduling process under each snapshot

DBA_HIST_DYN_REMASTER_STATS

Statistical information about the dynamic remastering process

DBA_HIST_IOSTAT_DETAIL
Historical I/O information by unseen types and functions

DBA_HIST_SHARED_SERVER_SUMMARY

Shared server statistical information

DBA_HIST_SNAPSHOT

Snapshot information

DBA_HIST_SQL_PLAN

Execution plan

DBA_HIST_WR_CONTROL

AWR control information

Related recommendations:

mysql Batch file for stopping and starting the oracle database

How to use PDO to access the oracle database using PDO Detailed explanation

Oracle string contains numbers ,Solution to the sorting problem of special symbols

The above is the detailed content of Detailed explanation of the management and maintenance of Oracle AWR. For more information, please follow other related articles on the PHP Chinese website!