Piotr Sajda Oracle Tuning Blog

SQL Translation Framework – Tackling ORA-04031 (Part 3)

,

Background: How SQL Translation Framework Became the Solution to ORA-04031

I recommend reading Part 1 first for a basic understanding of hard parsing internals, followed by Part 2 to familiarize yourself with the scripts and tools used in this article.

In the nutshell, the sequence of events was as follows :

  1. While working with SQL statements like the one below (note that the ANSI TIMESTAMP literal syntax is particularly relevant here), I encountered a problem with excessive hard parsing, often manifesting itself as ORA-04031 error, particularly when processing large numbers of such statements differing only in their literal values:
    select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2025-06-01 11:09:02'
  2. Next, I set CURSOR_SHARING=FORCE, which resulted in the following SQL statement:
    select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"
    At first glance the problem seemed solved.
  3. However, due to an apparent Oracle bug, I observed a storm of child cursors being spawned as the SQL statement shown above was repeatedly executed.
    With each execution, yet another child cursor was created (with an incrementally higher child number), dramatically increasing shared pool memory consumption and potentially aggravating fragmentation, ultimately causing ORA-04031 to occur even sooner.
    Consequently, rather than solving the original problem, CURSOR_SHARING=FORCE actually exacerbated it, making the fragmentation of shared pool even more severe.
  4. Then I discovered that executing a semantically equivalent version of the SQL statement from step (2), as shown below, resolved both problems: excessive hard parsing and the proliferation of child cursors.
    In other words, this simple modification made both problems disappear:
    select ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
  5. The challenge was this:
    without access to the application’s source code, I needed to find a way to transform SQL statement (2) into SQL statement (4), without modifying the underlying application.
  6. SQL Translation Framework turned out a satisfactory solution and this Part-3 of the whole series depicts what I did.

SQL Translation Framework in Action

Assumed is, we are at (2), so the cursor_sharing = FORCE has been set and the literals were replaced with the bind variable :SYS_B_0.
That means, we achieved the SQL statements which look like this :

select ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"

As mentioned in Part-1, I worked with other SQL statements, but our test example illustrates the entire process well enough.
Below is the mini-framework I used to test and tailor the SQL Translation Framework to my needs.
The most important part is the definition of the SQL translation pattern, which specifies both the source and target SQL statements.
In our case, the key change involves replacing the TIMESTAMP literal syntax with the TO_TIMESTAMP function, while ensuring that the resulting SQL conforms to Oracle’s syntax requirements.

Here it is :

BEGIN
  DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
    profile_name =>
      'SQL_TRANS4TS_JAVATEST',
    sql_text =>
      q'[select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"]',
    translated_text =>
      q'[select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')]',
    enable => TRUE
  );
END;
/

Let’s walk through the mini-framework presented below and subsequently, let’s run it.
It provides a convenient way to follow the entire process step by step and observe what happens at each stage.
Specifically, it performs the following steps:

  1. It creates the SQL translation profile: DBMS_SQL_TRANSLATOR.CREATE_PROFILE
  2. It then register the SQL translation profile: DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION
  3. It verifies the SQL-translation profile registration: user_sql_translations
  4. it attaches the created profile to the current session:
    ALTER SESSION SET SQL_TRANSLATION_PROFILE = SQL_TRANS4TS_JAVATEST; ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';
  5. It executes the test SQL query:
    select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:10'
  6. At the end it verifies the original and the mapped SQL: v$mapped_sql

Although v$mapped_sql gives us convenient way to verify which translation took place, the live confirmation that the SQL translation framework really works is the (5).

Namely, we will run the SQL statement as follows:
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:10'

First, this SQL will be transformed to the following one (thanks to cursor_sharing set to FORCE):
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"

Subsequently, this intermediate stage of the original SQL statement will be translated to : select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS') – and this is the moment in which the SQL Translator Framework kicks in.


Here is the entire my mini-framework.
Further below is the example of running it.

