Catch the SQL:s behind the scene

Views can create all kind of problems. It’s very common to see SQL against a view which in turn is based on another view or table, which in turn…….. No one has ever done a proper analyze of what is really needed nor how “heavy” these views are. The potential problems might first show up in production.

Let’s say we analyze the definition of the views first. We find that the SQL of the views looks “visually” perfect for what we are trying to achieve. But are we sure that Oracle really will execute the SQL statement as defined in the views?

One way to know for sure is to use dbms_utility.expand_sql_text.
Which recursively replaces any view references in the input SQL query with the corresponding view subquery.
dbms_utility.expand_sql_text is available from release 12.1 prior to that one could use dbms_sql2.expand_sql_text(unsupported)

Let’s create a table and a view based on that table.

create table emojis(smiley_name varchar2(100),tale varchar2(200),
constraint emojis_pk primary key (smiley_name));

Table EMOJIS created.

insert all
into emojis(smiley_name,tale)
values('Grinning','A yellow face grinning')

into emojis(smiley_name,tale)
values('Smiling','A yellow face with a modest smile, rosy cheeks')

into emojis(smiley_name,tale)
values('Disappointed','A yellow face with a frown and closed, downcast eyes')

into emojis(smiley_name,tale)
values('Angry','A red face with a frowning mouth and eyes and eyebrows scrunched downward in anger')
select 1 from dual;

4 rows inserted.

create or replace view emojis_vw as select * from emojis;

View EMOJIS_VW created.

The expand_sql_text procedure is defined as following

DBMS_UTILITY.EXPAND_SQL_TEXT (input_sql_text IN CLOB,
                              output_sql_text OUT NOCOPY   CLOB);

Now if we run a select * from emojis_vw and expand that sql.

set serveroutput on 
declare
  lv_sql clob;
begin
  dbms_utility.expand_sql_text ( 'select * from emojis_vw',lv_sql);
  dbms_output.put_line(lv_sql);
end;
/
PL/SQL procedure successfully completed.

SELECT
    "A1"."SMILEY_NAME"   "SMILEY_NAME",
    "A1"."TALE"          "TALE"
FROM
    (
        SELECT
            "A2"."SMILEY_NAME"   "SMILEY_NAME",
            "A2"."TALE"          "TALE"
        FROM
            "RIOR_LOGIK"."EMOJIS" "A2"
    ) "A1"

This is the actual SQL that Oracle will run. But dbms_utility.expand_sql_text does not for example show if an implicit conversion takes place. To see that level of detail you need to take a look at the execution plan.

Over&Out