5.3 Managing the Automatic Workload Repository

This section describes how to manage the AWR and contains the following topics: 這節討論怎樣去管理AWR，包含以下主題：

Managing Snapshots （先介紹快照的管理，因為快照是所有後續功能的基礎源）
Managing Baselines （有了快照，就可以建立基線了）
Managing Baseline Templates （有了基線，就想要基線模板）
Transporting Automatic Workload Repository Data （所有的資料有了之後，怎麼從生產系統轉移到測試環境中進行分析？）
Using Automatic Workload Repository Views （oracle為我們提供了多少檢視可供我們分析）
Generating Automatic Workload Repository Reports （如何更直觀的分析資料呢？看報表吧）
Generating Automatic Workload Repository Compare Periods Reports （噁心的資料看著倒胃口，那就去和基線進行比較一下吧，這樣直接定位到有問題的地方）
Generating Active Session History Reports （產生一個更詳細的ASH報表）
Using Active Session History Reports （產生完ASH報表之後，怎樣使用呢？）

5.3.1 Managing Snapshots

By default, Oracle Database generates snapshots once every hour, and retains the statistics in the workload repository for 8 days. When necessary, you can use DBMS_WORKLOAD_REPOSITORY procedures to manually create, drop, and modify the snapshots. To invoke these procedures, a user must be granted the DBA role.

The primary interface for managing snapshots is Oracle Enterprise Manager. Whenever possible, you should manage snapshots using Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can manage snapshots using the DBMS_WORKLOAD_REPOSITORY package, as described in the following sections:

預設情況下，資料庫每小時自動的產生一個快照，並且保留8天時間。當需要時，你可以透過 dbms_workload_repository 去手動的建立，刪除，修改快照。要使用這些儲存過程，必須要以DBA的許可權執行（所以很多時候簡單除錯時我們使用statpack工具而不用AWR，因為statpack在自己的使用者下就可以執行，而AWR需要DBA許可權）。

管理快照的主要介面是使用OEM。當可能時，也就是說你如果安裝了OEM那就儘可能使用OEM來管理。假如OEM無法使用，可以使用 dbms_workload_repository 包。

Creating Snapshots create_snapshot();
Dropping Snapshots drop_snapshot_range(low_snap_id=>xxx,high_snap_id=>xxx);
Modifying Snapshot Settings modify_snapshot_settings(retention => xxx, interval => xx, topnsql => xx);

5.3.1.1 Creating Snapshots

You can manually create snapshots with the CREATE_SNAPSHOT procedure to capture statistics at times different than those of the automatically generated snapshots. For example:

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

注：預設是TYPICAL，返回的是snapshot ID （Flush level for the snapshot is either 'TYPICAL' or 'ALL' ，我自己也不清楚這個值和 statistics_level的typical/all有什麼區別，但是可以看下面的3張圖，應該是一樣的）

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level IN VARCHAR2 DEFAULT 'TYPICAL') RETURN NUMBER;

In this example, a snapshot for the instance is created immediately with the flush level specified to the default flush level of TYPICAL. You can view this snapshot in the DBA_HIST_SNAPSHOT view. 在這個例子中，立即建立了一個例項快照，使用預設的TYPICAL的重新整理級別。建立完就立刻可以在dba_hist_snapshot 檢視中檢視到這個snapshot_id。

點選：TYPICAL之後就進入下面的引數設定頁面，進行statistics_level設定了

同時如何在OEM中建立快照呢？

因為我沒有安裝FLASH外掛（這破東西一會就導致firefox死掉或者CPU達到40%，所以看到的是黑色的，主要是從Performance->Snapshots）

5.3.1.2 Dropping Snapshots

You can drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. To view a list of the snapshot IDs along with database IDs, check the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of snapshots:

EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22, high_snap_id => 32, ~~dbid => 3310949047~~);

In the example, the range of snapshot IDs to drop is specified from 22 to 32. The optional database identifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. 預設情況下不需要寫dbid,如果不寫的話就代表本資料庫了。

Active Session History data (ASH) that belongs to the time period specified by the snapshot range is also purged when the DROP_SNAPSHOT_RANGE procedure is called.

