Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of AWR management and maintenance in Oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

I. snapshot management

"   by default, the Oracle database generates snapshots every hour and retains statistics for 8 days in the workload library." If necessary, you can manually create, delete or modify snapshots using the dbms_workload_repository program. Snapshots can be managed using OEM as well as dbms_workload_repository packages.

1. Create a snapshot manually

   uses the following procedure to manually create a snapshot, and 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_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

two。 Delete snapshot

   the following procedure deletes snapshots of snap_id from 162to164. Use the current default database when 41924548 is not specified

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

3. Modify the retention interval of a snapshot

-- check the current retention policy, as follows: default, collection interval of 1 hour, and retention of 8 days SQL > select * from dba_hist_wr_control DBID SNAP_INTERVAL RETENTION TOPNSQL- 41924548 + 00000 01 00.0 + 00008 0000lv 00.0 DEFAULT-- modify it to keep for 5 days The collection interval is 30 minutes, and the topnsql is 50 BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (retention = > 1024060, interval = > 30, topnsql = > 50, dbid = > 41924548). END;/-- to view the modified result SQL > select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL- 41924548 + 00000 00lv 30lv 00.0 + 00010 00lv 00lv 00.0 50

II. Baseline management

   AWR baselines can be created and deleted manually or automatically. For automatic baseline creation, it is necessary to create a baseline sample before automatic baseline creation can be achieved. The main description is as follows.

1. Create a baseline

The creation of    baselines depends 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 the baseline-related information can be queried from the data dictionary DBA_HIST_BASELINE after the baseline is created.

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id = > 160,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 Murray APRMY 17-create a baseline based on a specific time The following is an example of BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_time = > TO_DATE ('2017-04-14 6 yyyy-mm-dd hh34:mi:ss'), end_time = > TO_DATE (' 2017-04-14 8 yyyy-mm-dd hh34:mi:ss'), baseline_name = > 'peak_baseline2', expiration = > 10) END;/

In the above example,    creates a baseline with a range of 160161 named peak_baseline with a retention time of 10 days. After more than 10 days, the baseline is deleted and the corresponding snapshot is deleted. If a specified expiration period is specified, the baseline and the corresponding snapshot are permanently retained.

two。 Delete baseline

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

In the above example,    deletes the baseline named peak_baseline and specifies that cascade is false, which means that while deleting the baseline, the corresponding snapshot is not deleted.

3. Baseline renaming

BEGIN DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (old_baseline_name = > 'peak_baseline', new_baseline_name = >' peak_mondays', dbid = > 41924548); END;/-- verification result 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, the name of the baseline is changed from peak_baseline to peak_mondays.

4. Modify the default mobile window baseline retention value

-- check the default window_sizeSELECT baseline_name, baseline_type, moving_window_sizeFROM dba_hist_baselineWHERE baseline_name = 'SYSTEM_MOVING_WINDOW' BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE---- SYSTEM_MOVING_WINDOW MOVING_WINDOW 8BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (window_size = > 7, dbid = > 41924548); END /-window_size is a day and can only be less than or equal to the current snapshot retention time, otherwise an 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 yyyy-mm-dd hh34:mi:ss'), end_time = > TO_DATE (' 2017-04-14 1900 yyyy-mm-dd hh34:mi:ss'), baseline_name = > 'baseline_140414', template_name = > template_140414', expiration = > 10, dbid = > 41924548); END /-- if the baseline sample time is less than the current time, the following error ERROR at line 1:ORA-13537 is received: 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 above example, we created a single baseline sample and specified the corresponding time range, baseline name, retention period, and so on. Then the corresponding snapshot within this time frame is retained, and this baseline can be used for later comparison when a performance problem is found.

Create a duplicate baseline sample

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

SQL > alter session set nls_date_format='yyyy-mm-dd hh34: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-1409), end_time = > '2017-12-31 11); baseline_name_prefix = >' baseline_2017_mondays_', template_name = > 'template_2017_mondays', dbid = > 41924548) /-- Author: Leshami-- Blog: http://blog.csdn.net/leshami-- QQ (Weixin): 645746311-View the baseline samples that have been created 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:00template_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 from April 14, 2017 (day_of_week) will automatically generate a baseline with a start time of 9 o'clock (hour_in_day), a duration of 2 hours (duration), and a validity period of 30 days (expiration), with a starting and ending time range of 2017-04-14 09:00:00 to 2017-12-31 11:00:00 The name of the baseline sample and the baseline prefix name are also specified.

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 baseline sample we created earlier by specifying the sample name.

III. Occupation of AWR space

The data of the    AWR snapshot is all populated in the SYSAUX tablespace. So the longer the snapshot you want to keep, the more SYSAUX space you need. At the same time, you can view the details of the SYSAUX occupied by each object through awrinfo.sql.

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 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, Murray, Murray, 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 ORA11G ydq 07) 617410 NO# # (I) AWR Snapshots Information###* * * (1a) SYSAUX usage-Schema breakdown (dba_segments) * | | Total SYSAUX size 617.4 MB ( 2% of 32768.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 reports under a single instance

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

-- generate AWR reports 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 SQL statement AWR report

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

-- generate comparative AWR reports

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

-- generate comparative AWR reports in RAC environment

@ $ORACLE_HOME/rdbms/admin/awrgdrpt.sql

Important views and data dictionaries related to AWR

V$active_session_history: displays the activity of active database sessions, sampled once per second

V$metric and v$metric_history:

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

DBA_HIST_ACTIVE_SESS_HISTORY

   displays historical information of active sessions in memory

DBA_HIST_BASELINE

   displays information about captured baselines

DBA_HIST_BASELINE_DETAILS

   shows the details of a specific baseline

DBA_HIST_BASELINE_TEMPLATE

   baseline template related information

DBA_HIST_DATABASE_INSTANCE

   database environment

DBA_HIST_DB_CACHE_ADVICE

   predicts physical readings under different cache size based on historical data

DBA_HIST_DISPATCHER

Information of the scheduling process under each snapshot of   

DBA_HIST_DYN_REMASTER_STATS

Statistics for    dynamic remastering processes

DBA_HIST_IOSTAT_DETAIL

Historical    O information by unseen type and function

DBA_HIST_SHARED_SERVER_SUMMARY

Statistics for    shared servers

DBA_HIST_SNAPSHOT

   snapshot information

DBA_HIST_SQL_PLAN

   execution plan

DBA_HIST_WR_CONTROL

   AWR Control Information

This is the end of this article on "sample Analysis of AWR Management and maintenance in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.