DBA_Oracle AWR Report Performance Monitoring Report (CASE) (HTML Report monitors database performance), dba_oracleawr

DBA_Oracle AWR Report Performance Monitoring Report (CASE) (HTML Report monitors database performance), dba_oracleawr

2014-08-22

I. Summary

Oracle Database 10 Gb provides a significantly improved tool: Automatic Workload information library (AWR: Automatic Workload Repository ).

Oracle recommends that you replace Statspack with this.

 

1. AWR is essentially an Oracle built-in tool that collects performance-related statistics and exports performance measurements from those statistics to track potential problems.

A snapshot is a new background process called MMON and automatically collects data from the process.

$ Ps-ef | grep mmon

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

 

2. In 10 Gb, a new view v $ session_wait_history stores the last 10 waiting events of each active session in v $ session_wait.

However, the monitoring of data performance over a period of time is far from enough. To solve this problem, a new view is added to 10 GB: v $ active_session_history, Which is ASH, ASH collects the active sessions in the v $ session every second by default, and records the session waiting events. non-active sessions are not sampled. The interval is determined by the _ ash_sampling_interval parameter, because it takes a lot of time and space to record the activity information of a session, ASH adopts the policy of saving the information of the Active session in the waiting state, sampling from v $ session_wait every second, and save the sampling information in the memory (the ASH sample data is saved in the memory)

 

3. ASH sampling

The sample data of ASH is stored in the memory, while the memory space allocated to ASH is limited. When the allocated space is full, the old records will be overwritten; after the database is restarted, all the ASH information will disappear.

In this way, it is impossible to check the performance of oracle for a long time. In Oracle10g, a method is provided to retain the ASH information permanently. This is AWR, since it takes a lot of time and space to save all the information in ASH,

The AWR adopts the following policy: the MMON process samples the ASH every hour and saves the information to the disk. If the memory is insufficient and the ash buffer is full, the MMNL process will take the initiative to write the data, the record is retained for 7 days. The old record will be overwritten after 7 days. The sample information is stored in the table.

SELECT pool, name, bytes/1024/1024 FROM v$sgastat where name like '%ASH %';

 

 

In fact, the information recorded by AWR is not only ASH, but also statistical information and wait information of all aspects of database operation for diagnosis and analysis. The AWR sampling method is to sample all important statistics and load information at a fixed interval and save the sampling information in AWR. It can be said that the information in ASH is saved to the view wrh $ _ active_session_history in AWR. ASH is the real subset of AWR.

 

AWR uses several tables to store collected Performance Statistics. All Tables are stored in the SYS mode in the SYSAUX tablespace and run WRM $ _ * (five) and WRH $ _ * (94) format names.

WRM $ _ * this type stores metadata information (such as checked databases and collected snapshots) and WRH $ _ * stores actual collected statistics.

H Represents "historical", and M represents "metadata )".

Several views with DBA_HIST _ prefix (dba_hist_snapshot, dba_hist_baseline, etc.) are built on these tables. These views can be used to compile your own performance diagnostic tools.

 

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

 

4. generate an awr report ($ ORACLE_HOME/rdbms/admin /):

Awrrpt. SQL: generate a statistical report for the specified snapshot range;

Awrrpti. SQL: generate statistical reports for specified database instances and specified snapshot ranges;

Awrsqlrpt. SQL: generate a statistical report for a specified snapshot interval and a specified SQL statement (the SQLID of the statement is actually specified;

Awrsqrpi. SQL: generate statistical reports of specified SQL statements in the specified database instance and snapshot interval;

Awrddrpt. SQL: Specifies two different time periods to generate statistical comparison reports for these two periods;

 

Ii. AWR operations

2.1. view the current AWR Storage Policy

SQL> select * from dba_hist_wr_control;

 

3.2. Adjust AWR Configuration

AWR configurations are all configured through the dbms_workload_repository package.

 

2.2.1 adjust the frequency and retention policy of snapshot generation by AWR. For example, change the collection interval to 30 minutes. And keep the time for 5 days (in minutes ):

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

 

2.2.2 disable AWR and set interval to 0 to disable automatic snapshot capturing

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

 

2.2.3 manually create a snapshot

SQL> exec dbms_workload_repository.create_snapshot ();

 

2.2.4 viewing snapshots

SQL> select * from sys. wrh $ _ active_session_history

 

2.2.5 manually delete snapshots 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 a baseline and save the 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 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 migrate AWR data files to other databases

SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD (SCHNAME => 'awr _ test', dmpfile => 'awr _ data. dmp ', dmpdir => 'dir _ BDUMP ');

Transfer AWR data to SYS mode:

SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'test ');

 

Iii. Case-create and export snapshot Information