# cat crsqltrans_forts_javatest.sql 

-- ============================================================
prompt - 0. delete translation-profile (if you need to – goal is to obtain the fresh environment).
-- ============================================================

exec  dbms_sql_translator.DROP_PROFILE('&sql_transl_profile_to_drop') ;


-- ============================================================
prompt - 1. Create translation profile
-- ============================================================

PAUSE Press ENTER to continue...

BEGIN
  DBMS_SQL_TRANSLATOR.CREATE_PROFILE( profile_name => 'SQL_TRANS4TS_JAVATEST');
END;
/

-- ============================================================
prompt -- 2. Register original -> replacement
-- ============================================================

PAUSE Press ENTER to continue...

BEGIN
  DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
    profile_name =>
      'SQL_TRANS4TS_JAVATEST',
    sql_text =>
      q'[select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"]',
    translated_text =>
      q'[select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')]',
    enable => TRUE
  );
END;
/

-- ============================================================
prompt -- 3. Verify registration
-- ============================================================

PAUSE Press ENTER to continue...

SET LONG 100000

SELECT profile_name,
       sql_id,
       enabled,
       sql_text,
       translated_text
FROM   user_sql_translations
WHERE  profile_name = 'SQL_TRANS4TS_JAVATEST'
@pr

-- ============================================================
prompt -- 4. Attach profile to session
-- ============================================================

PAUSE Press ENTER to continue...

ALTER SESSION SET SQL_TRANSLATION_PROFILE = SQL_TRANS4TS_JAVATEST;
ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

set feedback on sql_id
set echo on

select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:10';

-- ============================================================
prompt -- 7. Verify original -> mapped SQL
-- ============================================================

PAUSE Press ENTER to continue...

SELECT sql_id,
       mapped_sql_id,
       sql_text,
       mapped_sql_text,
       translation_method,
       use_count
FROM   v$mapped_sql
WHERE  UPPER(sql_text) LIKE '%DELME.TEST4031%'
@pr

The @pr is the Tanel’s another little helper which depicts the output in the pivotal form.

The live example of testing and verifying the entire translation process is below.
Login as the “Application” user (my test app-user is DELME).

myhostname # sqlplus DELME

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 24 14:26:03 2026
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Enter password: ***********
Last Successful login time: Sat Oct 24 2026 13:51:46 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0



13:52:25 DELME @ TESTDB> @crsqltrans_forts_javatest.sql
13:52:27 DELME @ TESTDB> -- ============================================================
- 0. delete translation-profile (if you need to – goal is to obtain the fresh environment).
13:52:27 DELME @ TESTDB> -- ============================================================
13:52:27 DELME @ TESTDB> exec  dbms_sql_translator.DROP_PROFILE('&sql_transl_profile_to_drop') ;
Enter value for sql_transl_profile_to_drop: SQL_TRANS4TS_JAVATEST

PL/SQL procedure successfully completed.


13:52:42 DELME @ TESTDB> -- ============================================================
- 1. Create translation profile
13:52:42 DELME @ TESTDB> -- ============================================================
13:52:42 DELME @ TESTDB>
13:52:42 DELME @ TESTDB> PAUSE Press ENTER to continue...
Press ENTER to continue...

13:52:44 DELME @ TESTDB> BEGIN
13:52:44   2    DBMS_SQL_TRANSLATOR.CREATE_PROFILE( profile_name => 'SQL_TRANS4TS_JAVATEST');
13:52:44   3  END;
13:52:44   4  /

PL/SQL procedure successfully completed.

13:52:44 DELME @ TESTDB> -- ============================================================
-- 2. Register original -> replacement
13:52:44 DELME @ TESTDB> -- ============================================================
13:52:44 DELME @ TESTDB>
13:52:44 DELME @ TESTDB> PAUSE Press ENTER to continue...
Press ENTER to continue...

