SQL Translation Framework

My favorite new Oracle Database 12c feature is the SQL Translation Framework. The feature grew out of SQL Developer’s ability to translate SQL from non-Oracle RDBMS’s. For example, there is a pre-built Sybase ASE translation package that is designed to translate the Sybase dialect of SQL into Oracle SQL dialect. So that’s what the feature is designed for. But the developers decided to move it to the database and to allow us to write our own translations which opens up a whole world of possibilities.

The first thought that occurred to me when I saw this feature listed in the 12c New Features doc, was that I might be able to use it to fix badly written SQL behind the scenes. I’ve written and talked quite a bit about using hint based mechanisms (Outlines, SQL Profiles, Baselines, and SQL Patches) to alter execution plans without having to change the code. Those technique work great most of the time, but there are cases where hints alone can’t fix the problem. In some cases it is necessary to change the SQL statement text to get the desired results. And the SQL Translation Framework gives us the tool kit we need to do just that. And by the way, although I do have a tendency to use Oracle features for purposes for which they were not originally intended, in this case, I think the developers knew full well that the features could be used to address performance issues by re-writing SQL. As proof, here is a snippet from the 12c Release 1 Migration guide.

In addition to translating non-Oracle SQL statements, the SQL Translation Framework can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue. In this way, you can address an application issue without patching the client application.

So let’s dive in. There are two main components to the framework. The first is a pl/sql package to programmatically translate code (also called the Translator in the docs). The second is a set of maps for individual SQL statements (this is called a SQL Translation Profile). There are a couple of requirements to use this feature.

1. You must create a SQL Translation Profile (using dbms_sql_translator.create_profile)
2. You must assign a session to use the Translation Profile (generally with an alter session command)
3. You must set the 10601 system event

While the Translator Profile is required, it does not have to be assigned a translator. In other words, you can map individual statements without writing a PL/SQL package. Of course if you have a system that has a lot of problems caused by the same coding pattern, you could potentially use the framework to rewrite those statements on the fly.

Here’s a quick example for a simple case of mapping individual statements.

SYS@LAB1211> exec dbms_sql_translator.create_profile('FOO');
 
PL/SQL procedure successfully completed.
 
SYS@LAB1211> select object_name, object_type from dba_objects where object_name like 'FOO';
 
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
FOO                            SQL TRANSLATION PROFILE
 
SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select count(*) from hr.countries','select count(*) from hr.jobs');
 
PL/SQL procedure successfully completed.
 
SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select count(*) from countries','select count(*) from jobs');
 
PL/SQL procedure successfully completed.
 
SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select 1 from hr.countries','select count(*) from hr.countries');
 
PL/SQL procedure successfully completed.
 
SYS@LAB1211> grant all on sql translation profile foo to hr;
 
Grant succeeded.
 
SYS@LAB1211> alter session set sql_translation_profile = FOO;
 
Session altered.
 
SYS@LAB1211> alter session set events = '10601 trace name context forever, level 32';
 
Session altered.
 
SYS@LAB1211> set echo on
SYS@LAB1211> select count(*) from hr.countries;
 
  COUNT(*)
----------
        19
 
SYS@LAB1211> select /*+ fix_wrong_results */ count(*) from hr.countries;
 
  COUNT(*)
----------
        25
 
SYS@LAB1211> @x
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aaajpnhn25nza, child number 0
-------------------------------------
select /*+ fix_wrong_results */ count(*) from hr.countries
 
Plan hash value: 1399856367
 
----------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |                 |     1 |            |          |
|   2 |   INDEX FULL SCAN| COUNTRY_C_ID_PK |    25 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
 
14 rows selected.
 
SYS@LAB1211> select count(*) from hr.countries;
 
  COUNT(*)
----------
        19
 
SYS@LAB1211> @x
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c95vwg4jwqqfd, child number 0
-------------------------------------
select count(*) from hr.jobs
 
Plan hash value: 3870222678
 
