Basis and others: Note 712624 - High CPU consumption by Oracle

Summary

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

Therefore, depending on the current load, high CPU usage by Oracle processes is quite normal. However, if you notice the following, it may indicate a problem regarding 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.

The following factors may be responsible for a high CPU load by Oracle:

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.

              In more recent releases, the Shared Cursor Cache analysis in transaction ST04 or ST04N also displays in the "CPU Time" column, which specifies in microseconds the CPU time caused by the statement. Sort the SQL statements according to this column and check those with the highest values.

              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.

              See Note 651060 regarding Parallel Query and make sure that the CPU resources can handle the potential additional load caused by Parallel Query.

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.

              You must therefore consider the following points, if you find there are time-consuming accesses to Oracle DDIC views:

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.

              See also Note 697483.

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.

              When you use Oracle 10.2.0. 2 and have a large number of parsed statements (more than 100 per second), bug 5443568 may also be responsible for an unnecessary CPU overhead when parsing.

              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


Release Status:	Released for Customer
Released on:	03.01.2013 09:18:26
Master Language:	German
Priority:	Recommendations/additional info
Category:	Help for error analysis
Primary Component:	BC-DB-ORA Oracle

Affected Releases

Release-Independent

Related Notes

Note 712624 - High CPU consumption by Oracle

No comments: