Basics of hard parsing
You surely came across this too many times: hard parsing due to literals.
Let’s start from the basics:
SQL> set feedback on sql_id
SQL> select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:10';
no rows selected
SQL_ID: 8gb3f1q98run2
SQL> select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:11';
no rows selected
SQL_ID: 8539hwragf3ag
The direct reason for not sharing these two queries (SQL_IDs) are the literals.
In this case ‘2026-07-07 13:21:10' and ‘2026-07-07 13:21:11‘ make those two statements different (see different SQL_IDs: 8gb3f1q98run2 and 8539hwragf3ag), incurring hard parsing.
Excessive hard parsing (so, from admin’s perspective, hammering cpu) is not the only sacrifice though.
Something which always accompanies it, is the inevitable shared pool fragmentation which – by itself – is also related to wasted CPU cycles.
Why fragmentation? Because such unique cursor (and its numerous companions) needs to be placed “somewhere”.
This “somewhere” is obviously the region within the SGA: shared pool.
So, these two go together like Bonnie and Clyde.
It is difficult to say which one is worse, since, essentially, we are caught between a rock and a hard place, to use another idiomatic expression.
The problem becomes particularly apparent when thousands of such SQL statements are executed within a relatively short period of time, especially under high concurrency.
This is when we typically encounter the infamous ORA-04031 error, which generally looks something like this:
ORA-04031: unable to allocate 65560 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","KJSC rnb slots")
or even more nasty :
ORA-04031: unable to allocate bytes of shared memory ("","","","")It’s worth to mention that the predicates which are time-based are particularly prone to increase the probability of shared pool fragmentation.
Reason being – following the example above – if the business model is sending their sql requests with a predefined frequency, e.g. of 1 second, then the predicate will be assigned a different value with each such SQL-statement, resulting in placing a new (and unique) cursor in the shared pool.
Ideally, the application should use bind variables, being the placeholders for the ever-changing literals, thus making those cursors sharable.
It is worth mentioning that time-based predicates are particularly prone to contributing to shared pool fragmentation.
The reason is fairly straightforward. Following the example above, if the application uses some form of polling, it submits SQL statements at a predefined frequency, say, once per second, the time-based predicate will contain a different literal value with each execution.
Consequently, each such statement will result in a new, distinct parent cursor being created in the shared pool, even though the SQL statements are otherwise essentially identical.
Ideally, the application should use bind variables as placeholders for these ever-changing literal values, thereby allowing the cursors to be shared and reused.
The goal is, instead of potentially many of thousands of them, there would be just one, greatly decreasing both, the CPU overhead and the shared pool fragmentation.
Unfortunately, for number of reasons, it’s often not possible to change the code and replace the literals with the bind variables.
To address this deficiency, Oracle introduced the instance parameter: cursor_sharing.
The default value of this parameter is set by default to EXACT, meaning, no changes are to be made to the incoming SQL queries.
Should the problem of many unique cursors be not resolvable through changing the code, setting cursor_sharing to FORCE will replace the literals of such SQLs with on-the fly system generated bind variables.
Thus, with relatively little effort (and admittedly some limitations), the desired result is achieved.
Needless to mention, such problem of SQL queries which use the literals instead of bind variables comes much too often.
Let’s finally get to my problem.
The almost original query (I changed sensitive data, so also literals are fictious) looked like this :
1.
select max(sttime) as max_sttime from OWNER.TABLE
where job_rc = 'MY_ANOTHERJOB_001' and upper(jobname) = 'MY_JOBNAME_00' and sttime < TIMESTAMP '2026-04-01 00:00:00'
Then another one was :
2.
select max(sttime) as max_sttime
from
( select max(sttime) as sttime from OWNER.TABLE01
where job_rc = 'MY_VERYSEC_JOB_001'
and upper(jobname) = 'MY_SECRET_JOBNAME_00'
and sttime < TIMESTAMP '2026-04-01 00:00:00'
union
select max(seccol_stime) as sttime from OWNER.TABLE02
where seccol_status_code = 'MY_SECRET_STATUS_001'
and seccol_stime < '2026-04-02 00:00:00'
) alljobs
Since these queries were all of sudden got executed with much higher concurrency than usual, the ORA-4031 came up repeatedly.
As changing the code, thus introducing bind variables couldn’t be considered, the decision seemed obvious: alter system set cursor_sharing = FORCE scope=both sid='*'
Albeit, as mentioned, cursor_sharing set to FORCE has its own disadvantages, it’s been the matter of compromise.
As a result of alter system set cursor_sharing = FORCE scope=both sid=’*’ we achieved:
1.
select max(sttime) as max_sttime from OWNER.TABLE
where
(job_rc = :"SYS_B_0"
and upper(jobname) = :"SYS_B_1"
and sttime < TIMESTAMP :"SYS_B_2"
)
2.
select max(sttime) as max_sttime
from
(
select max(sttime) as sttime from OWNER.TABLE01
where job_rc = :"SYS_B_0"
and upper(jobname) = :"SYS_B_1"
and sttime < TIMESTAMP :"SYS_B_2"
union
select max(seccol_stime) as sttime from OWNER.TABLE02
where seccol_status_code = :"SYS_B_3"
and seccol_stime < TIMESTAMP :"SYS_B_4"
) alljobsEspecially the second SQL converted to the version with binds has awaken hopes as it involved more complex SQL Plan, so potentially it was to save more CPU cycles.
Considering parsing effort, it’s easy to check with the SQL 10053 trace how much more work (CPU cycles) is engaged once there are joins involved.
So, now neither of them contains any literals since they got replaced with the change of cursor_sharing to FORCE (see all occurrences of SYS_B_x).
At first glance, the problem appears to have been resolved, at least as far as ORA-4031 is concerned.
Unfortunately, solving one problem has introduced another, arguably even more severe one.
Namely, with each execution and the subsequent replacement of literals with system-generated bind variables, Oracle decided to create yet another child cursor under the same parent cursor, effectively exacerbating shared pool fragmentation.
So, instead of the original “horizontal” problem, Oracle introduced what could be described as a “vertical” one, arguably even more severe, ultimately spawning many thousands of child cursors under a single parent cursor, rather than creating a comparable number of distinct SQL_IDs.
A closer examination revealed that the proliferation of child cursors was caused by the use of the ANSI SQL timestamp literal syntax: TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'.The expression TIMESTAMP is obviously fully accepted within the predicates’ section and returns the expected results.
Then later I came across several articles which clearly show that the problem isn’t new.
Example: https://jonathanlewis.wordpress.com/2016/05/16/cursor_sharing-problem
I have even realized (it’s mentioned in the same article) Oracle qualified it as Bug 13407937 : HIGH VERSION COUNT FOR INSERT WITH CURSOR_SHARING=FORCE AND TIMESTAMP COLUMN.
A bit striking, the article is dated back to 2016, so roughly Oracle ver. 11/12, but the problem is still here, so with Oracle ver. 19 (Version 19.20.0.0.0, to be precise).
So much about bugs and versions (but not about references, some interesting articles and their links are still to come).
As the reality often shows, we need to deal with it “as it is”, not really counting on patches, fixes, and the like.
Then I have learned (I guess, with trial and error)
once I replaced the TIMESTAMP with TO_TIMESTAMP, the problem didn’t occur.To be precise – once I carried out the following changes, the problem was gone:
- First change: cursor_sharing = FORCE (this took care of replacing of all literals in the predicate section with on-the-fly generated bind variables)
- Second change: replaced all occurrences of
TIMESTAMPwithTO_TIMESTAMP(this stopped spawning storm of child cursors)
Worth mentioning :
The same issue can be spotted while applying DATE keyword using notation like this:
SQL> select * from DELME.TEST4031 where BUSINESS_DATE = DATE '2026-04-12';
Changing DATE 'string-date-format' to its counterpart: TO_DATE also made the problem go away.So, the challenge was to replace TIMESTAMP with TO_TIMESTAMP (without directly changing the code) keeping the system generated bind variables in place, being the result of setting the cursor_sharing to FORCE.
Let’s go through the entire test
First, the following table has been created and populated, although populating it with any data is meaningless, or even not recommended.
As you can see, any table would do, even a much simpler one. I just happened to have this one left over from my previous tests.
All in all, we don’t intend to retrieve any data – instead, we need to create a storm of unique SQL statements.
That saying, if we don’t hit any data, we achieve the creating of the problem of flooding of the shared pool even faster.
Then, the following index on BUSINESS_TS was created (it makes sense if you decide to populate the table):
CREATE INDEX DELME.IDX_TS ON DELME.TEST4031 (BUSINESS_TS) ;Let us zoom in :
Keeping the cursor_sharing = EXACT (default), I will simply execute two instances of nearly the same query, just changing the literals :
SQL> set feedback on sql_id
SQL> select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:20';
no rows selected
SQL_ID: bwp037f46fr89
SQL> select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:21';
no rows selected
SQL_ID: 2g8ag0w26b13d
The two SQL_ID differ (bwp037f46fr89 and 2g8ag0w26b13d), because of different literals (see ‘2026-07-07 13:21:20‘ and ‘2026-07-07 13:21:21‘).
Then, assuming we can’t change this behavior at the application level, let’s apply the band-aid and alter the cursor_sharing = FORCE.
SQL> alter system set cursor_sharing = FORCE scope=both sid='*'
System altered.
Finally, let’s execute the same family of queries again and keep changing the literals (see changed 30,31,32) :
SQL> set feedback on sql_id
SQL> select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:30';
no rows selected
SQL_ID: 8j6nb5xa3s5g5
SQL> select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:31';
no rows selected
SQL_ID: 8j6nb5xa3s5g5
SQL> select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:32';
no rows selected
SQL_ID: 8j6nb5xa3s5g5
All executions got the same SQL_ID: 8j6nb5xa3s5g5 and obviously the same sql hash value.
The SQL_TEXT derived from v$sql for the SQL_ID = 8j6nb5xa3s5g5 is as expected :
select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"Great – problem solved.
Well, at least until we take a closer look at V$SQL_SHARED_CURSOR and discover a rather disturbing oddity (for which I will use Tanel’s extremely useful PIVOT helper).
SQL> @why
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
Enter value for sqlid: 8j6nb5xa3s5g5
old 17: replace( '&cmd', '"', ''''),
new 17: replace( 'select * from v$sql_shared_cursor where sql_id = ''8j6nb5xa3s5g5''', '"', ''''),
SQL_ID : 8j6nb5xa3s5g5
ADDRESS : 00000000A89F7AC0
CHILD_ADDRESS : 00000001FBFF9050
CHILD_NUMBER : 0
HASH_MATCH_FAILED : Y
REASON :
CON_ID : 0
-----------------
SQL_ID : 8j6nb5xa3s5g5
ADDRESS : 00000000A89F7AC0
CHILD_ADDRESS : 00000000AB3FD6A0
CHILD_NUMBER : 1
HASH_MATCH_FAILED : Y
REASON :
CON_ID : 0
-----------------
SQL_ID : 8j6nb5xa3s5g5
ADDRESS : 00000000A89F7AC0
CHILD_ADDRESS : 00000002034D1AF8
CHILD_NUMBER : 2
HASH_MATCH_FAILED : Y
REASON :
CON_ID : 0
See what happened:
With each and every SQL execution another child cursor got spawned.
Obviously, this is just as bad as before, if not worse. Instead of having numerous distinct SQL_IDs, we now end up with numerous child cursors associated with the same parent cursor.
In fact, this might be even worse, and I hope to demonstrate precisely why with another test. So, we are back to square one.
As already pointed out, once the predicate such as: TIMESTAMP ‘2026-07-07 13:21:32’ gets replaced with the following equivalent: TO_TIMESTAMP (‘2026-07-07 13:21:32’, ‘YYYY-MM-DD HH24:MI:SS’) with cursor_sharing set to FORCE, the problem magically goes away.
Now, there are two choices:
- Wait until this bug will be fixed (it’s being fixed since 2016 or earlier).
Again, it manifests itself in the same way once DATE (instead of TO_DATE) is used in the predicate section. - Look for alternative.
That alternative might be something I believe is a somewhat underestimated yet powerful tool: the SQL Translation Framework.
Introduction to the SQL Translation Framework
It has originally been developed and promoted as a (SQL) migration tool which enables rewriting of the SQL syntax when migrating from, e.g., MS SQL to Oracle.
There are numerous articles explaining this functionality, concepts and configuration, to name one particularly useful written by Kerry Osborne.
There are also numerous examples of using the SQL Translation Framework for purposes similar to mine. In other words, rather than translating SQL syntax when migrating from one database platform (say, MS SQL Server) to Oracle, we can simply modify the syntax of SQL statements while remaining entirely within the Oracle environment, without having to modify the underlying application.
And yes, this may cause quite a bit of confusion, since the original (pre-translation) SQL statement does not even appear as a separate cursor in the shared pool.
In a way, you might think of the SQL Translation Framework as conceptually similar to a SQL Patch, although the two operate at different stages of SQL processing.
However, while a SQL Patch typically injects optimizer hints into a SQL statement, the SQL Translation Framework can actually rewrite the SQL text itself, based on predefined translation rules (or patterns).
There is also significant but completely legitimate (deliberately imposed) limitation of using SQL Translator Framework and I will mention it in the subsequent post (Part-2).
Part 2 provides a real-world example of how I addressed the problem using the SQL Translation Framework, specifically targeting the previously observed deficiency in handling TIMESTAMP literals and thereby mitigating the effects of the aforementioned Oracle bug.
It also includes a slightly more in-depth performance analysis, supported by SQL trace (event 10046).
Leave a Reply