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

[in-depth article] step by step interpretation of Oracle AWR performance analysis report

Abstract: the author introduces Han Feng, the database architect of CITIC Technology Research and Development Center. Proficient in a variety of relational databases, has worked in Dangdang, TOM online and other companies, has served as the chief DBA of many companies, database architect and other positions, many years of first-line database architecture, design, development experience. Author of "SQL Optimization Best practices".

Introduction to the author

Han Feng is a database architect at the Credit Technology Research and Development Center. Proficient in a variety of relational databases, has worked in Dangdang, TOM online and other companies, has served as the chief DBA of many companies, database architect and other positions, many years of first-line database architecture, design, development experience. Author of "SQL Optimization Best practices".

AWR in Oracle, full name is Automatic Workload Repository, automatic load information base. It collects operational and other statistics about a specific database, and Oracle performs a snapshot of all its important statistics and load information at regular intervals (the default is 1 hour) and stores the snapshot in AWR. This information is retained in AWR for a specified period of time (default is 1 week), and then the deletion is performed. The frequency and retention time of performing snapshots are customizable.

The introduction of AWR provides a very good convenience for us to analyze the database (MySQL is too different in this respect). There used to be an analogy: "A system is like a big dark room, and the statistics collected by the system are like candles placed in different parts of the room to illuminate the big dark room." Oracle, with just enough candles (AWR), with very little candlelight uncovered in the room, performance bottlenecks can be easily located. For systems with few or no candles, performance optimization is like a dancer in the dark. "

So how to interpret AWR's data? Oracle itself provides some reports for easy viewing and analysis. The following is an explanation for one of the most common reports, the AWR Database report. It is hoped that through this article, it will be convenient for everyone to make better use of AWR and facilitate the analysis work.

1. MAIN 1 Database Information

2 Snapshot Information

(1) Sessions

Indicates the number of sessions connected to the collection instance. This number can help us to understand the approximate number of concurrent users in the database. This number is helpful for us to determine the type of database.

(2) Cursors/session

The average number of cursors opened per session.

(3) Elapsed

Through the Elapsed/DB Time comparison, it reflects the busy degree of the database. If DB Time > > Elapsed, the database is busy.

(4) DB Time

Indicates the time taken by the user to operate, including CPU time and wait events. Usually this value interprets the load of the database at the same time.

Specific meaning

Db time = cpu time + wait time (without idle wait) (non-background process)

* db time is the time that the recorded server spends on database operations (non-background processes) and waiting (non-idle waiting). The elapsed_time field corresponding to V$SESSION accumulates.

"aggregate data"

It should be noted that AWR is a collection of data. For example, if one user waits for 30 seconds within 1 minute, then 10 users wait for an event of 300 seconds. CPU time is the same, within 1 minute, 1 CPU processing 30 seconds, then 4 CPU is 120 seconds. These times are recorded in AWR on a cumulative basis.

Example

DB CPU-- this is an important indicator used to measure the utilization of CPU. Assuming that the system has N CPU, then if the CPU is all busy, the DB CPU in one second is N seconds. In addition to using CPU for computing, the database also uses other computing resources, such as network, hard disk, memory and so on, which can also be measured by time. Assuming that there are M session running in the system, some session may be using CPU and some session may be accessing the hard disk at the same time, then the total time of all session in one second can indicate the busy degree of the system in this second. In general, the maximum value of this sum should be M. This is actually another important indicator provided by Oracle: DB time, which measures the total time consumed by the front-end process.

Oracle describes the access to computing resources after CPU as a wait event. Similarly, just as CPU can be divided into foreground consuming CPU and background consuming CPU, waiting events can also be divided into foreground waiting events and background waiting events. DB Time in general should be equal to the sum of the time spent by DB CPU + foreground waiting events. The wait time is counted through the v$system_event view, while DB Time and DB CPU are counted through the same view, that is, v$sys_time_model.

Description of DB Time in "Load Profile"

* the number of CPU of this system is 8, so we can know that the foreground process uses the 7.1 master 8 CPU 88.75% of the system CPU. The DB Time/s is 11.7, so you can see that the system is very busy with CPU. Among them, CPU accounted for 7.1, while other front-end waiting events accounted for 11.7-7.1 = 4.6 Wait Time/s. The proportion of DB Time to DB CPU: 7.1amp 11.7 = 60.68%

-- description of DB CPU in "Top 5 Timed Events"

