Oracle 10g servers experiencing high CPU usage with versioned queries

Description

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.

Cause

The cause of the problem is Oracle bug 6455161 - "Higher CPU / Higher "cache buffer chains" latch gets / Higher "consistent gets" after truncate/Rebuild".

Note:
 See Oracle's Metalink for the detailed description of the bug.

Code:
select owner||'.'||Object_name||'.'||subobject_name
    from dba_objects
    where object_id<>data_object_id
      and object_type like 'INDEX%'
  order by owner,object_name,subobject_name;

Solution or Workaround

Note:
It is not sufficient to REBUILD the index, it must be explicitly dropped and re-created.