當使用 drop_snapshot_range 時，snapshot 時間段中的ASH資料也被清除掉了。

5.3.1.3 Modifying Snapshot Settings

You can adjust the interval, retention, and captured Top SQL of snapshot generation for a specified database ID, but note that this can affect the precision of the Oracle Database diagnostic tools.

The INTERVAL setting affects how often the database automatically generates snapshots. The RETENTION setting affects how long the database stores snapshots in the workload repository. The TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, sharable Memory, and Version Count). The value for this setting is not affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. It is possible to set the value for this setting to MAXIMUM to capture the complete set of SQL in the shared SQL area, though by doing so (or by setting the value to a very high number) may lead to possible space and performance issues because there will more data to collect and store. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, interval => 30, topnsql => 100, ~~dbid => 3310949047~~);

In this example, the retention period is specified as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100. If NULL is specified, the existing value is preserved. The optional database identifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. You can check the current settings for your database instance with the DBA_HIST_WR_CONTROL view.

你可以調整快照的間隙，保留期，和捕捉的最高的SQL，但是注意調整後將會影響資料庫診斷工具的準確性。（要麼更準確，要麼不那麼準確）

interval這個值影響快照產生的間隙。（MIN_INTERVAL (10 minutes) to MAX_INTERVAL (1 year)，如果為0則禁用自動快照收集功能 ）

retention影響快照存留在倉庫中的時間。（MIN_RETENTION (1 day) to MAX_RETENTION (100 years)，如果為0則表示永遠儲存 ）

topnsql最高使用的SQL標準（消耗時間，CPU時間，解析，共享記憶體，版本號）。這個topnsql不會被statistics/flush級別所影響，但是將會覆蓋系統預設的AWR SQL收集行為。透過設定成MAXIMUM去捕捉全部的SQL area的SQL，這樣做（或者設定一個很大的值）將會導致效能和空間的問題，因為將要收集和儲存更多的資料。使用 modify_snapshot_settings去調整。（這個值可以設定成(DEFAULT, MAXIMUM, N)， DEFAULT：statistics typical-30,all-100;; MAXIMUM: 收集cursor cache中所有的sql;; N就是指定多少個）

5.3.2 Managing Baselines

This section describes how to manage baselines. The primary interface for managing baselines is Oracle Enterprise Manager. Whenever possible, you should manage baselines using Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can manage baselines using the DBMS_WORKLOAD_REPOSITORY package, as described in the following sections:

強烈的建議你使用OEM，但是如果沒有OEM的話就使用下面介紹的 dbms_workload_repository 包來管理。

Creating a Baseline create_baseline(start_snap_id=>xxx,end_snap_id=>xxx,baseline_name=>’xxxx’); 透過dba_hist_baseline檢視建立的基線
Dropping a Baseline drop_baseline(baseline_name=>’xxx’,cascade=>xxx); 先從dba_hist_baseline中檢視這個名字
Renaming a Baseline rename_baseline(old_baseline_name=>’xxx’,new_baseline_name=>’xxx’);
Displaying Baseline Metrics select_baseline_name(baseline_name=>’xxx’);
Modifying the Window Size of the Default Moving Window Baseline

5.3.2.1 Creating a Baseline

This section describes how to create a baseline using an existing range of snapshots.

To create a baseline:

Review the existing snapshots in the DBA_HIST_SNAPSHOT view to determine the range of snapshots to use.

Use the CREATE_BASELINE procedure to create a baseline using the desired range of snapshots:

先從 dba_hist_snapshot中檢視一下有哪些snap_id，可以透過begin_interval_time，end_interval_time去檢視這個snap_id發生在哪個時間段內。

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270, end_snap_id => 280, baseline_name => 'peak baseline',

~~dbid => 3310949047, expiration => 30~~);

In this example, 270 is the start snapshot sequence number and 280 is the end snapshot sequence. The name of baseline is peak baseline. The optional database identifier is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value. The optional expiration parameter is set to 30, so the baseline will expire and be dropped automatically after 30 days. If you do not specify a value for expiration, the baseline will never expire.

