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

AWR snapshot

By default, Oracle automatically generates a snapshot on its own every hour, saving 8 days of recent snapshots.

We can get the time interval and number of days to generate the snapshot by, for example, 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 change these values (in minutes) by, for example, 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, you are able to generate the AWR report, which you can run with SYSDBA, such as the following command, as a step by steps:

@?/rdbms/admin/awrrpt.sql

Another approach is to call Oralce's package directly, which is suitable for scenarios where you write your own initiative to get a script tool, such as the following:

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;

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