13:52:48 DELME @ TESTDB> BEGIN
13:52:48   2    DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
13:52:48   3      profile_name =>
13:52:48   4        'SQL_TRANS4TS_JAVATEST',
13:52:48   5      sql_text =>
13:52:48   6        q'[select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"]',
13:52:48   7      translated_text =>
13:52:48   8        q'[select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')]',
13:52:48   9      enable => TRUE
13:52:48  10    );
13:52:48  11  END;
13:52:48  12  /

PL/SQL procedure successfully completed.


13:52:48 DELME @ TESTDB> -- ============================================================
-- 3. Verify registration
13:52:48 DELME @ TESTDB> -- ============================================================
13:52:48 DELME @ TESTDB>
13:52:48 DELME @ TESTDB> PAUSE Press ENTER to continue...
Press ENTER to continue...

13:52:55 DELME @ TESTDB>
13:52:55 DELME @ TESTDB> SET LONG 100000
13:52:55 DELME @ TESTDB>
13:52:55 DELME @ TESTDB> SELECT profile_name,
13:52:55   2         sql_id,
13:52:55   3         enabled,
13:52:55   4         sql_text,
13:52:55   5         translated_text
13:52:55   6  FROM   user_sql_translations
13:52:55   7  WHERE  profile_name = 'SQL_TRANS4TS_JAVATEST'
13:52:55   8  @pr
13:52:55   8

==============================
PROFILE_NAME                  : SQL_TRANS4TS_JAVATEST
SQL_ID                        : 1d5kz05v57vkv
ENABLED                       : TRUE
SQL_TEXT                      : select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"
TRANSLATED_TEXT               : select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
=========================
Number of records: 5

PL/SQL procedure successfully completed.


13:53:00 DELME @ TESTDB> -- ============================================================
-- 4. Attach profile to session
13:53:00 DELME @ TESTDB> PAUSE Press ENTER to continue...
Press ENTER to continue...

13:53:10 DELME @ TESTDB> ALTER SESSION SET SQL_TRANSLATION_PROFILE = SQL_TRANS4TS_JAVATEST;

Session altered.

13:53:10 DELME @ TESTDB> ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

Session altered.

13:53:10 DELME @ TESTDB> set feedback on sql_id
13:53:10 DELME @ TESTDB> set echo off
13:53:10 DELME @ TESTDB>
13:53:10 DELME @ TESTDB> select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:10';

no rows selected

SQL_ID: 96w3th0pvwxr2
13:53:10 DELME @ TESTDB>
13:53:10 DELME @ TESTDB> -- ============================================================
-- 7. Verify original -> mapped SQL
13:53:10 DELME @ TESTDB> -- ============================================================
13:53:10 DELME @ TESTDB>
13:53:10 DELME @ TESTDB> PAUSE Press ENTER to continue...
Press ENTER to continue...

13:53:36 DELME @ TESTDB> SELECT sql_id,
13:53:36   2         mapped_sql_id,
13:53:36   3         sql_text,
13:53:36   4         mapped_sql_text,
13:53:36   5         translation_method,
13:53:36   6         use_count
13:53:36   7  FROM   v$mapped_sql
13:53:36   8  WHERE  UPPER(sql_text) LIKE '%DELME.TEST4031%'
13:53:36   9  @pr
13:53:36   9

==============================
SQL_ID                        : 1d5kz05v57vkv
MAPPED_SQL_ID                 : 96w3th0pvwxr2
SQL_TEXT                      : select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP :"SYS_B_0"
MAPPED_SQL_TEXT               : select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
TRANSLATION_METHOD            :
USE_COUNT                     : 1
==============================

PL/SQL procedure successfully completed.

As mentioned, here is the confirmation that the following two-step process kicked in.
I have executed the following statement :

13:53:10 DELME @ TESTDB> select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TIMESTAMP '2026-07-07 13:21:10';

no rows selected

SQL_ID: 96w3th0pvwxr2