不指定上面的dbid就是本地資料庫，expiration是指30天后自動清除，如果沒有指定的話，就是永遠保留。

The system automatically assign a unique baseline ID to the new baseline when the baseline is created. The baseline ID and database identifier are displayed in the DBA_HIST_BASELINE view.

5.3.2.2 Dropping a Baseline

This section describes how to drop an existing baseline. Periodically, you may want to drop a baseline that is no longer used to conserve disk space. The snapshots associated with a baseline are retained indefinitely until you explicitly drop the baseline or the baseline has expired.

To drop a baseline:

Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline to drop.

Use the DROP_BASELINE procedure to drop the desired baseline:

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',cascade => FALSE, ~~dbid => 3310949047~~);

END;

/

In the example, the name of baseline is peak baseline. The cascade parameter is set to FALSE, which specifies that only the baseline is dropped. Setting this parameter to TRUE specifies that the drop operation will also remove the snapshots associated with the baseline. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value.

如果設定cascade=>TRUE的話，那麼baseline和snapshot一起都被刪除了。預設的cascade引數就是FALSE,所以不用去指定了

5.3.2.3 Renaming a Baseline

This section describes how to rename a baseline.

To rename a baseline:

Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline to rename.

Use the RENAME_BASELINE procedure to rename the desired baseline:

BEGIN

DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (

old_baseline_name => 'peak baseline',

new_baseline_name => 'peak mondays',

~~dbid => 3310949047~~);

END;

/

In this example, the name of the baseline is renamed from peak baseline, as specified by the old_baseline_name parameter, to peak mondays, as specified by the new_baseline_name parameter. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, then the local DBID is the default value.

給baseline重新換個名字，這個不用解釋了！

5.3.2.4 Displaying Baseline Metrics

This section describes how to display metric threshold settings during the time period captured in a baseline. When used with adaptive thresholds, a baseline contains AWR data that the database can use to compute metric threshold values. The SELECT_BASELINE_METRICS function enables you to display the summary statistics for metric values in a baseline period.

To display metric information in a baseline:

Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline for which you want to display metric information.

Use the SELECT_BASELINE_METRICS function to display the metric information for the desired baseline:

BEGIN

DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (

baseline_name => 'peak baseline',

~~dbid => 3310949047,~~

~~instance_num => '1'~~);

END;

/

In this example, the name of baseline is peak baseline. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value. The optional instance_num parameter specifies the instance number, which in this example is 1. If you do not specify a value for instance_num, then the local instance is used as the default value.

5.3.2.5 Modifying the Window Size of the Default Moving Window Baseline

This section describes how to modify the window size of the default moving window baseline.

To resize the default moving window baseline, use the MODIFY_BASELINE_WINDOW_SIZE procedure:

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (

window_size => 30, 【這裡代表30天，這個引數無法透過OEM修改，只能透過這種方式進行】

~~dbid => 3310949047~~);

END;

/

The window_size parameter is used to specify the new window size, in number of days, for the default moving window size. In this example, the window_size parameter is set to 30. The window size must be set to a value that is equal to or less than the value of the AWR retention setting. To set a window size that is greater than the current AWR retention period, you must first increase the value of the retention parameter

視窗的大小必須小於等於AWR的保留期。如果要設定的大，就必須先增加AWR的保留期限。

In this example, the optional dbid parameter specifies the database identifier is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value.

5.3.3 Managing Baseline Templates

This section describes how to manage baseline templates. You can automatically create baselines to capture specified time periods in the future using baseline templates.

The primary interface for managing baseline templates is Oracle Enterprise Manager. Whenever possible, you should manage baseline templates using Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can manage baseline templates using the DBMS_WORKLOAD_REPOSITORY package, as described in the following sections:

Creating a Single Baseline Template create_baseline_template();
Creating a Repeating Baseline Template create_baseline_template();
Dropping a Baseline Template drop_baseline_template();

5.3.3.1 Creating a Single Baseline Template