Case: manually create two snapshots and import snapshots between the two nodes

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

AWR configurations are all configured through the dbms_workload_repository package. For example, you can change the collection interval to 30 minutes and retain the time for 31 days (in minutes ):

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

 

2. Disable AWR:

Set interval to 0 to disable automatic snapshot capturing:

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

 

3. manually create a snapshot:

SQL> exec dbms_workload_repository.create_snapshot ();

If some database parameters or applications are adjusted, You can manually create a snapshot, which is more conducive to statistical analysis of data.

 

4. view snapshots:

SQL> select * from sys. wrh $ _ active_session_history;

 

5. manually delete snapshots of a specified range

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

 

6. generate an awr report ($ ORACLE_HOME/rdbms/admin ):

Awrrpt. SQL: generate a statistical report for the specified snapshot range;

Awrrpti. SQL: generate statistical reports for specified database instances and specified snapshot ranges;

Awrsqlrpt. SQL: generate a statistical report for a specified snapshot interval and a specified SQL statement (the SQLID of the statement is actually specified;

Awrsqrpi. SQL: generate statistical reports of specified SQL statements in the specified database instance and snapshot interval;

Awrddrpt. SQL: Specifies two different time periods to generate statistical comparison reports for these two periods;

 

Iv. AWR Report Analysis

Case:

4.1 SQL ordered by Elapsed Time

Top SQL statements that record the total execution Time (note that the total execution Time of the SQL statement within the monitoring range, rather than the execution Time of a single SQL statement Elapsed Time = CPU Time + Wait Time ).

 

Elapsed Time (S): the total duration of SQL statement execution. This sorting is performed according to this field. Note that this time is not the time for running a single SQL statement, but the total time of the number of SQL statements executed within the monitoring range. Unit: seconds. Elapsed Time = CPU Time + Wait Time

CPU Time (s): The total CPU usage Time during SQL statement execution. This Time is less than or equal to the Elapsed Time. Unit: seconds.

Executions: Total number of SQL statement Executions within the monitoring range.

Elap per Exec (s): average time for executing an SQL statement. Unit: seconds.

% Total DB Time: the percentage of Elapsed Time of SQL to the Total Time of the database.

SQL id: ID of the SQL statement. Click it to navigate to the SQL detail column below.TableClick IE to return to the current SQL ID.

SQL Module: shows how the SQL statement is connected to the database for execution. If the SQL * Plus or PL/SQL link is used, someone is debugging the program. Generally, the SQL statement that is linked to the foreground application is empty.

SQL Text: A simple SQL prompt. For details, click the SQL ID.

 

4.2 SQL ordered by CPU Time

The top SQL statements with the longest execution time as the sum of CPU time are recorded (note that the execution time of this SQL statement is the sum of CPU time in the monitoring range, rather than the execution time of a single SQL statement ).

 

 

4.3 SQL ordered by Gets

The top SQL statements whose execution accounts for the total buffer gets (logical IO) are recorded (note that the execution of this SQL statement accounts for the total Gets within the monitoring scope, rather than the Gets occupied by a single SQL Execution ).

 

 

4.4 SQL ordered by Reads:

The top SQL statements that occupy the total Disk Physical reads (physical IO) are recorded (note that the execution of this SQL statement accounts for the total Disk Physical reads within the monitoring scope, instead of the Disk Physical reads occupied by a single SQL Execution ).

 

 

4.5 SQL ordered by Executions:

The top SQL statements sorted by the number of SQL executions are recorded. This sorting shows the number of SQL executions within the monitoring range.

 

 

4.6 SQL ordered by Parse cballs:

Top SQL that records the number of soft resolutions of SQL statements. When it comes to soft parsing and hard parsingOracleSQL processing.

 

 

4.7 SQL ordered by Sharable Memory:

Records top SQL statements that occupy the size of library cache. Sharable Mem (B): the size of the library cache in bytes.

 

4.8 SQL ordered by Version Count:

Records top SQL statements for opening sub-game targets.

 

4.9 SQL ordered by Cluster Wait Time:

Top SQL statements that record the cluster wait time

********************Author: Bao Xin********************

Oracle Database Management Training

Oracle Database Management (oracle DBA for short). Most oracle training providers have oracle DBA training. CUUG, which specializes in oracle Database management training, is very famous.

How Does ORACLE query table usage?

The simplest method is awr report.
SQL> @ admin \ awrrpt. SQL
Enter the value of report_type: <optional html or text. The default value of enter is html.>
Enter the value of num_days: 1
Value of begin_snap: 4965
Input end_snap value: 4966
Enter the value of report_name: <enter the name of the file to be saved, and enter uses the default file name>
Report written to awrrpt_4965_4966.html
SQL> exit
After the report is generated, you can see it.