In the previous post I showed how to get to the final query after SQL macro expansion using CBO trace. But there are still a few interesting things in the trace file that are worth looking at.
Here are three SQL macros we will test with: two scalar ones and one table macro:
create or replace function duration_years
(p_date_from in date
, p_date_to in date)
return varchar2 sql_macro(scalar) as
begin
return q'[ floor(months_between (p_date_to, p_date_from)/12) ]';
end;
create or replace FUNCTION upper_macro (p_param in varchar2)
return varchar2 sql_macro(scalar) as
begin
return 'UPPER(p_param)';
end;
create or replace function top_n (p_tab in dbms_tf.table_t, p_limit in number
, p_order in dbms_tf.columns_t)
return varchar2 sql_macro is
v_order_list varchar2(2000);
begin
-- turn PL/SQL table to comma separated list for ORDER BY clause
select listagg(replace(column_value,'"'),',') into v_order_list from table (p_order);
return 'select * from p_tab order by '||v_order_list||
' fetch first p_limit rows only';
end;
Listing 1
We’ll test with the following SQL:
select ename
, duration_years(hiredate, sysdate) as years2
, duration_years(date '2001-01-01', sysdate) as years_y2k1
, duration_years(:p_bind, sysdate) as years_bind1
, upper_macro(ename) as ename
, upper_macro('adams') as adams
from top_n(emp,10, columns(empno)) e;
Listing 2
Now we can resolve to the final SQL statement using the function introduced in the previous post.
select *
from resolve_sqm(
q'[select ename
, duration_years(hiredate, sysdate) as years2
, duration_years(date '2001-01-01', sysdate) as years_y2k1
, duration_years(:p_bind, sysdate) as years_bind1
, upper_macro(ename) as ename
, upper_macro('adams') as adams
from top_n(emp,10, columns(empno)) e]') k;
-- final SQL (formatted)
SELECT
"from$_subquery$_009"."ENAME" "ENAME",
floor(months_between(sysdate@!, "from$_subquery$_009"."HIREDATE") / 12) "YEARS",
floor(months_between(sysdate@!,TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) / 12) "YEARS_Y2K",
floor(months_between(sysdate@!, :b1) / 12) "YEARS_BIND",
upper("from$_subquery$_009"."ENAME") "ENAME",
'ADAMS' "ADAMS"
FROM
(
SELECT
"EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO",
"EMP"."EMPNO" "rowlimit_$_0",
ROW_NUMBER() OVER( ORDER BY "EMP"."EMPNO") "rowlimit_$$_rownumber"
FROM "EMP"
) "from$_subquery$_009"
WHERE "from$_subquery$_009"."rowlimit_$$_rownumber" <= 10
ORDER BY "from$_subquery$_009"."rowlimit_$_0"
Listing 3
First, we can inspect the final query. There are some interesting points there. Notice that with the resolve macro from the previous post we get the “final” query, that means after ALL transformations. So we can expect some changes in the initial query that may have nothing to do with expanding of SQL macros at all. Here, we can see how Oracle actually implements the Row Limiting Clause (introducing an inline subquery with ROW_NUMBER() and filtering afterwards). Also look at the line 18: obviously Oracle has evaluated the expression returned from the SQL macro already at parse time.
Now let’s then look at the contents of the trace file:
select payload
from sys.V_$diag_trace_file_contents
where trace_filename = 'FREE_ora_4911_SQM_205253.trc';
The first section that has something to do with SQL macros looks like this:
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
t0 DBMS_TF.Table_t := DBMS_TF.Table_t(column => :0, table_schema_name => '"ONFTEST"', table_name => '"EMP"');
begin
:macro_text := "TOP_N"(t0,10,:5 );
end;
qksptfSQM_GetTxt(): Macro Text
==============================
SELECT * FROM p_tab ORDER BY EMPNO FETCH FIRST p_limit ROWS ONLY
qksptfSQM_Template(): Template Text
===================================
with P_TAB as (select /*+ INLINE */ * from "ONFTEST"."EMP")
select "SYS__$".*
from (select NULL "P_LIMIT" from SYS.DUAL) "TOP_N",
lateral(SELECT * FROM p_tab ORDER BY EMPNO FETCH FIRST p_limit ROWS ONLY) "SYS__$"
Listing 4
qksptfSQM_GetTxt(): Anonymous Block
Obviously, the anonymous PL/SQL block is how Oracle calls the table SQL macro to get the SQL fragment back. The variable t0 of the type DBMS_TF.TABLE_T is defined and populated. A bind variable :0 is of type DBMS_TF.TABLE_COLUMNS_T and it is a collection of records containing column metadata. Oracle takes care of the correct population of the record structure behind this variable. The bind variable :5 is the column list.
qksptfSQM_GetTxt(): Macro Text.
We can see the SQL text returned from the macro function – no surprises here.
qksptfSQM_Template(): Template Text
I’m unsure about this: what exactly is this template for? We can see how the table used as a macro parameter is queried in the WITH-clause (CTE). Then we see the scalar parameter being selected from dual and then passed to the main query using a lateral join. But this subquery, selecting from dual, looks like a template where the actual parameter values have to be substituted for NULL’s. Once that has happened, the whole query could already be the final one (let’s leave the scalar SQL macros out of the equation for now). But it is not! The final query which we will see later in the trace file doesn’t have any CTE or lateral joins!
Next we can find similar blocks but for scalar macros this time (Listing 4). It starts the same: anonymous block to obtain the macro text, then showing it. But the template looks different this time. The macro expression is selected from a subquery which in turn selects all parameters from DUAL. Again all the parameters in this subquery are NULL.
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
begin
:macro_text := "UPPER_MACRO"(:3 );
end;
qksptfSQM_GetTxt(): Macro Text
==============================
UPPER(p_param)
qksptfSQM_Template(): Template Text
===================================
select (UPPER(p_param))
from (select NULL "P_PARAM" from SYS.DUAL) "UPPER_MACRO"
Listing 5
The difference between the two calls of upper_macro is the parameter being passed once as bind variable and once as NULL (compare Listings 5 and 6). You may think, the bind variable stays for the invocation with a column name and NULL for a literal? – Wrong! It is the other way round. How do i know? I’ve also tested these two invocations separately. Weird enough: the “Peeked values of the binds in SQL statement” section is completely empty, but afterwards we see the result of the function evaluation directly in the final query.
declare
begin
:macro_text := "UPPER_MACRO"(NULL);
end;
Listing 6
The invocation with the column name passed uses NULL as parameter. What sounds weird is actually quite logical: we are currently parsing and don’t have any access to column values, you cannot inspect data at this phase.
The following sections are looking similar, showing the invocations of the next macro DURATION_YEARS. There are three different invocations resulting in different qksptfSQM_GetTxt(): Anonymous Block sections, but resulting in identical qksptfSQM_GetTxt(): Macro Text and qksptfSQM_Template(): Template Text sections. I placed my own comments how they relate to the invocations in the test SQL statement (Listing 7):
-- Invocation duration_years(:p_bind, sysdate) as years_bind1
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
begin
:macro_text := "DURATION_YEARS"(:3 ,NULL);
end;
...
-- Invocation duration_years(date '2001-01-01', sysdate) as years_y2k1
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
begin
:macro_text := "DURATION_YEARS"(TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'),NULL);
end;
...
-- Invocation duration_years(hiredate, sysdate) as years2
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
begin
:macro_text := "DURATION_YEARS"(NULL,NULL);
end;
qksptfSQM_GetTxt(): Macro Text
==============================
floor(months_between (p_date_to, p_date_from)/12)
qksptfSQM_Template(): Template Text
===================================
select ( floor(months_between (p_date_to, p_date_from)/12) )
from (select NULL "P_DATE_FROM",NULL "P_DATE_TO" from SYS.DUAL) "DURATION_YEARS"
Listing 7
Again, we can’t see what exactly happens with those identical “template texts”. The fact is, they do not become part of the final SQL in that form. Instead, the expression as we see under “Macro Text” section is there. Noteworthy how these three expressions are different in the final query:
floor(months_between(sysdate@!, "from$_subquery$_009"."HIREDATE") / 12) "YEARS",
floor(months_between(sysdate@!,TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) / 12) "YEARS_Y2K",
floor(months_between(sysdate@!, :b1) / 12) "YEARS_BIND",
Listing 8
Of course, SYSDATE is not evaluated at parse time, but goes directly to the final query. Also we can see the column reference, the date literal and the bind variable in the final query – as we have used them for macro invocation – no surprise here.
It is worth mentioning, that we can already find the query after SQL macro expansion earlier in the trace file, being the part of the output sections for some (not all) query transformations. But it is harder to find the right one: sometimes we see only parts of the original query and so on. That’s why I’m looking at the section “Final query after transformations“.
DML Statements
You can use SQL macros in DML statements, for example like this
update emp set ename = upper_macro(ename);
Listing 9
Unfortunately, we cannot see the final statement in the trace file in this case. The UPDATE SQL can only be seen in the form as in the Listing 9 – with SQL macros. In the “Final query after transformations” section we just see this:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 0 FROM "ONFTEST"."EMP" "EMP"
Conclusion
Looking at the CBO trace file, we were able to get some insights how SQL macros are called to get back the macro text. Unfortunately, we can’t get the final statement for DML like UPDATE.