This section describes how to create a single baseline template. You can use a single baseline template to create a baseline during a single, fixed time interval in the future. For example, you can create a single baseline template to generate a baseline that is captured on April 2, 2009 from 5:00 p.m. to 8:00 p.m.

這節討論了怎樣去建立一個單一的基線模板。你可以使用一個單一基線模板在未來建立一個單一的或者固定時間間隔的基線。例如你可以使用一個基線模板建立一個基於2009/04/02 17:00到2009/04/02 20:00之間的一個基線。

To create a single baseline template, use the CREATE_BASELINE_TEMPLATE procedure:

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (

start_time => '2009-04-02 17:00:00 PST', 建立的起始時間是什麼

end_time => '2009-04-02 20:00:00 PST', 建立的結束時間是什麼

baseline_name => 'baseline_090402', 將來建立的這個基線名字是什麼

template_name => 'template_090402', 這個模板的名字是什麼

expiration => 30, 建立的這個基線過期日期，如果不指定就是永遠不過期，注意是基線過期，而不是基線模板過期

~~dbid => 3310949047~~);

END;

/

The start_time parameter specifies the start time for the baseline to be created.

The end_time parameter specifies the end time for the baseline to be created.

The baseline_name parameter specifies the name of the baseline to be created.

The template_name parameter specifies the name of the baseline template.

The optional expiration parameter specifies the expiration, in number of days, for the baseline. If unspecified, then the baseline never expires.

The optional dbid parameter specifies the database identifier. If unspecified, then the local database identifier is used as the default value.

In this example, a baseline template named template_090402 is created that will generate a baseline named baseline_090402 for the time period from 5:00 p.m. to 8:00 p.m. on April 2, 2009 on the database with a database ID of 3310949047. The baseline will expire after 30 days.

5.3.3.2 Creating a Repeating Baseline Template

This section describes how to create a repeating baseline template. A repeating baseline template can be used to automatically create baselines that repeat during a particular time interval over a specific period in the future. For example, you can create a repeating baseline template to generate a baseline that repeats every Monday from 5:00 p.m. to 8:00 p.m. for the year 2009.

這節描述了怎樣去建立一個重複的基線模板。重複基線模板被用作在未來的某一時間段內以一個特定的時間間隙去自動建立基線。例如：你可以在2009年這一年中，在每週一的5點到8點建立一個基線。

To create a repeating baseline template, use the CREATE_BASELINE_TEMPLATE procedure:

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (

day_of_week => 'monday',

hour_in_day => 17, 【從17:00開始，持續3小時，即17:00 ~ 20:00這個區間內建立基線】

duration => 3,

expiration => 30,

start_time => '2009-04-02 17:00:00 PST',

end_time => '2009-12-31 20:00:00 PST',

baseline_name_prefix => 'baseline_2009_mondays_',

template_name => 'template_2009_mondays',

~~dbid => 3310949047~~);

END;

/

The day_of_week parameter specifies the day of the week on which the baseline will repeat. The hour_in_day parameter specifies the hour in the day when the baseline will start. The duration parameter specifies the duration, in number of hours, that the baseline will last. The expiration parameter specifies the number of days to retain each created baseline. If set to NULL, then the baselines never expires. The start_time parameter specifies the start time for the baseline to be created. The end_time parameter specifies the end time for the baseline to be created. The baseline_name_prefix parameter specifies the name of the baseline prefix that will be appended to the data information when the baseline is created. The template_name parameter specifies the name of the baseline template. The optional dbid parameter specifies the database identifier. If unspecified, then the local database identifier is used as the default value.

In this example, a baseline template named template_2009_mondays is created that will generate a baseline on every Monday from 5:00 p.m. to 8:00 p.m. beginning on April 2, 2009 at 5:00 p.m. and ending on December 31, 2009 at 8:00 p.m. on the database with a database ID of 3310949047. Each of the baselines will be created with a baseline name with the prefix baseline_2009_mondays_ and will expire after 30 days.

本例子建立了一個重複基線模板，模板的名稱是：template_2009_mondays，建立後的基線名稱字首是：baseline_2009_mondays_，是從2009/04/02 17:00開始，到2009/12/31 20:00結束；在這個期間裡，每週一的下午17:00 ~ 20：00建立一個基線，這些基線在30天后過期。

