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

The PL/SQL process completed successfully.

The above stored procedure settings are sampled every half hour and retained for 5 days.

SQL > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

# + 00000 00 DEFAULT 30 DEFAULT 00.0 + 00005 00 00 00

The retention time for SQL > modified awr is 10 days.

SQL > exec dbms_workload_repository.modify_snapshot_settings (retention= > 1024060)

Query whether the retention time of awr has been modified: SQL > column dbid format 999999999999

SQL > column snap_interval format A20

SQL > column retention format A20

SQL > select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

# + 00000 01 DEFAULT 00lv 00.0 + 00010 00lv 00lv 00.0

Oracle AWR manual configuration

1. View the current AWR save policy

Select * from dba_hist_wr_control

DBID,SNAP_INTERVAL,RETENTION,TOPNSQL

The above results show that one SNAPSHOT is generated per hour and retained for 7 days.

two。 Adjust AWR configuration

AWR configurations are all configured through the dbms_workload_repository package

2.1 adjust the frequency and retention strategy of snapshot generated by AWR, for example, change the collection interval to 30 minutes. And keep it for 5 days (note: all in minutes):

Exec dbms_workload_repository.modify_snapshot_settings (interval= > 30, retention= > 5 / 24 / 60)

2.2 close AWR and set interval to 0 to turn off automatic snapshot capture

2.3 create a snapshot manually

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ()

2.4 View Snapshot

Select * from sys.wrh$_active_session_history

2.5 manually delete snapshots of a specified range

Exec WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id = > 22, high_snap_id = > 32, dbid = > 3310949047)

2.6Create baseline

Exec dbms_workload_repository.create_baseline (56, 5, 9, 5, 9, 5, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 5, 9, 9, 9, 9, 5, 9, 5, 9, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 9, 5, 4, 4, 9, 9, 4, 4, 4, 3, 4, 9, 1')

2.7 Delete baseline

Exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name = > 'apply_interest_1', cascade = > FALSE)

3. Production AWR report

$ORACLE_HOME/rdbms/admin/awrrpt.sql

AWR collects the characteristic statistics_level effect of statistics, and this parameter has three optional values.

BASIC:AWR statistics collection and all self-tuning features are turned off

TYPICAL: the database collects some statistical information for typical database monitoring needs. Default setting

ALL: all possible statistics are collected

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.