In AWR, the Load Profile table comes under the ‘Report Summary’ section. Refer to the below figure:
The following metrics are available in the Load Profile table of an AWR report:
DB Time(s)
DB Time(s) is the number of average active sessions during the given period. The active sessions are calculated by dividing the ‘DB Time’ by ‘Elapsed’ time.
Formula: DB Time(s) = DB Time / Elapsed Time
Example:
Refer to the above screenshot, the DB Time is 22.25 minutes and the Elapsed time is 9.04 minutes. Hence the average active sessions during AWR Time is:
DB Time(s) = DB Time / Elapsed => 22.25/9.04 = 2.46 = ~2.5 sessions
So load on database i.e. average active sessions = ~ 2.5
If DB Time has a higher value, DB Activity/Sessions were High during the AWR Time. This means that for every minute of Elapsed time, there are 2.5 minutes of work done in the database
DB CPU(s)
It indicates the amount of CPU time spent on user calls. It does not include the background process. The value is in microseconds. Referring to Figure 01, the value of the DB CPU(s) metric is 2.1.
Since we have 8 CPU cores, we can potentially use 8 seconds of CPU time per second. In this case, DB CPU(s) 2.1 (per second) is reporting that the system is using 2.1 seconds of CPU of the potential 8 seconds/second.
Redo size (bytes)
It notes the amount of DML activities (insert, update or delete operations) happening in the database. If the Redo size value is high, it shows either lots of new data was inserted into the database or the existing data has undergone lots of updates (changes).
If the ‘Redo Size’ is very high and there’s no legitimate business justification for it, then investigation is difficult in the absence of the ‘SQL ordered by redo’ feature in AWR.
Just remain vigilant for any irregular DML activity. Check whether
- Are there any unusual statements?
- Are some common statements being executed more frequently than often?
- Do they generate a greater number of rows per execution than what’s normal?
- Additionally, be sure to thoroughly examine the segment statistics section (including segments by physical writes and segments by DB block changes) to uncover any potential insights.
Logical reads (blocks)
It is the count of ‘the number of times, the data is found in the buffer cache per second’. If required data is available in the buffer cache i.e. SGA (System Global Area) then the count of logical reads is increased by 1.
‘Logical reads’ is the sum of two counters, which are:
- Consistent Gets: If Oracle gets the data in a block which is consistent with a given point in time, then the Consistent Gets counter increases by 1.
- DB Block Gets: If Oracle gets data in the current mode, that is, the most up-to-date copy of the data in that block, as it is right now or currently then, the DB Block Gets counter increases by 1.
Formula: Logical Reads = Consistent Gets + DB Block Gets
The values of both the counters are available in the ‘Instance Activity Stats’ section.
Example: In Figure 01, the Logical reads (block) value is 370,698.7 per second. In the ‘Instance Activity Stats’, the value of ‘Consistent Gets’ is 353,572.73 per second and DB Block Gets is 17,125.91.
If the value of Logical reads is high then go to the section ‘SQL by logical reads’ and check which SQL has more logical reads.
Block Changes
It indicates the number of blocks modified during the sample interval.
Physical reads (blocks)
If the data is not available in the buffer cache then Oracle reads the data from the physical blocks. Hence, physical reads (block) is the count of ‘the number of times, the data is read from physical blocks per second’.
Practically, ‘Logical Read’ is more performance efficient than ‘Physical Read’ because the database has to work harder (and more) to get the data from the physical block.
If the value of Physical reads is high then go to the section ‘SQL by Physical reads’ and check which SQL has more logical reads.
Physical write (blocks)
Physical write (block) is the count of ‘the number of times, the data is written in the physical blocks per second’.
Read IO requests
The number of requests sent by an application to read data from one or more database blocks.
Write IO requests
The number of requests sent by an application to write the data to the database.
Read IO (MB)
The amount of data in megabytes read from one or more database blocks.
Write IO (MB)
The amount of data in megabytes writes to the database.
IM scan rows
The number of rows scanned in all In-Memory Compression Units. Note that In-Memory Dynamic Scans (IMDS) is a new feature in Oracle Database 18c that allows parallelizing In-Memory table scans without having to use Parallel Query (PQ). It helps to improve the performance of the database.
Session Logical Read IM
The number of logical In-Memory reads per second during a sample period.
User calls
A user call occurs when a database client asks the server to do something e.g. login, parse, execute, fetch etc.
The high number of user calls indicates excessive context switching (e.g. a PL/SQL function in a SQL statement called too often because of a bad plan). In this case, refer to the ‘SQL ordered by executions’ section to understand the issue.
Parses (SQL)
Parsing means scanning of memory (cache, logical or physical) for searching the required data from the database. It is the sum of soft and hard parse during the sample period.
Formula: Parses (SQL) = Soft Parse + Hard Parse
Hard Parses
Hard parses happen when the server parses a query and cannot find an exact match for the query in the library cache. Hence the parses require a completely new parse of the SQL statement. These consume both latches and a shared pool area. It is a time and resource-consuming task. If
Hard Parses value < 1: OK
Hard Parses value = 1 to 100: Need Investigation
Hard Parses value > 100: Problematic
Note: Soft parses are not listed but are derived by subtracting the hard parses from (total) Parses. A soft parse reuses a previous hard parse and consumes far fewer resources than a hard parse.
Logons
It shows the number of logins to the database. The Oracle Database authenticates users by using information stored in the database. Users must provide a password to connect to the database. The high number of logins degrades the DB performance. It should be less than 1.
User Logons
For the ‘User Logons’ count, users are authenticated using their Windows login credentials, allowing them to access the database without additional login credentials.
Executes (SQL)
It indicates the number of SQL statements which were executed during the sample period.
Rollbacks
It provides the information about number of times rollback operations (to revert the database to the original state) occurred on the database. Rollbacks are expensive operations and can cause performance problems if used improperly, which can be controlled either by reducing the number of rollbacks or by tuning rollback segments. Rollbacks can also indicate that a branch of code is failing and thus forced to roll back the results.
Transactions
It indicates the total number of transactions that happened during the sample period.
Note that the number of commits is calculated by subtracting the value of rollbacks from transactions.
You may be interested: