Oracle's AWR report analysis

* Definition: The AWR report is a performance collection and analysis tool provided under Oracle 10g that provides a report of the overall system resource usage over a period of time, and through this report, we can understand the whole operation of a system, which is like a person's comprehensive medical report.

How to analyze:

* When looking at the AWR report, we do not need to know the meaning of all the performance indicators, we can determine the problem, these performance indicators represent the internal implementation of Oracle, the deeper the understanding of Oracle, when looking at the AWR report, the performance of the database will be more accurate judgment

* When looking at performance indicators, the mind must first understand that the database performance problems, generally in three places, IO, memory, CPU, these three are closely related (PS: We first assume that this three is not a physical fault), when the IO load increases, it will certainly need more memory to store, It also requires the CPU to spend more time filtering the data, whereas CPU time is much more likely to be parsing SQL statements or filtering too much data, not necessarily with IO or internal relationships.

* When we send a SQL to the database to execute, we need to know when to use the CPU, when to use the memory, when to use the IO

1. CPU: Parse SQL statements, try multiple execution plans, and finally generate a database that is considered a good execution plan, not necessarily optimal, because the association table too many times, the database does not exhaustive all the execution plan, this will consume too much time, Oracle how to know this data when you want, The other is not what you want, it's a CPU to filter.
2. Memory: SQL statements and execution plans need to be kept in memory for a period of time, as well as the data taken, according to the LRU algorithm will also be kept in memory, in the execution of SQL statements, the connection between various tables, sorting and other operations will also occupy memory
3. IO: If the required data is not in memory, you need to go to the disk to fetch, you will use the physical IO, there are too many connection data between tables, and sorting and other operations memory can not be placed, also need to use the temporary table space, also use the physical IO

One thing to note is that while Oracle occupies 8G of memory, the PGA generally accounts for only 8G of 20%, for the dedicated server model, each execution of SQL statements, the operation of table data, etc., is carried out in the PGA, that is, only about 1.6G of memory, if more than one user to execute
Multi-Table association, and more table data, coupled with inappropriate, memory becomes a bottleneck, all of the important point of optimizing SQL is to reduce the logical read and physical reading

How to generate an AWR report:

* 1: Log in to the corresponding database server
2: Find Oracle Disk space (D:oracle\product\10.2.0\db_1\rdbms\admin)
3: Execute CMD-CD D: Enter
4:CD D:oracle\product\10.2.0\db_1\rdbms\admin Enter
5:sqlplus User name/password @ Service Connection Name (example: Sqlplus carmot_esz_1/[email protected])
6: Perform @awrrpt.sql carriage return

First step input type: HTML
Enter the number of days in the second step: the number of days to customize (such as 1, representing the day, if 2, representing today and yesterday ...) ）
The third step is to enter the start value and the end value: (You can see the data listed above, snap value)
This value is entered to start, with the end
Step Fourth Enter the name of the export table: Name Custom carriage return
The fifth step is automatically completed by the program.

Sixth: Under the D:oracle\product\10.2.0\db_1\rdbms\admin directory. Locate the file that you just generated. Xxxx. LST file

Specific Analysis process:

* Before analyzing the AWR report, first determine whether our system belongs to OLTP or OLAP (when the database is installed, there will be an option to select OLTP or OLAP).
For different systems, the focus of performance indicators is not the same, for example, the library hit and buffer hit, in the OLAP system can almost ignore these two performance indicators, and in the OLTP system, these two indicators are very critical

* First we have to look at two times
elapsed:240.00 (mins) indicates that the sampling time is 240 minutes, and any data is measured by this time, leaving the sampling time, any data is no doubt
DB time:92,537.95 (mins) indicates that the user is spending time, including CPU time to drink wait time, perhaps some people will find it strange, why in the sampling of the 240-minute process, the user operation time has 92,537 minutes, far more than
Sampling time, because the AWR report is a collection of data, such as in a minute, a user waits 30 seconds, then 10 users wait 300 seconds, for the CPU, a CPU processing 30 seconds, 16 CPU is 4,800 seconds, These times are recorded in the AWR report in a cumulative manner.

Look at sessions, you can see a lot of connections.

* In order to have a general understanding of the database, first look at the following performance indicators

 

