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报表之后，怎样使用呢？）
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);
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）
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就是指定多少个）
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
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.
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,所以不用去指定了
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:
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
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拷贝走就是了！
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 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
- DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history for recent system activity
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1696240/viewspace-1178262/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/1696240/viewspace-1178262/
2848
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
