How to Use the SQL Translation Framework to Workaround Performance and Parsing Problems
(Doc ID 1585091.1)
Last updated on MARCH 28, 2019
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata 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|