Oracle10g New Feature -- 10. AWR (Automatic Workload Repository)
Oracle10g的AWR替代了Oracle9i的statspack，能自动收集数据库性能信息，并可调整每次收集时间间隔和信息保存时长，或者手动收集信息。
Time model和v$session / v$session_wait帮助用户确定各会话在各资源上所耗时间和在等待各资源上所耗的时间
[@more@]1. Automatic Workload RepositoryIn 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 ModelTime 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 CollectionCollect 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
1063589911 2 base_line_2 107
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
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 CollectionCollect 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
1063589911 2 base_line_2 107
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/207/viewspace-778810/ ，如需转载，请注明出处，否则将追究法律责任。
