Symptom
The CPU usage by Oracle is very high.
Other Terms
CPU
Reason and Prerequisites
Active Oracle sessions and background processes always have one of the following statuses (see Note 619188):
- Waiting for a resource
- Executing kernel source code and CPU usage
- Transaction ST06 (or another monitoring tool) suddenly displays a very high CPU load that is caused mainly by Oracle processes.
- The proportion of "Busy Wait Time" in transaction ST04 is less than one-and-a-half times as high as the "CPU Time".
- In V$SESSION_WAIT (or "ST04 -> Detail Analysis Menu -> Oracle Session"), the WAIT_TIME for an ACTIVE session is mostly greater than 0.
- 1. A time-consuming SQL statement with a large number of Buffer Gets
- 2. Parallel Query
- 3. Long IN lists or OR concatenations
- 4. Latch Waits
- 5. WINDOWS: Using AWE
- 6. SHARED_POOL_SIZE too small
- 7. R/3, Oracle 9i: _PUSH_JOIN_PREDICATE not set
- 8. USE_CONCAT hint and long IN lists
- 9. OR links of multiple range conditions
- 10. Time-consuming Oracle DDIC accesses
- 11. SOLARIS: Using dynamic SGA
- 12. BW, 9.2.0.6: Loop while parsing STAR transformations
- 13. DMTS: Space transactions
- 14. TRUNCATE, DROP, BEGIN BACKUP, Oracle 9i: CPU usage by CKPT process
- 15. Oracle 9.2.0.7 or higher: Activated Bind Value Peeking
- 16. Activated block check sums
- 17. Recursive SQL statement as part of INSERTs
- 18. INSERTs in connection with traditional PGA
- 19. Selecting a large number of columns
- 20. Oracle 10g, UNIX: High CPU consumption of M000, SMON and other processes
- 21. Oracle 10g: High CPU usage by mutual exclusions (mutexes)
- 22. Oracle 10.2.0.3 or lower: High CPU usage for a large buffer pool
- 23. High CPU usage for DML operations on ASSM segments
- 24. Parsing complex or a high number of SQL statements
- 25. Oracle 10.2.0.3 or lower, ASSM: High CPU consumption with UPDATEs
- 26. Bitmap indexes with a large number of specifications
- 27. BW, Oracle 10.2.0.3 or lower: Loop while parsing STAR transformations
- 28. Oracle 10.2.0.2 Pin overhead during nested loop joins
- 29. Oracle 10.2.0.2 or 10.2.0.4 without fix 7188932: Loop during parsing
- 30. Oracle 10g: Sporadic loop during parsing
- 31. Oracle 11.2.0.1: OLTP Table Compression
- 32. Oracle 11.2.0.2, 11.2.0.3: OLTP Table Compression with ASSM
- 33. Consistent Changes
- 34. Other causes
Solution
- 1. In most cases, high CPU usage results from a large number of
Buffer Gets on Oracle. You should therefore analyze and optimize SQL
statements that have a large number of Buffer Gets (see Note 766349).
You should also perform general checks (parameter settings, patch set,
statistics, and so on) in accordance with Note 618868.
On Oracle 10g or higher, you can use the SQL statement attached to this note to determine the SQL statements that exhibit the highest CPU consumption within the AWR history.
- 2.
If Parallel Query is activated, specific database actions such as index
construction or full table scans of several Oracle processes are
processed in parallel. These processes require several times the amount
of CPU resources compared with an individual session that is working
sequentially.
- 3. Parsing statements
with long OR concatenations can be very CPU-intensive. See Note 881083
and, if required, reduce the rsdb/max_blocking_factor if the CPU demand
is based on statements with a large number of OR operations.
- 4.
In "Willing to Wait" latch waits, CPU is consumed during spinning. You
must therefore check whether there is a latch problem, as described in
Note 767414.
- 5. The use of AWE on WINDOWS may result in high CPU usage. See Note 603041.
- 6.
A shared pool that is configured too low may lead to an increased
number of recursive SQL statements that, in turn, may be responsible for
increased CPU usage. Therefore, in accordance with Note 618868 (shared
pool section), check whether the shared pool is set large enough and
whether the number of recursive SQL statements is permanently higher
than expected.
- 7. To avoid performance problems and high levels
of CPU usage in connection with outer joins, the _PUSH_JOIN_PREDICATE
parameter must be set to FALSE in the case of 9i and non-BW systems
(Note 626172).
- 8. If you use a USE_CONCAT hint in connection
with long IN lists, this may result in high CPU usage when the access
path is created. The reason is that for each condition, additional
negation predicates have to be generated for all preceding conditions to
exclude duplicate data records. Therefore, the number of implicitly
evaluated conditions increases exponentially to the number of OR links.
For this reason, you should avoid using USE_CONCAT hints in connection
with long IN lists. If you encounter problems of this type with the
table AFKO, refer to correction instructions 552217 from Note 545932.
- 9.
In addition, the OR link of several range conditions results in a high
number of negation predicates if Oracle decides on an access using OR
CONCATENATION. The evaluation of these predicates result in a high CPU
usage. Therefore, avoid specifying a large number of range conditions
linked with OR in an SQL statement.
- 10. Accesses to Oracle DDIC
views such as DBA_SEGMENTS or DBA_IND_COLUMNS are often among the most
time-consuming database statements of all, and also cause a high CPU
load. These accesses are usually performed by monitoring tools.
- Do not schedule the checks of the monitoring tools more often than necessary. Generally it does not make any sense to check every 30 minutes for critical extents, or to see whether the MAXEXTENTS limit has been reached.
- Regularly check the statements used by the tools and contact the supplier if an unusually time-consuming access occurs.
- As of Oracle 8i, see Note 871455 to accelerate accesses to the Oracle dictionary.
- If an R/3 upgrade hangs with high CPU consumption while accessing Oracle DDIC views such as USER_IND_COLUMNS or DBA_IND_COLUMNS, you must modify the view definitions or remove statistics created on the Oracle DDIC. See Note 558197.
- 11. If the dynamic SGA is activated on
SOLARIS (see Note 617416) without the relevant prerequisites from Note
360438 (SOLARIS 8) or Note 550585 (SOLARIS 9) being fulfilled in the
operating system, high CPU usage (system) can occur.
- 12. Refer to Note 846364 if the parsing of statements with STAR transformations does not end and 100% CPU is required.
- 13.
When allocating and deallocating extents in dictionary-managed
tablespaces, a session can cause high CPU usage. The ST enqueue section
of Note 745639 describes the reasons for a large number of space
transactions, and describes further analysis options. There can only
ever be one session displaying high CPU usage caused by space
transactions, since further sessions cannot execute space transactions
in parallel due to the ST enqueue.
- 14. During TRUNCATE, DROP,
and BEGIN BACKUP operations, checkpoint-like operations are carried out,
during which the complete buffer pool must be scanned by the CKPT
process. To do this, see the "rdbms ipc reply" section from Note 61988.
- 15.
As of Oracle 9.2.0.7 at the latest, make sure that the parameter
_OPTIM_PEEK_USER_BINDS is set to FALSE (see Note 176754). Otherwise,
high CPU usage can occur due to time-consuming SQL statements with a lot
of Buffer Gets.
- 16. Activating block checksums using
DB_BLOCK_CHECKSUM may only result in a marginal CPU overhead. However,
if there is no other explanation for a high CPU usage, and if
DB_BLOCK_CHECKSUM is active, you can set this parameters to FALSE to
test it. See also Note 923919.
- 17. Due to an unsuitable
recursive access as part of INSERT statements, a large number of Buffer
Gets and a high CPU usage may occur, especially on Oracle 10.2.0.2. See
SAP Note 138639 in this regard. As of Oracle 11g with SBP 07/2012, you
can deactivate the recursive access via parameters (EVENT 31991).
- 18.
INSERT operations using the traditional PGA
(WORKAREA_SIZE_POLICY=MANUAL) administration can sometimes show high CPU
usage together with a high PGA allocation. This is prevented by
changing to the automatic PGA administration in accordance with Note
619876.
- 19. If you select data records that have a large number
of columns (for example, because "SELECT" was executed on a table that
has a lot of columns), the filling of all these columns requires
significant CPU resources. Therefore, avoid using "SELECT" on tables
that have many columns.
- 20. If, on Oracle 10g on UNIX, you
notice an inexplicable CPU usage of different processes such as M000,
SMON or shadow processes, check whether a relink of the Oracle software
in accordance with Note 986536 may solve the problem.
- 21. In accordance with Note 964344, check whether the increases CPU consumption occurs in connection with mutex waits.
- 22.
On Oracle 10.2.0.3 or lower, a large buffer pool may result in a high
CPU usage in the ktaifm() module. This particularly applies to massive
data changes such as imports. This is caused by Oracle bug 5530958. For
more information, see Note 1028099.
- 23. When you use ASSM
tablespaces with parallel DML operations (INSERT, UPDATE, DELETE) on a
table and with a relatively low COMMIT frequency, there may be a
significant increase in the number of buffer gets and, in turn, a
significant increase in CPU usage. For Oracle 9.2.0.8, fixes are offered
within Notes 1037755 and 896717. No fixes are available for Oracle
10.2.0.2. This means that you should consider upgrading to 10.2.0.4 or
above.
- 24. In general, parsing of complex SQL statements with
lots of tables may lead to a high CPU requirement. As of Oracle 10g, you
can take the duration of the parsing from the AVG_HARD_PARSE_TIME
column in the V$SQLSTATS table. High values often imply a
correspondingly high proportion of CPU usage.
Refer to note 1093485 for more information.
- 25.
If the ASSM tablespaces contain indexes, and if UPDATEs to the relevant
tables require an inexplicably large volume of CPU resources, the bug
described in Note 1043381 may be responsible in Oracle 10.2.0.3 and
lower.
- 26. When you create the index and when you use it during
a STAR transformation, bitmap indexes with a large number of distinct
keys may result in a high CPU usage. For more information about this,
see Note 1013912 and consider using a high cardinality dimension.
- 27.
With Oracle 10.2.0.3 or lower, bug 5369855, which is described in Note
971261, may result in a loop when parsing STAR transformations that
results in a high CPU consumption.
- 28. On Oracle 10.2.0.2, a
slightly increased CPU consumption during nested loop joins may be
traced back to the pin/unpin overhead described in Note 1159243.
However, it is unlikely that this will cause serious problems.
- 29. See Note 1227227 and ensure that event 10091 is set correctly for Oracle 10.2.0.2 or Oracle 10.2.0.4 without fix 7188932.
- 30.
Hanging sessions that sporadically occur on Oracle 10g with high CPU
consumption can be triggered by bug 6795880, which describes a loop
during parsing. For verification, you can create error stacks in
accordance with Note 613872 that must contain the module
kksSearchChildList. Use Oracle 10.2.0.4 or higher with bug fix 6795880
and set _CURSOR_FEATURES_ENABLED = 10 in accordance with Note 830576.
- 31.
If the OLTP table compression is used with Oracle Release 11.2.0.1,
this may lead to a large number of buffer gets and a high CPU
consumption. Therefore, the OLTP table compression is permitted in the
SAP environment only as of Oracle 11.2.0.2 (Note 1436352).
- 32.
With Oracle 11.2.0.2 and 11.2.0.3 also, DML operations on
OLTP-compressed tables in ASSM tablespaces may cause a very large number
of Buffer Gets and high CPU consumption (for example, due to bug
13641076 or 14762511). Reorganize the affected tables either in an MSSM
tablespace and/or create them again in uncompressed form (for example,
using BRSPACE online reorganization and option "-c dtab").
- 33.
If changes are made to data in the same block even as it is accessed,
the consistent reading mechanism and its associated access to CR blocks
("Consistent Change") may lead to significantly increased block
accesses. Refer to SAP Note 913247 for more detailed information.
- 34.
If the previous reasons do not apply, you can use several error stack
dumps to find out more exact details on which source code areas most
often contain a CPU-consuming process. To do this, also refer to Note
613872, which among other things describes how you can create the
current call stack of a session. You may be able to draw conclusions
about the trigger from the resulting traces.
Header Data
| Released On | 03.01.2013 09:18:26 |
| Release Status | Released for Customer |
| Component | BC-DB-ORA Oracle |
| Priority | Recommendations / Additional Info |
| Category | Help for error analysis |
No comments:
Post a Comment