"Reading Yourself" AWR related Settings

1. In order for the database to collect statistics automatically, the statistics_level is worth typical or all

Show Parameter Statistics_level

2. Modify the statistical time interval and retention period

--Query statistics time interval and retention period

Select Snap_interval time interval, retention save period from Dba_hist_wr_control

Modify the statistics interval and save period

Dbms_workload_repository.modify_snapshot_settings (retention=43200,interval=30);--time is calculated in minutes, if only set retention =0, it means that the snapshot is saved indefinitely.

3. Generate AWR Reports

1) Run $oracle_home/rdbms/admin/awrrpt.sql, according to the instructions to enter information to obtain the report, the information to be entered is:

2) generate the AWR report directly in the Plsql

SELECT * from Dba_hist_snapshot ORDER by snap_id DESC; --Find the range of snap_id to see

SELECT * from TABLE (dbms_workload_repository. Awr_report_html (dbid,instance_num,begin_snap_id,end_snap_id));

HTML can be changed to text.

4. Create a statistical baseline

1) Fixed baseline

EXEC dbms_workload_repository.create_baseline (start_snap_id =>47, end_snap_id =>48,baseline_name = ' Base_ Line ', expiration = 30);

start_snap_id, end_snap_id can replace Start_time and End_time, specifically, you can view the the related chapters in PL/SQL Packages and Types Reference.

Where expiration represents the expiration time, and if not specified, the baseline is never deleted.

2) Move baseline (automatically configured)

You can use Dbms_workload_repository to capture metrics based on the entire awr shelf life. . Modify_baseline_window_size to modify the time.

You can view the baselines through the view Dba_hist_baseline:

Select Baseline_name, start_snap_id,

To_char (Start_snap_time, ' yyyy-mm-dd hh24:mi:ss ') start_time,

END_SNAP_ID,

To_char (End_snap_time, ' yyyy-mm-dd hh24:mi:ss ') end_time,expiration

From Dba_hist_baseline

5. Managing the AWR statistics repository

--Renaming baselines

Dbms_workload_repository. Rename_baseline (

Old_baseline_name in VARCHAR2,

New_baseline_name in VARCHAR2,

dbid in number DEFAULT NULL);

--Delete a baseline

Dbms_workload_repository. Drop_baseline (

Baseline_name in VARCHAR2,

Cascade in BOOLEAN DEFAULT false,#If TRUE, the pair of snapshots associated with the baseline would al So is dropped. Otherwise, only the baseline is removed.

dbid in number DEFAULT NULL);

--Delete Snapshot range

Dbms_workload_repository. Drop_snapshot_range (

low_snap_id in number,

high_snap_id in number

dbid in number DEFAULT NULL);

6. Automatically create an AWR baseline

Use Dbms_workload_repository. Create_baseline_template can automatically create a fixed baseline within a repeating time interval and timeframe, with the following syntax:

Dbms_workload_repository. Create_baseline_template (

Start_time in DATE,

End_time in DATE,

Baseline_name in VARCHAR2,

Template_name in VARCHAR2,

Expiration in number,

dbid in number DEFAULT NULL);

7. Other Notes

Learn more about Dbms_workload_repository's other stored procedures by reading the 162th chapter of PL/SQL Packages and Types Reference; Learn to view the AWR report.

7. References

1. "Oracle Database 11g Performance Optimization Strategy" chapter fourth;

2. The 162th chapter of PL/SQL Packages and Types Reference;
   

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1835435

"Reading Yourself" AWR related Settings