ORACLE 10g AWR报告设置总结

 

1:查看、修改AWR报告快照数据的采样间隔、保存策略

SQL> COL DBID FOR 999999999999

SQL> COL SNAP_INTERVAL FOR A26

SQL> COL RETENTION FOR A26

SQL> COL TOPNSQL FOR A10

SQL> select * from dba_hist_wr_control;

 

         DBID SNAP_INTERVAL              RETENTION                  TOPNSQL

------------- -------------------------- -------------------------- ----------

   3990839260 +00000 01:00:00.0          +00014 00:00:00.0          DEFAULT

 

SQL> 

SNAP_INTERVAL=+00000 01:00:00.0 表示采样间隔是1小时

RETENTION=+00014 00:00:00.0 表示采样数据保留期限是14天

如上所示,快照采样间隔为1小时一次,快照数据保留14天。修改AWR的快照采样间隔、保存配置,改为30分钟采集一次快照数据,快照数据库保留7天。如下所示:

SQL> COL DBID FOR 999999999999

SQL> COL SNAP_INTERVAL FOR A26

SQL> COL RETENTION FOR A26

SQL> select * from dba_hist_wr_control;

 

         DBID SNAP_INTERVAL              RETENTION                  TOPNSQL

------------- -------------------------- -------------------------- ----------

   3990839260 +00000 01:00:00.0          +00014 00:00:00.0          DEFAULT

 

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>7*24*60);

 

PL/SQL procedure successfully completed.

 

SQL> select * from dba_hist_wr_control;

 

         DBID SNAP_INTERVAL              RETENTION                  TOPNSQL

------------- -------------------------- -------------------------- ----------

   3990839260 +00000 00:30:00.0          +00007 00:00:00.0          DEFAULT

 

SQL> 

ORACLE 10g AWR报告设置总结

 

2:手动创建快照(Snapshots)

 

数据库创建快照(Snapshots)一般是按Schedule定期创建的,默认一般一小时创建一个快照,只有当数据库出现问题的时候,你才需要手工创建一个快照。以获取那个时间段内的相关数据。

SQL> exec dbms_workload_repository.create_snapshot(); 

 

PL/SQL procedure successfully completed.

手工创建快照后,可以通过dba_hist_snapshot视图查看创建的快照信息。

 

3:手动删除快照(Snapshots)

 

--不指定DBID,默认当前实例DBID

SQL>  exec dbms_workload_repository.drop_snapshot_range(61263,61274);

 

PL/SQL procedure successfully completed.

 

 

--指定DBID

 

SQL> exec dbms_workload_repository.drop_snapshot_range(7417,7429,3990839260); 

PL/SQL procedure successfully completed.

4:创建基线(baseline)

一般来说当AWR自动维护快照时,如果定义过baseline,与baseline相关的快照不会被删除,即使是过期的快照,这样就相当于手动保留了一份统计数据的历史信息,DBA可以在适当的时间将其与现有的快照进行对比,以生成相关的统计报表

SQL> COL BASELINE_NAME FOR A32;

SQL> SELECT DBID,BASELINE_NAME,  START_SNAP_ID, END_SNAP_ID FROM DBA_HIST_BASELINE;

 

no rows selected

 

SQL> exec dbms_workload_repository.create_baseline(59806,59809,'20160726_07_11');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT DBID,BASELINE_NAME,  START_SNAP_ID, END_SNAP_ID FROM DBA_HIST_BASELINE;

 

      DBID BASELINE_NAME                    START_SNAP_ID END_SNAP_ID

---------- -------------------------------- ------------- -----------

3990839260 20160726_07_11                           59806       59809

 

SQL> 

ORACLE 10g AWR报告设置总结

修改基线名称

BEGIN

 

  DBMS_WORKLOAD_REPOSITORY.rename_baseline(

 

    old_baseline_name => 'old_name',

 

    new_baseline_name => 'new_name');

 

END;

 

/

