Detailed explanation of the management and maintenance of Oracle AWR
- 小云云Original
- 2017-12-11 15:10:461659browse
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
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; /
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
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;
/
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; /
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
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
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
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
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; /
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% )
## 4. Generate AWR report
--Generate AWR report under single instance
SQL> @?/rdbms/admin/awrrpt.sql
--Generate AWR report in RAC environment
SQL> @$ORACLE_HOME/rdbms/admin/awrgrpt.sql
--Specify database instance to generate AWR report
SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql
--Generate SQL statement AWR report
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
--Specify the instance to generate the SQL statement AWR report
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
--Generate the comparative AWR report
SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
--Generate comparative AWR report in RAC environment
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql
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!