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

AWR snapshot

By default, Oracle automatically generates a snapshot every hour, saving a snapshot for the last 8 days.

We can get the time interval and number of days to generate the snapshot by using the following statement:

[Email protected] (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 modify these two values in minutes by using the following statement:

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

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

exec dbms_workload_repository.create_snapshot;

After the AWR report generates a snapshot, the AWR report can be generated, and you use SYSDBA to perform the following commands, one step at a prompt:

@?/rdbms/admin/awrrpt.sql

Another way to do this is to call the Oralce package directly, which is suitable for scenarios where you can write an auto-fetch script tool, as follows:

Set pagesize 0set linesize 121spool d:\awr_commit_frequently.htmlselect output from table (Dbms_workload_repository.awr _report_html (977587123,1,1920,1921)); Spool off note: Dbms_workload_repository. Awr_report_html (   l_dbid in number    ,   l_inst_num   in        number, L_bid    in number,   L_eid    in number,   l_options in number    DEFAULT 0) RETURN awrrpt_text_type_table pipelined;