Shulou(Shulou.com)06/01 Report--

AWR in Oracle, full name is Automatic Workload Repository, automatic load information base.

AWR is one of the important tools for DBA to understand its running status. According to the AWR report, you can understand the overall performance of oracle database and optimize it. This article mainly introduces the relevant parts of AWR.

DB Name DB Id Instance Inst Num Startup Time Release RAC

-

1 16-Jan-17 09:27 11.2.0.4.0 NO

Host Name Platform CPUs Cores Sockets Memory (GB)

Linux x86 64-bit 8 8 2 7.81

Snap Id Snap Time Sessions Curs/Sess

--

Begin Snap: 10848 14-Mar-17 09:00:51 66 1.4

End Snap: 10849 14-Mar-17 10:00:55 66 1.5

Elapsed: 60.07 (mins)

DB Time: 0.93 (mins)

Sessions

When collecting performance information, the number of sessions linked to the oracle instance is helpful to determine the class of DB.

Cursors/Session

Average number of cursors opened per session

Elapsed

Actual usage time of DB

DB Time

The time spent on database operations, including CPU and Wait Event time,DB Time, the higher the database, the higher the database load.

The busy degree of the database is judged by the DB Time/Elapsed ratio. The higher the ratio is, the busier the database is.

DB Time = CPU time + Wait time (excluding background processes and idle waits)

Corresponding to the elapsed_time in V$SESSION

Load Profile Per Second Per Transaction Per Exec Per Call

~-

DB Time (s): 0.0 0.00 0.00

DB CPU (s): 0.0 0.00 0.00

Redo size (bytes): 1343.6 3388.8

Logical read (blocks): 394.1 993.9

Block changes: 5.4 13.6

Physical read (blocks): 0.4 1.1

Physical write (blocks): 0.6 1.4

Read IO requests: 0.4 1.1

Write IO requests: 0.4 1.1

Read IO (MB): 0.0

Write IO (MB): 0.0

User calls: 64.8 163.4

Parses (SQL): 21.052.9

Hard parses (SQL): 0.0 0.1

SQL Work Area (MB): 0.2 0.5

Logons: 0.1 0.2

Executes (SQL): 22.2 55.9

Rollbacks: 0.0 0.0

Transactions: 0.4

DB Time DB CPU

DB Time 3.3s DB CPU 1.4s Wait Event 3.3-1.4mm 1.9s, the proportion of DB CPU in DB Time is 1.4pm 3.3mm 42%

It can be seen that the proportion of non-CPU waiting in this DB system is relatively large.

DB CPU accounts for 42.55%

Db file sequential read/db file scattered read//libary cache:mutex X/latch:shared pool is the TOP 4 wait event that CPU is waiting for

(DB Time > DB CPU + FG Wait event DB Time will calculate the queue time waiting for CPU when CPU is busy)

Continue to analyze

Number of redo size logs generated

Logical reads logical read in blocks

A good OLTP logical reads/ Executes is around 50.

Block Changes

Data blocks that change per second, transaction

Physical reads

Physical reading

User Calls

The number of user calls per second (per transaction). User calls/Executes basically represents the number of requests per statement. The closer Executes is to User calls, the better.

Pasre

Parsing times, excluding fast soft parsing (MOS says that a SQL statement executed three times will cache the cursor to PGA, which is always open, and when the same SQL is executed again, all the parsing processes will be skipped to fetch the execution plan directly.)

Too much soft parsing means that the application is not efficient.

Hard Parse

The number of hard parsing. This indicator is too high to indicate that the bound variable is not well done.

Sorts

Sorting times

W/A MB processed

The amount of data processed in unit MB Wax A workarea workarea is viewed together with In-memory Sort%, sorts (disk) PGA Aggr.

Logon

Number of times to log in to the database

Executes

Number of execution

Rollbacks

Number of rollbacks

Transactions

Number of transactions

Instance Efficiency Percentages (Target 100%)

~

Buffer Nowait%: 100.00 Redo NoWait%: 100.00

Buffer Hit%: 100.00 In-memory Sort%: 100.00

Library Hit%: 99.30 Soft Parse%: 99.79

Execute to Parse%: 5.27 Latch Hit%: 100.00

Parse CPU to Parse Elapsd%: 78.31% Non-Parse CPU: 94.25

This module records the usage information of oracle instance memory, with a target of 100%. For OLTP systems, this module information is more important, but not meaningful for OLAP systems.

Buffer Nowait%

Percentage of data blocks obtained by non-waiting mode

This value is too small, indicating that the data block is being read into memory by another session when SQL access occurs, and you need to wait for this operation to complete. This usually happens when some blocks of data become hot blocks.

Buffer Nowait1: 98.06 97.25

% Memory for SQL w/exec > 1: 92.56 92.46

Memory Usage%

Shared pool memory usage.

It should be stable between 70% and 90%. If it is too small, it will waste memory, and if it is too large, it will be insufficient.

SQL with executions > 1

SQL ratio with more than 1 execution times.

If it is too small, it may be that binding variables are not used.

Memory for SQL w/exec > 1

SQL with more than 1 execution consumes memory / memory consumed by all SQL (that is, memory for sql with execution > 1).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.