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

AWR (Automatic Workload Repository, automatic workload knowledge base) is a feature introduced by Oracle 10g. It generates statistical reports about instance and database performance and health by comparing the statistical information collected by the two snapshots (snapshot).

It is critical to select a report period that represents a performance problem. If the snapshot cycle is not within this period of time, or if the snapshot cycle span is too long and contains a lot of free time, the analysis results are meaningless.

The following is an AWR report from the actual production system, which is briefly introduced with this example.

Header

This contains the most basic information about the host instance and database. The report contains the time before and after the snapshot, the number of sessions, and the overall time consumed by the database to run. The main point is to compare time consumption:

DB Time: the database time consumed by non-system background processes, that is, the time consumed by all session connections to the database. That is, DB Time = CPU Time + Wait Time (without idle waiting) (non-background process).

Elapsed: database run time.

If DB Time is much less than Elapsed, the database is relatively free, otherwise, the database is busy and stressful.

Summary of report

Here shows the statistical information of the main indicators of the database, reflecting the overall operation of the database.

Cache Sizes

This section illustrates the size of the main memory blocks of the example.

Database cache cache size in Buffer Cache:SGA.

Shared Pool Size:SGA shares pool size, including library cache, data dictionary cache, and so on.

Std Block Size: the size of the standard block.

Log buffer size in Log Buffer:SGA.

Load Profile

This section describes the load overview of the database. If the load changes little per second or per transaction, it means that the application runs stably. A single data only shows the load of the application, and most of the data does not have a so-called correct value, which should be compared to see its change.

Redo size: log size per second / per transaction (in bytes), reflecting the frequency of data changes and whether the database task is onerous or not.

Logical reads: blocks per second / logical reads generated per transaction, Logical Reads= Consistent Gets + DB Block Gets.

Block changes: blocks modified per second / per transaction.

Physical reads: the number of blocks physically read per second / transaction.

Physical writes: the number of blocks physically written per second / per transaction.

User calls: number of user calls per second per transaction.

Parses: number of parsing of SQL statements per second / per transaction, including hard parsing and soft parsing. Soft parsing more than 300 times per second means that the application is inefficient, while hard parsing means that the SQL statement is not hit in memory.

Hard parses: the number of hard parsing. There are too many hard parsing, indicating that the reuse rate of SQL is not high. The number of hard parsing per second exceeds 100, which means that the binding variable is not used well, or the shared pool setting is unreasonable.

Sorts: the number of sorts per second / per transaction.

Logons: the number of logins per second / transaction. If the session login is greater than 1: 2 per second, it indicates that there may be a contention problem.

Executes: SQL execution per second / per transaction.

Transactions: the number of transactions generated per second, reflecting whether the database task is heavy or not.

Blocks changed per Read: the percentage of blocks that have changed in each logical read.

Recursive Call: the percentage of recursive calls, which is higher if there is a lot of PL/SQL.

Rollback per transaction: the rollback rate per transaction. If the rollback rate is too high, the database has experienced too many invalid operations, and too much rollback may lead to competition for Undo blocks.

Rows per Sort: the average number of rows sorted each time.

Instance Efficiency Percentages

This section includes the memory hit ratio of Oracle key metrics and the efficiency of other database instance operations, with a target of 100%. With the Load Profile section, this section does not have the so-called correct value, but can only judge whether it is appropriate according to the characteristics of the application.

Buffer Nowait: represents the percentage of not waiting to get data in the cache. This value generally needs to be greater than 99%. Otherwise, there may be contention, which can be further confirmed in later waiting events.

Buffer Hit: indicates the read block hit ratio of the process in the block cache, and monitoring this value for significant changes is more important than the value itself. The hit rate of data blocks in the data buffer should usually be more than 95%. Otherwise, you may need to adjust memory allocation parameters, increase memory, and so on. A high hit rate does not necessarily mean that the performance of the system is the best. Frequent visits may cause the illusion of a high hit rate.

But a relatively low hit rate will generally have an impact on the performance of the system and need to be adjusted. A sudden change in hit rate is often a bad message. If the hit ratio suddenly increases, you can check the statements that cause a large number of logical reads, and if the hit ratio suddenly decreases, you can check the statements that produce a large number of physical reads, mainly those that do not use indexes.

Llibrary Hit: represents the rate at which Oracle retrieves parsed SQL or PL/SQL statements from the repository cache. When the application calls SQL or stored procedures, Oracle checks the library cache to determine if there is a parsed version, and if so, Oracle immediately executes the statement, and if not, Oracle parses the statement and allocates a shared SQL zone to it in the library cache. This low value indicates that there is too much parsing, which increases CPU consumption and reduces performance. If it is less than 90%, you may need to expand the shared pool, consider using binding variables, and so on.

