My Oracle Support Banner

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 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.


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)
Chapter 2 SQL Translation Framework Overview and Architecture

Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)

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.



To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Workaround for: Wrong column name used in SQL, causing a parsing error
 Workaround for: Wrong datatype used in SQL, causing a performance issue
 Setup the Translation Profile
 Create the profile
 Register the translations:
 Set the session to use the particular SQL Translation Profile
 Check that the SQL Translation Profile is used
 Tracking SQL Translations

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.