Oracle-DB: Use "SQL Translation Framework" to quickly fix problems with SQLs

SQL Translation Framework is one of the more hidden features of database release 12.1.

It allows to completely replace SQL text with replacement text before processing this SQL.

Primary aim for SQL Translation Framework is migrating client applications from other SQL dialects, but it is also a sharp weapon for performance troubleshooting.

Compared with SQL plan baselines or SQL profiles it is not restricted to execution plan or optimizer hints, instead allows to replace the complete SQL text.
This way this framework allows to fix any possible issues that may be solved by changes on SQL text without any change on the application executing the SQL. 

You can rewrite the whole SQL-statement, e.g. add missing JOIN- or WHERE-conditions, extract parts into WITH-clause etc.
Limitations for SQL text replacements are only:

• the result structure (number, order and types of result columns) must remain stable
• the bind variables must also remain stable regarding number, alias, order and types 

More information about SQL Translation Framework you can find here:

• Framework overwiew and architecture
• Detailed description of package DBMS_SQL_TRANSLATOR
• Explanations from Kerry Osborn, that inspired me to dig deeper

Panorama is now able to generate all actions for a specific SQL statement to create and activate a SQL translation. 
You will find a button "Generate SQL-Translation" in current and historic detail views of SQL.
Clicking this button generates the actions to create translation profile and instrument your users sessions by database trigger to activate this translation.
All you have to do is to place your SQL text adjustments in the script.

Clicking this button generates the following code snippet:

-- Script for establishing SQL translation for SQL-ID='7yvfn9sjpbm5a' based on SQL translation framework ( https://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#DRDAA131 )
-- Generated by Panorama at 2017-09-22 20:48:46 +0200
-- Executing this script allows you to change the complete syntax of this SQL. Only result structure and used bind variables must remain consistent.
-- This allows you to transparently fix each problem caused by the semantic of this SQL without any change on the calling application.
-- Existing translations for the resulting SQLs are shown by Panorama in SQL-details view.
-- All existing translations are listed in Panorama via menu 'SGA/PGA-details' / 'SQL plan management' / 'SQL translations'

-- Attributes that must be adjusted by you in this script:
--   - Name of the user that is really executing the SQL if this is different from current choosen user 'PANORAMA'
--   - Translated SQL text, currently initialized with the text of the original SQL

-- To activate the translation you must reconnect your session to make the LOGON-trigger working (restart application or reset session pool)

-- ############# Following acitivities should be sequentially executed in this order to establish translation #############

-- 1. ####### Execute as user SYS to establish translation:

GRANT CREATE SQL TRANSLATION PROFILE TO PANORAMA;

GRANT ALTER SESSION TO PANORAMA;


-- 2. ####### Execute as user PANORAMA to establish translation:

EXEC DBMS_SQL_TRANSLATOR.CREATE_PROFILE('PANORAMA_TRANSL_PANORAMA');

BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION('PANORAMA_TRANSL_PANORAMA',
'SELECT Table_Name FROM All_Tables WHERE Owner = :A1',
-- ####### Adjust the following SQL text as translation target on your needs
'SELECT Table_Name FROM All_Tables WHERE Owner = :A1',
TRUE);
END;
/


-- 3. ####### Execute as user SYS to establish translation:

CREATE TRIGGER Panorama_Transl_PANORAMA AFTER LOGON ON DATABASE
BEGIN
  -- created by SQL translation script generated by Panorama to allow translation of SQL with SQL-ID='7yvfn9sjpbm5a'
  IF USER = 'PANORAMA' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=PANORAMA';
    EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRANSLATION_PROFILE=PANORAMA_TRANSL_PANORAMA';
    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS =''10601 trace name context forever, level 32''';
  END IF;
END;
/

-- ############# Following acitivities should be sequentially executed in this order to remove translation if not needed anymore #############

-- 1. ####### Execute as user SYS to remove translation if not needed anymore:

DROP TRIGGER Panorama_Transl_PANORAMA;

-- 2. ####### Execute as user PANORAMA to remove translation if not needed anymore:

BEGIN
DBMS_SQL_TRANSLATOR.DEREGISTER_SQL_TRANSLATION('PANORAMA_TRANSL_PANORAMA',
'SELECT Table_Name FROM All_Tables WHERE Owner = :A1'
);
END;
/

-- Drop profile only if no more translations are registered to the profile (see DBA_SQL_Translations)
--EXEC DBMS_SQL_TRANSLATOR.DROP_PROFILE('PANORAMA_TRANSL_PANORAMA');


-- ############ Remarks:
-- To activate the translation you must usually reconnect your session to make the LOGON-trigger working.
-- There is a solution to set the needed event in an already running session like:
-- EXEC DBMS_SYSTEM.set_ev(, , 10601, 32, '');
-- but I did not found a solution for setting SQL_TRANSLATION_PROFILE=PANORAMA_TRANSL_PANORAMA in a running session

Existing SQL translations can be viewed at menu "SGA/PGA Details" / "SQL plan management" / "SQL translations"

If you view in detail a SQL resulting from SQL translation than SQL translation details will be show to inform you about this.

You can find more about Panorama at http://rammpeter.github.io/panorama.html