AWR report generation and simple analysis methods, awr Report Analysis Methods
How to generate an AWR report:
Step 1: When the database pressure test card starts: generate the first snapshot:
SQL> exec dbms_workload_repository.create_snapshot ();
Step 2: When the database stress test ends: generate the second Snapshot
SQL> exec dbms_workload_repository.create_snapshot ();
Step 3: generate an AWR report
SQL> @/oracle/product/10.2.0.5/rdbms/admin/awrrpt. SQL
(1) Select the report file type: html and text
(2) Select the number of days for the snapshot
Enter value for num_days: 1
(3) Select begin's snap and end's snap
(4) Input File Name: for example,/oracle/db_08091530.txt
.
AWR report analysis method:
Note the following:
Db cpu utilization (db time/snap_time/number of cpu cores): 10.5%
Log Buffer: 47 M
Redo size (redo size): 139 M/s
Logical read per second (logical reads): 1400
SQL parsing times (parses): 108
Sorts per second: 6
SQL statement execution per second (executes): 116
Transactions per second (transations): 96
Recursive call ratio (resursive call): 67%
Rows per sort: 51
Buffer nowait: the memory does not wait for data to be obtained: 99.33%
Buffer hit: Process Memory found Data 100%
Redo nowait: log buffer data not waiting: 100%
Library hit: Find the parsed SQL: 100%
Latch hit process obtains the memory structure license ratio: 99%
Parse cpu to parse elapsd: Percentage of pending resolution: 116%
Non-Parse CPU: Percentage of SQL statement execution time to total parsing time: 97.08%
Execute to parse: SQL Reuse Rate: 6.54%
In-memroy sort: Sorting ratio In data memory (pga) memory: 100%
Soft parse: Soft resolution proportion: 100%
Memory usage: Memory usage of the Shared Pool.
TOP 5 Timed Events Analysis
Log file sync: Too Many commits. The log buffer of Sga records changes in transactions and blocks.
Log file parallel write: also indicates the IO bottleneck
What permissions are required to generate an oracle database awr report?
The techniques for generating an AWR report () are as follows ::
1. log on to the Oracle database ::
Sqlplus/as sysdba
2. In sqlplus, run the following () command as sys to generate the first performance data snapshot ::
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level => 'all ');
3. Run all or many poorly performing applications or SQL statements you have found to keep the system under high pressure) status (such as CPU or I/O), and keep this status from half an hour to one hour.
4. In sqlplus, run the following () command again as sys to generate a second performance data snapshot ::
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level => 'all ');
5. In sqlplus, run the following () command as sys to generate a performance report ::
@? /Rdbms/admin/awrrpt. SQL
As prompted, select the two () performance data snapshots generated above, and finally generate a final () AWR performance report ..
What should I do with the awr Report of the oracle database and install oracle?
AWR needs to obtain information about the database in the Oracle running environment.
By running the awrrpt. SQL script in the $ ORACLE_HOME/rdbms/admin directory, AWR can immediately get the best description of the reports generated from the collected statistics and measurements. This script looks and feels similar to Statspack. It displays all existing AWR snapshots and requests two specific snapshots as the time interval boundary. It generates two types of output: The text format (similar to the text format of the Statspack report but from the AWR Information Library) and the default HTML format (with all the hyperlinks to the part and sub-part ), this provides very user-friendly reports. Run the script to view the report.
Manually generate the AWR snapshot Number:
Exec dbms_workload_repository.create_snapshot
Use the CRT software to log on to the server using Oracle:
Sqlplus/nolog
Conn system/system
@ $ ORACLE_HOME/rdbms/admin/awrrpt. SQL
Select generate HTML format.
Then we can see the time period and number. Select two numbers and generate an AWR report between the two numbers.