Then the reported and truly executed (and optimized!) SQL statement is the one with SQL_ID: 96w3th0pvwxr2 (originally, it would be: 1d5kz05v57vkv).
The SQL_TEXT for that SQL_ID is :

SYS @ TESTDB> select SQL_FULLTEXT from gv$sql where sql_id = '96w3th0pvwxr2';

SQL_FULLTEXT
---------------------------------------------------------------------------------------------------------------------------
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')


This is where some confusion may arise.

The SQL Translation Framework kicks in at a very early stage of SQL processing, meaning that the original SQL statement never even makes it into the shared pool.
But that’s not all.
Consequently, it is the translated SQL statement that undergoes optimization by the Cost-Based Optimizer (CBO), rather than the original one.
This is certainly worth considering when defining SQL mappings using DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION, particularly the sql_text (original SQL) and translated_text (target SQL) parameters.
What if, for example, the translated SQL suddenly introduces additional predicates or (certainly much worse) even eliminates an existing one that previously worked well?
Such changes could significantly affect the execution plan and, ultimately, SQL performance.

Let’s Automate It! Using an AFTER LOGON Trigger

Now it’s time to automate the entire process.
The most popular way seems using of the AFTER LOGON trigger, sort of :

# cat cr_logontrigger_to_set_sqltranslator_test4031_javatest.sql

CREATE OR REPLACE TRIGGER TRG_LOGON_SQLTRANS_JAVATEST
AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'DELME' THEN
    execute immediate 'alter session set sql_translation_profile = DELME.SQL_TRANS4TS_JAVATEST';
    execute immediate q'[
                         alter session set events = '10601 trace name context forever, level 32'
                      ]';
  END IF;
END;
/

The complete automated process follows these steps:

  1. Create the SQL translation profile : DBMS_SQL_TRANSLATOR.CREATE_PROFILE
  2. Register the SQL translation profile : DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION
  3. Create and enable an AFTER LOGON trigger that automatically associates the SQL Translation Profile, including its predefined SQL mappings, with all sessions intended to benefit from the SQL Translation Framework.

Let’s see it all in action :

SYS @ OSTESTA1> @cr_logontrigger_to_set_sqltranslator_test4031_javatest.sql

Trigger created.

SYS @ OSTESTA1> l
  1  CREATE OR REPLACE TRIGGER TRG_LOGON_SQLTRANS_JAVATEST
  2  AFTER LOGON ON DATABASE
  3  BEGIN
  4    IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'DELME' THEN
  5      execute immediate 'alter session set sql_translation_profile = DELME.SQL_TRANS4TS_JAVATEST';
  6      execute immediate q'[
  7                           alter session set events = '10601 trace name context forever, level 32'
  8                        ]';
  9    END IF;
10* END;

Now, with the SQL Translation Framework activated (so, SQL Translation profile created, and SQL mapping defined) and the trigger enabled, let us finally run my mini Java test code (see the Part 2 for details):

SYS @ OSTESTA1> alter system flush shared_pool ;

System altered.

# time java -cp .:/mytest/JAVA/ojdbc8.jar TestSqlTranslator

real    0m2.664s
user    0m3.500s
sys     0m0.664s

Tremendous improvement.

Let’s summarize it :

Running my mini-Java code with the original problem, that is, with literals (cursor_sharing = EXACT):
real    0m12.664s
user    0m3.647s
sys     0m0.607s

Running my mini-java code without literals, but with storm of child cursors spawned on behalf of single SQL_ID (cursor_sharing = FORCE):
real    0m31.727s
user    0m3.684s
sys     0m0.618s

Running my mini-Java code without literals and with SQL Translator Framework
(cursor_sharing = FORCE and SQL Translator Framework defined and enabled):
real    0m2.664s
user    0m3.500s
sys     0m0.664s

