Dba_oracle AWR Report Performance monitoring reports (case) (HTML report monitoring database performance)

2014-08-22

I. Summary

Oracle Database 10g provides a significantly improved tool: the automated workload repository (awr:automatic Workload Repository).

Oracle recommends that users replace statspack with this.

1. AWR is essentially a built-in tool for Oracle that collects performance-related statistics and derives sexual energy from those statistics to track potential problems.

A snapshot consists of a new background process called Mmon and its automatic acquisition of data from the process.

$ ps-ef|grep Mmon

Oracle 12861 1 0 Oct09? 00:02:08 Ora_mmon_survey

2. A new view in 10g v$session_wait_history This view holds the last 10 wait events for each active session in V$session_wait.

But this is not enough for a period of time to monitor the performance of the data, in order to solve this problem, in the 10g also added a new view: V$active_session_history, this is the Ash,ash default every second to collect v$ In the event of an active session in the session, recording the event waiting for the conversation, the inactive session is not sampled, the interval is determined by the _ash_sampling_interval parameter, because the activity information of the session is time and space-consuming, The strategy for Ash is to save information for the active session in the waiting state, sample from v$session_wait every second, and store the sampled information in memory (Ash's sampled data is stored in memory)

3. Ash Sampling

Ash's sampled data is stored in memory, and the amount of memory allocated to Ash is limited, and when the allocated space fills up, the old records are overwritten, and after the database restarts, all of these ash messages will disappear.

This is not possible for long-term detection of Oracle's performance, in oracle10g, which provides a permanent way to keep ash information, which is awr, because it is time-consuming and space-saving to save all the information in ash.

So AWR's strategy is: the Mmon process samples ASH once per hour and saves the information to disk, such as out-of-memory, ash buffer full, the MMNL process will be actively written, and retained for 7 days, 7 days after the old record will be overwritten. These sampling information is saved in the table

SELECT pool, name, bytes/1024x768/1024x768fromwhere  like'%ash%';

In fact, the AWR record information is not only ash, but also can collect all aspects of the database running statistics and waiting information to diagnose the analysis. AWR is sampled by performing a sample of all of its important statistics and payload information at a fixed time interval and storing the sampled information in the AWR. You can say this: The information in Ash is saved in the view wrh$_active_session_history in Awr. Ash is a true subset of AWR.

AWR uses several tables to store the collected performance statistics, all of which are stored in the SYS mode in the Sysaux table space, and in wrm$_* (5) and wrh$_* (94) format names.

Wrm$_* This type stores metadata information, such as checked databases and captured snapshots, wrh$_* this type holds the actual collected statistics.

H stands for "Historical Data (historical)", while M stands for "metadata (metadata)".

Several views (dba_hist_snapshot,dba_hist_baseline, etc.) with prefix dba_hist_ have been built on these tables, which can be used to write your own performance diagnostic tools.

To save space, the default data collected by the system is automatically cleared after 7 days. The snapshot frequency and retention time can be modified by the user:

4. Generate the AWR report ($ORACLE _home/rdbms/admin/):

Awrrpt.sql: Generate a statistical report of the specified snapshot interval;

Awrrpti.sql: Generates the specified DB instance, and specifies a statistical report of the snapshot interval;

Awrsqlrpt.sql: Generates a statistical report of the specified snapshot interval, specifying the SQL statement (which is actually specified as the sqlid of the statement);

Awrsqrpi.sql: Generates a statistical report of the specified SQL statement for the specified DB instance, specifying the snapshot interval;

Awrddrpt.sql: Specify two different time periods, generate a statistical comparison report of the two periods;

Two. AWR operation

2.1. View the current AWR save policy

Sql> select * from Dba_hist_wr_control;

3.2. Adjust the AWR configuration

The AWR configuration is configured through the Dbms_workload_repository package.

2.2.1 Adjusts the frequency and retention policy of the AWR to generate snapshot, such as changing the collection interval to 30 minutes. and keep for 5 days (units are minutes):

sql> exec dbms_workload_repository.modify_snapshot_settings (interval=>30, retention=>5*24*60);

2.2.2 Close awr, set interval to 0 to turn off Auto capture snapshot

sql> exec dbms_workload_repository.modify_snapshot_settings (interval=>0);

2.2.3 Creating a snapshot manually

Sql> exec dbms_workload_repository.create_snapshot ();

2.2.4 Viewing snapshots

Sql> SELECT * from Sys.wrh$_active_session_history

2.2.5 manually deleting a snapshot of a specified range

sql> exec dbms_workload_repository.drop_snapshot_range (low_snap_id = 973, high_snap_id = 999, dbid = 262089084);

2.2.6 Create baseline to save this data for future analysis and comparison

sql> exec dbms_workload_repository.create_baseline (start_snap_id = 1003, end_snap_id = 1013, ' Apply_ Interest_1 ');

2.2.7 Delete Baseline

sql> exec dbms_workload_repository.drop_baseline (baseline_name = ' apply_interest_1 ', cascade = FALSE);

2.2.8 to export and migrate awr data to other databases for later analysis

Sql> exec dbms_swrf_internal. Awr_extract (dmpfile = ' awr_data.dmp ', Mpdir = ' dir_bdump ', bid = 1003, Eid = 1013);

2.2.9 migrating awr data files to other databases

Sql> exec dbms_swrf_internal. Awr_load (schname = ' awr_test ', dmpfile = ' awr_data.dmp ', Dmpdir = ' dir_bdump ');

To transfer the AWR data to the SYS mode:

Sql> exec dbms_swrf_internal. Move_to_awr (schname = ' TEST ');

Iii. Cases-Create snapshot information and export

Case: Create two snapshot manually and import a snapshot between the two nodes

1. Configure the AWR configuration information to adjust the frequency and retention policy of the AWR to generate snapshot

The AWR configuration is configured through the Dbms_workload_repository package, such as changing the collection interval to 30 minutes and reserving 31 days (units are minutes):

sql> exec dbms_workload_repository.modify_snapshot_settings (interval=>30, retention=>31*24*60);

2. Turn off AWR:

Set Interval to 0 to turn off auto capture snapshots:

sql> exec dbms_workload_repository.modify_snapshot_settings (interval=>0);

3. Create a snapshot manually:

Sql> exec dbms_workload_repository.create_snapshot ();

If some of the database parameters or applications have been adjusted, it is possible to manually create a snapshot on the fly to facilitate statistical analysis of the data.

4. View the snapshot:

Sql> select * from Sys.wrh$_active_session_history;

5. Manually delete a snapshot of a specified range

sql> exec dbms_workload_repository.drop_snapshot_range (low_snap_id = 4, high_snap_id = 5, dbid = 1368620684);

6. Generate the AWR report ($ORACLE _home/rdbms/admin):

Awrrpt.sql: Generate a statistical report of the specified snapshot interval;

Awrrpti.sql: Generates the specified DB instance, and specifies a statistical report of the snapshot interval;

Awrsqlrpt.sql: Generates a statistical report of the specified snapshot interval, specifying the SQL statement (which is actually specified as the sqlid of the statement);

Awrsqrpi.sql: Generates a statistical report of the specified SQL statement for the specified DB instance, specifying the snapshot interval;

Awrddrpt.sql: Specify two different time periods, generate a statistical comparison report of the two periods;

Four. AWR Report analysis

Case:

4.1 SQL ordered by Elapsed time

Top SQL that records the total time of execution (note that the sum of the execution time of the SQL is monitored within the scope, not the single-time SQL Execution Time (Elapsed).

Elapsed Time (S): The total length of the SQL statement execution, this sort is done by this field. Note that this time is not a single SQL run time, but a sum of the number of SQL executions that are monitored within the range. Unit time is seconds. Elapsed time = CPU time + Wait time

CPU time (s): The total duration of CPU elapsed for SQL statement execution, which is less than or equal to elapsed time. Unit time is seconds.

The total number of executions of the EXECUTIONS:SQL statement within the monitoring scope.

Elap per Exec (s): Average time to execute SQL once. Unit time is seconds.

% Total DB time: Elapsed time for SQL is the percentage of the database.

The ID number of the SQL Id:sql statement, which can then be navigated to the SQL detail list below, click on the return of IE to go back to the current SQL ID.

SQL Module: Shows how the SQL is connected to the database for execution, and if it is linked with sql*plus or PL/SQL, it is basically someone debugging the program. SQL that is typically executed with the foreground app link is empty.

SQL Text: Simple SQL hint, detailed need to click on SQL ID.

4.2 SQL ordered by CPU time

The top SQL that executes the longest total CPU time is recorded (note that the execution of the SQL in the monitoring scope is the sum of the CPU time, not the single SQL execution time).

4.3 SQL ordered by Gets

The top SQL that performed the total buffer gets (logical IO) was recorded (note that the execution of the SQL in the monitoring range is the sum of the total, rather than the total of a single SQL execution).

4.4 SQL ordered by Reads:

Records the top SQL that performs physical read (physical IO) for the total disk (note that the SQL execution in the monitoring scope is the sum of the physical reads of the disk, not the physical read of the disk as a single SQL execution).

4.5 SQL ordered by executions:

The top SQL that is sorted by the number of executions of SQL is recorded. This sort shows the number of SQL executions that are within the scope of the monitoring.

4.6 SQL ordered by Parse Calls:

Top SQL that records the number of soft parse times for SQL. When it comes to soft-parsing (soft prase) and hard-parsing prase, it's impossible to say how Oracle handles SQL.

4.7 SQL ordered by sharable Memory:

The top SQL that SQL occupies the size of the library cache is logged. Sharable Mem (b): Takes up the size of the library cache, in bytes.

4.8 SQL ordered by Version Count:

Top SQL that records open child cursors for SQL.

4.9 SQL ordered by Cluster Wait time:

Top SQL that records the waiting time for a cluster

Abalone New ********************

Dba_oracle AWR Report Performance monitoring reports (case) (HTML report monitoring database performance)