AWR , DBMS_WORKLOAD_REPOSITORY，手工修改历史快照信息
来源：互联网 发布：东村明子 知乎 编辑：程序博客网 时间：2024/04/30 20:38
AWR (Automatic Workload Repository)
一堆历史性能数据,放在SYSAUX表空间上, AWR和SYSAUX都是10g出现 的,是Oracle调优的关键特性;
默认快照间隔1小时,10g保存7天、11g保存8天;
可以通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS修改
DBA_HIST_WR_CONTROL AWR程序核心是dbms_workload_repository包
@?/rdbms/admin/awrrpt 单实例
@?/rdbms/admin/awrrpti RAC中选择实例号
（1）修改AWR报告默认收集时间设置
col SNAP_INTERVAL for a25
col retention for a30
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------- ------------------------------ --------------------
3569708122 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
##修改快照收集时间间隔为30min/次
15:08:29 @felix SQL>exec dbms_workload_repository.modify_snapshot_settings(retention => 15*1440,interval => 30);
PL/SQL procedure successfully completed.
##再次查看收集时间间隔
sys@felix SQL>col SNAP_INTERVAL for a25
sys@felix SQL>col retention for a30
sys@felix SQL>select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------- ------------------------------ --------------------
3569708122 +00000 00:30:00.0 +00015 00:00:00.0 DEFAULT
sys@felix SQL>
interval：将间隔设置为0时，oracle将禁止采用自动和手工快照的机制。
retention：（单位是min）最小值为1天最大值是100年，当保存时间设置为0时，oracle将永久地保存这个快照。
Parameter Description
------------------------------------------------------------------------------------------------------------------
retention：
New retention time (in minutes). The specified value must be in the range of MIN_RETENTION (1 day) toMAX_RETENTION (100 years).
If ZERO is specified, snapshots will be retained forever. A large system-defined value will be used as the retention setting.
If NULL is specified, the old value for retention is preserved.
NOTE: The retention setting must be greater than or equal to the window size of the 'SYSTEM_MOVING_WINDOW' baseline. If the retention needs to be less than the window size, the MODIFY_BASELINE_WINDOW_SIZE Procedure can be used to adjust the window size.
interval：
New interval setting between each snapshot, in units of minutes. The specified value must be in the rangeMIN_INTERVAL (10 minutes) to MAX_INTERVAL (1 year).
If ZERO is specified, automatic and manual snapshots will be disabled. A large system-defined value will be used as the retention setting.
If NULL is specified, the current value is preserved.
topnsql：
If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.
If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULLfor this argument will keep the current setting.
dbid：
Database identifier in AWR for which to modify the snapshot settings. If NULL is specified, the local dbid will be used. Defaults to NULL.
------------------------------------------------------------------------------------------------------------------
2、创建和删除快照
创建快照：
sys@felix SQL>exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--查看历史快照
sys@felix SQL>col BEGIN_INTERVAL_TIME for a40
sys@felix SQL>col end_INTERVAL_TIME for a40
sys@felix SQL>select snap_id,dbid, BEGIN_INTERVAL_TIME ,END_INTERVAL_TIME,SNAP_LEVEL from dba_hist_snapshot;
SNAP_ID DBID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- ---------- ---------------------------------------- ---------------------------------------- ----------
13 3569708122 07-NOV-13 09.00.21.022 PM 07-NOV-13 10.00.28.850 PM 1
15 3569708122 07-NOV-13 11.00.42.688 PM 08-NOV-13 12.00.48.633 AM 1
26 3569708122 08-NOV-13 10.00.44.335 AM 08-NOV-13 11.00.52.435 AM 1
31 3569708122 09-NOV-13 09.12.57.397 PM 09-NOV-13 10.00.01.163 PM 1
14 3569708122 07-NOV-13 10.00.28.850 PM 07-NOV-13 11.00.42.688 PM 1
17 3569708122 08-NOV-13 01.01.00.243 AM 08-NOV-13 02.00.06.492 AM 1
22 3569708122 08-NOV-13 06.11.18.856 AM 08-NOV-13 07.00.23.999 AM 1
23 3569708122 08-NOV-13 07.00.23.999 AM 08-NOV-13 08.00.30.606 AM 1
24 3569708122 08-NOV-13 08.00.30.606 AM 08-NOV-13 09.00.36.975 AM 1
25 3569708122 08-NOV-13 09.00.36.975 AM 08-NOV-13 10.00.44.335 AM 1
27 3569708122 08-NOV-13 11.00.52.435 AM 08-NOV-13 12.00.59.646 PM 1
29 3569708122 08-NOV-13 01.40.48.000 PM 08-NOV-13 02.00.47.576 PM 1
30 3569708122 09-NOV-13 09.02.00.000 PM 09-NOV-13 09.12.57.397 PM 1
4 3569708122 07-NOV-13 12.00.21.202 PM 07-NOV-13 01.00.26.557 PM 1
5 3569708122 07-NOV-13 01.00.26.557 PM 07-NOV-13 02.00.32.464 PM 1
6 3569708122 07-NOV-13 02.00.32.464 PM 07-NOV-13 03.00.39.198 PM 1
7 3569708122 07-NOV-13 03.00.39.198 PM 07-NOV-13 04.00.45.434 PM 1
8 3569708122 07-NOV-13 04.00.45.434 PM 07-NOV-13 05.00.51.938 PM 1
9 3569708122 07-NOV-13 05.00.51.938 PM 07-NOV-13 06.00.58.379 PM 1
12 3569708122 07-NOV-13 08.00.14.426 PM 07-NOV-13 09.00.21.022 PM 1
21 3569708122 08-NOV-13 06.00.14.000 AM 08-NOV-13 06.11.18.856 AM 1
32 3569708122 09-NOV-13 10.00.01.163 PM 09-NOV-13 11.00.07.590 PM 1
2 3569708122 07-NOV-13 10.18.36.000 AM 07-NOV-13 11.00.14.575 AM 1
3 3569708122 07-NOV-13 11.00.14.575 AM 07-NOV-13 12.00.21.202 PM 1
10 3569708122 07-NOV-13 06.00.58.379 PM 07-NOV-13 07.00.04.665 PM 1
11 3569708122 07-NOV-13 07.00.04.665 PM 07-NOV-13 08.00.14.426 PM 1
16 3569708122 08-NOV-13 12.00.48.633 AM 08-NOV-13 01.01.00.243 AM 1
18 3569708122 08-NOV-13 02.00.06.492 AM 08-NOV-13 03.00.12.489 AM 1
19 3569708122 08-NOV-13 03.00.12.489 AM 08-NOV-13 04.00.19.944 AM 1
20 3569708122 08-NOV-13 04.00.19.944 AM 08-NOV-13 05.00.26.406 AM 1
28 3569708122 08-NOV-13 12.00.59.646 PM 08-NOV-13 01.00.06.539 PM 1
33 3569708122 30-OCT-14 03.05.29.000 PM 30-OCT-14 03.16.27.214 PM 1
34 3569708122 30-OCT-14 03.16.27.214 PM 30-OCT-14 03.19.09.539 PM 1
33 rows selected.
sys@felix SQL>
----删除10到30序号的归档：
sys@felix SQL>exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 10,high_snap_id =>30);
PL/SQL procedure successfully completed.
sys@felix SQL>
sys@felix SQL>select snap_id,dbid, BEGIN_INTERVAL_TIME ,END_INTERVAL_TIME,SNAP_LEVEL from dba_hist_snapshot;
SNAP_ID DBID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- ---------- ---------------------------------------- ---------------------------------------- ----------
31 3569708122 09-NOV-13 09.12.57.397 PM 09-NOV-13 10.00.01.163 PM 1
4 3569708122 07-NOV-13 12.00.21.202 PM 07-NOV-13 01.00.26.557 PM 1
5 3569708122 07-NOV-13 01.00.26.557 PM 07-NOV-13 02.00.32.464 PM 1
6 3569708122 07-NOV-13 02.00.32.464 PM 07-NOV-13 03.00.39.198 PM 1
7 3569708122 07-NOV-13 03.00.39.198 PM 07-NOV-13 04.00.45.434 PM 1
8 3569708122 07-NOV-13 04.00.45.434 PM 07-NOV-13 05.00.51.938 PM 1
9 3569708122 07-NOV-13 05.00.51.938 PM 07-NOV-13 06.00.58.379 PM 1
32 3569708122 09-NOV-13 10.00.01.163 PM 09-NOV-13 11.00.07.590 PM 1
2 3569708122 07-NOV-13 10.18.36.000 AM 07-NOV-13 11.00.14.575 AM 1
3 3569708122 07-NOV-13 11.00.14.575 AM 07-NOV-13 12.00.21.202 PM 1
33 3569708122 30-OCT-14 03.05.29.000 PM 30-OCT-14 03.16.27.214 PM 1
34 3569708122 30-OCT-14 03.16.27.214 PM 30-OCT-14 03.19.09.539 PM 1
12 rows selected.
sys@felix SQL>
-- dba_hist_snapshot视图将列出有关可用快照的信息。
3、创建和删除基线
创建基线：
sys@felix SQL>exec dbms_workload_repository.create_baseline(start_snap_id =>31,end_snap_id=>34,baseline_name=>'base_line_felix');
PL/SQL procedure successfully completed.
sys@felix SQL>col baseline_name for a40
sys@felix SQL>select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;
BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------------------------------------- ------------- -----------
base_line_felix 31 34
SYSTEM_MOVING_WINDOW 33 34
## 删除基线：
sys@felix SQL>exec dbms_workload_repository.drop_baseline(baseline_name=>'base_line_felix',cascade=>true);
PL/SQL procedure successfully completed.
sys@felix SQL>select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;
no rows selected
sys@felix SQL> exec dbms_workload_repository.create_baseline(to_date('2014-04-03 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2014-05-30 15:20:00','yyyy-mm-dd hh24:mi:ss'),'base line2');
PL/SQL 过程已成功完成。
sys@felix SQL> select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;
BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------------------------------------------------------------- ------------- -----------
base line 685 686
base line2 685 686
SYSTEM_MOVING_WINDOW 640 696
4、自动创建AWR基线
sys@felix SQL> alter session set NLS_DATE_FORMAT= 'yyyy-mm-dd hh24:mi:ss';
会话已更改。
sys@felix SQL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template( -
> day_of_week => 'WEDNESDAY', -
> hour_in_day => 0, -
> duration => 8, -
> start_time => '2014-11-01:00:00:00', -
> end_time => '2014-12-31:06:00:00', -
> baseline_name_prefix => 'Base Baseline ', -
> template_name => 'Base Template', -
> expiration => 365);
sys@felix SQL> select t.template_name,
2 t.template_type,
3 t.start_time,
4 t.end_time,
5 t.day_of_week,
6 t.hour_in_day,
7 t.duration
8 from dba_hist_baseline_template t;
TEMPLATE_NAME TEMPLATE_ START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY DURATION
------------------------------ --------- ------------------- ------------------- --------- ----------- ----------
Batch Template REPEATING 2014-11-01 00:00:00 2014-12-31 06:00:00 WEDNESDAY 0 8
sys@ORCL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template('Base_Template');
day_of_week：天或者星期，基线在这个时间上重复。Specify one of the following values:('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY','THURSDAY', 'FRIDAY', 'SATURDAY', 'ALL')
hour_in_day：0-23，执行基线在这个小时开始。
duration：持续时间(小时数)。
start_time：创建基线的开始时间。
end_time：创建基线的结束时间。
expiration :基线过期的天数。
对于上面这个模板，将会在每周三根据0:00到上午6：00的时间窗口创建固定基线。
- AWR , DBMS_WORKLOAD_REPOSITORY，手工修改历史快照信息
- dbms_workload_repository手工管理AWR
- Oracle AWR 删除历史快照 说明
- Oracle AWR 删除历史快照 说明
- 手工创建快照及awr相关配置说明
- 【AWR】调整AWR数据采样时间间隔及历史快照保留时间
- oracle 【AWR】调整AWR数据采样时间间隔及历史快照保留时间
- oracle用dbms_workload_repository取AWR报告方法
- 修改awr报告的保留时间和快照间隔
- 修改oracle awr修改信息收集频率
- Oracle AWR 删除历史快照 说明【转自dave偶像大神】
- 生成awr快照
- 手工生成awr报告
- 手工搜集AWR日志
- AWR:select output from table(dbms_workload_repository.awr_report_html( :dbid, * ERROR
- 手工执行awr报告生成
- git修改历史提交信息commit message
- 生成awr报告和手动生成awr快照
- 数组差的绝对值
- 12306客服电话 是多少
- JniHelper类的使用
- 博弈问题及SG函数
- HDU 3995 Special Fish（KM最大匹配）
- AWR , DBMS_WORKLOAD_REPOSITORY，手工修改历史快照信息
- sqlmap使用总结
- 将应用部署到jboss，java.lang.OutOfMemoryError: PermGen space解决方法
- （转）livecd 修复Grub
- （转）乌班图12.04超级用户登录
- Eddy's picture（并查集+最小生成树）
- （原）双核四线程
- （原）super.paintComponent解析
- sed行编辑器使用介绍
