When using Oracle 10g high CPU utilization may occur on the DBMS server (the server node running Oracle 10g).
For example, after upgrading from Oracle 9i to Oracle 10g, a system's administrator may notice an increase in the CPU utilization on the server when there has been no change in the application or typical workflow being performed by the organization.
The behavior can be noticed when working with versioned classes using either the ArcSDE application server or direct connect. The CPU usage is dependent on the number of concurrent users executing queries. Some users may not notice the increased CPU usage, solely based on the number of users accessing the Oracle server.
The cause of the problem is Oracle bug 6455161 - "Higher CPU / Higher "cache buffer chains" latch gets / Higher "consistent gets" after truncate/Rebuild".
See Oracle's Metalink for the detailed description of the bug.
The problem was introduced in Oracle 10.1.0.2. The problem is specific to SQL statements' access paths that use NESTED LOOPS, which are commonly used with geodatabase versioned queries. For each iteration of the NESTED LOOP, additional CPU usage is consumed while performing more 'cache buffer chains', so the latch becomes exposed with a larger than expected 'CONSISTENT GETS' statistic (located in the 'query' column on tkprof).
The problem is exposed when an index is rebuilt using the ALTER INDEX REBUILD command or when a table is truncated.
To identify the list of objects impacted by this problem execute the following SQL statement in SQL*Plus as a user with DBA privileges:
and object_type like 'INDEX%'
order by owner,object_name,subobject_name;
It is very likely that many of the versioned table's indexes will be returned from the above query. The reason is that it is best practice to rebuild delta table indexes nightly to ensure the number of blocks for the index are kept as small as possible.
Solution or Workaround
The Oracle bug has been fixed in a patch set available on top of the Oracle 10.2.0.4 release. Once again, visit Oracle's Metalink Web site to obtain the patch for the platform being used.
The suggested workaround to the problem provided by Oracle is to drop and re-create all indexes which OBJECT_ID <> DATA_OBJECT_ID. This synchronizes the two values.
It is not sufficient to REBUILD the index, it must be explicitly dropped and re-created.