How Oracle Changes Your SQL: Uncovering Hidden Query Transformations

INTRO:

I am truly passionate about databases and the intricate mechanisms that operate behind the scenes. In this post, I aim to uncover some of the hidden transformations that occur within Oracle databases. Often, developers approach me with a straightforward SQL statement, puzzled by its performance:

“I’ve written a basic SELECT statement from a table, why does it take seconds to execute instead of milliseconds? Can you tune it?”

Typically, we begin tuning with analyzing the logic, often without prior knowledge of the data model. These situations highlight the challenging aspects of our daily roles. What appears to be a simple starting point can quickly evolve into a complex puzzle. A seemingly basic query may, in reality, be a labyrinthine journey, especially when it references to a view. This view might join six or more tables and using some functions for calculations, transforming the original query into a complex operation that requires a detailed investigation.

This experience shows that things are not always as they seem. In this post, I will explore some of the tools that can perform these “magical” transformations and others that can help uncover the underlying processes, without resorting to 10053 event tracing.

Starting with a Simple Query:

Let’s begin with a seemingly straightforward SQL query:

SQL> select first_name,last_name from hr.employers where job_title='BOSS';

At first glance, this appears to be a simple select from the hr.employers table. However, before executing it, let’s utilize a cool feature to learn sql_id before executing the sql statement.

Leveraging DBMS_SQL_TRANSLATOR.SQL_ID to learn sql_id before execution

Oracle provides a mechanism to obtain the SQL_ID of a statement without executing it, using the DBMS_SQL_TRANSLATOR.SQL_ID function. This can be particularly useful for performance analysis and tuning or even making service level agreements on the execution times of sql statements.

SQL> SELECT DBMS_SQL_TRANSLATOR.SQL_ID('select first_name,last_name from hr.employers where job_title=''BOSS''') AS sql_id FROM dual;
SQL_ID
--------------
cu8fradq0d2b7

This function computes the SQL_ID for the given statement without executing it, allowing you to analyze or monitor the query in advance. For a more detailed explanation and additional context, I recommend reading Connor McDonald’s blog post: Get the SQL_ID for a SQL statement BEFORE you run it.

Lets execute it.

SQL> select first_name,last_name from hr.employers where job_title='BOSS';
FIRST_NAME LAST_NAME
---------- ---------
Michael    Scott

It returned Michael Scott.

Lets execute another one.

Oops, it returned the same result. I was expecting it to fail with ORA-00923: FROM keyword not found where expected. Since AI is trendy these days, you may think I am using it, but this is not a SELECT AI demo 🙂 . My database version is 19.26.

SQL> select banner_full as version from v$version;
VERSION
------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.26.0.0.0

You’ll be even more surprised now: I don’t have a table, view, synonym, or any database object named employers or the_boss, and I’m not doing any case sensitivity trick either.

SQL> select * from dba_objects where upper(object_name) in ('EMPLOYERS','THE_BOSS');
no rows selected.

Another strange thing is that my SQL statement isn’t even present in the shared pool, as it never actually got executed.

SQL> SELECT DBMS_SQL_TRANSLATOR.SQL_ID('select the_boss') AS sql_id FROM dual;
SQL_ID
---------------
a16jpjhkr8uyw
SQL> select * from gv$sql where sql_fulltext like 'select the_boss' or sql_id='a16jpjhkr8uyw';
no rows selected.

Those familiar with the Oracle Database 12c feature SQL Translation Framework have probably already guessed it. I won’t cover it in detail here. In short, it allows SQL statements to be transformed on the fly, which can serve many purposes; most commonly when you can’t modify the application code. It can also be used for quick bug fixes that would otherwise require a full release cycle, for performance tuning, or even just to catch the attention of the Oracle community.

A side note: While using SQL Translation Framework be careful with the bind parameters used. If a bind variable is present in the original query but not needed in the translated SQL statement, you can handle it by adding a dummy condition like :B1 = :B1 which is equivalent to simply 1=1.

Now I will discover the translated sql statement.

Using DBMS_SQL_TRANSLATOR.TRANSLATE_SQL to find out:

DBMS_SQL_TRANSLATOR.TRANSLATE_SQL translates a SQL statement using a SQL translation profile.

SQL> set serveroutput on
DECLARE
    TRANSLATED_SQLTEXT CLOB;
BEGIN
    DBMS_SQL_TRANSLATOR.TRANSLATE_SQL(
        SQL_TEXT        => 'select the_boss',
        TRANSLATED_TEXT => TRANSLATED_SQLTEXT
    );
    DBMS_OUTPUT.PUT_LINE(TRANSLATED_SQLTEXT);