Execute to Parse: the ratio of statement execution to analysis, which can be high if the SQL reuse rate is high. The higher the value, the more times it is repeated after a parse.

Parse CPU to Parse Elapsd: parse the actual elapsed time / (parse the actual elapsed time + the waiting time for resources in parsing). If the ratio is 100%, it means that the CPU parsing process is not waiting.

Redo NoWait: indicates the percentage of not waiting to get buffer in the log buffer. If it is too low (refer to the 90% threshold), consider increasing the log buffer size. Generally, when the redo buffer setting exceeds 1m, it is unlikely to wait for buffer space allocation.

In-memory Sort: if the proportion of sorting in memory is too low (see 95% threshold), it means that more sorting needs to be done in temporary tablespaces, which will affect system performance.

Soft Parse: the proportion of soft parsing, which can be approximately regarded as the hit rate of SQL in the shared pool. If it is too low, you need to adjust the use of binding variables. The hit rate of SQL in the shared pool is less than 99%, otherwise it means that there is Latch contention. It may be due to unshared SQL, or the library cache is too small, which can be solved by binding changes or enlarging the shared pool. You can find the problem with the help of later wait events and Latch analysis.

Non-Parse CPU: actual elapsed time of SQL / (actual elapsed time of SQL + SQL parsing time). If this value is too low, it means that parsing takes too much time.

Shared Pool Statistics

This section is the statistics used by the shared pool.

Memory Usage: shared pool memory usage. For a database that has been running for a period of time, the value should be 75% Mo 90%. If it is too small, there is waste in the shared pool. If it is higher than 90%, there is contention in the shared pool and insufficient memory. Over-setting of shared pools creates an additional administrative burden, which affects system performance. Too small a shared pool will age the component too quickly, and if the SQL statement is executed again, it will cause the SQL statement to be hard parsed.

SQL with executions > 1: the SQL ratio with more than 1 execution times. If this value is too small, you need to use more binding variables in the application to avoid too much SQL parsing.

Memory for SQL w/exec > 1: the percentage of memory consumed by SQL that is executed more than 1, a measure of how much memory is consumed by frequently used SQL statements compared with infrequently used SQL statements.

Top 5 Timed Events

This is the last section of the report summary and shows the five most serious wait events in the system, in reverse order in proportion to the wait time. We should start from here and consider what to do next. For example, the report shows that db file scattered read is the main waiting, which accounts for more than 50%, indicating that there are a large number of full table scans that do not leave indexes. Usually in a system where there is no problem, CPU time should be listed first, otherwise the system spends most of its time waiting.

Focus on the main problems

Wait Events Statistics

Waiting event statistics can quickly reflect the main waits that affect the performance of the system. Here we mainly look at the statistics of the Wait Events section.

The table is ranked by waiting time, and the wait events that affect performance most are at the top of the list. Here is an explanation of some common waiting events.

Db file scattered read: data files are read separately. This wait event typically occurs on a full table scan or a full index scan, reflecting the Ibig O wait that occurs when reading a contiguous set of data blocks. If the event is in the front column, it means that there may be a large number of SQL execution without missing indexes consuming database time, so you can find the SQL statement that scans the whole table without leaving the index with the statistics of the following SQL Statistics.

Db file sequential read: data files are read sequentially. This event typically occurs on an index lookup or access to a record through rowid, reflecting the Icano wait on reading a single block of data. The high value is usually due to poor join order between tables and improper use of indexes.

Db file parallel write: the wait for the data blocks in the cache to occur when the DBWR process writes them to disk in parallel. A high value may require an improvement in disk Iripple O performance.

Log file parallel write: occurs when logging is written from the log buffer to an online log file. If there is more than one log group member, the write operation is parallel, and the wait event may occur. If the disk supports asynchronous IO or uses IO SLAVE, this wait is possible even if there is only one log group member. The way to improve this wait is to put the online log files on a fast disk, avoid using the RAID5 array as much as possible, make sure that the tablespace is not in hot standby mode, and make sure that the log files and data files are on different disks.

Log file sync: occurs when waiting for the LGWR process to write the contents of the log buffer to disk. This event occurs when the user executes the commit command. When the user submits the data, LGWR needs to populate the log records of the session from the log buffer into the log file, and the user's process must wait for this fill to complete. If this wait event affects database performance, then you need to consider modifying the application's commit frequency, committing more records at a time, or putting redo log files on different physical disks to avoid using RAID5 arrays to store online log files and improve disk Imax O performance.

Log buffer space: the process is waiting for free space in the log buffer. Typically, this event indicates that the application generates logs faster than the LGWR process writes logs to disk. When the event is high, due to the analysis of the reasons for generating a large number of logs, the log buffer is increased and the performance of disk Ihand O is improved.

