Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to interpret the ORACLE AWR performance report and ASH performance report, which may not be well understood by many people. In order to make you understand better, the editor summarizes the following contents for you. I hope you can get something from this article.

Database performance analysis can be divided into session level and system level: if you determine that there is a performance problem in a session, the most common way of analysis is to do a SQL_TRACE or 10046 event on the session, and locate the problem by analyzing the trace file. If you can't determine which session has a performance problem, you need to analyze the problem at the instance level.

Awr is a performance collection and analysis tool provided under oracle 10g, which can provide a report on the overall system resource usage over a period of time.

Awr collects the collection information of the last 7 days by default, and the snapshot collection interval information can also be modified by the following methods.

Awr is automatically and periodically collected and saved by the background process running on oracle. Every hour, awr generates a snapshot of performance data to provide DBA with data information for database performance analysis at a certain time.

Execute $ORACLE_HOME/RDBMS/ADMIN/awrrpt.sql to generate the awr report.

The awr report should be analyzed according to the actual situation of the system (OLAP or OLTP). For example, for an OLTP system, library hit and buffer hit should be paid more attention. It is not very important to OLAP.

The awr report does not require full reading, which may be more confusing, and if the performance problem is caused by some reason, it will be present in all parts of the report.

To do performance analysis in a database with RAC structure, it is usually necessary to make an awr performance report for each instance because it is impossible to know which instance each user is connected to.

For a system, you need to do several more awr reports in order to get system performance data for all time periods. When viewing awr reports, if you understand the database business, you should be targeted.

Look at some parts that may have performance problems, and judge according to the actual situation of the business; it can also be triggered from the waiting event of TOP5, according to the type of waiting event

Go to the corresponding part to get detailed information to judge the system performance problems.

Through the awr report, you can: 1) view the load and busy level of the system; 2) view the bottlenecks of the system and wait events that occur; and 3) view the sql that can be optimized.

1. Report Summary:

1. SESSIONS: the number of sessions connected to the instance and the approximate number of concurrent users in the database

2. Cursors/session: the average number of cursors opened per session

3. DB time: a collection of time spent on user operations, including CPU time and wait events

4. Cache sizes: enumerate the data buffer pool and shared pool size of awr at the beginning and end of performance collection to understand the changes of system memory consumption and judge whether the memory allocation of SGA is reasonable.

5. Load profile: a detailed list of database resource loads, which is used to judge the busy degree of the system. Split into resource load per second and resource load per transaction.

6. Instance Efficiency Percentages: memory efficiency statistics should be as close to 100% as possible for OLTP systems. If which data is on the low side, it is necessary to do relevant analysis and research.

1) buffer nowait: get the database in a non-waiting way

2) redo nowait: obtain redo data in a non-waiting way

3) buffer hit: memory block hit ratio

4) in-memory sort: the percentage of blocks sorted in memory

5) library hit: the hit rate of sql resolution in the shared pool

6) execute to parse: the percentage of times performed to the number of analyses

7) percentage of latch Hit:latch hits

8) parse cpu to parse elapsed: the percentage of time consumed by CPU in the total parsing time

9) percentage of non-parse cpu:cpu non-analysis time in total cpu time

7. TOP 5 TIMED EVENTS: check the top 5 elapsed time and wait events, and contact the collection cycle of the report to see whether the time spent is reasonable. In general, CPU time appears first in TOP5 and takes up a large proportion of the total time. It can show that the system is running normally.

For common waiting events in ORACLE, please refer to http://blog.itpub.net/29371470/viewspace-1063994/.

The above part is the overall summary of the awr report, which needs to be paid attention to. According to this information, we can know the events with a long waiting time, and then according to these events, go to the specific section below to find the cause of the problem.

2. Wait Events Statistics:

1. Time Model Statistics lists the percentage of database time occupied by various operations.

2. Foreground Wait Class lists the types of waiting events, and you can see the events with the longest waiting time.

3. Foreground Wait Events is the detailed part of the first part TOP 5 TIMED EVENTS

4. The background process of the Background Wait Events instance waits for the event

3. SQL Statistics:

1. SQL ordered by Elapsed Time: sort by the execution time of sql from long to short

1) CPU time consumed by CPU time:sql

2) elapsed time:sql execution time

3) the number of times executions:sql is executed

4) elapsed per exec: the execution time consumed for each execution

2. SQL ordered by CPU Time: sort by the CPU time of sql from long to short:

3 、 SQL ordered by User I/O Wait Time:

4. SQL ordered by Gets: sort by the number of memory blocks obtained by sql:

5. SQL ordered by Reads performs physical read sorting according to sql:

6. SQL ordered by Physical Reads (UnOptimized):

7. SQL ordered by Executions: sort by the number of times sql is executed

8. SQL ordered by Parse Calls: sort by the number of times sql is parsed (regardless of whether soft parsing or hard parsing)

9. SQL ordered by Version Count:sql generates multiple versions of information; version count is the number of versions of sql

The above indicators are of no practical significance in isolation. We need to focus on the analysis of the type and performance of the system. For example, SQL ordered by Executions and SQL ordered by Parse Calls are more important to OLTP, while OLAP systems don't need to pay much attention.

4. Instance Activity Statistics:

The unit of cpu consumed by cpu used by this session:oracle, you can see the load of cpu; if total is 1000, per sec is 80, the number of CPU is 2

So the whole statistical cycle consumes 1000 cpu units, 80 cpu units per second, corresponding to the actual time of 80 cpu units per second, 80 cpu units per second, 20 cpu units per cpu per second, and 40 cpu units per cpu processing per second.

5. IO Stats:

Tablespace IO Stats: IO performance statistics for tablespaces

File IO Stats:

1) reads: how many physical reads have occurred

2) writes: how many writes have occurred

3) Av reads: the number of physical reads per second

4) Av Rd: average time of one physical read

5) Blks/Rd: how many blocks are read at a time

6) Av writes: number of writes per second

7) buffer waits: the number of times to get memory blocks to wait

Segments by logical reads and segment by physical reads show the situation of IO from an object point of view. By analyzing these two parts of information, we can know which object access leads to the degradation of IO performance.

ASH focuses on the information analysis of active sessions in the current data, which is stored in the data dictionary for a long time and can be obtained by querying the view V$ACTIVE_SESSION_HISTROY.

Run the script as $ORACLE_HOME/RDBMS/ADMIN/ashrpt.sql

Using ashrpti.sql scripts in the same directory, you can generate ASH performance reports for other databases or instances, or wait events for a session ID, SQL_ID, program, or class

To generate the ASH report, as shown below:

The ASH report is analyzed as follows:

If DATA SOURCE comes from DBA_HIST_ACTIVE_SESS_HISTORY, then the information comes from a snapshot of AWR; if it comes from V$ACTIVE_SESSION_HISTORY, then

The information of the view means that the performance data is stored in memory.

1. Top user events: information about the waiting event of the user's session

2. Top event p1/p2/p3 values: a specific description of the waiting event

3. Top service/module: list the top five applications by frequency of activity

4. Top sql command types: lists the most active operations in the database

5. Top sql statements: list sql statements by frequency of activity

6. Top session: list the most active sessions or processes

7. Top sessions running PQs: lists the session information of the first few frequently active bits executed in parallel

8. Top DB files:IO 's most frequent data files

9. Activity over time: after grouping the collection time period according to one time interval, the waiting event information in each time interval is generated.

After reading the above, do you have any further understanding of how to interpret the ORACLE AWR performance report and the ASH performance report? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.