Oracle AWR report analysis and OracleAWR Report Analysis

Oracle AWR report analysis and OracleAWR Report Analysis

* Definition: awr report is a performance collection and analysis tool provided by oracle at 10 Gb. It provides a report on the usage of system resources over a period of time. Through this report, we can understand the overall operation of a system, just like a comprehensive health check report.

How to analyze:

* When reading the awr report, we do not need to know the meaning of all the performance indicators to determine where the problem is located. These performance indicators actually represent the internal implementation of oracle, the deeper understanding of oracle, the more accurate the judgment on the database performance when reading the awr report

* When looking at performance indicators, you should first understand that there are performance problems in the database, generally in three places, I/O, memory, cpu, and these three are closely related (ps: let's assume there are no physical faults in these three locations.) When I/O load increases, more memory is required for storage, at the same time, the cpu needs to spend more time to filter the data. On the contrary, if the cpu time is too much, it may be to parse SQL statements or filter too much data, it may not be related to io or memory.

* When we send an SQL statement to the database for execution, we need to know when cpu, memory, and io are used.

1. cpu: parses SQL statements, tries multiple execution plans, and finally generates a database, which is considered to be a better execution plan, not necessarily the best, because when there are too many associated tables, the database does not exhaust all execution plans, which consumes too much time. How Does oracle know that this data is required by the cpu?
2. memory: Both SQL statements and execution plans need to be retained in the memory for a period of time, and the obtained data will be retained in the memory according to the lru algorithm. During the execution of SQL statements, connections and sorting among various tables also occupy memory.
3. io: if the required data is not in the memory, it needs to be retrieved from the disk and physical io will be used. There are too many data connections between tables, when the operating memory such as sorting cannot be stored, temporary tablespace is also required, and physical io is used.

Although oracle occupies 8 GB of memory, pga generally only accounts for 20% of 8 GB. In dedicated server mode, each SQL statement and table data operation are executed, all are performed in pga, that is, only about GB of memory can be used. If multiple users execute
Multi-Table Association, and a large amount of table data, coupled with improper association, memory becomes a bottleneck. all important aspects of SQL optimization are to reduce logical read and physical read.

How to generate an awr report:

* 1: log on to the corresponding database server
2: Find the oracle disk space (d: oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ Admin)
3: Run cmd-cd d: Enter
4: cd d: oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ Admin press ENTER
5: sqlplus username/password @ service connection name (for example, sqlplus carmot_esz_1/carmot @ igrp)
6: Run @ awrrpt. SQL and press Enter.

Step 1 input type: html
Step 2 enter the number of days: the number of days (for example, 1 represents the day, if 2 represents today and yesterday ...)
Step 3 enter the start value and end value: (you can see the data listed above, snap value)
Start, end
Step 4 enter the name of the exported table: Enter the custom name and press ENTER
Step 5: The program automatically completes the import.

Sixth: Go to the d: oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ Admin directory. Find the generated file. XXXX. LST file

Specific analysis process:

* Before analyzing the awr report, you must first determine whether our system belongs to oltp or olap (when the database is installed, there will be an option when selecting oltp, or olap)
For different systems, the focus of performance indicators is different. For example, library hit and buffer hit can almost ignore these two performance indicators in olap systems. In oltp systems, these two indicators are crucial.

* First, we need to check the time.
Elapsed: 240.00 (mins) indicates that the sampling time is 240 minutes. Any data is measured by this time. Without this sampling time, there is no doubt about any data.
DB Time: 92,537.95 (mins) indicates the Time spent by the user, including cpu Time consumption and waiting Time. Some may wonder why, during the 240-minute sampling process, the user's operation time is actually 92537 minutes, far more
The sampling time is because the awr report is a collection of data. For example, if a user waits for 30 seconds within one minute, then 10 users wait for 300 seconds. For cpu usage, A cpu processes 30 seconds, and 16 CPUs are 4800 seconds. These times are recorded in the awr report in a cumulative manner.

Let's look at sessions. We can see that there are many connections.

* To have a general understanding of the database, first look at the following performance indicators

 

1. Buffer Nowait indicates that when retrieving data from the memory, there is no waiting rate. The expected value is 100%.
2. buffer Hit indicates the buffer Hit rate when retrieving data from the memory. The expected value is 100%, but 100% does not mean performance. This is only a ratio. For example, execute an SQL statement. # The Execution Plan requires 10000 data blocks. As a result, the memory actually contains these 10000 data blocks. The ratio is 100%, which is the highest performance on the surface, another execution plan requires 500 data blocks, with 250 in memory, and the other 250 must be retrieved from the physical disk,
In this case, buffer hit is 50%. As a result, the performance of the second execution plan is the highest, so 100% does not represent the best performance.
3. Library Hit indicates the SQL Hit rate in the Shared Pool. The expected value is 100%.
4. execute to Parse indicates the ratio between SQL parsing and SQL Execution. The higher the ratio, the better. It indicates that a resolution is performed at a time and executed everywhere. If there are more parse and fewer execute, a negative number will appear, because the formula is 100 * (1-parse/execute)
5. parse CPU to Parse Elapsd indicates the percentage of cpu to the total parsing time during SQL statement parsing. The expected value is 100%, indicating that no waiting is generated, even if there is hard resolution, as long as the cpu does not have performance problems, it can also be tolerated. Hard resolution also has its advantages.
6. Redo NoWait indicates that no wait is generated when logs are generated. The expected value is 100%.
7. Soft Parse indicates the proportion of Soft resolution, with an expected value of 100%. One thing to note is that you should not unilaterally pursue the high proportion of Soft resolution, but bind variables, depending on the performance bottleneck.
8. latch Hit indicates latch's Hit rate. The expected value is 100%. latch is similar to a lock. It is a memory lock, but it only produces waiting and no blocking. It is different from lock, latch is generated in the case of concurrency.
9. non-Parse CPU indicates the proportion of the Non-resolution cpu. The higher the cpu, the better. Use 100 minus this proportion. We can see that the CPU consumed for parsing SQL is 100-99.30 = 0.7, it indicates that the cpu used for SQL Parsing is very small.

* Combined with Time Model Statistics

 


It can be seen that during the SQL Execution time (SQL execute elapsed time) of 5552019 seconds, the parsing time (parse time elapsed) takes 36 seconds, the hard parse elapsed time takes 34 seconds. Although the hard parsing time accounts for the majority of the entire parsing time, the parsing time is very short, SQL parsing has not become a bottleneck in performance. We further speculate that SQL encountered a bottleneck during data acquisition.

* Continue to check the Top 5 Timed Events. here we can see what the Top five Events are waiting for. Basically, we can determine where the performance bottleneck is.

 

1. buffer busy waits indicates that in the process of obtaining data, frequent wait events are generated, and hotspot blocks may be generated. That is to say, many sessions read the same data blocks, this event waited 5627394 times, with a total wait of 5322924 seconds. The average wait time was 946 milliseconds, and the highest frequency was 95.9%. The waiting category was concurrency.
Here is a concept: the minimum unit of oracle operations is block. When a session needs to modify a record in this block, it reads the entire block, if the data to be modified by another session is in this block
2. If the session modification record is different, a temporary Tablespace will be used for waiting for direct path write temp and direct path read temp. Let's take a look at Tablespace IO Stats.

 

Each metric is very high. Based on the above In-memory Sort is 100%, no disk sorting is generated, so temporary tablespace is not used during sorting. Further speculation is that multiple sessions, multiple tables are associated in the SQL statement executed by each session, resulting in a lot of intermediate data, which cannot be stored in the pga memory,
The temporary tablespace is used, or the lob field is used. When the lob field is used, the temporary table is also used.

* Continue viewing SQL Statistics
Based on the buffer busy waits wait times, time, and frequency, we focus on the logic read, physical read, and SQL statements with the longest execution time, and optimize the top few
The optimization principle is to reduce the number of sub-operations in physical reads, logical reads, and SQL statements as few as possible. The execution plan estimated by oracle does not show the number of sub-operations, depends on the execution plan at run time

* If you are interested, take a look at Segment Statistics.
Used indexes and table usage are listed. The usage frequency of indexes and tables can also be seen here.

* You can also take a look at the Load Profile
It lists the logs, logical reads, physical reads, and other indicators generated by each firm per second.