oracle 11g建立基線詳細資訊
效能最佳化時大體有如下兩種評估方式:如果希望知道效能度量值指示伺服器接近容量限制,則應設定絕對值;但是如果希望知道今天的效能與上週(或者上個月)同一時間的效能之間的差異,則當前效能必須與基線進行比較,基線是某個時段內生成的一組快照,按照統計學對這些快照進行了分組,以便獲得一組隨時間變化的基線值,可以以任何時段快照取樣來做基線,只不過一般情況下我們大多會選擇系統正常時段的快照來做基線。
基線在oracle 10g中就出現了,而在oracle database 11g進一步增強了自動工作量資料檔案庫基線:
1 即用型移動視窗基線,可透過該基線指定自適應閥值
2 使用基線模板安排基線的建立操作
3 重新命名基線
4 設定基線的到期日期
AWR baseline是指一個特定時間段內的效能資料,保留這些資料是為了在效能問題產生時與其他類似的工作負載時間段進行比較:
fixed baseline:fixed baseline表示的是您制定的一個固定的、連續的時間段
moving window baseline:表示的是war保留期記憶體在的所有war資料
Baseline template:您可以使用baseline template建立將來某個連續時間段的baseline,oracle中有兩種baseline模板分別是single和repeating
建立fixed baseline:
SQL> begin
2 dbms_workload_repository.create_baseline(
3 start_snap_id=>1510,
4 end_snap_id=>1511,
5 baseline_name=>'test_baseline');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline;
DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION
---------- ---------------------------------------------------------------- ------------- ----------- ------------------ ----------
4257181262 test_baseline 1510 1511
4257181262 SYSTEM_MOVING_WINDOW 1515 1591 8
建立baseline的procedure和function
根據snap_id建立:
PROCEDURE CREATE_BASELINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_SNAP_ID NUMBER IN
END_SNAP_ID NUMBER IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
FUNCTION CREATE_BASELINE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_SNAP_ID NUMBER IN
END_SNAP_ID NUMBER IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
根據時間建立:
PROCEDURE CREATE_BASELINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
FUNCTION CREATE_BASELINE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
還可以刪除和重新命名baseline:
SQL> begin
2 dbms_workload_repository.drop_baseline(
3 baseline_name=>'test_baseline',
4 cascade=>true);
5 end;
6 /
PL/SQL procedure successfully completed.
在oracle 11g中系統已經預設建立moving window baseline:
SQL> select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline;
DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION
---------- ---------------------------------------------------------------- ------------- ----------- ------------------ ----------
4257181262 SYSTEM_MOVING_WINDOW 1515 1591 8
drop baseline時如果指定cascade=>true,將把baseline對應的snap也級聯刪除了
oracle database會自動維護系統定義的moving window baseline,系統定義的moving window baseline的預設視窗大小就是當前的AWR保留期,如果打算使用自適應閥值,請考慮使用更長的移動視窗,比如30天,以便精確的計算閥值。移動視窗的大小調整為小於或等於AWR的保留天數,因為要增加移動視窗的大小,必須要先增加相應的AWR保留期限。
調整awr的保留週期:
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200);
PL/SQL procedure successfully completed.
SQL> select retention from dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0
SQL> select baseline_id,baseline_name,moving_window_size from dba_hist_baseline;
BASELINE_ID BASELINE_NAME MOVING_WINDOW_SIZE
----------- ---------------------------------------------------------------- ------------------
0 SYSTEM_MOVING_WINDOW 8
SQL> exec dbms_workload_repository.modify_baseline_window_size(window_size=>30);
PL/SQL procedure successfully completed.
SQL> select baseline_id,baseline_name,moving_window_size from dba_hist_baseline;
BASELINE_ID BASELINE_NAME MOVING_WINDOW_SIZE
----------- ---------------------------------------------------------------- ------------------
0 SYSTEM_MOVING_WINDOW 30
基線模板:基線模板允許定義可能在將來要捕捉的基線,create_baseline_template儲存過程定義單一基線或重複基線的捕捉,建立單一基線模板與建立基於時間的基線類似,除了將來的時間外。
single基線模板:
PROCEDURE CREATE_BASELINE_TEMPLATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME VARCHAR2 IN
TEMPLATE_NAME VARCHAR2 IN
EXPIRATION NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
SQL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template(start_time=>TO_DATE('09-MAR-2016 00:00', 'DD-MON-YYYY HH24:MI'),end_time=>TO_DATE('16-MAR-2016 05:00', 'DD-MON-YYYY HH24:MI'),baseline_name=>'09_11_14_BS1',template_name =>'09_11_14_TP1',expiration=> 10);
PL/SQL procedure successfully completed.
SQL> select dbid,template_name,template_type,baseline_name_prefix,start_time,end_time,duration,expiration from dba_hist_baseline_template;
DBID TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME END_TIME DURATION EXPIRATION
---------- ------------------------------ --------- ------------------------------ ------------------- ------------------- ---------- ----------
4257181262 09_11_14_TP1 SINGLE 09_11_14_BS1 2016-03-09 00:00:00 2016-03-16 05:00:00 10
重複基線模板稍微有點不同,因為它需要排程資訊,start_time和end_time引數分別在模板啟用和釋放時定義,day_of_week、hour_in_day和duration定義產生基線的日期、時間和持續時間,因為模板會產生多個基線,基線名是以baseline_name_prefix開始的。
repeat基線模板:
PROCEDURE CREATE_BASELINE_TEMPLATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DAY_OF_WEEK VARCHAR2 IN
HOUR_IN_DAY NUMBER IN
DURATION NUMBER IN
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME_PREFIX VARCHAR2 IN
TEMPLATE_NAME VARCHAR2 IN
EXPIRATION NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template(day_of_week=>'MONDAY',hour_in_day=>0,duration=>5,start_time=>SYSDATE,end_time=>ADD_MONTHS(SYSDATE, 6),baseline_name_prefix =>'monday_morning_bl',template_name=>'monday_morning_tp',expiration=> NULL);
SQL> select dbid,TEMPLATE_NAME,TEMPLATE_TYPE,BASELINE_NAME_PREFIX,START_TIME,END_TIME,DAY_OF_WEEK,HOUR_IN_DAY,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template;
DBID TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY DURATION EXPIRATION
---------- ------------------------------ --------- ------------------------------ ------------------- ------------------- --------- ----------- ---------- ----------
REPEAT_INTERVAL
--------------------------------------------------------------------------------
4257181262 09_11_14_TP1 SINGLE 09_11_14_BS1 2016-03-09 00:00:00 2016-03-16 05:00:00 10
4257181262 monday_morning_tp REPEATING monday_morning_bl 2016-03-15 02:54:55 2016-09-15 02:54:55 MONDAY 0 5
FREQ=WEEKLY;INTERVAL=1;BYDAY=MON;BYHOUR=0;BYMINUTE=0;BYSECOND=0
上面介紹了基線,那麼如何來使用基線和指定時段的快照做awr資料對比了,其實Oracle為我們提供出AWR Compare Period Report來方便實現指定時段的awr資料對比。
由於基線又由於可以一直儲存在awr快照中,DBA做效能分析時則可以利用基線和故障時段的awr做對比,更進一步的分析資料庫的效能趨勢變化,下面簡單的來做一個AWR資料時段對比:
SQL> select dbid,min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;
DBID MIN(SNAP_ID) MAX(SNAP_ID)
---------- ------------ ------------
4257181262 1508 1669
這裡建立snap_id 1656到1657的基線
SQL> begin
2 dbms_workload_repository.create_baseline(
3 start_snap_id=>1656,
4 end_snap_id=>1657,
5 baseline_name=>'test_baseline');
6 end;
7 /
PL/SQL procedure successfully completed.
刪除大部分snapshot
SQL> exec dbms_workload_repository.drop_snapshot_range(1508, 1667, 4257181262);
PL/SQL procedure successfully completed.
SQL> select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid=4257181262;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
1669 15-MAR-16 10.23.42.387 PM 15-MAR-16 11.10.41.380 PM
1656 11-MAR-16 10.00.33.158 PM 14-MAR-16 08.40.27.499 PM
1657 14-MAR-16 08.40.27.499 PM 14-MAR-16 10.00.35.439 PM
1668 15-MAR-16 08.41.44.009 PM 15-MAR-16 10.23.42.387 PM
這裡也驗證了之前awr baseline的部分對應的snapshot並不會被awr保留策略或者手動刪除awr而刪除掉。
接下來使用awrddrpt.sql指令碼來生成兩個awr的對比資料:
SQL> @awrddrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
4257181262 4257181262 ORA11G 1 1 ora11g
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
2350429211 1 TLINK tlink ylqz_s
* 4257181262 1 ORA11G ora11g redhat-ora
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 4257181262 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
without
specifying a number lists all completed snapshots.
Enter value for num_days: 3
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ora11g ORA11G 1656 14 Mar 2016 20:40 1
1657 14 Mar 2016 22:00 1
1668 15 Mar 2016 22:23 1
1669 15 Mar 2016 23:10 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1656
First Begin Snapshot Id specified: 1656
Enter value for end_snap: 1657
First End Snapshot Id specified: 1657
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
2350429211 1 TLINK tlink ylqz_s
* 4257181262 1 ORA11G ora11g redhat-ora
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 4257181262 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
without
specifying a number lists all completed snapshots.
Enter value for num_days2: 3
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ora11g ORA11G 1656 14 Mar 2016 20:40 1
1657 14 Mar 2016 22:00 1
1668 15 Mar 2016 22:23 1
1669 15 Mar 2016 23:10 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 1668
Second Begin Snapshot Id specified: 1668
Enter value for end_snap2: 1669
Second End Snapshot Id specified: 1669
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_1656_1_1668.html To use this name,
press
to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awr0316.hmtl
基線在oracle 10g中就出現了,而在oracle database 11g進一步增強了自動工作量資料檔案庫基線:
1 即用型移動視窗基線,可透過該基線指定自適應閥值
2 使用基線模板安排基線的建立操作
3 重新命名基線
4 設定基線的到期日期
AWR baseline是指一個特定時間段內的效能資料,保留這些資料是為了在效能問題產生時與其他類似的工作負載時間段進行比較:
fixed baseline:fixed baseline表示的是您制定的一個固定的、連續的時間段
moving window baseline:表示的是war保留期記憶體在的所有war資料
Baseline template:您可以使用baseline template建立將來某個連續時間段的baseline,oracle中有兩種baseline模板分別是single和repeating
建立fixed baseline:
SQL> begin
2 dbms_workload_repository.create_baseline(
3 start_snap_id=>1510,
4 end_snap_id=>1511,
5 baseline_name=>'test_baseline');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline;
DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION
---------- ---------------------------------------------------------------- ------------- ----------- ------------------ ----------
4257181262 test_baseline 1510 1511
4257181262 SYSTEM_MOVING_WINDOW 1515 1591 8
建立baseline的procedure和function
根據snap_id建立:
PROCEDURE CREATE_BASELINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_SNAP_ID NUMBER IN
END_SNAP_ID NUMBER IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
FUNCTION CREATE_BASELINE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_SNAP_ID NUMBER IN
END_SNAP_ID NUMBER IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
根據時間建立:
PROCEDURE CREATE_BASELINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
FUNCTION CREATE_BASELINE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME VARCHAR2 IN
DBID NUMBER IN DEFAULT
EXPIRATION NUMBER IN DEFAULT
還可以刪除和重新命名baseline:
SQL> begin
2 dbms_workload_repository.drop_baseline(
3 baseline_name=>'test_baseline',
4 cascade=>true);
5 end;
6 /
PL/SQL procedure successfully completed.
在oracle 11g中系統已經預設建立moving window baseline:
SQL> select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline;
DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION
---------- ---------------------------------------------------------------- ------------- ----------- ------------------ ----------
4257181262 SYSTEM_MOVING_WINDOW 1515 1591 8
drop baseline時如果指定cascade=>true,將把baseline對應的snap也級聯刪除了
oracle database會自動維護系統定義的moving window baseline,系統定義的moving window baseline的預設視窗大小就是當前的AWR保留期,如果打算使用自適應閥值,請考慮使用更長的移動視窗,比如30天,以便精確的計算閥值。移動視窗的大小調整為小於或等於AWR的保留天數,因為要增加移動視窗的大小,必須要先增加相應的AWR保留期限。
調整awr的保留週期:
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200);
PL/SQL procedure successfully completed.
SQL> select retention from dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0
SQL> select baseline_id,baseline_name,moving_window_size from dba_hist_baseline;
BASELINE_ID BASELINE_NAME MOVING_WINDOW_SIZE
----------- ---------------------------------------------------------------- ------------------
0 SYSTEM_MOVING_WINDOW 8
SQL> exec dbms_workload_repository.modify_baseline_window_size(window_size=>30);
PL/SQL procedure successfully completed.
SQL> select baseline_id,baseline_name,moving_window_size from dba_hist_baseline;
BASELINE_ID BASELINE_NAME MOVING_WINDOW_SIZE
----------- ---------------------------------------------------------------- ------------------
0 SYSTEM_MOVING_WINDOW 30
基線模板:基線模板允許定義可能在將來要捕捉的基線,create_baseline_template儲存過程定義單一基線或重複基線的捕捉,建立單一基線模板與建立基於時間的基線類似,除了將來的時間外。
single基線模板:
PROCEDURE CREATE_BASELINE_TEMPLATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME VARCHAR2 IN
TEMPLATE_NAME VARCHAR2 IN
EXPIRATION NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
SQL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template(start_time=>TO_DATE('09-MAR-2016 00:00', 'DD-MON-YYYY HH24:MI'),end_time=>TO_DATE('16-MAR-2016 05:00', 'DD-MON-YYYY HH24:MI'),baseline_name=>'09_11_14_BS1',template_name =>'09_11_14_TP1',expiration=> 10);
PL/SQL procedure successfully completed.
SQL> select dbid,template_name,template_type,baseline_name_prefix,start_time,end_time,duration,expiration from dba_hist_baseline_template;
DBID TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME END_TIME DURATION EXPIRATION
---------- ------------------------------ --------- ------------------------------ ------------------- ------------------- ---------- ----------
4257181262 09_11_14_TP1 SINGLE 09_11_14_BS1 2016-03-09 00:00:00 2016-03-16 05:00:00 10
重複基線模板稍微有點不同,因為它需要排程資訊,start_time和end_time引數分別在模板啟用和釋放時定義,day_of_week、hour_in_day和duration定義產生基線的日期、時間和持續時間,因為模板會產生多個基線,基線名是以baseline_name_prefix開始的。
repeat基線模板:
PROCEDURE CREATE_BASELINE_TEMPLATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DAY_OF_WEEK VARCHAR2 IN
HOUR_IN_DAY NUMBER IN
DURATION NUMBER IN
START_TIME DATE IN
END_TIME DATE IN
BASELINE_NAME_PREFIX VARCHAR2 IN
TEMPLATE_NAME VARCHAR2 IN
EXPIRATION NUMBER IN DEFAULT
DBID NUMBER IN DEFAULT
exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template(day_of_week=>'MONDAY',hour_in_day=>0,duration=>5,start_time=>SYSDATE,end_time=>ADD_MONTHS(SYSDATE, 6),baseline_name_prefix =>'monday_morning_bl',template_name=>'monday_morning_tp',expiration=> NULL);
SQL> select dbid,TEMPLATE_NAME,TEMPLATE_TYPE,BASELINE_NAME_PREFIX,START_TIME,END_TIME,DAY_OF_WEEK,HOUR_IN_DAY,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template;
DBID TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY DURATION EXPIRATION
---------- ------------------------------ --------- ------------------------------ ------------------- ------------------- --------- ----------- ---------- ----------
REPEAT_INTERVAL
--------------------------------------------------------------------------------
4257181262 09_11_14_TP1 SINGLE 09_11_14_BS1 2016-03-09 00:00:00 2016-03-16 05:00:00 10
4257181262 monday_morning_tp REPEATING monday_morning_bl 2016-03-15 02:54:55 2016-09-15 02:54:55 MONDAY 0 5
FREQ=WEEKLY;INTERVAL=1;BYDAY=MON;BYHOUR=0;BYMINUTE=0;BYSECOND=0
上面介紹了基線,那麼如何來使用基線和指定時段的快照做awr資料對比了,其實Oracle為我們提供出AWR Compare Period Report來方便實現指定時段的awr資料對比。
由於基線又由於可以一直儲存在awr快照中,DBA做效能分析時則可以利用基線和故障時段的awr做對比,更進一步的分析資料庫的效能趨勢變化,下面簡單的來做一個AWR資料時段對比:
SQL> select dbid,min(snap_id),max(snap_id) from dba_hist_snapshot group by dbid;
DBID MIN(SNAP_ID) MAX(SNAP_ID)
---------- ------------ ------------
4257181262 1508 1669
這裡建立snap_id 1656到1657的基線
SQL> begin
2 dbms_workload_repository.create_baseline(
3 start_snap_id=>1656,
4 end_snap_id=>1657,
5 baseline_name=>'test_baseline');
6 end;
7 /
PL/SQL procedure successfully completed.
刪除大部分snapshot
SQL> exec dbms_workload_repository.drop_snapshot_range(1508, 1667, 4257181262);
PL/SQL procedure successfully completed.
SQL> select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid=4257181262;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
1669 15-MAR-16 10.23.42.387 PM 15-MAR-16 11.10.41.380 PM
1656 11-MAR-16 10.00.33.158 PM 14-MAR-16 08.40.27.499 PM
1657 14-MAR-16 08.40.27.499 PM 14-MAR-16 10.00.35.439 PM
1668 15-MAR-16 08.41.44.009 PM 15-MAR-16 10.23.42.387 PM
這裡也驗證了之前awr baseline的部分對應的snapshot並不會被awr保留策略或者手動刪除awr而刪除掉。
接下來使用awrddrpt.sql指令碼來生成兩個awr的對比資料:
SQL> @awrddrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
4257181262 4257181262 ORA11G 1 1 ora11g
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
2350429211 1 TLINK tlink ylqz_s
* 4257181262 1 ORA11G ora11g redhat-ora
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 4257181262 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
without
specifying a number lists all completed snapshots.
Enter value for num_days: 3
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ora11g ORA11G 1656 14 Mar 2016 20:40 1
1657 14 Mar 2016 22:00 1
1668 15 Mar 2016 22:23 1
1669 15 Mar 2016 23:10 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1656
First Begin Snapshot Id specified: 1656
Enter value for end_snap: 1657
First End Snapshot Id specified: 1657
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
2350429211 1 TLINK tlink ylqz_s
* 4257181262 1 ORA11G ora11g redhat-ora
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 4257181262 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
without
specifying a number lists all completed snapshots.
Enter value for num_days2: 3
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ora11g ORA11G 1656 14 Mar 2016 20:40 1
1657 14 Mar 2016 22:00 1
1668 15 Mar 2016 22:23 1
1669 15 Mar 2016 23:10 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 1668
Second Begin Snapshot Id specified: 1668
Enter value for end_snap2: 1669
Second End Snapshot Id specified: 1669
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_1656_1_1668.html To use this name,
press
to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awr0316.hmtl
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31530407/viewspace-2153365/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 11G RAC 建立詳細過程Oracle
- oracle 11g result 整理詳細版Oracle
- Oracle 查詢欄位詳細資訊Oracle
- Oracle 11G DataGuard重啟詳細過程Oracle
- Oracle->Mysql dblink 建立詳細過程OracleMySql
- Linux Oracle 11g Dataguard配置詳細步驟LinuxOracle
- Oracle 11g升級PSU詳細步驟Oracle
- 安裝oracle 11g 使用ASM儲存 詳細OracleASM
- Oracle記憶體結構:ProcessMemory的詳細資訊Oracle記憶體SSM
- 超詳細oracle 11g安裝步驟 win版本Oracle
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- Oracle 11G EM建立Oracle
- oracle 11g dataguard 建立Oracle
- oracle 11g 建立 jobOracle
- ORACLE 11G 搭建dataguard詳細步驟(所有操作總結)Oracle
- Oracle記憶體結構(三)----Process Memory的詳細資訊Oracle記憶體
- Oracle從10g升級到11g詳細步驟Oracle
- Oracle 11g在RHEL 6.4下的詳細安裝過程Oracle
- Oracle 11g 建立臨時window時間視窗 收集統計資訊Oracle
- table細線表格詳解
- Oracle從10g升級到11g詳細操作步驟Oracle
- Oracle記憶體結構(三)----Process Memory的詳細資訊(轉)Oracle記憶體
- Oracle記憶體結構(二)----Shared Pool的詳細資訊Oracle記憶體
- 建立表空間、回滾段、使用者、表的詳細語法資訊
- 建立Oracle 11g logical standbyOracle
- Oracle 11g 建立物化檢視Oracle
- LVM建立的詳細步驟LVM
- LVM詳細介紹及建立LVM
- mysql建立master/slave詳細步驟MySqlAST
- Oracle 11G 統計資訊TaskOracle
- oracle 11g統計資訊收集Oracle
- 淘寶sku詳細資訊介面接入方案
- iphone 獲取地址的詳細資訊iPhone
- Oracle 11g RAC 環境打PSU補丁的詳細步驟Oracle
- Oracle記憶體結構(二)----Shared Pool的詳細資訊(轉)Oracle記憶體
- 建立表空間、回滾段、使用者、表的詳細語法資訊(轉)
- 【Oracle】11g Oracle自動收集統計資訊Oracle
- ORACLE EXPDP命令使用詳細Oracle