建立的這個重複基線在OEM中是看不到的，但是透過這個dba_hist_baseline_template檢視可以檢視到

注意如果要測試的話，儘量不要直接複製這些SQL語句，因為我是從word中編輯的，這裡有太多不可見的亂七八糟的字元，所以建議在記事本或者vi中自己手寫一下，一般就能執行成功的。

5.3.3.3 Dropping a Baseline Template

This section describes how to drop an existing baseline template. Periodically, you may want to remove baselines templates that are no longer used to conserve disk space.

To drop a baseline template:

Review the existing baselines in the DBA_HIST_BASELINE_TEMPLATE view to determine the baseline template you want to drop.

Use the DROP_BASELINE_TEMPLATE procedure to drop the desired baseline template:

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (

template_name => 'template_2009_mondays',

~~dbid => 3310949047~~);

END;

/

The template_name parameter specifies the name of the baseline template that will be dropped. In the example, the name of baseline template that will be dropped is template_2009_mondays. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value.

只要指定模板名就可以刪除了，這個沒什麼好介紹的了

5.3.4 Transporting Automatic Workload Repository Data

Oracle Database enables you to transport AWR data between systems. This is useful in cases where you want to use a separate system to perform analysis of the AWR data. To transport AWR data, you must first extract the AWR snapshot data from the database on the source system, then load the data into the database on the target system, as described in the following sections:

oracle可以讓我們將AWR資料傳輸到其他系統中。對於想在不同的資料庫中分析AWR資料是非常有用的。為了複製AWR資料庫，你當然首先要從源庫中抽取資料，然後再載入到自己的目標庫中，包含下面2部分：

Extracting AWR Data
Loading AWR Data

5.3.4.1 Extracting AWR Data

The awrextr.sql script extracts the AWR data for a range of snapshots from the database into a Data Pump export file. After it is created, you can transport this dump file to another database where you can load the extracted data. To run the awrextr.sql script, you must be connected to the database as the SYS user.

awrextr.sql 指令碼可以將一系列的快照抽取到datapump檔案中。然後就可以將此檔案再匯入到目標庫中。為了執行awrextr.sql,你必須以SYS使用者身份連線到資料庫上。

To extract AWR data:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrextr.sql

A list of the databases in the AWR schema is displayed.

Specify the database from which the AWR data will be extracted:

Enter value for db_id: 1377863381 【這裡可以不用輸入，下圖就是例子，直接回車就好了】

In this example, the database with the database identifier of 1377863381 is selected.

Specify the number of days for which you want to list snapshot IDs.

Enter value for num_days: 【2】下圖就是我輸入2天后的情況，因為在虛擬機器上，所以沒有snapshot

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

Define the range of snapshots for which AWR data will be extracted by specifying a beginning and ending snapshot ID:

Enter value for begin_snap: 【30】

Enter value for end_snap: 【40】

In this example, the snapshot with a snapshot ID of 30 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 40 is selected as the ending snapshot.

A list of directory objects is displayed.

Specify the directory object pointing to the directory where the export dump file will be stored:

Enter value for directory_name: 【DATA_PUMP_DIR】這裡可以看到，oracle已經幫我們顯示出來所有的目錄了，這個DATA_PUMP_DIR的目錄是：$ORACLE_BASE/admin/PROD/dpdump/，當然也可以自己手動建立一個目錄，然後把這個放到自己喜歡的地方，比如我一般會建立一個OHOME目錄，如：

create directory ohome as ‘/home/oracle/dump’; 這樣就可以指定到OHOME目錄下了

In this example, the directory object DATA_PUMP_DIR is selected.

Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):

Enter value for file_name: 【awrdata_30_40】這是要輸入的檔名，一定要輸入的，不輸入就會出錯退出，oracle應該自己產生一個檔名，供我們選擇，這裡oracle處理的不好，要是喬幫主設計的話，估計會幫我們處理好的！

In this example, an export dump file named awrdata_30_40 will be created in the directory corresponding to the directory object you specified:

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

