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

[zt] awr的一些基本知識

相關文章