Shulou(Shulou.com)06/01 Report--

1. If the database can collect statistics automatically, the value of statistics_level is typical or all.

Show parameter statistics_level

two。 Modify the statistical interval and preservation period

-- query statistical interval and preservation period

Select snap_interval interval, retention retention period from dba_hist_wr_control

Modify the statistical interval and preservation period

Dbms_workload_repository.modify_snapshot_settings (retention=43200,interval=30);-time is calculated in minutes. If only retention=0 is set, the snapshot will be saved indefinitely.

3. Generate AWR report

1) run $ORACLE_HOME/rdbms/admin/awrrpt.sql to get the report according to the input information as instructed. The information to be entered is:

2) generate AWR reports directly in plsql

SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;-find the range of SNAP_ID you want to view

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, base_line',expiration = > 30)

Start_snap_id and end_snap_id can be replaced with start_time and end_time. For more information, you can check out the relevant chapters in "PL/SQL Packages and Types Reference".

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

2) move the baseline (will be automatically configured)

To capture metrics based on the entire AWR retention period, you can use DBMS_WORKLOAD_REPOSITORY..MODIFY_BASELINE_WINDOW_SIZE to modify the time.

You can view the baseline through the view dba_hist_baseline:

Select baseline_name, start_snap_id

To_char (start_snap_time, 'yyyy-mm-dd hh34:mi:ss') start_time

End_snap_id

To_char (end_snap_time, 'yyyy-mm-dd hh34:mi:ss') end_time,expiration

From dba_hist_baseline

5. Manage AWR statistics base

-rename the baseline

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 will also be 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 AWR baselines

Using DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE, you can automatically create a fixed baseline within repeated intervals and time frames, as shown in 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 instructions

Learn about other stored procedures in PL/SQL Packages and Types Reference by reading Chapter 162 of DBMS_WORKLOAD_REPOSITORY; learn to view AWR reports.

7. references

Chapter 4 of "Oracle Database 11g performance Optimization Strategy"

Chapter 162 of PL/SQL Packages and Types Reference

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.