Hidden Hero: DBMS_SQL_TRANSLATOR Package

Oracle liefert mit der Datenbank eine vielzahl von Packages mit. Einige davon kennt man als DBA in der Regel sehr gut, es gibt aber auch die großteils unbekannten Packages, zu denen ich auch DBMS_SQL_TRANSLATOR zählen möchte.

Das Package DBMS_SQL_TRANSLATOR – seit Oracle 12c verfügbar – dient als Interface zum Erzeugen, Anpassen und Verwalten von SQL Translation Profiles. Der primäre Zweck ist SQL Statements und Fehler Codes on the fly zu übersetzen, damit man mit anderen SQL Dialekten (MS SQL, Sybase, MySQL,…) auf Oracle Datenbanken zugreifen kann ohne die Statements selbst zu modifizieren. Damit soll die Portierung von Applikationen, die für andere Datenbanken geschrieben wurden, auf Oracle erleichtert werden.

Die Funktion besteht darin, dass man das SQL Statement vor der Ausführung auf der Oracle Datenbank modifiziert (Text Substitution) bis hin zu einem vollständigen Austausch des Statements. Nun hört sich das für einen Oracle DBA einmal nicht so an, als wäre das ein DBA Thema, das kling mehr nach einem Entwicklungsthema, richtig?

Die Antwort ist wie so oft ein klares: Jein!

Ermitteln der `SQL_ID` eines Statements

Gestoßen bin ich auf das Packages durch die Frage: Welche SQL_ID hat dieses Statement, damit ich es schnell und direkt in V$SQL bzw. V$SQLAREA finden kann, ohne die Spalte SQL_TEXT zu „parsen“. Möchte ich beispielsweise für folgendes SQL Statement die SQL_ID wissen:

select sysdate from dual;

kann ich das ganz einfach herausfinden:

select DBMS_SQL_TRANSLATOR.SQL_ID ('select sysdate from dual') as SQL_ID 
  from dual;

SQL_ID
--------------
7h35uxf5uhmm1

Vorsicht: Die die kleinste Änderung – und sei es nur ein zusätzliches Leerzeichen zwischen from und dual ändert die SQL_ID!

select DBMS_SQL_TRANSLATOR.SQL_ID ('select sysdate from  dual') as SQL_ID 
  from dual;

SQL_ID
--------------
0prchuqbkby5k

Diese Funktion akzeptiert als Input ein CLOB, somit kann man komplexere, längere Statements zuerst in ein CLOB speichern und dann die SQL_ID ermitteln.

Die SQL_ID kann man jetzt einfach für beliebige Abfragen in V$SQL, V$SQLARA, V$SQL_PLAN, etc. nutzen um gezielt die Informationen für genau das Statement auszulesen.

Aber das nicht nicht das Highlight, was man mit dem Package machen kann!

Patchen von SQL Statements

Regelmäßig finde ich als DBA ineffiziente und schlecht geschriebene SQL Statements. Meldet man diese an die – oft externe – Software Entwicklung weiter, passiert leider sehr oft lange Zeit (oder überhaupt) nichts. Manchmal – aber bei weiten nicht immer – kann man dem Statement mit Indizes auf die Sprünge helfen, aber das geht nicht, wenn das Statement massiv schlecht geschrieben ist.

Man müsste das Statement on-the-fly umschreiben können… Moment mal, da war doch DBMS_SQL_TRANSLATOR! Wo steht geschrieben, dass dies nur andere SQL Dialekte geeignet sein soll, warum nicht auch für schlecht geschrieben Oracle SQLs?

Vorbereitung

Applikationsowner berechtigen, Tabelle mit Daten erzeugen und Index anlagen:

connect / as sysdba

-- Appliaktionsowner Chris mit den notwendigen Berechtigungen versehen:
-- Erzeugen von DBMS_SQL_TRANSLATOR Profiles
grant execute on DBMS_SQL_TRANSLATOR to chris;
grant create sql translation profile to chris;
-- Nutzen von DBMS_SQL_TRANSLATOR Profiles
grant USE ANY SQL TRANSLATION PROFILE to chris;
-- Erlaubnis des Objekteigentümers an den Nutzer, dass die Zugriffe übersetzt werden dürfen.
GRANT TRANSLATE SQL ON USER chris TO chris;

grant select on V_$SQLAREA to CHRIS;

connect chris/chris

-- Test Daten anlegen
create table demo as 
select owner, table_name, column_name, data_type, data_length, data_scale 
 from all_tab_columns;

create index demo_idx on demo(owner, column_name);

Umsetzung

Machen wir ein einfaches Beispiel:

define colname = 'sql_id'
define ownname = 'sys'

set feedback on sql_id

select *
  from demo 
 where lower(column_name) = '&colname'
   and lower(owner) = '&ownname';

Hinweis: Mit set feedback on sql_id gibt uns Oracle auch die SQL_ID des Statements aus. In diesem Beispiel ist diese SQL_ID: 35yn48qpun344.

Jetzt checken wir den Ausführungsplan mittels Autotrace:

set feedback off
set autotrace traceonly explain

select *
  from demo 
 where lower(column_name) = '&colname'
   and lower(owner) = '&ownname';

----------------------------------...
| Id  | Operation         | Name |...
----------------------------------...
|   0 | SELECT STATEMENT  |      |...
|*  1 |  TABLE ACCESS FULL| DEMO |...
----------------------------------...

