[zt] awr的一些基本知识

最新推荐文章于 2024-01-11 20:38:04 发布

转载最新推荐文章于 2024-01-11 20:38:04 发布 · 190 阅读

本文介绍了Oracle AWR（自动工作负载记录）的配置方法，包括如何调整快照间隔、保留时间和顶级SQL数量。此外，还详细展示了如何手动创建和删除AWR快照，以及如何使用awrrpt.sql生成AWR报告。

1.awr一些配置和试图
--DBA_HIST_SNAPSHOT displays information on snapshots in the system
--DBA_HIST_WR_CONTROL displays the settings for controlling AWR
--dbms_workload_repository.modify_snapshot_settings 设置awr的收集频率，保留时间，topsql
--exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); 手工创建awr数据
--exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 3073,high_snap_id => 3073); --手工删除awr数据

下面我们来手工实践一下：

SQL> select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
1810911595 +00000 00:20:00.0 +00015 00:00:00.0 30 --以前我改过的

SQL> exec dbms_workload_repository.modify_snapshot_settings(12*24*60,30,40);

PL/SQL procedure successfully completed

SQL> select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
1810911595 +00000 00:30:00.0 +00012 00:00:00.0 40 --30分钟间隔，保留12天，40个topsql，

SQL> select * from (select snap_id from DBA_HIST_SNAPSHOT order by snap_id desc) where rownum<10; --查看现在最大的snapid

   SNAP_ID
----------
      3069
      3068
      3067
      3066
      3065
      3064
      3063
      3062
      3061

9 rows selected

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); --手工生成snap

PL/SQL procedure successfully completed

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

PL/SQL procedure successfully completed

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

PL/SQL procedure successfully completed

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

PL/SQL procedure successfully completed

SQL> select * from (select snap_id from DBA_HIST_SNAPSHOT order by snap_id desc) where rownum<10;

   SNAP_ID
----------
      3073          --发现已经生成了
      3072
      3071
      3070
      3069
      3068
      3067
      3066
      3065

9 rows selected

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 3073,high_snap_id => 3073); --删除snapid

PL/SQL procedure successfully completed

SQL> select * from (select snap_id from DBA_HIST_SNAPSHOT order by snap_id desc) where rownum<10;

   SNAP_ID
----------
      3072
      3071
      3070
      3069
      3068
      3067
      3066
      3065
      3064

9 rows selected

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 3070,high_snap_id => 3072);

PL/SQL procedure successfully completed

SQL> select * from (select snap_id from DBA_HIST_SNAPSHOT order by snap_id desc) where rownum<10;

   SNAP_ID
----------
      3069   --发现刚才生成的都删除掉了
      3068
      3067
      3066
      3065
      3064
      3063
      3062
      3061

9 rows selected

2.报表脚本

--生成arw报告
awrrpt.sql生成 awr报告，awrrpt.sql里调用了awrrpti.sql （这个才是真正生成awr报告的脚本）
awrddrpt.sql 对比两个awr报告，这个是很有用的哦
awrsqrpt.sql 查看具体sql的执行计划等信息（要输入sql_id)

在 awrrpti.sql 脚步里面您可以自己设置
define inst_num=1;
define num_days=2;
define inst_name = 'sun';
define db_name = 'sun';
define dbid=1810911595;
define begin_snap=3003;
define end_snap=3004;
define report_type='text';
define report_name=d:\crb_awr;
如果你已经设置了上面的变量，那么执行awrrpt.sql的时候就不需要输入任何东西了

--那么一些以前statspack可以设置的选项哪里去了呢？
我们看到储存过程DBMS_SWRF_REPORT_INTERNAL（awrrpti.sql查看，一步一步看下去，可以看到调用了DBMS_SWRF_REPORT_INTERNAL）

PACKAGE BODY DBMS_SWRF_REPORT_INTERNAL
PROCEDURE SET_THRESHOLD
IS
BEGIN
    TOP_N_EVENTS := 5;
    TSQL_MIN     := 10;
    TSQL_MAX     := 65;
    TOP_PCT_SQL := 1.0;
    TOP_N_SEG    := 5;
    SHMEM_THRES := 1048576;
    VCNT_THRES   := 20;
    TOP_N_SVC    := 10;
    DIFF_TOP_SQL := 10;
    DIFF_TOP_WAIT := 5;
    DIFF_TOP_SEG := 5;
    DIFF_TOP_FILE := 10;
    TOP_N_SVC := 10;
END SET_THRESHOLD;

不知道修改上面的参数是不是会有效呢？
我测试了一下修改了 TOP_N_EVENTS := 8;
生成awr报告后，看到了这样一段（看来时生效的）
Top 8 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file scattered read            1,214,685       7,063      6   96.5   User I/O
CPU time                                            355           4.8
control file sequential read          3,824          41     11    0.6 System I/O
control file parallel write           1,190          19     16    0.3 System I/O
db file sequential read               2,938          12      4    0.2   User I/O
log file parallel write                 801          10     13    0.1 System I/O
db file parallel write                  400           1      4    0.0 System I/O
log file sync                            64           1     13    0.0     Commit

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-675474/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-675474/