Scheduling and Switching
The operating system may spend excessive time scheduling and switching processes. Examine the way in which you are using the operating system, because you could be using too many processes. On NT systems, do not overload your server with too many non-Oracle processes.
Oracle CPU Utilization
This section explains how to examine the processes running in Oracle. Three dynamic performance views provide information on Oracle processes:
V$SYSSTAT shows Oracle CPU usage for all sessions. The statistic "CPU used by this session" shows the aggregate CPU used by all sessions.
V$SESSTAT shows Oracle CPU usage per session. You can use this view to determine which particular session is using the most CPU.
V$RSRC_CONSUMER_GROUP shows CPU utilization statistics on a per consumer group basis, if you are running the Oracle Database Resource Manager.
Reparsing SQL Statements
When Oracle executes a SQL statement, it parses it to determine whether the syntax and its contents are correct. This process can consume significant overhead. Once parsed, Oracle does not parse the statement again unless the parsing information is aged from the memory cache and is no longer available. Ineffective memory sharing among SQL statements can result in reparsing. Use the following procedure to determine whether reparsing is occurring:
Get the parse time CPU and CPU figures used by this session from the "Statistics" section of the estat report or from V$SYSTATS. For example:
SELECT * FROM V$SYSSTAT
WHERE NAME IN('parse time cpu', 'parse time elapsed', 'parse count (hard)');
Now you can detect the general response time on parsing. The more your application is parsing, the more contention exists, and the more time your system spends waiting. If parse time CPU represents a large percentage of the CPU time, then time is being spent parsing instead of executing statements. If this is the case, then it is likely that the application is using literal SQL and not sharing it, or the shared pool is poorly configured.
Query V$SQLAREA to find frequently reparsed statements. For example:
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
FROM V$SQLAREA
ORDER BY PARSE_CALLS;
Tune the statements with the higher numbers of parse calls.
If the parse time CPU is only a small percentage of the total CPU used, then you should determine where the CPU resources are going. There are several things you can do to help with this.
Find statements with large numbers of buffer gets, because these are typically heavy on CPU.
The following statement finds SQL statements which frequently access database buffers. Such statements are probably looking at many rows of data.
SELECT ADDRESS, HASH_VALUE, BUFFER_GETS, EXECUTIONS,
BUFFER GETS/EXECUTIONS "GETS/EXEC", SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_GETS > 50000
AND EXECUTIONS > 0
ORDER BY 3;
This example shows which SQL statements have the most buffer_gets and use the most CPU. The statements of interest are those with a large number of gets per execution, especially if execution is high. It is very beneficial to have an understanding of the application components to know which statements are expected to be expensive.
After candidate statements have been isolated, the full statement text can be obtained using the following query, substituting relevant values for ADDRESS and HASH_VALUE pairs. For example:
SELECT SQL_TEXT
FROM V$SQLTEXT
WHERE ADDRESS='&ADDRESS_WANTED'
AND HASH_VALUe=&HASH_VALUE
ORDER BY piece;
The statement can then be explained (using EXPLAIN PLAN) or isolated for further testing to see how CPU-intensive it really is. If the statement uses bind variables and if your data is highly skewed, then the statement may only be CPU-intensive for certain bind values.
Find which sessions are responsible for most CPU usage. The following statement helps locate sessions which have used the most CPU:
SELECT v.SID, SUBSTR(s.NAME,1,30) "Statistic", v.VALUE
FROM V$STATNAME s, V$SESSTAT v
WHERE s.NAME = 'CPU used by this session'
AND v.STATISTIC# = s.STATISTIC#
AND v.VALUE > 0
ORDER BY 3;
Note:
CPU time is cumulative; therefore, a session that has been connected for several days may appear to be heavier on CPU than one that has only been connected for a short period of time. Thus, it is better to write a script to sample the difference in the statistic between two known points in time, letting you see how much CPU was used by each session in a known time frame.
After any CPU-intensive sessions have been identified, the V$SESSION view can be used to get more information. At this stage, it is generally best to revert to user session tracing (SQL_TRACE) to determine where the CPU is being used.
Trace typical user sessions using the SQL_TRACE option to see how CPU is apportioned amongst the main application statements.
After these statements have been identified, you have the following three options for tuning them:
Rewrite the application so that statements do not continually reparse.
Reduce parsing by using the initialization parameter SESSION_CACHED_CURSORS.
If the parse count is small, the execute count is small, and the SQL statements are very similar except for the WHERE clause, then you may find that hard coded values are being used instead of bind variables. Use bind variables to reduce parsing.
If your system must scan too many buffers in the foreground to find a free buffer, then it wastes CPU resources. To alleviate this problem, tune the DBWn process(es) to write more frequently.
Wait Detection
Whenever an Oracle process waits for something, it records it as a wait using one of a set of predefined wait events. (See V$EVENT_NAME for a list of all wait events). Some of these events can be considered idle events; i.e., the process is waiting for work. Other events indicate time spent waiting for a resource or action to complete. By comparing the relative time spent waiting on each wait event and the "CPU used by this session" (from above), you can see where the Oracle instance is spending most of its time. To get an indication of where time is spent, follow these steps:
Review either the V$SYSTATS view or the wait events section of the AWR report.
Ignore any idle wait events. Common idle wait events include:
Client message
SQL*Net message from client
SQL*Net more data from client
RDBMS IPC message
Pipe get
Null event
PMON timer
SMON timer
Parallel query dequeue
Ignore any wait events that represent a very small percentage of the total time waited.
Add the remaining wait event times, and calculate each one as a percentage of total time waited.
Compare the total time waited with the CPU used by this session figure.
Find the event with the largest wait event time. This may be the first item you want to tune.
Latch Contention
Latch contention is a symptom of CPU problems; it is not usually a cause. To resolve it, you must locate the latch contention within your application, identify its cause, and determine which part of your application is poorly written.
In some cases, the spin count may be set too high. It's also possible that one process may be holding a latch that another process is attempting to secure. The process attempting to secure the latch may be endlessly spinning. After a while, this process may go to sleep and later resume processing and repeat its ineffectual spinning. To resolve this:
Check the Oracle latch statistics. The "latch free" event in V$SYSTEM_EVENT shows how long processes have been waiting for latches. If there is no latch contention, then this statistic does not appear. If there is a lot of contention, then it may be better for a process to go to sleep at once when it cannot obtain a latch, rather than use CPU time by spinning.
Look for the ratio of CPUs to processes. If there are large numbers of both, then many processes can run. But, if a single process is holding a latch on a system with ten CPUs, then reschedule that process so it is not running. But, ten other processes may run ineffectively trying to secure the same latch. This situation wastes, in parallel, some CPU resource.
Check V$LATCH_MISSES, which indicates where in the Oracle code most contention occurs.
The operating system may spend excessive time scheduling and switching processes. Examine the way in which you are using the operating system, because you could be using too many processes. On NT systems, do not overload your server with too many non-Oracle processes.
Oracle CPU Utilization
This section explains how to examine the processes running in Oracle. Three dynamic performance views provide information on Oracle processes:
V$SYSSTAT shows Oracle CPU usage for all sessions. The statistic "CPU used by this session" shows the aggregate CPU used by all sessions.
V$SESSTAT shows Oracle CPU usage per session. You can use this view to determine which particular session is using the most CPU.
V$RSRC_CONSUMER_GROUP shows CPU utilization statistics on a per consumer group basis, if you are running the Oracle Database Resource Manager.
Reparsing SQL Statements
When Oracle executes a SQL statement, it parses it to determine whether the syntax and its contents are correct. This process can consume significant overhead. Once parsed, Oracle does not parse the statement again unless the parsing information is aged from the memory cache and is no longer available. Ineffective memory sharing among SQL statements can result in reparsing. Use the following procedure to determine whether reparsing is occurring:
Get the parse time CPU and CPU figures used by this session from the "Statistics" section of the estat report or from V$SYSTATS. For example:
SELECT * FROM V$SYSSTAT
WHERE NAME IN('parse time cpu', 'parse time elapsed', 'parse count (hard)');
Now you can detect the general response time on parsing. The more your application is parsing, the more contention exists, and the more time your system spends waiting. If parse time CPU represents a large percentage of the CPU time, then time is being spent parsing instead of executing statements. If this is the case, then it is likely that the application is using literal SQL and not sharing it, or the shared pool is poorly configured.
Query V$SQLAREA to find frequently reparsed statements. For example:
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
FROM V$SQLAREA
ORDER BY PARSE_CALLS;
Tune the statements with the higher numbers of parse calls.
If the parse time CPU is only a small percentage of the total CPU used, then you should determine where the CPU resources are going. There are several things you can do to help with this.
Find statements with large numbers of buffer gets, because these are typically heavy on CPU.
The following statement finds SQL statements which frequently access database buffers. Such statements are probably looking at many rows of data.
SELECT ADDRESS, HASH_VALUE, BUFFER_GETS, EXECUTIONS,
BUFFER GETS/EXECUTIONS "GETS/EXEC", SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_GETS > 50000
AND EXECUTIONS > 0
ORDER BY 3;
This example shows which SQL statements have the most buffer_gets and use the most CPU. The statements of interest are those with a large number of gets per execution, especially if execution is high. It is very beneficial to have an understanding of the application components to know which statements are expected to be expensive.
After candidate statements have been isolated, the full statement text can be obtained using the following query, substituting relevant values for ADDRESS and HASH_VALUE pairs. For example:
SELECT SQL_TEXT
FROM V$SQLTEXT
WHERE ADDRESS='&ADDRESS_WANTED'
AND HASH_VALUe=&HASH_VALUE
ORDER BY piece;
The statement can then be explained (using EXPLAIN PLAN) or isolated for further testing to see how CPU-intensive it really is. If the statement uses bind variables and if your data is highly skewed, then the statement may only be CPU-intensive for certain bind values.
Find which sessions are responsible for most CPU usage. The following statement helps locate sessions which have used the most CPU:
SELECT v.SID, SUBSTR(s.NAME,1,30) "Statistic", v.VALUE
FROM V$STATNAME s, V$SESSTAT v
WHERE s.NAME = 'CPU used by this session'
AND v.STATISTIC# = s.STATISTIC#
AND v.VALUE > 0
ORDER BY 3;
Note:
CPU time is cumulative; therefore, a session that has been connected for several days may appear to be heavier on CPU than one that has only been connected for a short period of time. Thus, it is better to write a script to sample the difference in the statistic between two known points in time, letting you see how much CPU was used by each session in a known time frame.
After any CPU-intensive sessions have been identified, the V$SESSION view can be used to get more information. At this stage, it is generally best to revert to user session tracing (SQL_TRACE) to determine where the CPU is being used.
Trace typical user sessions using the SQL_TRACE option to see how CPU is apportioned amongst the main application statements.
After these statements have been identified, you have the following three options for tuning them:
Rewrite the application so that statements do not continually reparse.
Reduce parsing by using the initialization parameter SESSION_CACHED_CURSORS.
If the parse count is small, the execute count is small, and the SQL statements are very similar except for the WHERE clause, then you may find that hard coded values are being used instead of bind variables. Use bind variables to reduce parsing.
If your system must scan too many buffers in the foreground to find a free buffer, then it wastes CPU resources. To alleviate this problem, tune the DBWn process(es) to write more frequently.
Wait Detection
Whenever an Oracle process waits for something, it records it as a wait using one of a set of predefined wait events. (See V$EVENT_NAME for a list of all wait events). Some of these events can be considered idle events; i.e., the process is waiting for work. Other events indicate time spent waiting for a resource or action to complete. By comparing the relative time spent waiting on each wait event and the "CPU used by this session" (from above), you can see where the Oracle instance is spending most of its time. To get an indication of where time is spent, follow these steps:
Review either the V$SYSTATS view or the wait events section of the AWR report.
Ignore any idle wait events. Common idle wait events include:
Client message
SQL*Net message from client
SQL*Net more data from client
RDBMS IPC message
Pipe get
Null event
PMON timer
SMON timer
Parallel query dequeue
Ignore any wait events that represent a very small percentage of the total time waited.
Add the remaining wait event times, and calculate each one as a percentage of total time waited.
Compare the total time waited with the CPU used by this session figure.
Find the event with the largest wait event time. This may be the first item you want to tune.
Latch Contention
Latch contention is a symptom of CPU problems; it is not usually a cause. To resolve it, you must locate the latch contention within your application, identify its cause, and determine which part of your application is poorly written.
In some cases, the spin count may be set too high. It's also possible that one process may be holding a latch that another process is attempting to secure. The process attempting to secure the latch may be endlessly spinning. After a while, this process may go to sleep and later resume processing and repeat its ineffectual spinning. To resolve this:
Check the Oracle latch statistics. The "latch free" event in V$SYSTEM_EVENT shows how long processes have been waiting for latches. If there is no latch contention, then this statistic does not appear. If there is a lot of contention, then it may be better for a process to go to sleep at once when it cannot obtain a latch, rather than use CPU time by spinning.
Look for the ratio of CPUs to processes. If there are large numbers of both, then many processes can run. But, if a single process is holding a latch on a system with ten CPUs, then reschedule that process so it is not running. But, ten other processes may run ineffectively trying to secure the same latch. This situation wastes, in parallel, some CPU resource.
Check V$LATCH_MISSES, which indicates where in the Oracle code most contention occurs.
No comments:
Post a Comment