Thursday, June 27, 2013

Oracle 12c New Features - DBMS_UTILITY.EXPAND_SQL_TEXT

As a DBA you occasionally get handed SQL statements many hundreds of lines long, and asked to help tune it.  Often the SQL selects from views based on views based on views, which can force the database to access the same table multiple times and join to itself, leading to poor performance.  To work out what the sql is actually doing the DBA needs to extract the sql from every view, merge it, then try to work out if it can be improved.  This can be time consuming, but 12c has introduced DBMS_UTILITY.EXPAND_SQL_TEXT to help.

The following gives and example of expanding a simple sql statement based on a view.


SQL> create table employee (emp_id integer, emp_name varchar2(20));

Table created.

SQL> insert into employee values (1,'John');

1 row created.

SQL> insert into employee values (2,'David');

1 row created.

SQL> commit;

Commit complete.

SQL> create view v1 as select * from employee;

View created.

SQL> create view v2 as select * from employee;

View created.

If we were just given the following piece of SQL we could run it through dbms_utility.expand_sql_text to get a better idea of what the logic in v1 and v2 is.  

SQL> select * from v1 union select * from v2;

    EMP_ID EMP_NAME
---------- --------------------
         1 John
         2 David

SQL> set linesize 32000 pagesize 0 serveroutput on
SQL> declare
   original_sql clob :='select * from v1 union select * from v2';
   expanded_sql clob := empty_clob();
begin
    dbms_utility.expand_sql_text(original_sql,expanded_sql);
    dbms_output.put_line(expanded_sql);
end;
/  
(SELECT "A3"."EMP_ID" "EMP_ID","A3"."EMP_NAME" "EMP_NAME" FROM  (SELECT "A4"."EMP_ID" "EMP_ID","A4"."EMP_NAME" "EMP_NAME" FROM TEST."EMPLOYEE" "A4") "A3")UNION (SELECT "A2"."EMP_ID" "EMP_ID","A2"."EMP_NAME" "EMP_NAME" FROM  (SELECT "A5"."EMP_ID" "EMP_ID","A5"."EMP_NAME" "EMP_NAME" FROM TEST."EMPLOYEE" "A5") "A2")

PL/SQL procedure successfully completed.

So we can see from the expanded query that the original sql was just doing a union of two identical selects from table TEST.EMPLOYEE, which we can easily simplify to a single query with better performance.

# I found the following reference from Jonathan Lewis which indicates this procedure previously existed in package DBMS_SQL2