Oracle Query Translation mit Hilfe des SQL-Translation-Frameworks

Ich möchte hier ein Feature der Oracle Datenbank vorstellen, das eigentlich zur Unterstützung von Migrationen von anderen Datenbanken auf die Oracle Datenbank gedacht ist. Aber man kann dieses Feature durchaus noch für andere spannende Dinge verwenden. Es handelt sich um das SQL Translation Framework, das im SQL Translation and Migration Guide beschrieben ist. Die Idee ist, SQL-Statements, die für andere Datenbanken entwickelt wurden und von Oracle nicht direkt ausgeführt werden können, noch vor dem Parsing so anzupassen, dass Oracle die Statements verarbeiten kann. Im Prinzip wird ein Regelwerk erstellt, wie Statements anzupassen sind, damit sie ausgeführt werden können. Man kann sich das wie ein Suchen und Ersetzen im SQL-Text vorstellen. Da es damit aber eben möglich ist, beliebige Statements vor dem Parsing anzupassen, kann man das SQL Translation Framework noch für viel mehr verwenden.

Das Ganze wird für den Schema User SHOWROOM16 angewandt.
Der Schema User muss dann entsprechend adaptiert werden.
Ausprobiert habe ich es auf der ORA43.

Den folgenden Grant für das translation profile erstellen:
Als Oracle DBA User

/* Formatted on 02/12/2022 15:54:47 (QP5 v5.362) */

GRANT CREATE SQL TRANSLATION PROFILE TO SHOWROOM16;

GRANT CREATE TRIGGER TO SHOWROOM16;

GRANT ALTER SESSION TO SHOWROOM16;

Als DB User SHOWROOM16, das folgende Translation Profil anlegen.

BEGIN

dbms_sql_translator.create_profile (profile_name => 'MMI_SQLTRANS_TEST');

dbms_sql_translator.set_attribute (

profile_name => 'MMI_SQLTRANS_TEST',

attribute_name => dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,

attribute_value => dbms_sql_translator.ATTR_VALUE_FALSE);

dbms_sql_translator.register_sql_translation (

profile_name => 'MMI_SQLTRANS_TEST',

sql_text =>

'select max(DATEPROCESSED) from history where status <> 5',

translated_text =>

'SELECT /*+ INDEX_JOIN("HISTORY") */ max("DATEPROCESSED") from "HISTORY" where "STATUS" <> 5 + UID * 0');

END;

Überprüfen, das das Translation Profil angelegt worden ist:

/* Formatted on 02/12/2022 16:11:54 (QP5 v5.362) */

SELECT * FROM user_sql_translation_profiles;

PROFILE_NAME	TRANSLATOR	FOREIGN_SQL_SYNTAX	TRANSLATE_NEW_SQL	RAISE_TRANSLATION_ERROR	LOG_TRANSLATION_ERROR	TRACE_TRANSLATION	LOG_ERRORS
MMI_SQLTRANS_TEST	NULL	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE

Welche Translations sind mit unserem Profil registriert:

SELECT * FROM all_sql_translations;

Will man das Translation Profil nun einmal händisch ausprobieren, so muss man seine Session Parameter einmal ändern.

/* Formatted on 02/12/2022 16:46:32 (QP5 v5.362) */

ALTER SESSION SET sql_translation_profile = MMI_SQLTRANS_TEST;

Als User sys einen logon trigger erstellen:
Hier den logon User passend einsetzen, damit das nur für diesen Schema User gilt.

Logon-Trigger sind System-Trigger, die beim Anmelden eines Benutzers eine Aktion ausführen. Man könnte es mit der Autostart-Funktion von Windows vergleichen.

CREATE OR REPLACE TRIGGER SYS.CHANGE_SESSION_PARAMS

AFTER LOGON

ON DATABASE

BEGIN

IF sys.login_user = 'SHOWROOM16'

THEN

EXECUTE IMMEDIATE 'alter session set sql_translation_profile=SHOWROOM16.MMI_SQLTRANS_TEST';

END IF;

END;

Jetzt in der Session als SHOWROOM16 DB User das folgende Statement abfeuern:

select max(DATEPROCESSED) from history where status <> 5;

Als DBA-User nun die folgende Query ausführen, um das translatete SQL zu sehen:

SELECT parsing_schema_name, sql_text

FROM v$sql

WHERE sql_text LIKE ('%max%') AND parsing_schema_name = 'SHOWROOM16'

ORDER BY first_load_Time DESC;

Registrieren weiterer Translations zum angelegten Profil:
Um weitere Translations zum angelegten Profil 'MMI_SQLTRANS_TEST' hinzuzufügen ruft man die PL/SQL Prozedur dazu einfach noch einmal auf:

BEGIN

dbms_sql_translator.register_sql_translation (

profile_name => 'MMI_SQLTRANS_TEST',

sql_text => 'SELECT MAX (emailid) FROM history',

translated_text =>

'SELECT emailid, status FROM history WHERE emailid = (SELECT MAX (emailid) FROM history) AND status > 6');

END;

Entsorgen des SQL Translation Profils:

Als User SHOWROOM16 das folgende ausführen:

BEGIN

DBMS_SQL_TRANSLATOR.DROP_PROFILE(

profile_name => 'MMI_SQLTRANS_TEST');

END;

Entsorgen des Logon Triggers
Als User sys das folgende ausführen:

DROP TRIGGER SYS.CHANGE_SESSION_PARAMS;

Vorher nachher Vergleich des Ausfühungsplans der Statements:

Vorher:

Nachher:

Der Kostenfaktor Table access full wurde durch einen Index fast full scan ersetzt.

Weitere Informationen können z.B. hier gefunden werden:
https://docs.oracle.com/en/database/oracle/oracle-database/19/drdaa/index.html