END;
select * from find_the_boss();

We ended up with a function call, but it’s in the FROM clause, not in the projection. That is a SQL Macro with table expression, a new feature introduced in Oracle Database 21c and backported to 19c starting with the 19.6 Release Update.

Now we have a SQL Macro, but we still don’t have any information about the base tables or how my SQL statement is being transformed by the optimizer. To find out the actual query being executed by the SQL Macro, I will use the DBMS_UTILITY.EXPAND_SQL_TEXT procedure.

Leveraging DBMS_UTILITY.EXPAND_SQL_TEXT to uncover

When you execute a SQL query that references views or utilizes SQL Macros like this example, the database internally expands these constructs into their base components. The EXPAND_SQL_TEXT procedure allows you to see this expanded version of your SQL statement, providing clarity on the actual operations being performed.

SQL> SET SERVEROUTPUT ON
DECLARE
    sql_statement            CLOB;
    expanded_sql_statement   CLOB;
BEGIN
    sql_statement := q'[select * from find_the_boss()]';
    DBMS_UTILITY.EXPAND_SQL_TEXT (sql_statement, expanded_sql_statement);
    DBMS_OUTPUT.put_line (expanded_sql_statement);
END;
SELECT "A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAME" "LAST_NAME" FROM
(SELECT "A2"."FIRST_NAME" "FIRST_NAME","A2"."LAST_NAME" "LAST_NAME" FROM
(SELECT "A3"."FIRST_NAME" "FIRST_NAME","A3"."LAST_NAME" "LAST_NAME" FROM
"HR"."EMPLOYEES" "A3" WHERE "A3"."MANAGER_ID" IS NULL) "A2") "A1"

Finally I have the statement executed by the optimizer. By examining the expanded SQL, you can identify unnecessary joins or filters that may be impacting performance, allowing for more targeted optimizations. DBMS_UTILITY.EXPAND_SQL_TEXT can also be utilized to reveal how Oracle internally transforms ANSI SQL syntax, such as LEFT OUTER JOIN, into its native Oracle join syntax using the (+) operator. This feature is particularly useful for understanding the actual execution plan and behavior of SQL statements, especially when dealing with complex queries or performance tuning. We could also use 10053 event tracing “Final query after transformations” section, as Tim Hall did in his blog post “SQL Macros in Oracle Database 21c” to uncover the SQL Macro. However, this method seems simpler to me, and it also doesn’t require flushing the SQL statement from the shared pool to force a hard parse for the 10053 trace.

Which commands were used to prepare the demo environment?

SQL> update hr.employees set first_name='Michael', last_name='Scott' where manager_id is null;
SQL> commit;
-- Create SQL Macro function returning the boss
CREATE OR REPLACE FUNCTION find_the_boss
    RETURN VARCHAR2
    SQL_MACRO
IS
BEGIN
    RETURN q'{select first_name,last_name from hr.employees where manager_id is null}';
END;
-- Create sql translations
exec dbms_sql_translator.create_profile(profile_name => 'MY_TRANSLATOR_PROFILE');
exec dbms_sql_translator.register_sql_translation( profile_name => 'MY_TRANSLATOR_PROFILE', sql_text => 'select first_name,last_name from hr.employers where job_title=''BOSS''', translated_text => 'select * from find_the_boss()', enable => TRUE);
exec dbms_sql_translator.register_sql_translation( profile_name => 'MY_TRANSLATOR_PROFILE', sql_text => 'select the_boss', translated_text => 'select * from find_the_boss()', enable => TRUE);
-- Set sql translation profile and the required event
ALTER SESSION SET events = '10601 trace name context forever, level 32';
ALTER SESSION SET SQL_TRANSLATION_PROFILE = MY_TRANSLATOR_PROFILE;
-- Run the query
select the_boss;

Conclusion

In this post, I have covered the transformations that Oracle performs behind the scenes. DBMS_SQL_TRANSLATOR.SQL_ID allowed us to obtain the SQL ID of a statement before execution and DBMS_SQL_TRANSLATOR.TRANSLATE_SQL_TEXT showed us the trasnlated sql statement. We also used DBMS_UTILITY.EXPAND_SQL_TEXT as a utility to expand SQL statements, for revealing the underlying operations by replacing views and SQL macros with their base queries. This may not only aid in understanding the actual workload but also in identifying potential performance bottlenecks.

Just a reminder, what looks simple on the surface might be hiding a lot more complexity underneath.

Hope it helps.