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

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

----------

……

130

SQL> execute dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select snap_id from wrm$_snapshot;

SNAP_ID

----------

……

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

----------

……

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