Let’s take a closer look at why these 10,000 SQL statements executed so much faster.
To understand what happens under the hood, we’ll turn once again to the 10046 trace (raw SQL trace) from the latest execution.
To make tracing easier to intercept, I simply increased the number of executions from 10,000 to 1,000,000, recompiled the Java code, and ran it again.
Naturally, this made the execution considerably longer, but it gave me enough time to identify the SID and SERIAL# of the Java session (query V$SESSION, filtering, for example, by PROGRAM = 'JDBC Thin Client' and USERNAME = 'DELME').

Here is the change in the Java code I performed:  

        for (int i = 1; i <= 1000000; i++) {

            String sql =
                    "select /* my_java_testsql */ ID " +
                    "from DELME.TEST4031 " +
                    "where BUSINESS_TS = TIMESTAMP '" +
                    ts.plusSeconds(i).format(fmt) +
                    "'";

            stmt.execute(sql);
        }

My 10046 Tracing mini-script :  tr.sql

set echo on
ALTER SESSION SET max_dump_file_size = unlimited;
exec dbms_monitor.session_trace_enable(session_id=>&sid_to_trace,serial_num=>&serial_to_trace,binds=>true,waits=>true);

Already given in the Part-2, small yet representative excerpt of the raw 10046 (SQL-Trace) from the last Java-code execution, so with cursor_sharing = FORCE and SQL Translator Framework defined and enabled.

=====================
PARSING IN CURSOR #139635562442496 len=122 dep=0 uid=102 oct=3 lid=102 tim=49600306163248 hv=733902562 ad='2185c6098' sqlid='96w3th0pvwxr2'
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
END OF STMT
PARSE #139635562442496:c=34,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163248
BINDS #139635562442496:

Bind#0
  oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7eff701be438  bln=32  avl=19  flg=09
  value="2025-06-01 11:09:01"
EXEC #139635562442496:c=82,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163378
WAIT #139635562442496: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306163398
FETCH #139635562442496:c=7,e=8,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163422
STAT #139635562442496 id=1 cnt=0 pid=0 pos=1 obj=393714 op='TABLE ACCESS BY INDEX ROWID BATCHED TEST4031 (cr=3 pr=0 pw=0 str=1 time=12 us cost=476 size=43952 card=2747)'
STAT #139635562442496 id=2 cnt=0 pid=1 pos=1 obj=393715 op='INDEX RANGE SCAN IDX_TS (cr=3 pr=0 pw=0 str=1 time=11 us cost=10 size=0 card=2747)'
WAIT #139635562442496: nam='SQL*Net message from client' ela= 38 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306163565
CLOSE #139635562442496:c=6,e=6,dep=0,type=1,tim=49600306163591
=====================
PARSING IN CURSOR #139635562441168 len=122 dep=0 uid=102 oct=3 lid=102 tim=49600306163628 hv=733902562 ad='2185c6098' sqlid='96w3th0pvwxr2'
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
END OF STMT
PARSE #139635562441168:c=27,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163628
BINDS #139635562441168:

Bind#0
  oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7eff701be438  bln=32  avl=19  flg=09
  value="2025-06-01 11:09:02"
EXEC #139635562441168:c=66,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163747
WAIT #139635562441168: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306163765
FETCH #139635562441168:c=6,e=6,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163786
STAT #139635562441168 id=1 cnt=0 pid=0 pos=1 obj=393714 op='TABLE ACCESS BY INDEX ROWID BATCHED TEST4031 (cr=3 pr=0 pw=0 str=1 time=9 us cost=476 size=43952 card=2747)'
STAT #139635562441168 id=2 cnt=0 pid=1 pos=1 obj=393715 op='INDEX RANGE SCAN IDX_TS (cr=3 pr=0 pw=0 str=1 time=9 us cost=10 size=0 card=2747)'
WAIT #139635562441168: nam='SQL*Net message from client' ela= 35 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306163933
CLOSE #139635562441168:c=6,e=6,dep=0,type=1,tim=49600306163959
=====================
PARSING IN CURSOR #139635562439840 len=122 dep=0 uid=102 oct=3 lid=102 tim=49600306163995 hv=733902562 ad='2185c6098' sqlid='96w3th0pvwxr2'
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
END OF STMT
PARSE #139635562439840:c=26,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163995
BINDS #139635562439840:

