Shulou(Shulou.com)06/02 Report--

1.AWR default sampling interval and historical snapshot retention time

The default is seven days, and the collection interval is 1 hour. This information can be obtained from the DBA_HIST_WR_CONTROL view.

Sys@ora10g > col SNAP_INTERVAL for A20

Sys@ora10g > col RETENTION for A20

Sys@ora10g > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

3973098169 + 00000 01 DEFAULT 00lv 00.0 + 00007 00lv 00lv 00.0

SNAP_INTERVAL=+00000 01VOG 00VOO indicates that the sampling interval is 1 hour.

RETENTION=+00007 00.0 indicates that the retention period of sampled data is 7 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

two。 GC method for modifying AWR default sampling interval and historical snapshot retention time

Please do it in the following order.

1) Click Targets

2) Click Databases, select the database to be adjusted and enter

3) search for the keyword "Automatic Workload Repository" (this content belongs to Administration-Database Administration-Statistics Management) to enter, and you can see the current settings of the system.

4) Click Edit to modify. Note that there are only 10 Minutes, 15 Minutes, 20 Minutes, 30 Minutes, 1Hour, and 2 Hours for System Snapshot Interval, which are somewhat limited (command line modification method is not subject to this constraint).

3. SQL method for modifying AWR default sampling interval and historical snapshot retention time

Everything remains the same, and anything that can be modified from the interface can be done through SQL adjustment.

What we are going to use is the MODIFY_SNAPSHOT_SETTINGS stored procedure in the DBMS_WORKLOAD_REPOSITORY package.

A brief description of the stored procedure is as follows.

Sec@ora10g > desc DBMS_WORKLOAD_REPOSITORY

…… ……

PROCEDURE MODIFY_SNAPSHOT_SETTINGS

Argument Name Type In/Out Default?

RETENTION NUMBER IN DEFAULT

INTERVAL NUMBER IN DEFAULT

TOPNSQL VARCHAR2 IN

DBID NUMBER IN DEFAULT

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.

Verify the modification result

Sys@ora10g > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

4134757407 + 00000 DEFAULT 1500 DEFAULT 00.0 + 00002 0000 virtual 00.0

For more information on this stored procedure, please refer to the official Oracle documentation http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#BACEHJJI.

It is worth noting that:

The value range of RETENTION parameter is 1 day to 100 years, that is, snapshots need to be retained for at least 1 day, and can be retained for up to 100 years.

The value range of the INTERVAL parameter is 10 minutes to 100 years, that is, the snapshot collection interval can be at least 10 minutes and the longest can be 100 years. (there is something wrong with the official document, not only the words described here are incorrect, but also the maximum one year is incorrect, and the word error has been corrected in the 11gR1 document).

1) verify the value range of the RETENTION parameter

An informative error ORA-13510 is given when the RETENTION parameter is assigned less than 1 day or more than 100 years. The value range of the parameter can also be obtained from the prompt (1440, 52560000), 1440 minutes is 1 day, 52560000 minutes is 100 years.

Sys@ora10g > BEGIN

2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (

3 interval = > 60

4 retention = > 0.5 / 24 / 60)

5 END

6 /

BEGIN

*

ERROR at line 1:

ORA-13510: invalid RETENTION 720, must be in the range (1440, 52560000)

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133

ORA-06512: at line 2

Sys@ora10g > BEGIN

2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (

3 interval = > 60

4 retention = > 36501 / 24060)

5 END

6 /

BEGIN

*

ERROR at line 1:

ORA-13510: invalid RETENTION 52561440, must be in the range (1440, 52560000)

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133

ORA-06512: at line 2

2) verify the value range of INTERVAL parameters

Sys@ora10g > BEGIN

2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (

3 interval = > 9

4 retention = > 724060)

5 END

6 /

BEGIN

*

ERROR at line 1:

ORA-13511: invalid INTERVAL 9, must be in the range (10, 52560000)

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133

ORA-06512: at line 2

It can be seen here that the value of the INTERVAL parameter is 10 minutes to 52560000 minutes (that is, 100 years), and there is no problem when the interval is 100 years.

Sys@ora10g > BEGIN

2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (

3 interval = > 36500mm 24060

4 retention = > 724060)

5 END

6 /

PL/SQL procedure successfully completed.

Sys@ora10g > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

4134757407 + 36500 36500 DEFAULT 00lv 00.0 + 00007 00lv 00lv 00.0

4. A circuitous method to stop AWR sampling

We can "disable" AWR by adjusting the sampling interval to a maximum of 100 years and adjusting the sampling data retention time to a minimum of 1 day.

Sys@ora10g > BEGIN

2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (

3 interval = > 36500mm 24060

4 retention = > 1 / 24 / 60)

5 END

6 /

PL/SQL procedure successfully completed.

Sys@ora10g > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

4134757407 + 36500 36500 DEFAULT 00lv 00.0 + 00001 00lv 00lv 00.0

5. Summary

This article shows you how to adjust the sampling interval of AWR data and the retention time of historical snapshots through a simple demonstration.

Oracle official documents are also flawed, believing in books is better than no books at all. The most effective tool for verifying the correctness of official Oracle documents is your hands.

Good luck.

Secooler

10.07.18

-- The End--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.