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

Last updated on SEPTEMBER 20, 2022

Applies to:

Goal

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:

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

Solution

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