Kerry Osborne's Oracle Blog

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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  
    
      select 1 from dual
      select 2 from dual
    
    
      select 1 from hr.countries
      select count(*) from hr.countries
    
    
      select count(*) from countries
      select count(*) from jobs
    
    
      select count(*) from hr.countries
      select count(*) from hr.jobs
    
  
  


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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  
    
      select 1 from dual
      select 2 from dual
    
    
      select 1 from hr.countries
      select count(*) from hr.countries
    
    
      select count(*) from countries
      select count(*) from jobs
    
    
      select count(*) from hr.countries
      select count(*) from hr.jobs
    
  
  


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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  
    
      select 1 from hr.countries
      select count(*) from hr.countries
    
    
      select count(*) from countries
      select count(*) from jobs
    
    
      select count(*) from hr.countries
      select count(*) from hr.jobs
    
  
  

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.