C:\ORACLE\PRODUCT\11.1.0.5\DB_1\RDBMS\LOG\AWRDATA_30_40.DMP

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:58:20

Depending on the amount of AWR data that must be extracted, the AWR extract operation may take a while to complete. After the dump file is created, you can use Data Pump to transport the file to another system.

至此匯出工作就全部完成了，把這個awrdata_30_40.dmp複製走就是了！

5.3.4.2 Loading AWR Data

After the export dump file is transported to the target system, you can load the extracted AWR data using the awrload.sql script. The awrload.sql script will first create a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you must be connected to the database as the SYS user.

To load AWR data:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrload.sql

A list of directory objects is displayed.

Specify the directory object pointing to the directory where the export dump file is located:

Enter value for directory_name: DATA_PUMP_DIR 要把上面匯出的檔案放到這個目錄下的

In this example, the directory object DATA_PUMP_DIR is selected.

Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):

Enter value for file_name: awrdata_30_40 指定你要匯入的檔名

In this example, the export dump file named awrdata_30_40 is selected.

Specify the name of the staging schema where the AWR data will be loaded:

Enter value for schema_name: AWR_STAGE 指定要匯入的使用者名稱

In this example, a staging schema named AWR_STAGE will be created where the AWR data will be loaded.

Specify the default tablespace for the staging schema:

Enter value for default_tablespace: SYSAUX 想要把資料匯入到哪個表空間裡，可以自己建立一個

In this example, the SYSAUX tablespace is selected.

Specify the temporary tablespace for the staging schema:

Enter value for temporary_tablespace: TEMP 指定一個臨時表空間，所有資料庫都有臨時表空間的，隨便指定一個就是了，但是別太小了，太小就容易出錯

In this example, the TEMP tablespace is selected.

A staging schema named AWR_STAGE will be created where the AWR data will be loaded. After the AWR data is loaded into the AWR_STAGE schema, the data will be transferred into the AWR tables in the SYS schema:

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Completed 113 CONSTRAINT objects in 11 seconds

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Completed 1 REF_CONSTRAINT objects in 1 seconds

Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30

... Dropping AWR_STAGE user

End of AWR Load

Depending on the amount of AWR data that must be loaded, the AWR load operation may take a while to complete. After the AWR data is loaded, the staging schema will be dropped automatically.

至此，AWR資料就已經匯入到我們指定的目標資料庫裡了！

5.3.5 Using Automatic Workload Repository Views

Typically, you would view the AWR data through Oracle Enterprise Manager or AWR reports. However, you can also view the statistics using the following views:

V$ACTIVE_SESSION_HISTORY 【這個檢視中存著ASH每秒鐘採集一次的動態活躍會話歷史記錄，因為每秒記錄一次，資料很多，所以會迴圈刪除的】

This view displays active database session activity, sampled once every second.

V$ metric views provide metric data to track the performance of the system

The metric views are organized into various groups, such as event, event class, system, session, service, file, and tablespace metrics.

These groups are identified in the V$METRICGROUP view.

V$METRIC， V$METRICGROUP， V$METRIC_HISTORY ，V$METRICNAME

V$METRICGROUP

V$METRICGROUP displays information about the metric group for each of the four major Streams components: capture, propagation, apply, and queue.

Column

Datatype

Description

GROUP_ID

NUMBER

Internal ID associated with each group

NAME

VARCHAR2(64)

External name of the group

INTERVAL_SIZE

NUMBER

How often to collect statistics --> 多久去收集一次

MAX_INTERVAL

NUMBER

Total number of intervals over which statistics should be collected

DBA_HIST views

