What Can You Use DBMS_UTILITY.EXPAND_SQL_TEXT For?

Have you ever worked through a tuning exercise (I’d like to call them exercises instead of problems), where the SQL Statement looked very simple, like:

select * from complex_view;

yet the execution plan was 2-3 pages long.

And as you started digging deeper down into the view, you noticed the complex_view was referencing two other views and a table. Then the underlying views were referencing another view and some tables. And then finally you figured out the last tables.
So instead of a SQL Statement that takes up one line only, you are actually dealing with a very complex SQL statement.

The purpose of the complex_view view, is to hide the complexity of the SQL statement.

Usually this is a nightmare to tune and to figure out! It can take hours to drill down to the base objects that are used in the SQL statement.

That is why today’s post is dedicated to the following package: DBMS_UTILITY.EXPAND_SQL_TEXT, to take you out of the tuning nightmare and put you into a nice dream!

I just came across the EXPAND_SQL_TEXT procedure of the DBMS_UTILITY package, a few days ago, by accident. This procedure was introduced in 12.1, so it is not so new, but it is new to me, and new to anyone else who has not used it or has not heard of it.

So what does this procedure do?

Based on Oracle’s documentation, DBMS_UTILITY.EXPAND_SQL_TEXT recursively replaces any view references in the input SQL query with the corresponding view sub-query.

To better understand it, let’s have a look with an example:

This will be a basic example, with a view that joins two tables. Then I will call DBMS_UTILITY.EXPAND_SQL_TEXT to see what I get when I query the view:

CREATE OR REPLACE VIEW HR.EMP_DEPT_V AS
 SELECT E.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT(E.EMPLOYEE_ID) NUM_EMP
 FROM   EMPLOYEES E JOIN DEPARTMENTS D 
 ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
 GROUP BY E.DEPARTMENT_ID, D.DEPARTMENT_NAME
 ORDER BY E.DEPARTMENT_ID;

SET SERVEROUTPUT ON 
DECLARE
  output_sql_text CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'SELECT * FROM HR.EMP_DEPT_V',
    output_sql_text => out_txt
    );

  DBMS_OUTPUT.put_line(output_sql_text);
END;
/

SELECT "A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."DEPARTMENT_NAME"
"DEPARTMENT_NAME","A1"."NUM_EMP" "NUM_EMP" FROM  (SELECT
"A2"."QCSJ_C000000000400000_1" "DEPARTMENT_ID","A2"."DEPARTMENT_NAME_3"
"DEPARTMENT_NAME",COUNT("A2"."EMPLOYEE_ID_0") "NUM_EMP" FROM  (SELECT
"A4"."EMPLOYEE_ID" "EMPLOYEE_ID_0","A4"."DEPARTMENT_ID"
"QCSJ_C000000000400000_1","A3"."DEPARTMENT_ID"
"QCSJ_C000000000400001","A3"."DEPARTMENT_NAME" "DEPARTMENT_NAME_3" FROM
"HR"."EMPLOYEES" "A4","HR"."DEPARTMENTS" "A3" WHERE
"A4"."DEPARTMENT_ID"="A3"."DEPARTMENT_ID") "A2" GROUP BY
"A2"."QCSJ_C000000000400000_1","A2"."DEPARTMENT_NAME_3" ORDER BY
"A2"."QCSJ_C000000000400000_1") "A1"

The output you get is not very well formatted, but you can use your DBA Magik, remove double quotes, align the key words, replace the aliases with lower case, and voila you get a nicely formatted output.

SELECT a1.DEPARTMENT_ID DEPARTMENT_ID,
       a1.DEPARTMENT_NAME DEPARTMENT_NAME,
       a1.NUM_EMP NUM_EMP 
FROM  (SELECT a2.QCSJ_C000000000400000_1 DEPARTMENT_ID,
              a2.DEPARTMENT_NAME_3 DEPARTMENT_NAME,
              COUNT(a2.EMPLOYEE_ID_0) NUM_EMP 
       FROM  (SELECT a4.EMPLOYEE_ID EMPLOYEE_ID_0,
                     a4.DEPARTMENT_ID QCSJ_C000000000400000_1,
                     a3.DEPARTMENT_ID QCSJ_C000000000400001,
                     a3.DEPARTMENT_NAME DEPARTMENT_NAME_3 
              FROM HR.EMPLOYEES a4, HR.DEPARTMENTS a3 
              WHERE a4.DEPARTMENT_ID=a3.DEPARTMENT_ID) a2 
       GROUP BY a2.QCSJ_C000000000400000_1,a2.DEPARTMENT_NAME_3 
       ORDER BY a2.QCSJ_C000000000400000_1) a1

This procedure DBMS_UTILITY.EXPAND_SQL_TEXT, makes your tuning exercise a bit easier, and this can save you a few hours of investigative work.

Give it a try, and save it into your toolbox!

I am curious, have you used this package and procedure before? Were you aware of it?
Leave a comment below, I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana