AWRレポートを手軽に利用しよう。

AWR(Automatic Workload Reposistory)はオラクルからのプレゼントです。もちろん、ただではないけどど…

私は特定な作業の性能特徴を分析する時、次のように手軽にAWRレポートを利用しています。

@snap_begin

alter session enable parallel dml;

insert /*+ append parallel(t1 4) trace */ into t1
select /*+ parallel(t2 4) */ * from t2;
commit;

@snap_end

@snap_report

-- AWR Report
WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
UKJA1021       738915393 ukja1021            1 10.2.0.1.0  NO  UKJAX

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      1284 29-Dec-09 15:04:38        19       3.5
  End Snap:      1285 29-Dec-09 15:04:42        19       3.5
   Elapsed:                0.08 (mins)
   DB Time:                0.15 (mins)

Cache Sizes
~~~~~~~~~~~                       Begin        End
                             ---------- ----------
               Buffer Cache:       452M       452M  Std Block Size:         8K
           Shared Pool Size:       116M       116M      Log Buffer:     6,968K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:          1,201,392.36          5,725,836.00
              Logical reads:              2,178.56             10,383.00
              Block changes:                298.99              1,425.00
             Physical reads:                 97.36                464.00
            Physical writes:                142.89                681.00
                 User calls:                 12.80                 61.00
                     Parses:                 26.44                126.00
                Hard parses:                  0.42                  2.00
                      Sorts:                  8.60                 41.00
                     Logons:                  1.68                  8.00
                   Executes:                 27.07                129.00
               Transactions:                  0.21
...


Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file scattered read                   42     .0           2      55      42.0
log file parallel write                  27     .0           1      32      27.0
rdbms ipc reply                           8     .0           0      50       8.0
control file sequential read            674     .0           0       1     674.0
db file sequential read                  11     .0           0      27      11.0
...

snap_begin、snap_end、snap_reportスクリプトは下記のとおりです。簡単なSQL*Plusだけで手軽にAWRレポートを作れます。
1. snap_begin.sql

col begin_snap new_value begin_snap;
col db_id new_value db_id;
col inst_num new_value inst_num;

select dbid as db_id from v$database;
select instance_number as inst_num from v$instance;

select dbms_workload_repository.create_snapshot as begin_snap from dual;

2. snap_end.sql

col end_snap new_value end_snap;

select dbms_workload_repository.create_snapshot as end_snap from dual;

3. snap_report.sql

select * from table(
  dbms_workload_repository.awr_report_text(
          &db_id,
          &inst_num,
          &begin_snap,
          &end_snap)
  );

もう一度言いますが、AWRはオラクルからの大切なプレゼントです。たとえその値段は高いかも知れませんが、よく利用すればするほど支払いの甲斐があります。