-----------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |     1 (100)|
|   1 |  SORT AGGREGATE  |           |     1 |            |
|   2 |   INDEX FULL SCAN| JOB_ID_PK |    19 |     0   (0)|
-----------------------------------------------------------
 
 
14 rows selected.


So here’s what I did in the previous code section. I created a sql translation profile called FOO. I then registered a couple of SQL statements and mapped them to different SQL statements (i.e. I translated “select count(*) from hr.countries” to “select count(*) from hr.jobs”). Then I did an alter session to enable the Translation Profile for my session and set event 10601. Then I executed one of the mapped SQL statements. Then I executed a non-mapped SQL statement (the one with the comment). This statement was passed directly on to the database since there was no translation registered for that statement. Then I showed the actual text that the DB had processed for both statements. Note that there is no trace of the original version of the translated SQL in the shared pool (I’ll show that in the next code snippet).

I should point out that there is currently a bug that causes SQL_ID and HASH_VALUE to be calculated incorrectly for statements that are passed through via the translation framework. (i.e. statements that are not actually translated because there is not a match in the profile) I blogged about it here (although I didn’t understand why it was happening at the time).

As always I wrote a couple of scripts to make things a little easier:

create_translation_profile.sql – creates a translation profile
create_translation.sql – adds a translation to a profile
drop_translation.sql – drops a translation from a profile
export_translation_profile.sql – shows the contents of a profile
enable_translation.sql – turns on translation for the current session
disable_translation.sql – turns off translation for the current session
trace_translation_on.sql – turns on tracing
trace_translation_off.sql – turns off tracing
translation_logon_trigger.sql – creates a logon trigger to enable translation

Here’s a couple of examples using some of them:

SYS@LAB1211> @create_translation
Enter value for sql_translation_profile: FOO
Enter value for sql_id_to_replace: 
Enter value for sql_text_to_replace (null): select 1 from dual
Enter value for sql_id_to_be_executed (null): 
Enter value for translated sql_text (null): select 2 from dual
 
SYS@LAB1211> @export_translation_profile.sql
Enter value for translation_profile_name: FOO
 
SQLTranslationProfile
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<SQLTranslationProfile ForeignSQLSyntax="TRUE" TranslateNewSQL="TRUE" RaiseTranslationError="FALSE" LogTranslationError="FALSE" TraceTranslation="FALSE" Editionable="TRUE">
  <SQLTranslations>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select 1 from dual</SQLText>
      <TranslatedText>select 2 from dual</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select 1 from hr.countries</SQLText>
      <TranslatedText>select count(*) from hr.countries</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select count(*) from countries</SQLText>
      <TranslatedText>select count(*) from jobs</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select count(*) from hr.countries</SQLText>
      <TranslatedText>select count(*) from hr.jobs</TranslatedText>
    </SQLTranslation>
  </SQLTranslations>
  <ErrorTranslations/>
</SQLTranslationProfile>
 
SYS@LAB1211> select 1 from dual;
 
         2
----------
         2
SYS@LAB1211> select sql_id,sql_text from v$sql where sql_text like '%select 1 from dual%';
 
SQL_ID
-------------
SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bvkb8c54bnrtf
BEGIN dbms_sql_translator.register_sql_translation('FOO','select 1 from dual','select 2 from dual'); END;
 
ds8y510pbq7f0
select sql_id,sql_text from v$sql where sql_text like '%select 1 from dual%'
 
 
SYS@LAB1211> select 1 from dual;
 
         2
----------
         2
 
SYS@LAB1211> select sql_id,sql_text from v$sql where sql_text like '%select 1 from dual%';
 
SQL_ID
-------------
SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bvkb8c54bnrtf
BEGIN dbms_sql_translator.register_sql_translation('FOO','select 1 from dual','select 2 from dual'); END;
 
ds8y510pbq7f0
select sql_id,sql_text from v$sql where sql_text like '%select 1 from dual%'

