Intro
The Issue: Excessive SQL Versioning and Hard Parses
The ExaCS database was receiving a continuous stream of dynamically
structured INSERT statements
from Kafka. The column structure of these inserts varied significantly; some
contained 100 columns, while others had up to 150. This variation was
ongoing and unpredictable.
-
Excessive hard parses
-
High CPU utilization
-
Shared pool contention and pressure
Even though we were running Oracle
19.24, which includes
enhancements to SQL plan management and version count handling, the
optimizer still created new cursor versions for each structurally distinct
INSERT, which led to rapid
cursor growth and degraded overall performance.
Temporary Workaround: Manual Flushing of High Version Count Cursors
As an immediate mitigation step, we identified the SQLs with high version counts and manually flushed them from the shared pool using their memory IDs. This helped temporarily relieve pressure on CPU and memory by:
-
Reducing shared pool bloat
-
Freeing up memory consumed by excessive cursor versions
-
Preventing further hard parsing on the same overloaded SQL
INSERT statements with
varying structures continue streaming in from Kafka.
[oracle@exaprd01-node01 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
34697081;NOT SHIPPING LIBAUTH_SDK_IAM.SO IN 23 SHIPHOME INSTALL
36538667;JDK BUNDLE PATCH 19.0.0.0.240716
36414915;OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)
36587798;OCW RELEASE UPDATE 19.24.0.0.0 (36587798)
36582781;Database Release Update : 19.24.0.0.240716 (36582781)
OPatch succeeded.
[oracle@exaprd01-node01 ~]$
SELECT version_count, sql_id, sql_text FROM v$sqlarea WHERE version_count > 512;
select inst_id,ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '&sqlid';
exec sys.DBMS_SHARED_POOL.PURGE ('-ADDRESS-, -HASH-VALUE-', 'C');
Permanent Solution
To address the high version count issue more permanently, Oracle provides specific guidance in the following My Oracle Support note:
High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance Doc ID 2431353.1
As per the recommendation, you should add the following initialization parameter _cursor_obsolete_threshold = <recommended_value>
This parameter helps control the number of obsolete child cursors and can significantly reduce version count growth, improving shared pool performance and overall database stability.
If you’re running in a RAC environment, apply this change and restart the database instances in a rolling fashion to avoid downtime.
alter system set “_cursor_obsolete_threshold”=1024 scope=spfile;
