Oracle 11goracle modifies AWR data sampling interval and historical snapshot retention time
Editor to share with you Oracle 11goracle modified AWR data sampling interval and history snapshot retention time, I believe that most people do not know much, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
SQL > col SNAP_INTERVAL for A20
SQL > col RETENTION for A20
SQL > select * from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL
--
890208719 + 00000 01 DEFAULT 00lv 00.0 + 00008 00lv 00lv 00.0
SNAP_INTERVAL=+00000 01VOG 00VOO indicates that the sampling interval is 1 hour.
RETENTION=+00008 00.0 indicates that the retention period of sampled data is 8 days.
More references to the DBA_HIST_WR_CONTROL view can be found in the official Oracle documentation
Http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3202.htm#REFRN23462
SQL > select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control
DBID SNAP_INTERVAL SNAPINT_NUM RETENTION
--
890208719 + 00000 01purl 000.00 00.0 3600 + 00008 000000purl 00.0
SQL > select dbms_stats.get_stats_history_retention from dual
GET_STATS_HISTORY_RETENTION
-
thirty-one
SQL > execute dbms_workload_repository.modify_snapshot_settings (interval = > 60 minutes = > 20160)
PL/SQL procedure successfully completed.
Modify the sampling interval to 1 hour.
Modify the retention period of sampled data to 14 days. 60,24,14,20160.
SQL > select * from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL
--
890208719 + 00000 01 DEFAULT 00lv 00.0 + 00014 00lv 00lv 00.0
SQL > execute dbms_workload_repository.modify_snapshot_settings (interval = > 60 minutes = > 23040)
PL/SQL procedure successfully completed.
Modify the sampling interval to 1 hour.
Modify the retention period of sampled data to 14 days. 60, 24, 16, 23040.
SQL > select * from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL
--
890208719 + 00000 01 DEFAULT 00lv 00.0 + 00016 00lv 00lv 00.0
For example, if we need to adjust the collection interval to 15 minutes and retain the sampling data for 2 days, we can make the following adjustments
Sys@ora10g > BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (
3 interval = > 15
4 retention = > 224060)
5 END
6 /
PL/SQL procedure successfully completed.
The above is all the contents of the article "Oracle 11goracle modifies AWR data sampling interval and historical snapshot retention time". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