Bind#0
  oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7eff701cf320  bln=32  avl=19  flg=09
  value="2025-06-01 11:09:03"
EXEC #139635562439840:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306164154
WAIT #139635562439840: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306164174
FETCH #139635562439840:c=0,e=6,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306164196
STAT #139635562439840 id=1 cnt=0 pid=0 pos=1 obj=393714 op='TABLE ACCESS BY INDEX ROWID BATCHED TEST4031 (cr=3 pr=0 pw=0 str=1 time=10 us cost=476 size=43952 card=2747)'
STAT #139635562439840 id=2 cnt=0 pid=1 pos=1 obj=393715 op='INDEX RANGE SCAN IDX_TS (cr=3 pr=0 pw=0 str=1 time=9 us cost=10 size=0 card=2747)'
WAIT #139635562439840: nam='SQL*Net message from client' ela= 41 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306164341
CLOSE #139635562439840:c=0,e=6,dep=0,type=1,tim=49600306164366
=====================
PARSING IN CURSOR #139635562704640 len=122 dep=0 uid=102 oct=3 lid=102 tim=49600306164404 hv=733902562 ad='2185c6098' sqlid='96w3th0pvwxr2'
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
END OF STMT
PARSE #139635562704640:c=190,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306164404
BINDS #139635562704640:

Bind#0
  oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7eff7018e390  bln=32  avl=19  flg=09
  value="2025-06-01 11:09:04"
EXEC #139635562704640:c=64,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306164514
WAIT #139635562704640: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306164532
FETCH #139635562704640:c=6,e=6,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306164552
STAT #139635562704640 id=1 cnt=0 pid=0 pos=1 obj=393714 op='TABLE ACCESS BY INDEX ROWID BATCHED TEST4031 (cr=3 pr=0 pw=0 str=1 time=9 us cost=476 size=43952 card=2747)'
STAT #139635562704640 id=2 cnt=0 pid=1 pos=1 obj=393715 op='INDEX RANGE SCAN IDX_TS (cr=3 pr=0 pw=0 str=1 time=8 us cost=10 size=0 card=2747)'
WAIT #139635562704640: nam='SQL*Net message from client' ela= 36 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306164690
CLOSE #139635562704640:c=6,e=6,dep=0,type=1,tim=49600306164715
=====================

Conclusions.
First and foremost, the SQL text which made into SQL-Trace is the target (mapped) one.
See the predicate section reporting the TO_TIMESTAMP.

PARSING IN CURSOR #139635562442496 len=122 dep=0 uid=102 oct=3 lid=102 tim=49600306163248 hv=733902562 ad='2185c6098' sqlid='96w3th0pvwxr2'
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')

From a performance perspective however (referencing the first run-cycle), we managed to significantly reduce two major contributors to the overall execution time:

  • PARSE: elapsed time (e) dropped from approximately 900 to just 34 microseconds, when comparing the scenario with cursor_sharing = EXACT, thus plenty of distinct SQL_IDs
  • EXEC: elapsed time (e) dropped from approximately 3,400 to just 82 microseconds, with cursor_sharing = FORCE and storm of child cursors

Both have now become almost negligible compared to the original 10046 trace samples shown in the Part-2 of this series.
As a reminder, e represents the total elapsed time, expressed in microseconds, for a given database call, such as PARSE or EXEC.

Also, the sections “mis=0” in both, PARSE and EXEC show the reusing of the hash-value of the already parsed cursor and likely it’s the only one child cursor.

Let’s speculate..
One can even be tempted to estimate the total elapsed time of execution of the entire Java code once the SQL Translator was in play, looking just at this tiny excerpt above.

Assuming this small section can indeed be treated as representative (we have all rights to make such assumption – after all this one excerpt shows a “single loop” from my code of 10,000 such “loops”), let’s make such attempt.
So, let’s reference one such full cycle :

