【Oracle Database】Oracle AWR

快照
SQL>?col?begin_interval_time?for?a30
SQL>?col?end_interval_time?for?a30
SQL>?select?snap_id,?
????to_char(begin_interval_time,'yyyy-mm-dd?hh34:mi:ss')?begin_interval_time,
????to_char(end_interval_time,'yyyy-mm-dd?hh34:mi:ss')?end_interval_time
????from?dba_hist_snapshot?order?by?1;
????
???SNAP_ID?BEGIN_INTERVAL_TIME????????????END_INTERVAL_TIME
----------?------------------------------?------------------------------
???????155?2018-06-25?19:00:22????????????2018-06-25?20:00:25
???????156?2018-06-25?20:00:25????????????2018-06-25?21:00:27
???????157?2018-06-25?21:00:27????????????2018-06-25?22:00:33
???????158?2018-06-25?22:00:33????????????2018-06-25?23:00:36
???????159?2018-06-25?23:00:36????????????2018-06-26?00:00:39
???????160?2018-06-26?00:00:39????????????2018-06-26?01:00:41
???????161?2018-06-26?01:52:17????????????2018-06-26?02:04:22
???????162?2018-06-26?02:04:22????????????2018-06-26?03:00:24
???????163?2018-06-26?03:00:24????????????2018-06-26?04:00:27
???????164?2018-06-26?04:00:27????????????2018-06-26?05:00:29
???????165?2018-06-26?05:00:29????????????2018-06-26?06:00:32
???????
SQL>?exec?dbms_workload_repository.create_snapshot();
PL/SQL?procedure?successfully?completed.

SQL>?exec?dbms_workload_repository.drop_snapshot_range(low_snap_id=>150,high_snap_id=>154);
PL/SQL?procedure?successfully?completed.

SQL>?col?snap_interval?for?a20
SQL>?col?retention?for?a20
SQL>?select?snap_interval,retention,topnsql?from?dba_hist_wr_control;
SNAP_INTERVAL????????RETENTION????????????TOPNSQL
--------------------?--------------------?------------------------------
+00000?01:00:00.0????+00008?00:00:00.0????DEFAULT

SQL>?exec?dbms_workload_repository.modify_snapshot_settings(retention=>15*1440,interval=>30);
PL/SQL?procedure?successfully?completed.

SQL>?select?snap_interval,retention,topnsql?from?dba_hist_wr_control;

SNAP_INTERVAL????????RETENTION????????????TOPNSQL
--------------------?--------------------?------------------------------
+00000?00:30:00.0????+00015?00:00:00.0????DEFAULT

基線
SQL>?exec?dbms_workload_repository.create_baseline(start_snap_id=>155,end_snap_id=>156,baseline_name=>'baseline01');
PL/SQL?procedure?successfully?completed.

SQL>?col?baseline_name?for?a30
SQL>?select?baseline_name,baseline_id,start_snap_id,end_snap_id?from?dba_hist_baseline;

BASELINE_NAME??????????????????BASELINE_ID?START_SNAP_ID?END_SNAP_ID
------------------------------?-----------?-------------?-----------
baseline01???????????????????????????????1???????????155?????????156
SYSTEM_MOVING_WINDOW?????????????????????0?????????????1?????????168

SQL>?exec?dbms_workload_repository.drop_baseline(baseline_name=>'baseline01',cascade=>false);
PL/SQL?procedure?successfully?completed.

SQL>?select?baseline_name,baseline_id,start_snap_id,end_snap_id?from?dba_hist_baseline;

BASELINE_NAME??????????????????BASELINE_ID?START_SNAP_ID?END_SNAP_ID
------------------------------?-----------?-------------?-----------
SYSTEM_MOVING_WINDOW?????????????????????0?????????????1?????????168

報告
SQL>?@?/rdbms/admin/awrrpt.sql??--生成最常見的AWR報告方式

SQL>?@?/rdbms/admin/awrddrpt.sql?--比較兩個AWR報告

SQL>?@?/rdbms/admin/awrsqrpt.sql?--生成AWR報告中指定sql_id的執行計劃