Top 5 is listed here according to the percentage of CPU/ wait events to DB Time. If a workload is CPU busy, then you should see the shadow of DB CPU here.

* notice that we have just calculated the% DB time,60% of DB CPU. Other external table read, direct path write, PX Deq: read credit, PX Deq: Slave Session Stats these are the Top 4 in the waiting event that accounts for 40%.

Limitations of "Top 5 Timed Foreground Events"

Then take a look at this Top 5 Timed Foreground Events. If you don't look at the Load Profile first, you can't calculate the workload of a CPU-Bound. To know the busy program of the system CPU, you also need to know the time interval between the two snapshot on which the AWR is based, and the number of system CPU. Or the system can be a very IDLE system. Remember that CPU utilization = DB CPU/ (CPU_COUNT*Elapsed TIME). The only message given to us by this Top 5 is that the workload should be a parallel query, reading data from external tables and writing to disk in the form of insert append, while spending most of its time on CPU operations.

-- interpretation of "DB Time" > "DB CPU" + "time spent by foreground waiting events"-- process queue time

As mentioned above, DB Time generally should be equal to the sum of the time spent waiting for events in the DB CPU + foreground. There are statistics for these three values below:

DB CPU = 6474.65

DB TIME = 10711.2

FG Wait Time = 1182.63

Obviously, DB CPU + FG Wait Time < DB Time accounts for only 71.5%.

* where is the other 28.5% consumed? There is actually a hidden problem of how Oracle calculates DB CPU and DB Time. When CPU is busy, if there are many processes in the system, the process will queue up for CPU. In this way, DB TIME takes into account the time the process is queued for CPU, while DB CPU does not include this part of time. This is an important reason for DB CPU + FG Wait Time < DB Time. If a system CPU is not busy, the two should be closer. Don't forget that in this example, this is a very busy CPU system, and 71.5% is a signal that this system may be a CPU-Bound system.

2. Report Summary 1 Cache Sizes

This section lists the size of the data buffer pool (buffer cache) and shared pool (shared pool) of AWR at the beginning and end of performance collection. By comparing the changes before and after, we can understand the changes in memory consumption of the system.

2 Load Profile

These two parts are a detailed list of database resource loads, separated into the resource load per second and the resource load for each transaction.

Redo size

Log size per second (per transaction) (in bytes)

Logical reads

Logical reads (in block) produced per second (per transaction). In many systems, the number of select execution is much greater than the number of transaction. In this case, you can refer to Logical reads/Executes. In a good oltp environment, this should not exceed 50, usually only about 10. If this value is large, some statements need to be optimized.

Block Changes

The number of blocks changed per second (per transaction).

Physical reads

Physical reads (in block) produced per second (per transaction). General physical reading is accompanied by logical reading, unless read this way directly, without going through cache.

Physical writes

Physical writes (in block) generated per second (per transaction).

User calls

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

Parses

The number of parses (or parses) generated per second (per transaction), including soft and hard parsing, but excluding fast soft parsing. Soft parsing more than 300 times per second means that your "application" is not efficient, does not use soft soft parse, and adjusts session_cursor_cache.

Hard parses

The number of hard parses per second (per transaction). More than 100 times per second may indicate that you are not using binding well.

Sorts

The number of sorts per second (per transaction).

Logons

The number of times per second (per transaction) that logs in to the database.

Executes

The number of SQL statements executed per second (per transaction). It includes the SQL statement executed by the user and the SQL statement executed by the system, indicating the busy degree of the SQL statement of a system.

Transactions

Transactions per second. Indicates how busy a system is. By far the busiest system known is Taobao's online trading system, with a value of 1000.

% Blocks changed per Read

Represents the proportion of blocks that logical reads are used for read-only rather than modification. If there is a lot of PLSQL, then it will be higher.

Rollback per transaction%

See if the rollback rate is high, because rollback consumes a lot of resources.

Recursive Call%

The percentage of recursive calls to SQL, and the SQL executed on PL/SQL is called recursive SQL.

3 Instance Efficiency Percentages (Target 100%)

This section is the memory efficiency statistics. For OLTP systems, these values should be as close to 100% as possible. For OLAP systems, it doesn't make much sense. Because in OLAP systems, the speed of large queries is the biggest factor affecting performance.

Buffer Nowait%

The percentage of data blocks obtained in a non-wait way.

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 Nowait

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.