....
....
CLOSE #139635562442496:c=6,e=6,dep=0,type=1,tim=49600306163591
=====================
PARSING IN CURSOR #139635562441168 len=122 dep=0 uid=102 oct=3 lid=102 tim=49600306163628 hv=733902562 ad='2185c6098' sqlid='96w3th0pvwxr2'
select /* my_java_testsql */ ID from DELME.TEST4031 where BUSINESS_TS = TO_TIMESTAMP(:"SYS_B_0" , 'YYYY-MM-DD HH24:MI:SS')
END OF STMT
PARSE #139635562441168:c=27,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163628
BINDS #139635562441168:

Bind#0
  oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7eff701be438  bln=32  avl=19  flg=09
  value="2025-06-01 11:09:02"
EXEC #139635562441168:c=66,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163747
WAIT #139635562441168: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306163765
FETCH #139635562441168:c=6,e=6,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=557049255,tim=49600306163786
STAT #139635562441168 id=1 cnt=0 pid=0 pos=1 obj=393714 op='TABLE ACCESS BY INDEX ROWID BATCHED TEST4031 (cr=3 pr=0 pw=0 str=1 time=9 us cost=476 size=43952 card=2747)'
STAT #139635562441168 id=2 cnt=0 pid=1 pos=1 obj=393715 op='INDEX RANGE SCAN IDX_TS (cr=3 pr=0 pw=0 str=1 time=9 us cost=10 size=0 card=2747)'
WAIT #139635562441168: nam='SQL*Net message from client' ela= 35 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=49600306163933
CLOSE #139635562441168:c=6,e=6,dep=0,type=1,tim=49600306163959

Let’s take the timestamp (tim) from the final CLOSE call and subtract the timestamp from the preceding CLOSE call:

49600306163959 - 49600306163591 = 368 microseconds

With a little optimism, we can assume that a complete PARSE–BINDS–EXEC–FETCH–CLOSE cycle took approximately 368 microseconds.

Now, multiplying this by 10,000 executions gives us:

368 × 10,000 = 3,680,000 microseconds

Converting this into seconds:

3,680,000 / 1,000,000 = 3.68 seconds

This rough estimate is actually not too far from the 2.664 seconds reported by the Linux time command.
Admittedly, the two figures differ, but they are certainly within the same order of magnitude. 🙂

Of course, this is only an approximation based on a single trace sample, rather than an average across all 10,000 executions. The interval between the two CLOSE calls also includes some client-side processing and SQL*Net activity, so it should not be interpreted as pure database execution time.
In particular then, the sum of all “e” and “ela” values will not necessarily match that figure calculated, representing the end-to-end execution cycle.

Limitations of SQL Translation Framework

Last but not least. Definitely not the least!
You might be wondering why the heck I used Java code to quantify the potential benefits of using the SQL Translation Framework.
So, why didn’t I use something more natural (no offense to Java developers), like PL/SQL?
After all, I could have achieved the same thing with PL/SQL: looping through a huge number of executions of a SQL statement, deliberately using literals, and so on.
Well, I did.
And I spent quite some time struggling with something that, at first, I simply couldn’t wrap my head around. Eventually, though, I understood the reason when I found this thread below.
Namely, the SQL Translation Framework cannot be used to translate SQL statements embedded in PL/SQL code by design.

Here’s the cause: imagine the damage that could be done if SQL statements inside Oracle-supplied packages were subject to translation?!

The entire threat: https://asktom.oracle.com/ords/asktom.search?tag=sql-translator-in-12c

And before it may be unreachable :

—-

Connor McDonald :
August 30, 2016 – 2:46 am UTC

Confirmed. Not designed for use within plsql.
One obvious reason – think of the damage that could be done if you translated SQL’s within Oracle-deliverd packages.

—-

Leave a Reply

Your email address will not be published. Required fields are marked *

All tags

Latest posts