1. Buffer Nowait shows that when data is accessed from within, there is no waiting ratio, and the expected value is 100%
2. Buffer hit indicates that the ratio of hits to buffer is 100%, but 100% does not represent performance, because this is just a scale, for example, execute an SQL statement, # Execution plan is to take 10,000 blocks of data, The result is that there are 10,000 data blocks in memory, then the scale is 100%, the surface is the highest performance, there is an execution plan that requires 500 data blocks, 250 in memory, and 250 in the physical disk,
in this case, the buffer hit is 50%, As a result, the second execution plan performance is the highest, so saying 100% does not represent the best performance
3. The Library hit describes the SQL hits in the shared pool, with the expected value of 100%
4. The Execute to Parse illustrates the ratio between parsing SQL and executing SQL, and the higher the better, the better the parsing, the execution everywhere, and the negative number if the parse is more, and execute is less, because the formula is 100* (1-parse/execute)
5. Parse CPU to parse ELAPSD description in the process of parsing SQL statements, the CPU accounted for the entire resolution time ratio, the expected value is 100%, stating that there is no waiting, it is necessary to note that even if there is a hard parsing, as long as the CPU does not have performance problems, it can be tolerated, The comparison of hard parsing also has its benefits for
6. Redo NoWait indicates that there is no wait when generating the log, and the expected value is 100%
7. Soft parse illustrates the ratio of soft parsing, the expected value is 100%, one thing to explain is, do not unilaterally pursue the high proportion of soft parsing, and to bind variables, to see the performance of the bottleneck where
8. Latch hit shows the Latch, the expected value is 100%,latch similar to the lock, is a memory lock, but only to produce a wait, no blocking, and lock or a difference, Latch is in the case of concurrency generated
9. Non-parse CPU describes the ratio of non-analytic CPUs, the higher the better, with 100 minus this ratio, you can see the cpu,100-99.30=0.7 spent parsing SQL, the CPU spent on parsing SQL is very few

* Combined with the time Model Statistics

 


You can see that the parse time elapsed 36 seconds for the entire SQL execution time (SQL execute elapsed time) in 5,552,019 seconds. Hard parse elapsed time takes 34 seconds while the hard parse time takes up the majority of the parsing time, the parsing time is rarely spent, so it can be judged that SQL parsing does not become a bottleneck in performance, and further speculation SQL encountered a bottle   neck in the process of getting the data

* Continue to look at top 5 Timed events, from here you can see what is waiting time in the top five, basically can determine where the performance bottlenecks in the place

1. Buffer busy waits shows that in the process of acquiring data, there is a frequent waiting event, it is possible to generate a hotspot block, that is, many sessions to read the same data block, this event has been waiting 5,627,394 times, a total of 5,322,924 seconds, The average wait time is 946 milliseconds, and the frequency is the highest, with 95.9%, waiting category is concurrency
Here's a concept: the smallest unit of an oracle operation is a block, and when a session modifies a record in the block, the entire block is read, and if the data that the other session is modifying is exactly in this block, though the two
2. The record of the session modification is different, and the wait for direct path write temp and direct path read temp description is used in the temp table space, so let's look at Tablespace IO Stats



The indicators are very high, and then according to the above In-memory sort is 100%, does not produce a disk ordering, also in the time of the sort is not used to temporary table space, further speculation, multiple sessions, each session executed SQL statement in the Multi-table association, Produces a lot of intermediate data, and the PGA does not fit in memory,
Temporary table space is used, or LOB fields may be used, and temporary tables are used when using LOB fields

* Continue to see SQL Statistics
According to the buffer busy waits waits, time, frequency are the highest, we focus on logical reading, physical reading, and the longest execution of SQL, the first few to take out the optimization
The principle of optimization is to reduce physical reads, logical reads, and the number of sub-operations performed in SQL statements as little as possible, in view of Oracle's estimated execution plan is not to see the number of executions of child operations, depends on the runtime execution plan

* If you are interested, you can also look at segment Statistics
Lists the indexes used and the usage of the table, as well as how often indexes and tables are used

* You can also check the load profile
It lists every second, the log generated by each transaction, logical reading and physical reading indicators

Oracle's AWR report analysis