Oracle10g New Feature -- 10. AWR (Automatic Workload Repository)

Oracle10g的AWR替代了Oracle9i的statspack，能自動收集資料庫效能資訊，並可調整每次收集時間間隔和資訊儲存時長，或者手動收集資訊。

Time model和v$session / v$session_wait幫助使用者確定各會話在各資源上所耗時間和在等待各資源上所耗的時間

[@more@]

1.    Automatic Workload Repository

In Oracle10g, AWR(Automatic Workload Repository) replaces STATSPACK, and can collect snapshots automatically every hour by a new background process called MMON and its slave processes.

 

To Run AWR

SQL> @C:oracleproduct10.1.0db_1RDBMSADMINawrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

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

 1063589911 ORCL                1 orcl

……

 

To Determine and Change Snapshot Frequency and Retention Time:

SQL> select snap_interval,retention from dba_hist_wr_control;

SNAP_INTERVAL    RETENTION

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

+00000 01:00:00.0      +00007 00:00:00.0

SQL> begin

  2  dbms_workload_repository.modify_snapshot_settings(

  3     interval=>20,

  4     retention => 3*24*60

  5  );

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select snap_interval,retention from dba_hist_wr_control;

SNAP_INTERVAL    RETENTION

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

+00000 00:20:00.0      +00003 00:00:00.0

 

AWR stores the collected statistics in tables with AWM$_ (M: Metadata) and AWH$_ (H: Historical)  prefix, which are under the SYS schema in tablespace SYSAUX. Views with prefix DBA_HIST_ are built on these tables, and can be used to write your own performance diagnosis tools

 

SQL>select view_name from user_views where view_name like 'DBA_HIST_%' escape '';

VIEW_NAME

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

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_BASELINE

DBA_HIST_BG_EVENT_SUMMARY

......

Some important views are DBA_HIST_METRIC_NAME,       DBA_HIST_SYSMETRIC_SUMMARY.

Time Model

Time Model helps you to determine where the time was spent – not waiting, but actually doing the work.

To determine overall system time spent:

SQL>select * from v$sys_time_model;

   STAT_ID      STAT_NAME                                                             VALUE

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

3649082374       DB time                                                           163043103

2748282437       DB CPU                                                             41050690

………

17 rows selected.

 

To determine the specific session time spent:

SQL>select * from v$sess_time_model where sid=130

       SID    STAT_ID STAT_NAME                                                             VALUE

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

       130 3649082374       DB time                                                              963473

       130 2748282437       DB CPU                                                               238167

       130 4157170894       background elapsed time                                                   0

       130 2451517896       background cpu time                                                       0

       130 4127043053       sequence load elapsed time                                                0

       130 1431595225       parse time elapsed                                                   564605

       130  372226525       hard parse elapsed time                                              547423

       130 2821698184       sql execute elapsed time                                             634277

       130 1990024365       connection management call elapsed time                                7203

       130 1824284809       failed parse elapsed time                                                 0

       130 4125607023       failed parse (out of shared memory) elapsed time                          0

       130 3138706091       hard parse (sharing criteria) elapsed time                                0

       130  268357648       hard parse (bind mismatch) elapsed time                                   0

       130 2643905994       PL/SQL execution elapsed time                                           163

       130  290749718       inbound PL/SQL rpc elapsed time                                           0

       130 1311180441       PL/SQL compilation elapsed time                                      198053

       130  751169994       Java execution elapsed time                                               0

17 rows selected.

ASH( Active Session History )

In Oracle10g, view V$SESSION includes wait events and their duration, which were only contained in view V$SESSION_WAIT.

View V$ACTIVE_SESSION_HISTORY stores session performance statistics in a buffer.

If the ASH information is flashed out of memory, it will be stored in table DBA_HIST_ACTIVE_SESS_HISTORY permanently by the MMON slave.

For instance, if one of the events the sessions waited on is buffer busy wait, proper diagnosis must identify the segments on which the wait event occurred. You get that from the ASH view column CURRENT_OBJ#, which can then be joined with DBA_OBJECTS to get the segments in question.

SQL> desc v$active_session_history

 Name                                      Null?    Type

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

 SAMPLE_ID                                          NUMBER

 SAMPLE_TIME                                        TIMESTAMP(3)

 SESSION_ID                                         NUMBER

 SESSION_SERIAL#                                    NUMBER

 USER_ID                                            NUMBER

……

SQL> DESC DBA_HIST_ACTIVE_SESS_HISTORY;

 Name                                      Null?    Type

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

 SNAP_ID                                            NUMBER

 DBID                                               NUMBER

 INSTANCE_NUMBER                                    NUMBER

 SAMPLE_ID                                          NUMBER

 SAMPLE_TIME                                        TIMESTAMP(3)

 SESSION_ID                                         NUMBER

 SESSION_SERIAL#                                    NUMBER

 USER_ID                                            NUMBER

……

Manual Collection

Collect statistics on demand:

SQL> select snap_id from wrm$_snapshot;

   SNAP_ID

----------

        78

        79

     ……

       130

SQL> execute dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select snap_id from wrm$_snapshot;

   SNAP_ID

----------

        78

        79

     ……

       130

       131

Delete statistics manually:

SQL> execute dbms_workload_repository.drop_snapshot_range(130,131);

PL/SQL procedure successfully completed.

SQL> execute dbms_workload_repository.drop_snapshot_range(120,129);

PL/SQL procedure successfully completed.

SQL> select snap_id from wrm$_snapshot;

   SNAP_ID

----------

        78

        79

       ……

       118

       119

Baseline

In Oracle 10g, you can create two baselines before and after the changes made. This two sets can be compared to examine the effect of changes.

SQL> exec dbms_workload_repository.create_baseline(100,103,'base_line_1');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_baseline(107,109,'base_line_2');

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_baseline;

    DBID BASELINE_ID BASELINE_NAME   START_SNAP_ID START_SNAP_TIME       END_SNAP_ID END_SNAP_TIME

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

1063589911           1 base_line_1            100            16-SEP-04 09.24.56.079 AM                   103 16-SEP-04 10.40.56.448 AM

1063589911           2 base_line_2            107            16-SEP-04 12.01.01.128 PM                    109 16-SEP-04 12.41.02.744 PM

To Drop Baseline:

SQL> exec dbms_workload_repository.drop_baseline('base_line_1');

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_baseline;

     DBID BASELINE_ID BASELINE_NAME    START_SNAP_ID START_SNAP_TIME     END_SNAP_ID END_SNAP_TIME

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

1063589911           2 base_line_2                            107               16-SEP-04 12.01.01.128 PM      109           16-SEP-04 12.41.02.744 PM