A large number of physical and logical reads and few block changes indicate a DWH or a DSS system.
Another indicator for a DWH or a DSS system would be "W/A MB processed" (Work area processing) value is high which means a large amount of sorting is going on in PGA.
Another indicator to support this hypothesis is if the user calls and parses are low indicating the transactions contain few long running statements.
In an OLTP system there would be larger no of logical reads, user calls , parses , executes, rollbacks and executions.
In general a DWH would have fewer long running transactions that utilize the work area while OLTP environments have a larger number of small transactions with frequent commits and rollbacks.
Below is a brief explanation of each of the elements in the Load Profile.
DB Time
DB time is a statistic which represents CPU time consumed by all Oracle processes over a period of time
plus non-idle wait time for these processes. It is the time spent by the database server executing user calls ie - it is the total time spent by the all user processes which are actively working
or actively waiting in the database calls.
It includes the CPU Time, IO Wait time and non-idle time.
It tells us that how much activity performed by the database for the duration.
It does not include the time taken by the background processes.
Consider below example.
DB Time - 1700
Elapsed Time - 875
So average no of active session - 1.942
This means that at least 2 users were active in the database for the elapsed time period
or 4 users for elapsed time/2
or 8 users for elapsed time/4.
ie
For every second of elapsed time 1.942 seconds of work is done in the database
So in the AWR if the DB Time is high it means that the no of active sessions in the database is high
and the activity per session is also high
It is the CPU consumed by all Oracle server processes, foreground and shadow processes.
It does not include the time taken by the background processes.
Each oracle server process gathers its own CPU consumption by using C programming system calls.
Potentially, if we have an 8 CORE processor, 8 seconds of CPU time can be used per second.
So in the AWR if this value is showing 2.3 seconds of the potential 8 seconds/second,
it means we are not CPU bound
It gives an idea of the number of bytes written per second and per transaction to the redo logs.
A high value for redo size, block changes and % blocks changed per read indicate increased DML activity.
Logical reads is the sum of consistent gets and DB block gets.
Consistent Gets
What is a consistent get? When a request for data is made by an oracle query, a check will be made if the data needs reconstructing from rollback info to give you a consistent view.
For example - session A has modified data but has not commited.
This is updated in the buffer cache and the buffer block gets marked as dirty.
If the same data is requested from another session, the data will need to be reconstructed from the rollback segment to get a read consistent image.
DB Block Gets -
Blocks read from the buffer cache. These are clean buffer blocks and no checks for data consistency to be made.
This measure along with physical reads gives an indicative idea of how many disk reaks and how many buffer reads are happening.
If this value is very high, you can check the section "SQL by logical reads” to see which SQL is having more logical reads and if tuning for the query would be required.
The no of blocks modified during the snapshot interval period.
If oracle does not find the data in the buffer cache it does a physical read from disk.
Thus resulting in an I/O operation which is more costly than a buffer cache read.
This is a measure of the amount of I/O that is happening on the database.
The writes done by the DBWR background process to write datablocks to disk. These writes are done by DBWR whenever the count of dirty blocks in buffer cache reaches a threshold.
A high value for this is an indicator of high amount of DML activity in the database.
It refers to a user process making a call to a server process to do some work in the database.
This metric represents the number of logins, parses, or execute calls per second during the sample period.
The value of this parameter will be low if there are fewer write or update transactions committed or rolled back during the sample period. This parameter is useful as it sets the scale for other parameters like commits, hard parses etc.
If per user call the number of executes is high it could be an indicator of excessive context switching
eg - a PLSQL function in an SQL statment executing many times
Total of hard and soft parses.
If a SQL statement being executed is not in the shared pool, a hard parse will occur where the database has to do additional work for checking the validity of the sql syntactically and semantically and also choosing the optimum plan to execute the query.
A high value for hard parse (say greater than 100/second) indicates that bind variables are not being effectively used.
In such case the CURSOR_SHARING initialization parameter can be set.
The values for this are FORCE/SIMILAR/EXACT.
If set to FORCE there is a greater chance of the query being found in the shared pool,
which will result in a soft parse only and hence better performance.
If still the hard parse values are high it could indicate a sizing problem and the shared pool size could be increased.
A soft parse is recorded when the Oracle Server checks the shared pool for a SQL statement and finds a parsed version of the statement that it can reuse. This avoids the additional workdone in a hard parse and enhances performance.
This Oracle metric is the total number of bytes processed in the PGA.
It is the number of logons. A new database connection is also expensive and a large number of logons are known to create performance problems.
"Connection management elapsed time" in "Time model statistics" is an indicative parameter for
performance issues due to logons.
The no of sql statement executed per second. If this value is high along with high CPU usage,
the database load may be due to large no of high cost query executions. It may also indicate a large amount of context switching between SQL and PLSQL.
It is the total of transaction rollbacks and user rollbacks.
Transaction rollbacks - Oracle automatically executes a rollback when an oracle error occurs like a constraint violation.
User Rollbacks - When a user issues a rollback due to some functional rule violation.
Rollbacks are also an expensive operation and the undo records from the UNDO segment need to be applied to the data blocks that were modified.
Transaction in Oracle is simply the total of number of commits and rollbacks.
So if the awr load profile shows 0.1 rollbacks per second, then for the duration of the snapshot ie - 3600 seconds, the no of rollbacks will be 0.1 * 3600 = 360 rollbacks.
And if the no of transactions per second is 7, then for the duration of the snapshot ie - 3600 seconds
the no of transactions will be 7 *3600 = 25200 transactions.
Therefore no of commits = No of transactions - No of rollbacks
= 25200 - 360 = 24840 commits
The important aspect is not just the number of transactions,
you need to calculate how many queries Oracle has executed (average) per transactions.
This can be calculated as Total execution per sec / number of transactions per sec.
In Load profile if no of execute is 356, then oracle has executed 358/7 = ~51 SQLs per second.
But these factors combined with the other factors in the awr, it will be helpful in doing a better analysis and coming up with a more appropriate root cause for any performance degradation in the database.
No comments:
Post a Comment