1. AWR report header information
- DB name: Database Name dbid: Database ID
- Elapsed: Sampling Period
- DB time: the time spent on user operations, excluding the time consumed by Oracle background processes
- DB time is much less than elapsed time, indicating that the database is relatively idle
2. AWR load Summary
- Per second and per transaction: These two parts are a detailed list of database resource loads, divided into the resource loads per second and the resource loads per transaction
- Redo size: the redo volume (in bytes) generated per second/each transaction indicates the degree of database busy.
- Logical reads: Number of logical reads per second/transaction
- Block changes: number of data blocks changed per second/transaction
- Physical reads: Physical reads per second/produced by each transaction
- Physical writes: Number of physical writes per second/transaction
- User CILS: the number of calls per second per transaction user
- Parses: per second/each transaction analysis times less than 300 indicates normal
- Hard parses: the number of hard analyses per second per transaction is less than 100, indicating normal
- Sorts: sorts transactions per second.
- Logons: number of database logins per second/per transaction
- Executes: the number of executions per second per transaction SQL statement
- Rollbacks: the number of rollbacks per second per transaction
- Transactions: number of transactions per second
3. AWR instance Efficiency
- Buffer Nowait %: Percentage of data not waiting in the memory
- Buffer hit %: indicates the ratio of data blocks found by the process from the memory, and the memory data block hit rate. Increase the data buffer pool if the value is less than 80%
- Library hit %: indicates the hit rate of SQL parsing in the shared pool. If the value is lower than 90%, you need to increase the share pool.
- Execute to parse: Ratio of statement execution to analysis. If the SQL reuse rate is high, this ratio is very high. The higher the value, the more repeated executions are performed after a resolution.
- Parse CPU to parse elapsd: Percentage of total CPU consumed in total parsing time
- Redo Nowait: the percentage of unwaited buffer obtained in the log buffer.
- In-memory sort %: Ratio of sorting in memory. If it is too low, a large number of sorting is performed in the temporary tablespace. Consider tuning up the PGA.
- Soft parse %: Percentage of soft resolution (Softs/Softs + hards), which is similar to the hit rate of SQL in the sharing zone. If it is too low, you need to adjust the application to use the binding variable.
- Latch hit: latch is a lock that protects the memory structure and can be considered as a license for the server process to obtain access to the memory data structure.
- Non-Parse CPU: actual SQL running time/(actual SQL running time + SQL parsing time). Too low indicates excessive parsing time consumption.
4. Shared Pool Overview
- Memory usage %: For a database that has been running for a period of time, the memory usage of the shared pool should be stable between 75% and 90%. If it is too small, it indicates that the shared pool is a waste, if the value is higher than 90, it indicates that the shared pool is in contention and the memory is insufficient.
- SQL with executions> 1: Ratio of SQL statements with execution times greater than 1. If this value is too small, you need to use more bound variables in the application to avoid excessive SQL parsing.
- Memory for SQL w/exec> 1: Percentage of memory consumed by SQL statements with execution times greater than 1.
5. AWR top wait event
Shows the five most serious waits in the system, which are listed in descending order according to the proportion of the waiting time. When tuning, we always want to see the most significant effect, so we should start from here to determine what we will do next.
Generally, CPU time is always listed in the first.
6. AWR top SQL Tuning
1) SQL ordered by elapsed time: the top SQL statement that records the total execution time (note that the total execution time of the SQL statement within the monitoring range, rather than the execution time of a single SQL statement)
- Elapsed time (s): the total duration of SQL statement execution. This sorting is performed according to this field. Note that this time is not the time for running a single SQL statement, but the total time of the number of SQL statements executed within the monitoring range. Unit: seconds
Elapsed time = CPU time + wait time
- CPU time (s): The total CPU usage time during SQL statement execution. This time is less than or equal to the elapsed time. Unit: seconds.
- Executions: Total number of SQL statement executions within the monitoring range.
- Elap per exec (s): average time for executing an SQL statement. Unit: seconds.
- % Total dB time: the percentage of elapsed time of SQL to the total time of the database.
- SQL id: ID of the SQL statement. Click it to navigate to the SQL details list below. Click IE to return to the current SQL ID.
- SQL Module: shows how the SQL statement is connected to the database for execution. If the SQL * Plus or PL/SQL link is used, someone is debugging the program. Generally, the SQL statement that is linked to the foreground application is empty.
- SQL text: A simple SQL prompt. For details, click the SQL ID.
2) SQL ordered by CPU time: records the top SQL statements with the longest execution time as the sum of CPU time (note that the total execution time of this SQL statement within the monitoring range is the sum of CPU time, instead of a single SQL Execution time ).
3) SQL ordered by gets: records the top SQL statements in which execution occupies the total buffer gets (logical Io) (note that the execution of this SQL statement accounts for the total gets in the monitoring range, instead of gets occupied by a single SQL Execution ).
4) SQL ordered by reads: records the top SQL statements that occupy the total Disk Physical reads (physical Io). Note that the execution of this SQL statement accounts for the total Disk Physical reads within the monitoring range, instead of the Disk Physical reads occupied by a single SQL Execution ).
5) SQL ordered by executions: records the top SQL statements sorted by the number of SQL executions. This sorting shows the number of SQL executions within the monitoring range.
6) SQL ordered by parse CILS: Top SQL that records the number of SQL soft resolutions.
7) SQL ordered by sharable memory: records the top SQL statements that occupy the size of the library cache.
Sharable MEM (B): the size of the library cache. The Unit is byte.
8) SQL ordered by version count: records top SQL statements for enabling Sub-game targets.
It mainly targets ordered by elapsed time, orderedby CPU time, orderedby gets, and orderedby read top three SQL statements for observation and optimization.
Oracle SQL processing steps:
- Syntax check (check whether the SQL spelling syntax is correct)
- Semantic check (check whether the Access Object in SQL exists and whether it has the corresponding permissions)
- Parsing (PARSE) (using internal algorithms for SQL parsing, generation of parsing tree (parse tree) and Execution Plan (Execution Plan) à soft and hard parsing occurs in this process
- Execute SQL and return results
Analyze AWR reports