The DBA_HIST views displays historical data stored in the database. This group of views includes:

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history for recent system activity
DBA_HIST_BASELINE displays information about the baselines captured on the system, such as the time range of each baseline and the baseline type
DBA_HIST_BASELINE_DETAILS displays details about a specific baseline
DBA_HIST_BASELINE_TEMPLATE displays information about the baseline templates used by the system to generate baselines
DBA_HIST_DATABASE_INSTANCE displays information about the database environment
DBA_HIST_DB_CACHE_ADVICE displays historical predictions of the number of physical reads for the cache size corresponding to each row
DBA_HIST_DISPATCHER displays historical information for each dispatcher process at the time of the snapshot
DBA_HIST_DYN_REMASTER_STATS displays statistical information about the dynamic remastering process
DBA_HIST_IOSTAT_DETAIL displays historical I/O statistics aggregated by file type and function
DBA_HIST_SHARED_SERVER_SUMMARY displays historical information for shared servers, such as shared server activity, common queues and dispatcher queues
DBA_HIST_SNAPSHOT displays information on snapshots in the system
DBA_HIST_SQL_PLAN displays the SQL execution plans
DBA_HIST_WR_CONTROL displays the settings for controlling AWR

5.3.6 Generating Automatic Workload Repository Reports

An AWR report shows data captured between two snapshots (or two points in time). The AWR reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains the workload profile of the system for the selected range of snapshots.

The primary interface for generating AWR reports is Oracle Enterprise Manager. Whenever possible, you should generate AWR reports using Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can generate AWR reports by running SQL scripts, as described in the following sections:

Generating an AWR Report
Generating an Oracle RAC AWR Report
Generating an AWR Report on a Specific Database Instance
Generating an Oracle RAC AWR Report on Specific Database Instances
Generating an AWR Report for a SQL Statement
Generating an AWR Report for a SQL Statement on a Specific Database Instance

To run these scripts, you must be granted the DBA role.

Note:

If you run a report on a database that does not have any workload activity during the specified range of snapshots, calculated percentages for some report statistics can be less than 0 or greater than 100. This result simply means that there is no meaningful value for the statistic.

5.3.6.1 Generating an AWR Report

The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs.

To generate an AWR report:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify whether you want an HTML or a text report:

Enter value for report_type: 【text】【這裡也可以寫html，以便產生一個html的檔案，建議使用html，用瀏覽器看比較方便些】

In this example, a text report is chosen.

Specify the number of days for which you want to list snapshot IDs.

Enter value for num_days: 【2】

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

Specify a beginning and ending snapshot ID for the workload repository report:

Enter value for begin_snap: 【150】

Enter value for end_snap: 【160】

In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

Enter a report name, or accept the default report name:

Enter value for report_name: 【這裡直接回車的話，oracle會幫我們產生一個報表名的】

Using the report name awrrpt_1_150_160

In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated.

注意：這裡不像在匯出AWR資料時要求指定一個directory，匯出因為是使用datapump技術，所以需要目錄；而這裡是匯出文字檔案（html也是文字啦），所以不需要目錄，而是直接產生在當前目錄下，即你使用sqlplus命令登入的那個目錄下面。

5.3.6.2 Generating an Oracle RAC AWR Report

The awrgrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using the current database identifier and all available database instances in an Oracle Real Application Clusters (Oracle RAC) environment.

Note:

In an Oracle RAC environment, you should always try to generate an HTML report (instead of a text report) because they are much easier to read.

To generate an AWR report in an Oracle RAC environment:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrgrpt.sql 【單機的話是awrrpt.sql,叢集是awrgrpt.sql僅此而已，其他都是一樣的】

Specify whether you want an HTML or a text report:

Enter value for report_type: 【html】

In this example, an HTML report is chosen.

Specify the number of days for which you want to list snapshot IDs.

Enter value for num_days: 【2】

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last day are displayed.

Specify a beginning and ending snapshot ID for the workload repository report:

Enter value for begin_snap: 【150】

Enter value for end_snap: 【160】

In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name awrrpt_rac_150_160.html

In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated.

5.3.6.3 Generating an AWR Report on a Specific Database Instance

The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using a specific database and instance. This script enables you to specify a database identifier and instance for which the AWR report will be generated.

To generate an AWR report on a specific database instance:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrrpti.sql 【與單機的awrrpt.sql相比，這個後面加一個i,代表instance】

Specify whether you want an HTML or a text report:

Enter value for report_type: 【text】

In this example, a text report is chosen.