So in that section I added a translation, showed it was in the profile and then executed the statement to show it was translated. Then looked in v$sql to see if there was any record of the original text (and found none). Here’s another quick example showing dropping a translation.

SYS@LAB1211> @export_translation_profile.sql
Enter value for translation_profile_name: FOO
 
SQLTranslationProfile
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<SQLTranslationProfile ForeignSQLSyntax="TRUE" TranslateNewSQL="TRUE" RaiseTranslationError="FALSE" LogTranslationError="FALSE" TraceTranslation="TRUE" Editionable="TRUE">
  <SQLTranslations>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select 1 from dual</SQLText>
      <TranslatedText>select 2 from dual</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select 1 from hr.countries</SQLText>
      <TranslatedText>select count(*) from hr.countries</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select count(*) from countries</SQLText>
      <TranslatedText>select count(*) from jobs</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select count(*) from hr.countries</SQLText>
      <TranslatedText>select count(*) from hr.jobs</TranslatedText>
    </SQLTranslation>
  </SQLTranslations>
  <ErrorTranslations/>
</SQLTranslationProfile>
 
SYS@LAB1211> @drop_translation
Enter value for sql_translation_profile: FOO
Enter value for sql_id_to_replace: 
Enter value for sql_text_to_replace (null): select 1 from dual
 
SYS@LAB1211> @export_translation_profile.sql
Enter value for translation_profile_name: FOO
 
SQLTranslationProfile
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<SQLTranslationProfile ForeignSQLSyntax="TRUE" TranslateNewSQL="TRUE" RaiseTranslationError="FALSE" LogTranslationError="FALSE" TraceTranslation="TRUE" Editionable="TRUE">
  <SQLTranslations>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select 1 from hr.countries</SQLText>
      <TranslatedText>select count(*) from hr.countries</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select count(*) from countries</SQLText>
      <TranslatedText>select count(*) from jobs</TranslatedText>
    </SQLTranslation>
    <SQLTranslation Enabled="TRUE">
      <SQLText>select count(*) from hr.countries</SQLText>
      <TranslatedText>select count(*) from hr.jobs</TranslatedText>
    </SQLTranslation>
  </SQLTranslations>
  <ErrorTranslations/>
</SQLTranslationProfile>

Final thoughts: I think translation profiles are the coolest new feature I’ve seen in a long time and I look forward to seeing how it will be used in the future. As always, your comments are welcome – so let me know what you think.

