Who has not experienced this before? You want to tune a relatively simple query and suddenly you have to deal with a very long execution plan. After some analysis, you realize that multiple views are used in the query; of course hiding extensive queries using views to reduce query complexity belongs to our best practices.
How do you get this information? One way to answer these questions is to query the data dictionary. View definitions can be found e.g. in DBA_VIEWS. In addition as of Oracle Database 12c we have a simple and documented way to see the output of the expansion that is done by the parser – the DBMS_UTILITY.EXPAND_SQL_TEXT procedure. The procedure EXPAND_SQL_TEXT of the package DBMS_UTILITY takes a query as input parameter and replaces the view references with the corresponding queries on the tables. Examples of this can be found e.g. in the Maria Colgan’s or Tim Hall’s blogs. To get an idea or to refresh your knowledge, let’s illustrate this functionality in a short example.
Let’s assume we have the following query:
SELECT prod_name, sum(amount_sold) FROM my_view INNER JOIN sh.products USING (prod_id) GROUP BY prod_name;
How can we find out what is behind the view? Of course we could query the data dictionary views such as USER_VIEWS. However let’s try the EXPAND_SQL_TEXT functionality:
set serveroutput on
declare
input_1 clob;
output_1 clob;
begin
input_1:='SELECT prod_name, sum(amount_sold) FROM my_view INNER JOIN sh.products using(prod_id) GROUP BY prod_name';
DBMS_UTILITY.EXPAND_SQL_TEXT (input_1,output_1);
dbms_output.put_line('The result is: ');
dbms_output.put_line(output_1);
end;
/
The result is:
SELECT "A1"."PROD_NAME_1" "PROD_NAME",SUM("A1"."AMOUNT_SOLD_0")
"SUM(AMOUNT_SOLD)" FROM (SELECT "A3"."AMOUNT_SOLD"
"AMOUNT_SOLD_0","A2"."PROD_NAME" "PROD_NAME_1" FROM (SELECT "A4"."PROD_ID"
"PROD_ID","A4"."AMOUNT_SOLD" "AMOUNT_SOLD" FROM SH."SALES" "A4" WHERE
"A4"."AMOUNT_SOLD"<1300) "A3","SH"."PRODUCTS" "A2" WHERE
"A3"."PROD_ID"="A2"."PROD_ID") "A1" GROUP BY "A1"."PROD_NAME_1"
The output provides the information about the underlying tables and queries. The result still has to be formatted a bit, since everything is capitalized and apostrophes are used.
To make it more readable, I formatted the result:
SELECT
“A1”.”PROD_NAME_1″ “PROD_NAME”,
SUM(“A1”.”AMOUNT_SOLD_0″) “SUM(AMOUNT_SOLD)”
FROM
(SELECT
“A3″.”AMOUNT_SOLD” “AMOUNT_SOLD_0”,
“A2″.”PROD_NAME” “PROD_NAME_1”
FROM
(SELECT
“A4″.”PROD_ID” “PROD_ID”,
“A4″.”AMOUNT_SOLD” “AMOUNT_SOLD”
FROM SH.”SALES” “A4”
WHERE “A4”.”AMOUNT_SOLD”<1300) “A3”, “SH”.”PRODUCTS” “A2”
WHERE “A3″.”PROD_ID”=”A2″.”PROD_ID”) “A1”
GROUP BY “A1”.”PROD_NAME_1″
It can also help to explore Oracle database functions and uncover the database techniques behind them. As an example let’s investigate the feature SQL MACRO that was introduced in 21c.
We create the following simple self explaining SQL MACRO function:
create function emp_per_location (p_location varchar2) return varchar2 SQL_MACRO(TABLE) as begin return( 'select ename, job from emp where deptno = (select deptno from dept where loc = p_location)'); end; /
Note: SQL Macros are also available in 19c with slightly different syntax – instead of SQL_MACRO(TABLE) use SQL_MACRO.
Now we use EXPAND_SQL_TEXT again.
set serveroutput on
declare
input_1 clob;
output_1 clob;
begin
input_1:=q'!select * from emp_per_location('DALLAS')!';
DBMS_UTILITY.EXPAND_SQL_TEXT (input_1,output_1);
dbms_output.put_line('the result is: ');
dbms_output.put_line(output_1);
end;
/
Here is the formatted result:
SELECT “A1″.”ENAME” “ENAME”,
“A1″.”JOB” “JOB”
FROM (SELECT “A2″.”ENAME” “ENAME”,
“A2″.”JOB” “JOB”
FROM (SELECT “A3″.”ENAME” “ENAME”,
“A3″.”JOB” “JOB”
FROM “ADMIN”.”EMP” “A3”
WHERE “A3”.”DEPTNO”= (SELECT “A4″.”DEPTNO” “DEPTNO”
FROM “ADMIN”.”DEPT” “A4” WHERE “A4”.”LOC”=’DALLAS’)
) “A2”
) “A1”
There are also other interesting use cases for example VPD (Virtual Private Database). Virtual Private Databases (VPD) allow multiple users to access a single schema whilst preventing them from accessing data that is not relevant to them.To implement VPD, you must create a function to generate the dynamic WHERE clause, and a policy to attach this function to the objects that you want to protect.Only the records filtered by the WHERE condition is visible for the user. If you want to tune such a query, you have to find out what the filter condition looks like.
As an example, consider sales department users with the SELECT privilege on the emp table, which is protected with a column-level Oracle Virtual Private Database policy. The user SCOTT runs the following query.
SQL> SELECT e.ename, d.dname, e.job, e.sal, e.comm FROM emp e, dept d WHERE d.deptno = e.deptno;
The database returns the following rows:
ENAME DNAME JOB SAL COMM ------------------------------ ------------------------------------------ --------------------------- ---------- ---------- BLAKE SALES MANAGER 2850 ALLEN SALES SALESMAN 1600 300 WARD SALES SALESMAN 1250 500 MARTIN SALES SALESMAN 1250 1400 TURNER SALES SALESMAN 1500 0 JAMES SALES CLERK 950 6 rows selected.
Now instead of investigating DBA_POLICIES and the required policy function, let’s use EXPAND_SQL_TEXT.
set serveroutput on
declare
input_1 clob;
output_1 clob;
begin
input_1:='SELECT e.ename, d.dname, e.job, e.sal, e.comm FROM emp e, dept d WHERE d.deptno = e.deptno';
dbms_utility.expand_sql_text(input_1, output_1);
dbms_output.put_line('The result is: ');
dbms_output.put_line(output_1);
end;
/
The result is:
SELECT "A2"."ENAME" "ENAME","A1"."DNAME" "DNAME","A2"."JOB" "JOB","A2"."SAL" "SAL","A2"."COMM" "COMM" FROM (SELECT "A3"."EMPNO" "EMPNO","A3"."ENAME" "ENAME","A3"."JOB" "JOB","A3"."MGR" "MGR","A3"."HIREDATE""HIREDATE","A3"."SAL" "SAL","A3"."COMM" "COMM","A3"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "A3"
WHERE "A3"."DEPTNO"=30) "A2","SCOTT"."DEPT" "A1" WHERE "A1"."DEPTNO"="A2"."DEPTNO"
Obviously the result is filtered by the WHERE clause “A3”.”DEPTNO”=30.
These are only some examples to explain the usage. There are many more cases in which the procedure can be helpful. For example the posting from Oren Nakdimon EXPAND_SQL_TEXT – Much More Than Just Expanding Views gives further examples such as Pivoting, Row Limiting or even Temporal Validity.
The best is to try it yourself and find your own preferred usage scenario. 🙂
A detailed syntax description can be find in the documentation.