| |
AWR is Oracle 10g's new version features full name Automatic Workload Repository- Automatic Workload Repository, AWR is fast statistical information by comparing the two, according to the (snapshot) to collect and report data to generate reports that include a plurality section.
Setting AWR sampling frequency acquisition and retention strategies
Historical data of sampling default seven days, and the default collection once per hour.
Snapshot retention policy and the current sampling frequency of collection can be queried using the following statement. Shown below are 7 days and 1 hour
SQL> select to_char (snap_interval, 'DD'), to_char (retention, 'DD') FROM dba_hist_wr_control;
TO_CHAR (SNAP_INTER TO_CHAR (RETENTION,
------------
+00000 01: 00: 00.0 +00007 00: 00: 00.0;
We changed by the following manner, set the snapshot collection interval is every 20 minutes reserved two days of historical data. Parameters are specified minutes.
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2 * 24 * 60
);
end;
AWR related tables
Metadata (WRM $)
Historical data (WRH $)
AWR report suggestions method (WRI $)
AWR Oracle 11g New Features of the relevant information (WRR $)
Workload Repository Reports
Oracle provides two main scripts produce AWR report (awrrpt.sql and awrrpti.sql). Their report is very similar to the format and statspack provide HTML and text formats. Two report output in the same format, but allows you to select awrrpti.sql single instance:
@ $ ORACLE_HOME / rdbms / admin / awrrpt.sql
@ $ ORACLE_HOME / rdbms / admin / awrrpti.sql
The following are frequently used to script:
REPORT NAME SQL Script
Automatic Workload Repository Report awrrpt.sql
Automatic Database Diagnostics Monitor Report addmrpt.sql
ASH Report ashrpt.sql
AWR Diff Periods Report awrddrpt.sql
AWR Single SQL Statement Report awrsqrpt.sql
AWR Global Report awrgrpt.sql
AWR Global Diff Report awrgdrpt.sql
AWR snapshot data export and import
AWR data stored in the SYSAUX tablespace and WRH $ DBA_HIST table. If the table grow very large, long data retention time can affect database performance, so by default only seven days of historical data.
A better solution is to transfer the data to another AWR data center storage database, Oracle provides two scripts awrextr.sql and awrload.sql scripts can migrate data to AWR
Another database. In the $ ORACLE_HOME / rdbms / admin directory can be found in these two scripts.
- In source db
SQL> @? / Rdbms / admin / awrextr.sql
- In target db
SQL> @? / Rdbms / admin / awrload.sql
or
Use oracle internal packet
dbms_swrf_internal.AWR_EXTRACT
DBMS_SWRF_INTERNAL.AWR_LOAD
DBMS_SWRF_INTERNAL.MOVE_TO_AWR
DBMS_SWRF_INTERNAL.CLEAR_AWR_DBID
Clear AWR
exec dbms_swrf_internal.unregister_database ();
dbms_workload_repository.DROP_SNAPSHOT_RANGE;
Disabling Oracle AWR
If you want to disable the AWR, you can use the following ways to disable AWR, the following list of detailed steps.
1. Set STATISTICS_LEVEL parameter BASIC.
2, delete the script execution CATNOAWR.sql related awr table. This script execute the stored procedure procedure dbms_swrf_internal.remove_wr_control, delete wrm $ _wr_control table and all relevant data related to AWR.
3, the implementation DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval => 0):
4, downloaded from Metalink dbms_awr.plb script execution DBMS_AWR.DISABLE_AWR () [script See Metalink note 436386.1].
5, if the database is created manually, instead of using the need to perform CATAWRTB.sql DBCA created.
6, set _awr_restrict_mode = TRUE
Reconstruction AWR
Oracle recommends that we reconstruct AWR, the data stored in the SYSAUX table space:
alter system set sga_target = 0 scope = spfile;
alter system set statistics_level = basic scope = both;
alter system set cluster_database = false;
shutdown immediate
startup restrict
- In 10g begin -
@? / Rdbms / admin / catnoawr.sql
alter system flush shared_pool;
@? / Rdbms / admin / catsvrm.sql -in the script had calls catawrtb.sql
- In 10g end -
- In 11g begin-
SQL> @? / Rdbms / admin / catnoawr.sql
SQL> alter system flush shared_pool;
SQL> @? / Rdbms / admin / catawr.sql
SQL> @? / Rdbms / admin / utlrp.sql
sql> @? / rdbms / admin / execsvrm.sql
- In 11g end-
Then re-enable the AWR statistics gathering as required, by setting STATISTICS_LEVEL back to its original value, and restart the instance normally
Tip:
When SYSAUX tablespace is keep growing, you can check the V $ SYSAUX_OCCUPANTS View to find out who / what is occupying space in SYSAUX. |
|
|
|