This is the 31st day of my participation in the August More Text Challenge

Commands for adjusting the AWR snapshot policy

  • Query the snapshot
select snap_id,
       to_char(begin_interval_time, 'yyyy-mm-dd hh:mi:ss') start_time,
       to_char(end_interval_time, 'yyyy-mm-dd hh:mi:ss') end_time
  from dba_hist_snapshot
 order by snap_id;
Copy the code
  • Manually generate a snapshot
exec dbms_workload_repository.create_snapshot();
Copy the code
  • To delete a snapshot
exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>213,high_snap_id=>215);
Copy the code
  • View the snapshot generation frequency and retention policy
select snap_interval pinlv,retention save from dba_hist_wr_control;
Copy the code
  • Adjust awR generation and retention policies (retention for 10 days, one production every 10 minutes)
exec dbms_workload_repository.modify_snapshot_settings(retention=>10*24*60,interval=>10);
Copy the code

Interval: Disables automatic and manual snapshots when the interval is set to 0. Retention: Oracle stores this snapshot permanently when the retention time is set to 0.

Some commands about awR baselines

Oracle Database AWR baseline, the main purpose of which is to create a faulty view of the workload for the database so that it can be compared with other AWR snapshots later. Awr baseline performance statistics are stored in the database and are not automatically deleted. Oracle database has two types of baselines: fixed baselines and mobile baselines.

  • Look at the baseline
select dbid,
       baseline_id,
       baseline_name,
       to_char(start_snap_time, 'yyyy-mm-dd hh24:mi:ss'),
       to_char(end_snap_time, 'yyyy-mm-dd hh24:mi:ss'),
       expiration,
       creation_time
  from dba_hist_baseline;
Copy the code
  • Manually create a fixed baseline
exec dbms_workload_repository.create_baseline(start_snap_id => 220,end_snap_id => 221, baseline_name => 'AWR JIXIAN'); 
Copy the code
  • Delete manually created fixed baselines
exec dbms_workload_repository.drop_baseline (baseline_name => 'AWR JIXIAN');
Copy the code
  • Create mobile baselines based on time periods
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'MONDAY',
hour_in_day => 0,
duration => 3,
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name_prefix => 'MONDAY_MORNING',
template_name => 'MONDAY_MORNING',
expiration => 30 );
END;
/
Copy the code

Day_of_week: day or week, and the baseline is repeated at this time. Specify one of the following values:(‘ SUNDAY ‘, ‘MONDAY’, ‘TUESDAY’, ‘WEDNESDAY’, ‘THURSDAY’, ‘FRIDAY’, ‘SATURDAY’, ‘ALL’) hour_IN_day: 0-23, execution baseline starts at this hour. Duration: duration (hours) Start_time: start time of baseline creation. End_time: end time of baseline creation. Expiration: number of days when the baseline expires.

  • Query the activity baseline created above
select template_name,
       template_type,
       start_time,
       end_time,
       day_of_week,
       hour_in_day,
       duration
  from dba_hist_baseline_template;
Copy the code
  • Delete the above mobile baseline
exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE('MONDAY_MORNING');
Copy the code
  • Generate awR baseline comparison report
@? /rdbms/admin/awrddrpt.sqlCopy the code