A list of available database identifiers and instance numbers are displayed:

Instances in this Workload Repository schema

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

DB Id Inst Num DB Name Instance Host

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

3309173529 1 MAIN main examp1690

3309173529 1 TINT251 tint251 samp251

Enter the values for the database identifier (dbid) and instance number (inst_num):

Enter value for dbid: 【3309173529】

Using 3309173529 for database Id

Enter value for inst_num: 【1】

Specify the number of days for which you want to list snapshot IDs.

Enter value for num_days: 【2】

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

Specify a beginning and ending snapshot ID for the workload repository report:

Enter value for begin_snap: 【150】

Enter value for end_snap: 【160】

In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name awrrpt_1_150_160

In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated on the database instance with a database ID value of 3309173529.

5.3.6.4 Generating an Oracle RAC AWR Report on Specific Database Instances

The awrgrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using specific databases and instances running in an Oracle RAC environment. This script enables you to specify database identifiers and a comma-delimited list of database instances for which the AWR report will be generated.

Note:

In an Oracle RAC environment, you should always try to generate an HTML report (instead of a text report) because they are much easier to read.

To generate an AWR report on a specific database instance in an Oracle RAC environment:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrgrpti.sql

Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

A list of available database identifiers and instance numbers are displayed:

Instances in this Workload Repository schema

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

DB Id Inst Num DB Name Instance Host

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

3309173529 1 MAIN main examp1690

3309173529 1 TINT251 tint251 samp251

3309173529 2 TINT251 tint252 samp252

Enter the value for the database identifier (dbid):

Enter value for dbid: 3309173529

Using 3309173529 for database Id

Enter the value for the instance numbers (instance_numbers_or_all) of the Oracle RAC instances you want to include in the report:

Enter value for instance_numbers_or_all: 1,2

Specify the number of days for which you want to list snapshot IDs.

Enter value for num_days: 2

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

Specify a beginning and ending snapshot ID for the workload repository report:

Enter value for begin_snap: 150

Enter value for end_snap: 160

In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name awrrpt_rac_150_160.html

In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated on the database instance with a database ID value of 3309173529.

5.3.6.5 Generating an AWR Report for a SQL Statement

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot IDs. Run this report to inspect or debug the performance of a SQL statement.

To generate an AWR report for a particular SQL statement:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Specify whether you want an HTML or a text report:

Enter value for report_type: 【html】

In this example, an HTML report is chosen.

Specify the number of days for which you want to list snapshot IDs.

Enter value for num_days: 【1】

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

Specify a beginning and ending snapshot ID for the workload repository report:

Enter value for begin_snap: 【146】

Enter value for end_snap: 【147】

In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.

Specify the SQL ID of a particular SQL statement to display statistics:

Enter value for sql_id: 【2b064ybzkwf1y】

In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.

Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name awrrpt_1_146_147.html

In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated.

5.3.6.6 Generating an AWR Report for a SQL Statement on a Specific Database Instance

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot IDs using a specific database and instance.This script enables you to specify a database identifier and instance for which the AWR report will be generated. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

To generate an AWR report for a particular SQL statement on a specified database instance:

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

A list of available database identifiers and instance numbers are displayed:

Instances in this Workload Repository schema

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

DB Id Inst Num DB Name Instance Host

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

3309173529 1 MAIN main examp1690

3309173529 1 TINT251 tint251 samp251

Enter the values for the database identifier (dbid) and instance number (inst_num):

Enter value for dbid: 3309173529

Using 3309173529 for database Id

Enter value for inst_num: 1

Using 1 for instance number

Specify the number of days for which you want to list snapshot IDs.

Enter value for num_days: 1

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

Specify a beginning and ending snapshot ID for the workload repository report:

Enter value for begin_snap: 146

Enter value for end_snap: 147

In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.

Specify the SQL ID of a particular SQL statement to display statistics:

Enter value for sql_id: 2b064ybzkwf1y

In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.

Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name awrrpt_1_146_147.html

In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated on the database instance with a database ID value of 3309173529.

Oracle Performance Tuning 11g2 (5-2)

相關文章