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.
