View and modify awr report retention time
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
