My Oracle Support Banner

How to Diagnose and Track SQL Translations (Doc ID 1586667.1)

Last updated on MARCH 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


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

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. 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 to which this need to be translated. These pairs of information are stored in a SQL Translation PROFILE as pairs of statements (original and translated). The original code must be an exact match with the original SQL.

You can see a worked example of how to do this in:

<Document 1585091.1> How to Use the SQL Translation Framework to Workaround Performance and Parsing Problems

 This document shows how you might investigate further and track which SQL Translation Profiles have been created.


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
 How to diagnose and track SQL translations
 Key points

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