How to Use the SQL Translation Framework to Workaround Performance and Parsing Problems (Doc ID 1585091.1)

Last updated on JUNE 22, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
This article aims to allow users to work around SQL performance problems caused by various things such as improper syntax where a change in SQL code would normally be needed ( including hints ) but the SQL statement can not be changed immediately.

This can be used to workaround parsing issues and wrong results problems caused by SQL statements that can not be changed immediately.

Goal

The SQL Translation Framework is a tool to facilitate the migration from a non-Oracle database that uses different syntax to Oracle database. By using the framework, you do not need to modify the application. SQL Translation Framework profiles can be used to translate the 'foreign' database syntax in to Oracle Syntax.

For more details about the SQL Translation Framework see:

Oracle® Database Migration Guide
12c Release 1 (12.1)
E22508-10
Chapter 2 SQL Translation Framework Overview and Architecture
http://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#DRDAA131

Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)
E17602-14
DBMS_SQL_TRANSLATOR
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_sql_trans.htm#ARPLS73542

Although designed for non-Oracle databases, the SQL Translation Framework can be applied to Oracle databases so as to workaround issues by replacing the original SQL statement that comes from the application with another SQL statement that solves a performance or parsing problem. As per the documentation, "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."

Since the SQL Translation Framework is applied at an early stage before the SQL goes for parsing, the translated SQL is the only code that is parsed and the Cost Based Optimizer (CBO) does not know about the original SQL code (it may not be Oracle SQL code at all). The SQL Translation Framework takes the original SQL code and pairs this with the code that will replace it. These pairs of statements (original and translated) are stored in a SQL Translation PROFILE. The original code must be an exact match with the original SQL.