基线(baseline)是自Oracle 10g开始引入的,不过只包括了一些简单的基线功能,创建一个基线允许保留指定范围内的快照用于性能对比,与AWR保留策略无关,可以使用DBMS_WORKLOAD_REPOSITORY包来管理这个功能,在11g中,它被增强了。新增了许多功能。例如固定基线、移动窗口(Moving Window)基线等

 

5: 删除基线(baseline)

首先查看基线设置,找到对应的baseline_name,然后删除,过程如下所示:

SQL>SELECT * FROM DBA_HIST_BASELINE;

 

SQL> COL BASELINE_NAME FOR A32;

SQL> SELECT DBID,BASELINE_NAME,  START_SNAP_ID, END_SNAP_ID FROM DBA_HIST_BASELINE;

 

      DBID BASELINE_NAME                    START_SNAP_ID END_SNAP_ID

---------- -------------------------------- ------------- -----------

2179993557 20110112                                 12980       13004

2179993557 20100616                                  7959        7968

2179993557 20100818                                  9461        9485

2179993557 20101020                                 10964       10988

2179993557 20110119                                 13148       13172

2179993557 20101222                                 12476       12500

2179993557 20100602                                  7624        7632

ORACLE 10g AWR报告设置总结

SQL> exec dbms_workload_repository.drop_baseline('20110112',true);

 

PL/SQL procedure successfully completed.

删除是如果指定了CASCADE参数为true,对应的snap也会被删除。

 

6: 查看快照记录信息

SELECT * FROM  DBA_HIST_SNAPSHOT ORDER BY 1 DESC ;

 

 

SELECT SNAP_ID, DBID, INSTANCE_NUMBER, STARTUP_TIME,

       BEGIN_INTERVAL_TIME, END_INTERVAL_TIME,

       FLUSH_ELAPSED, SNAP_LEVEL, ERROR_COUNT

FROM WRM$_SNAPSHOT

WHERE STATUS = 0;

 

SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY

7:导出、导入AWR快照数据

 

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

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

8:删除导入的AWR快照数据。

 

如果要删除导入的awr数据,可以使用下面脚本进行删除。

exec dbms_swrf_internal.unregister_database(dbid);

sql> exec dbms_swrf_internal.unregister_database(3990839260);

dbms_workload_repository:

可以删除本地和其他数据库的快照,可以选择不同的快照来进行删除。

dbms_swrf_internal:

只能对其他数据库的快照来进行操作,会把整个快照unregister掉。

9:生成各种类型的AWR报告

Generating Various Types of AWR Reports

AWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format:

·         awrrpt.sql

Displays various statistics for a range of snapshots Ids.

·         awrrpti.sql

Displays statistics for a range of snapshot Ids on a specified database and instance.

·         awrsqrpt.sql

Display statistics of a particular SQL statement for a range of snapshot Ids. Run

this report to inspect or debug the performance of a particular SQL statement.

·         awrsqrpi.sql

Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

·         awrddrpt.sql

Compares detailed performance attributes and configuration settings between two selected time periods.

·         awrddrpi.sql

Compares detailed performance attributes and configuration settings between two selected                time periods on a specific database and instance.

查看AWR报告里面某个具体SQL语句的执行计划

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

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

Workload Repository Views

The following workload repository views are available:

·         V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.

·         V$METRIC - Displays metric information.

·         V$METRICNAME - Displays the metrics associated with each metric group.

·         V$METRIC_HISTORY - Displays historical metrics.

·         V$METRICGROUP - Displays all metrics groups.

·         DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.

·         DBA_HIST_BASELINE - Displays baseline information.

·         DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

·         DBA_HIST_SNAPSHOT - Displays snapshot information.

·         DBA_HIST_SQL_PLAN - Displays SQL execution plans.

·         DBA_HIST_WR_CONTROL - Displays AWR settings.

参考资料:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=518649661691529&id=748642.1&_afrWindowMode=0&_adf.ctrl-state=1gfse6hc1_4

上一篇:修复Ubuntu下XTerm不能正常显示中文字体的问题


下一篇:Linux shell中的竖线(|)——…