Das Problem an dem Statement ist, dass die Entwickler die Funktion – in diesem Beispiel lower – auf die Objekt Spalte ausführen, was dazu führen kann, dass Indizes nicht genutzt werden. In diesem Beispiel wird auf die Tabelle DEMO mit einem Full Table Scan statt via dem Index DEMO_IDX zugegriffen. Korrekt wäre folgendes Statements:

set autotrace off

define colname = 'sql_id'
define ownname = 'sys'

set feedback on sql_id

select *
  from demo 
 where column_name = upper('&colname')
   and owner = upper('&ownname');

Die SQL_ID für dieses Statement lautet 3a1ztcu077vb8. Jetzt noch den Ausführungplan prüfen:

set feedback off
set autotrace traceonly explain

select *
  from demo 
 where column_name = upper('&colname')
   and owner = upper('&ownname');

--------------------------------------------------------...
| Id  | Operation                           | Name     |...
--------------------------------------------------------...
|   0 | SELECT STATEMENT                    |          |...
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO     |...
|*  2 |   INDEX RANGE SCAN                  | DEMO_IDX |...
--------------------------------------------------------...

Hier würde der Zugriff auf DEMO korrekt über den Index DEMO_IDX erfolgen. Bei diesem Beispiel wird die Laufzeit keinen nennenswerten Unterscheid machen, in vielen Statements auf Tabellem mit größeren Datenmengen allerdings schon.

Wie könnte man das Problem jetzt beheben?

Zuerst legen wir ein Translator Profil an:

exec dbms_sql_translator.create_profile( 
     profile_name => 'PATCH_SQL_WITH_TRANSLATOR_PROFILE' );

Als nächstes müssen wir ein Mapping vom alten auf das neue Statement umsetzen.
Damit auch wirklich das richtige Statement erwischt wird, ist es das beste diese direkt aus V$SQLAREA zu entnehmen – und dafür brauchen wir die SQL_IDs der Statements (und beide müssen vor kurzem gelaufen sein).

set feedback off
set autotrace off

set serverout on

declare
  bad_sql_id  varchar2(20) := '35yn48qpun344';
  good_sql_id varchar2(20) := '3a1ztcu077vb8';

  bad_sql     clob;
  good_sql    clob;
begin

  -- SQL Statements auswählen
  select sql_fulltext into bad_sql 
    from v$sqlarea where sql_id = bad_sql_id;
  select sql_fulltext into good_sql 
    from v$sqlarea where sql_id = good_sql_id;

  dbms_output.put_line(bad_sql_id || ': ' || bad_sql);
  dbms_output.put_line(good_sql_id || ': ' || good_sql);


  -- Mapping einrichten
  dbms_sql_translator.register_sql_translation 
       (profile_name => 'PATCH_SQL_WITH_TRANSLATOR_PROFILE', 
        sql_text => bad_sql, 
        translated_text => good_sql, 
        enable => TRUE );

end;
/

Prüfen ob die Translation korrekt registriert ist:

select PROFILE_NAME, SQL_ID, 
       SQL_TEXT, TRANSLATED_TEXT 
  from USER_SQL_TRANSLATIONS;

Gekürzter Output:

PROFILE_NAME                      SQL_ID
--------------------------------- -------------
PATCH_SQL_WITH_TRANSLATOR_PROFILE 35yn48qpun344

Man sieht, dass die SQL_ID das schlechten Statements für die Erkennung genutzt wird.

Jetzt muss diese nur noch für die relevanten Sessions aktiviert werden – beispielsweise mittels Logon-Trigger.

ALTER SESSION SET SQL_TRANSLATION_PROFILE = PATCH_SQL_WITH_TRANSLATOR_PROFILE;

Prüfen wir ob die Umsetzung funktioniert:

set feedback off
set autotrace off

set serverout on

declare
  bad_sql_id  varchar2(20) := '35yn48qpun344';

  bad_sql     clob;
  translated_sql    clob;
begin

  -- SQL Statements auswählen
  select sql_fulltext into bad_sql 
    from v$sqlarea where sql_id = bad_sql_id;

  dbms_output.put_line('Bad SQL');
  dbms_output.put_line('------------------------------------');
  dbms_output.put_line(bad_sql);


  -- Mapping einrichten
  dbms_sql_translator.translate_sql 
       (sql_text => bad_sql, 
        translated_text => translated_sql);

  dbms_output.put_line('translated SQL');
  dbms_output.put_line('-------------------------------------');
  dbms_output.put_line(translated_sql);

end;
/

Bad SQL
------------------------------------
select *
  from demo
 where lower(column_name) = 'sql_id'
   and lower(owner) = 'sys'

translated SQL
-------------------------------------
select *
  from demo
 where column_name = upper('sql_id')
   and owner = upper('sys')

Ab jetzt wird Oracle die korrigierte Version des Statements nutzen.

relevante Views

In den Beispielen wurden nur die Übersetzungsfunktionalität genutzt:

DBA_SQL_TRANSLATION_PROFILES
DBA_SQL_TRANSLATIONS
DBA_ERROR_TRANSLATIONS

DBMS_SQL_TRANSLATOR kann aber auch Transformationen von Statements, das wurde in dem Artikel nicht behandelt:

DBA_TRANSFORMATIONS
DBA_ATTRIBUTE_TRANSFORMATIONS

Referenzen und weitere Informationen

Oracle Dokumentation zu DBMS_SQL_TRANSLATOR
How Oracle Changes Your SQL: Uncovering Hidden Query Transformations
Have DBMS_SQL_TRANSLATOR Rewrite a SQL statement

Ermitteln der SQL_ID eines Statements