21 Comments

  1. Stew Ashton says:

    Wow, generalized bottom-up SQL injection! Nice security feature.

    Does it work on DML as well?

    Is there a check to see if the result set has the same column pattern (number and datatypes)?

    What about RETURNING INTO?

    • osborne says:

      Hi Stew,

      DML – Yes

      There are no checks in the Translation code, but obviously a program that has allocated 3 variables will not be able to fetch 4 return values. SQL*Plus is a very dynamic program that allows a lot flexibility, but a normal application would need to have matching numbers of variables and data types.

      Kerry

  2. Hi Kerry,
    i don’t think that the different sql id and hash value calculation by passing through the translation framework is a bug. Why?

    Well i have not figured out the exact algorithm for it (i think this is something for Tanel *beckon Tanel over*), but the sql id and hash value calculation is based on the session’s SQL translation profile and it seems like the translation profile name is included (maybe also hashed?) in some way. The corresponding kernel functions are also getting these meta values for calculation.

    Here is my test case and the corresponding information.

    – DBMS_SQL_TRANSLATOR

    Procedure: sql_id
    Computes the SQL identifier of a SQL statement in the session’s SQL
    translation profile.

    Procedure: sql_hash
    Computes the hash value of a SQL statement in the session’s SQL
    translation profile. It may be used to speed up the lookup of a SQL
    translation in SQL translation views

    – DBMS_SQL_TRANSLATOR (Body)

    352 FUNCTION SQL_ID(SQL_TEXT IN CLOB) RETURN VARCHAR2 DETERMINISTIC AS
    353 EXTERNAL NAME “kksxSQLID” PARAMETERS
    354 (CONTEXT,
    355 SQL_TEXT OCILOBLOCATOR,
    356 SQL_TEXT INDICATOR SB2,
    357 RETURN LENGTH SB4,
    358 RETURN INDICATOR SB2,
    359 RETURN STRING)
    360 LIBRARY DBMS_SQL_TRANSLATOR_LIB WITH CONTEXT;
    361
    362
    363 FUNCTION SQL_HASH(SQL_TEXT IN CLOB) RETURN NUMBER DETERMINISTIC AS
    364 EXTERNAL NAME “kksxSQLHash” PARAMETERS
    365 (CONTEXT,
    366 SQL_TEXT OCILOBLOCATOR,
    367 SQL_TEXT INDICATOR SB2,
    368 RETURN INDICATOR SB2,
    369 RETURN OCINUMBER)
    370 LIBRARY DBMS_SQL_TRANSLATOR_LIB WITH CONTEXT;

    SQL> select 1 from dual; – 520mkxqpf15q8 / 2866845384 / 7d4dc9b423f0bcfb510272edaae096c8

    SQL> exec DBMS_SQL_TRANSLATOR.CREATE_PROFILE (‘MYTEST’);
    SQL> alter session set sql_translation_profile = MYTEST;
    SQL> alter session set events = ’10601 trace name context forever, level 32′;

    SQL> select 1 from dual; – fhgp6r4brx81g / 394174511 / e518a6456b0105aae83ea6b9177ea02f

    SQL> select DBMS_SQL_TRANSLATOR.SQL_ID(‘select 1 from dual’) AS SQL_ID, DBMS_SQL_TRANSLATOR.SQL_HASH(‘select 1 from dual’) AS SQL_HASH from dual;
    SQL_ID SQL_HASH
    —————————— ———-
    fhgp6r4brx81g 394174511

    SQL> exec DBMS_SQL_TRANSLATOR.CREATE_PROFILE (‘MYTEST1′);
    SQL> alter session set sql_translation_profile = MYTEST1;
    SQL> alter session set events = ’10601 trace name context forever, level 32′;

    SQL> select 1 from dual; – dvvfz9c9yhc0c / 333983756 / c8c58faf2196383eddeddf4b13e8300c

    SQL> select DBMS_SQL_TRANSLATOR.SQL_ID(‘select 1 from dual’) AS SQL_ID, DBMS_SQL_TRANSLATOR.SQL_HASH(‘select 1 from dual’) AS SQL_HASH from dual;
    SQL_ID SQL_HASH
    —————————— ———-
    dvvfz9c9yhc0c 333983756

    SQL> alter session set sql_translation_profile = MYTEST;
    SQL> select DBMS_SQL_TRANSLATOR.SQL_ID(‘select 1 from dual’) AS SQL_ID, DBMS_SQL_TRANSLATOR.SQL_HASH(‘select 1 from dual’) AS SQL_HASH from dual;
    SQL_ID SQL_HASH
    —————————— ———-
    fhgp6r4brx81g 394174511

    shell> dtrace -n ‘pid$target::kksxSQLID: { trace(copyinstr(arg0)); }’ -n ‘pid$target::kksxSQLID: { trace(copyinstr(arg1)); }’ -p 1484

    0 69081 kksxSQLID:13b “SYS”.”MYTEST”
    0 69082 kksxSQLID:142 “SYS”.”MYTEST”
    0 69083 kksxSQLID:146 “SYS”.”MYTEST”
    0 69084 kksxSQLID:14d “SYS”.”MYTEST”
    0 69085 kksxSQLID:154 “SYS”.”MYTEST”
    0 69086 kksxSQLID:15a “SYS”.”MYTEST”
    0 69087 kksxSQLID:15d “SYS”.”MYTEST”
    0 69088 kksxSQLID:162 select 1 from dual
    0 69089 kksxSQLID:166 ?F
    0 69089 kksxSQLID:166 select 1 from dual
    0 69090 kksxSQLID:16a ?F

    So i think it is pretty fine so far :-)

    Regards
    Stefan

    • osborne says:

      Hi Stefen,

      Cool. (and thanks for the details) The Oracle guys have indicated that it is not intended behavior. (i.e. the SQL_ID should always be the same regardless of whether it’s run though the translation framework or not). I understand that a bug will be opened on this shortly. I’m running out now but will look at your response in more detail later. :)

      Kerry

  3. Really nice, Kerry. I agree – this is a great feature and O look forward to all the creative ways developers and DBAs will wreak under-the-cover havoc in their databases! :-)

  4. Wojciech says:

    Kerry, have you tested it with bind variables? Does it make any sense at all?

    • osborne says:

      Hi Wojciech,

      Yes, I’ve tested it with binds, and they work just fine. You must have them set up of course. You can’t swap a statement with 3 bind variables for one that had only 2 to start with for example. But you could swap one with 2 for one that originally had 3. Types and names would have to match. In general you should be thinking of swapping a statement that does the same thing as before (same binds, same set of items in the select list) but rewritten to eliminate an error or to improve performance.

      Kerry

  5. Rich Headrick says:

    Kerry,

    If I wanted to run a SQLT report against a statement that has a translation profile which SQL ID would I need to pick? I can see this new feature causing a lot of confusion for SQL tuners.
    –Rich

  6. Rich Headrick says:

    Just another though: I wonder if the folks to created the ADDM framework could leverage this new feature to help produce more acceptable solutions to problematic SQL statements.

  7. osborne says:

    Perhaps. I haven’t seen any indication of that yet, but it may be possible. It would require some set up (creating a default translation profile, assigning some/all sessions to use that profile, setting the 10601 event). before even creating the translation.

    Kerry

  8. Rajiv says:

    Kerry,

    This does look to be a great feature in 12c. Thanks for explaining it in detail.
    But alas it’s still not going to work with tools like OBIEE which create sqls dynamically and can possibly change the sql hash for the same requirement.
    E.g Switching the conditions in WHERE clause up and down will change the sql hash value. In these cases the only solution is to register all possible incarnations of the sql in the translation as you did.

    Thanks,
    Rajiv.

    • osborne says:

      Hi Rajiv,

      OBIEE can be tough. One of the cool things about the framework is that you can write your own translator. So assuming the badly behaving SQL follows a recognizable pattern, and the solution can be programmatically arrived at, you may be able to accomplish your purpose without mapping every possible variation. Although, it probably would be easier and more reliable to re-work the OBIEE model to get it to do a better job in the first place. But sometimes that is very difficult because the people that know what’s wrong with the SQL and how to fix it, often don’t know how to manipulate the model to get OBIEE to spit out better SQL.

      Kerry

  9. [...] This looks like an interesting feature of Oracle 12c. I’m still not sure about the security implications but it does say interesting things about pure network monitoring security tools. Now, more than ever, what you see on the network can be something completely different than what runs on the database. So, you can see a statement like ‘select * from dual’ on the network but in the database it will be translated to ‘select * from credit_cards’… [...]

  10. [...] подмены текста обычных запросов в Oracle – Kerry Osborne. SQL Translation Framework – оригинальная дополнительная возможность [...]

  11. Jure Bratina says:

    Hi Kerry,

    do you know if there are any similarities with the functionality that the DBMS_ADVANCED_REWRITE package offers? Both seem to intercept an incoming SQL and rewrite it on the fly, although the SQL Translation Framework seems to offer more flexibility?

    Thanks,
    Jure

  12. osborne says:

    Hi Jure,

    I haven’t looked at DBMS_ADVANCED_REWRITE in several years but last time I looked it was virtually useless due to all the restrictions.

    Kerry

  13. DB Oriented says:

    [...] SQL translation framework: SQL Translation Framework translates SQL statements of a client program from a non-Oracle dialect into the Oracle dialect (and can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue) [a post by Kerry Osborne] [...]

Leave a Reply