[Oracle]-performance optimization tool (1)-AWR

AWR Snapshot

By default, Oracle automatically generates a snapshot every hour to save the snapshot of the last eight days.

We can use the following statement to obtain the snapshot generation interval and retention days:

SYS@orcl(lx15)> select SNAP_INTERVAL,RETENTION from dba_hist_wr_control;SNAP_INTERVAL                                                               RETENTION--------------------------------------------------------------------------- ---------------------------------------------------------------------------+00000 01:00:00.0                                                           +00008 00:00:00.0

You can use the following statement to modify the two values (in minutes ):

exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention = > 10*24*60);

Of course, you can also manually generate a snapshot at any time:

exec dbms_workload_repository.create_snapshot;

After an AWR report is generated with a snapshot, you can generate an AWR report. You can use sysdba to execute the following command and follow the prompts to perform one step:

@?/rdbms/admin/awrrpt.sql

Another method is to directly call the ORALCE package. This method is suitable for writing the script tool automatically, as shown below:

Set pagesize 0 set linesize 121 spool d: \ awr_commit_frequently.htmlselect output from table (partition (977587123,1, 1920,1921); spool off Note: partition (l_dbid in number, l_inst_num in number, rochelle bid in number, Rochelle id in number, Rochelle options in number default 0) RETURN awrrpt_text_type_table PIPELINED;