AWR report generation and simple analysis methods

AWR report generation and simple 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

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report