High Version Count Causing High CPU Usage

SYMPTOM

The database experiences an application slow down or the Capacity Planning Team identify an unusual increase in CPU activity, in particular due to high CPU being consumed by a particular query.

CAUSE

Because of the nature of the application, JDBC and the way Oracle manages child cursors, it is possible that the server is slowing down, sometimes almost hanging, due to the high number of versions of this code that it is having to keep in the library cache.

WORKAROUND FIX 1

  • Log on to the affected database as SYS and issue the following command:
SELECT 
   COUNT(*), sql_id 
FROM 
   v$sql 
WHERE 
   sql_text LIKE '%<some_identifiable_text_from_the_SQL>%' 
GROUP BY 
   sql_id
/
  • If the result of the above query shows a number count for the affected sql_id (e.g. 9jt2rzcu98qhq) as high, i.e. in excess of 1,000, then an immediate improvement to the system can be achieved by issuing the following command:
ALTER SYSTEM FLUSH SHARED_POOL
/
  • Confirm the number of versions has gone down, by re-issuing the query from Step 1.
    • Note: This query may return no rows selected initially until the query is next called by the application.

WORKAROUND FIX 2

  • Log on to the affected database as SYS and issue the following command:
ALTER SYSTEM SET "_optimizer_use_feedback"=FALSE SCOPE=BOTH
/

LONG TERM FIX

This is a known issue with earlier versions of Oracle which is reportedly fixed in version 11.2.0.3 and later.

In the meantime, create a scheduled job which will the flush the shared pool each day, thereby hopefully preventing the build up of so many versions and thus avoiding the problem.

© copyright 2001-2014 ABCdba.com | all rights reserved