Control file parallel write / control file single write / control file sequential read: this event occurs when the server process updates or reads the control file. If the wait is short, you don't have to think about it. If the waiting time is long, you should check if there is a bottleneck on the physical disk Iswap O where the control files are stored. Multiple control files are identical copies and are used for mirroring to improve security, so they should be stored on different disks. It doesn't make sense to save multiple control files on the same disk. To reduce this wait, you can consider reducing the number of control files on the premise of ensuring security, using the asynchronous disk Iripo, and transferring the control files to the disk with lighter burden.

Enqueue: queue waiting due to the locking mechanism. Typically, if a user tries to modify a row of data in a table, that row of data is being modified by another user.

Lach free: this wait event occurs when a process is waiting for the latch held by another process to be released. Latch is a memory lock that can be acquired and released quickly, which is used to protect the shared memory structure in SGA from being accessed by multiple users at the same time. This event can occur for many reasons, such as the absence of binding variables in SQL statements, competition for buffer storage, and the presence of hot blocks in buffers.

Direct path read / direct path write: a wait occurs when a process reads and writes disk blocks directly without going through the database cache cache. Typically, this type of wait occurs when sorting operations, parallel DML operations, or direct path loading. Find the data files that operate most frequently. If there are too many sorting operations, they are likely to be temporary files, which will spread their load and improve their performance.

SQL*Net message from client: a wait event that occurs when the server process of the user session has completed a user request and is waiting for the user's next request.

SQL Statistics

SQL statistics to see the more time-consuming SQL that affect the performance of the system. SQL ordered by Elapsed Time is given priority here and is sorted by the time it takes to execute the SQL statement.

For example, you can see here that a piece of SQL executed by the OPU barcode printing program consumes a lot of database time, which is much higher than other statements. Click SQL Id, and the page jumps to the question statement:

This is a full-field query performed against the WIP_ tracking table by SN. Combined with the previous analysis of waiting events, because there are a large number of db file scattered read waiting events at the same time, it is estimated that the statement may not walk the index and perform a full table scan, resulting in serious performance problems. By taking out the statement and looking at its execution plan separately, you can verify that there is indeed a situation where the index is not taken, and the execution time is more than a few seconds. In addition, you can see that although the query is executed many times (Executions), it does not use binding variables, which leads to frequent hard parsing. Find the source of the problem statement, as a result, we find that it comes from the config.xml file in the OPU barcode printing package. The purpose of executing this statement is to determine whether the last barcode has been online when printing online, and the last barcode is not allowed to be spit out until the previous SN is online. As can be seen from the business function, it is completely unnecessary to do the operation of select *. Use select 1 from rmes.r_wip_tracking_t where sn = '…' Would be a better way.

You can also see some other influential statements here. BoschPDC_SQLPUSH_ICON_MySQLPush_V3 comes from BOSCH programs, w3wp comes from Web programs, and you can also see the main time-consuming stored procedures and statements executed by FlexSite.

Other SQL statistics are summarized as follows:

SQL ordered by CPU Time: sort by the consumption of SQL statements on CPU run time, excluding the various wait times during the execution of SQL statements.

SQL ordered by Gets: sort by the number of logical reads performed by the SQL statement. You can compare the Buffer Gets of this statement with the physical reads of the following statistics. If the two are relatively close, there is a problem with the statement. In addition, we can also look at the value of gets per exec here, which, if too large, indicates that the statement may have used a poor index or improper table joins.

SQL ordered by Reads: sort by the number of physical reads performed by the SQL statement. This shows the SQL that causes most physical Imax O, when there is a bottleneck in our system, we need to pay attention to the statements that have a lot of Imax O operations here. Inefficient SQL is often accompanied by a large number of physical reads, which also causes a large number of data blocks to read and wait.

SQL ordered by Executions: here's what tells us about the most executed SQL statements during this period. You can consider whether it is necessary to change the logic to avoid too many queries. Even if it runs fast, a large number of repetitive operations can consume more database time.

SQL ordered by Parse Calls: this mainly shows the comparison between Parse and Executions. If Parse / Executions > 1, it often indicates that there is a problem with this statement, such as not using binding variables, or the shared pool setting is too small.

SQL ordered by Sharable Memory: this is mainly sorted according to the occupancy of shared pools.

SQL ordered by Version Count: the main purpose here is to sort multiple versions of SQL statements. The same SQL text, but different attributes, such as different owners of objects, different session optimization modes, different types, different lengths, and different binding variables, cannot be shared, so there will be many different versions in the cache, which, of course, will result in more consumption of resources.

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.