Oracles SQL Translation Framework (STF) is designed to simplify some aspects of migration an application which was written for another relational database to Oracle. The idea is to translate each SQL which does not fulfil Oracles SQL dialect and replace it with the corresponding SQL.
Beside this main feature, it also can be used to "replace" a bad written SQL with one which generates the same results but with better performance. But these activities can only be done within the limitations of the STF.
One of this limitations is the restriction to SQLs which come directly from the application. It should be kind of obvious that SQLs from within PL/SQL must by correct Oracle SQLs by definition. So there is no reason to put those SQLs through the STF and waste cpu-cycles. This is somehow explained in this Ask TOM article.
I would *suspect* the reason is similar to what I've said above. Anything being run in PLSQL must *by definition* be valid Oracle SQL, because it would not have compiled otherwise. So my hypothesis is that the translation facility is not even invoked in such cases. I'm seeking a confirmation for this.
But still it might be interesting to observe the STF in it's activities. My first choice is to check the "new" (since 11g) kernel diagnostic and tracing infrastructure. Unfortunately there is nothing mentioned for the STF.
The next idea is to check the Oracle Optimizer. As it's all about SQL statements, that might be a good idea:
my simple translation is
exec dbms_sql_translator.register_sql_translation('FOO','select 1','select 2');
The test is quite simple:
select 1;
alter system flush shared_pool;
alter session set sql_translation_profile = sys.FOO;
alter session set tracefile_identifier='TEST1';
alter session set events = '10601 trace name context forever, level 32';
alter session set events '10053 trace name context forever';
select 1;
alter session set events '10053 trace name context off';
the result as expected:
1
----------
1
SQL> SQL>
System altered.
SQL>
Session altered.
SQL> SQL>
Session altered.
SQL> SQL>
Session altered.
SQL>
Session altered.
SQL>
2
----------
2
SQL>
and the tracefile starts with
Trace file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_15105_TEST1.trc
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
Build label: RDBMS_23.2.0.0.0_LINUX.X64_230325
ORACLE_HOME: /opt/oracle/product/23c/dbhomeFree
System name: Linux
Node name: localhost.localdomain
Release: 5.4.17-2136.312.3.4.el8uek.x86_64
Version: #2 SMP Wed Oct 19 17:42:16 PDT 2022
Machine: x86_64
VM name: KVM CPUID feature flags: 0x01000089
CLID: P
Instance name: FREE
Redo thread mounted by this instance: 1
Oracle process number: 70
Unix process pid: 15105, NID: 4026531836, image: oracle@localhost.localdomain
*** 2023-07-09T07:38:57.883180+00:00 (FREEPDB1(3))
*** SESSION ID:(45.5733) 2023-07-09T07:38:57.883205+00:00
*** CLIENT ID:() 2023-07-09T07:38:57.883215+00:00
*** SERVICE NAME:(freepdb1) 2023-07-09T07:38:57.883223+00:00
*** MODULE NAME:(SQL*Plus) 2023-07-09T07:38:57.883231+00:00
*** ACTION NAME:() 2023-07-09T07:38:57.883239+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-07-09T07:38:57.883247+00:00
*** CONTAINER ID:(3) 2023-07-09T07:38:57.883255+00:00
*** CLIENT IP:(127.0.0.1) 2023-07-09T07:38:57.883263+00:00
*** CONNECTION ID:(AAn1ABgrOv/gZQAAAAAAAQ==) 2023-07-09T07:38:57.883273+00:00
Registered qb: SEL$1 0x7fa657242d38 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=147 hint_alias="DUAL"@"SEL$1"
SPM: Processing SQL: select 2
SPM: statement not found in SMB
SPM: capture of plan baseline is OFF
kkoctx(): choosing CBO since optimizer mode set to all/first_rows
...
...
I see only the new SQL:
select 2 - but nothing regarding the STF.
The reason for this is simple, I should just RTFM:
It translates the SQL statements of a client program before they are processed by the Oracle Database SQL compiler.
OK, the SQL_Optimizer can not even show me something about STF.
exec dbms_sql_translator.set_attribute('FOO', dbms_sql_translator.attr_trace_translation,
dbms_sql_translator.attr_value_true);
With this set, a new attempt to trace the STF works fine now:
...
*** 2023-07-09T07:59:10.127763+00:00 (FREEPDB1(3))
*** SESSION ID:(279.57941) 2023-07-09T07:59:10.127792+00:00
*** CLIENT ID:() 2023-07-09T07:59:10.127802+00:00
*** SERVICE NAME:(freepdb1) 2023-07-09T07:59:10.127810+00:00
*** MODULE NAME:(SQL*Plus) 2023-07-09T07:59:10.127818+00:00
*** ACTION NAME:() 2023-07-09T07:59:10.127826+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-07-09T07:59:10.127833+00:00
*** CONTAINER ID:(3) 2023-07-09T07:59:10.127841+00:00
*** CLIENT IP:(127.0.0.1) 2023-07-09T07:59:10.127850+00:00
*** CONNECTION ID:(AAo8a2DBVZDgZQAAAAAAAQ==) 2023-07-09T07:59:10.127860+00:00
SQL Translation Profile "SYS"."FOO": original SQL text "select 1"
SQL Translation Profile "SYS"."FOO": translated SQL text "select 2"
and checking with SQL_OPTIMIZER trace enabled in addition there I see the SQL:
----- Current SQL Statement for this session (sql_id=3q69sdmxzv0t6) -----
select 2
----- Parser State -----
With all this knowledge, I'm tempted to do a testcase with SQL within PL/SQL and see how STF and the Optimizer cooperate:
set serveroutput on
alter system flush shared_pool;
alter session set sql_translation_profile = sys.FOO;
alter session set tracefile_identifier='TEST5';
alter session set events = '10601 trace name context forever, level 32';
alter session set events '10053 trace name context forever';
declare
stmt varchar2(100) := 'select 1';
nn number;
begin
execute immediate stmt
into nn;
dbms_output.put_line(' nn => ' || nn );
end;
/
SQL>
System altered.
SQL>
Session altered.
SQL> SQL>
Session altered.
SQL> SQL>
Session altered.
SQL>
Session altered.
nn => 1
PL/SQL procedure successfully completed.
As expected, there was no STF for the select within my PL/SQL block, and the trace confirms that:
*** 2023-07-09T08:13:04.202341+00:00 (FREEPDB1(3))
*** SESSION ID:(285.30162) 2023-07-09T08:13:04.202367+00:00
*** CLIENT ID:() 2023-07-09T08:13:04.202377+00:00
*** SERVICE NAME:(freepdb1) 2023-07-09T08:13:04.202386+00:00
*** MODULE NAME:(SQL*Plus) 2023-07-09T08:13:04.202394+00:00
*** ACTION NAME:() 2023-07-09T08:13:04.202406+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-07-09T08:13:04.202425+00:00
*** CONTAINER ID:(3) 2023-07-09T08:13:04.202443+00:00
*** CLIENT IP:(127.0.0.1) 2023-07-09T08:13:04.202458+00:00
*** CONNECTION ID:(AApvQtb1aJngZQAAAAAAAQ==) 2023-07-09T08:13:04.202474+00:00
SQL Translation Profile "SYS"."FOO": original SQL text "BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;"
SQL Translation Profile "SYS"."FOO": translated SQL text ""
SQL Translation Profile "SYS"."FOO": original SQL text "alter session set events '10053 trace name context forever'"
SQL Translation Profile "SYS"."FOO": translated SQL text ""
SQL Translation Profile "SYS"."FOO": original SQL text "BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;"
SQL Translation Profile "SYS"."FOO": translated SQL text ""
AP: qesdpDPCSetupNextExec(enter): SQL_ID=9babjv8yq8ru3
AP: qesdpDPCSetupNextExec: Not an adaptive plan
AP: qesdpInitExecMutable(enter): SQL_ID=9babjv8yq8ru3
AP: qesdpInitExecMut (exit)
AP: qesdpSetupMutable(enter): SQL_ID=9babjv8yq8ru3
SQL Translation Profile "SYS"."FOO": original SQL text "declare
stmt varchar2(100) := 'select 1';
nn number;
begin
execute immediate stmt
into nn;
dbms_output.put_line(' nn => ' || nn );
end;"
SQL Translation Profile "SYS"."FOO": translated SQL text ""
Registered qb: MISC$1 0x7f059f8e4d38 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature(): NULL
SPM: Processing SQL: declare
stmt varchar2(100) := 'select 1';
nn number;
begin
execute immediate stmt
into nn;
dbms_output.put_line(' nn => ' || nn );
end;
SPM: disallowed: statement type not supported
kkoctx(): choosing CBO since optimizer mode set to all/first_rows
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: instance state restriction (parallelism not supported).
PM: Considering predicate move-around in query block MISC$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=002rtvjv7wxc9) -----
declare
stmt varchar2(100) := 'select 1';
nn number;
begin
execute immediate stmt
into nn;
dbms_output.put_line(' nn => ' || nn );
end;
----- Parser State -----
...
...
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=147 hint_alias="DUAL"@"SEL$1"
SPM: Processing SQL: select 1
SPM: statement not found in SMB
SPM: capture of plan baseline is OFF
kkoctx(): choosing CBO since optimizer mode set to all/first_rows
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: instance state restriction (parallelism not supported).
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=0y30pf6xwqt3x) -----
select 1
----- Parser State -----
...
Of course, there could have been a chance my SQL within the PL/SQL is not literally identical to the one defined in my STF, but that's not the case as a quick check in DBA_SQL_TRANSLATION shows:
The SQL_ID for the select 1 to be translated is 0y30pf6xwqt3x - which is also visible in the last SQL_Optimizer trace.
This should provide some insights how STF works - and where it just can't be used at all.
I also did some tests with the parameters
_STFForceTranslateOracleSQL if TRUE translation profile will translate Oracle SQL statements
_STFTranslateDynamicSQL if TRUE translation profile will translate dynamic SQL statements
(mentioned
here) - but could not make any sense out of those - maybe some time I can find another angle of approach to bring them into the whole picture.
It might be worth to mention there is another framework which can replace SQLs:
Advanced Query Rewrite - but this has it's own limitations.
