In my very first post about SQL macros, I mentioned that for table macros, there is a simple way to see the SQL statement after macro expansion using dbms_utility.expand_sql_text. However, for scalar SQL macros, there is no such straightforward method. We can activate a CBO trace (also known as event 10053) and find the final statement in the trace file. This approach works for both scalar and table SQL macros. In this post, we will explore how to do this, and we will use… a SQL macro for that! Well, at least we will give it a try…
If you are a DBA, generating and accessing a CBO trace may not be an issue at all for you. If you are a developer then it may be a bit trickier in terms of the workflow and the required privileges. It would probably make sense to develop this process as a privileged user and encapsulate it in a definer rights PL/SQL unit and then grant the developers the execute privilege on it.
But do we have to use SQL macro for it? – Definitely not! We could develop a procedure or function returning CLOB or a pipelined function returning a content of a trace file row by row. But because I’m just trying to get to know the SQL macros more deeply – what limitations, edge cases, do’s and don’ts there are – I’m reaching for every opportunity to use them. That’s my reason for trying to solve the task as a SQL macro. And indeed, it paid off immediately. Because it was just the use case that made me realize that the behavior of the macros with regard to definer/invoker rights does not correspond to the documentation, as described in the previous post.
The Approach
How do I expect the solution to work? We’ll develop a table SQL macro, which we can select from, passing the original statement with SQL macro(s) as parameter and getting the final query back.
We will use the following SQL macro for the test (let’s name it test macro for clarity):
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;
/
Listing 1
And here is the SQL statement using this macro that we want to inspect more closely:
select ename
, duration_years(hiredate, sysdate) as years
, duration_years(date '2000-01-01', sysdate) as years_y2k
from emp e;
Listing 2
Note 1: How to pass a query as a parameter?
It is not clearly documented, but the values for the text parameters are just not visible (nulled) inside the macro body. If we want to pass the query as a parameter, we can do this by wrapping it in a collection, as Stew Ashton proposes. For example, using a pre-defined Oracle type sys.odcivarchar2list. If you want to learn more about SQL macros, I would highly recommend reading his blog (there are currently 13 posts about SQL macros). And have a look at his very useful package SQM_UTIL.
Note 2: How to force the CBO trace creation
First of all, what is the goal of it? We don’t want to examine the execution plan and why a particular execution plan was generated an selected. We just want to see the query after macro expansion. Hence, we don’t have to pay attention to exact environment such as NLS-settings and so on, compared to how the query will run in real life. Maybe I am wrong, but I don’t yet see how they can influence the macro expansion.
On the other hand, we want the trace file to be created each time we call the SQL macro. The point is, the trace file will only be created while hard parsing. If the SQL has already been hard parsed, no trace file will be created. To overcome this behavior we can use the procedure DBMS_SQLDIAG.DUMP_TRACE. Per default, it also behaves the above-mentioned way, but according to MOS Note 2909982.1 we can force the trace creation by using the value ‘Compiler’ instead of ‘Optimizer’ for the parameter p_component:
begin
DBMS_SQLDIAG.DUMP_TRACE( p_sql_id=>'f8hxn7zk6jvyu'
, p_child_number=>1
, p_component=>'Compiler'
, p_file_id=>'SQM_2023');
end;
/
Listing 3
As you can see, we pass the SQL_ID and the child number. That is, the SQL_ID have to be in the shared pool already or you’ll get an error. First, I thought just to do an OPEN <cursor> FOR <query>. We don’t need to fetch, actually. However, this approach will not work for DML. After all, I decided to treat the presence of as SQL_ID in a shared pool as a requirement.
Now we can start coding and implement the first version.
create or replace function resolve_sqlmacro (P_query in sys.odcivarchar2list)
return varchar2 sql_macro is
v_result varchar2(4000);
v_tracefile varchar2(100) ;
v_dump_trace_cmd varchar2(500);
v_sql_id varchar2(20);
v_cur sys_refcursor;
v_child number;
begin
v_sql_id := dbms_sql_translator.sql_id(p_query(1));
-- will not work for DML
-- open v_cur for p_query (1);
select max(child_number) into v_child from v$sql where sql_id = v_sql_id;
if v_child is not null then
v_dump_trace_cmd :=
replace(
replace(
replace( q'[
begin
DBMS_SQLDIAG.DUMP_TRACE( p_sql_id=>'#sql_id#'
, p_child_number=> '#child#'
, p_component=>'Compiler'
, p_file_id=>'#v_trace_identifier#');
end;]','#sql_id#',v_sql_id)
, '#v_trace_identifier#', 'SQM_'||to_char(sysdate, 'HH24MISS'))
, '#child#', v_child) ;
-- create a trace file
execute immediate v_dump_trace_cmd;
-- what is the name of the trace file?
select substr(regexp_substr(value, '/[^/]*$'),2)
into v_tracefile
from sys.V_$diag_info
where name = 'Default Trace File';
-- return the query to find the relevant line
v_result := q'[ select x.trcline from v$diag_trace_file_contents
match_recognize (
partition by trace_filename
order by line_number
measures payload as trcline
pattern (a b)
define a as (payload like 'Final query after%')
) x
where trace_filename = '#TRACEFILENAME#' ]';
return replace (v_result,'#TRACEFILENAME#', v_tracefile);
else
v_result := replace(q'[select 'The SQL_ID #sql_id# was not found, please execute the statement first.']', '#sql_id#', v_sql_id );
return v_result;
end if;
end;
/
Listing 4
As clarified in the previous post, SQL macros are executed with definer rights. This is also how we want it to work in this case. The macro can be created by a user, who has all required privileges. In this case we need direct grants for V_$SQL and for V_$SQL_BIND_CAPTURE (used in DBMS_SQLDIAG, but this package is in turn invoker rights, so that we need a direct grant after all)
Line 10: Thanks, Ulrike Schwinn for the tip to use the function dbms_sql_translator.sql_id to get the SQL_ID of the statement.
Line 13: find out the child number
Lines 16-29: build the command for creating a CBO trace file and then execute it
Lines 31-34: find out the trace file name
Lines 36-45: prepare the query reading the content of the trace file and using row pattern matching to find the line following “Final Query”.
Let’s test it!
SQL> select *
2 from definer_user.resolve_sqlmacro(
3 sys.odcivarchar2list(
4 q'[select ename
5 , duration_years(hiredate, sysdate) as years
6 , duration_years(date '2000-01-01', sysdate) as years_y2k
7 from emp e]')) k;
TRCLINE
---------------------------------------------------------------------------
The SQL_ID 0b8w4khcapnw6 was not found, please execute the statement first.
Listing 5
So far so good, now we run the SQL statement to get it hard parsed and then run the macro again:
SQL> select ename
2 , duration_years(hiredate, sysdate) as years
3 , duration_years(date '2000-01-01', sysdate) as years_y2k
4 from emp e;
ENAME YEARS YEARS_Y2K
---------- ---------- ----------
SMITH 42 23
ALLEN 42 23
WARD 42 23
...
SQL> select *
2 from definer_user.resolve_sqlmacro(
3 sys.odcivarchar2list(
4 q'[select ename
5 , duration_years(hiredate, sysdate) as years
6 , duration_years(date '2000-01-01', sysdate) as years_y2k
7 from emp e]')) k;
TRCLINE
---------------------------------------------------------------------------
The SQL_ID 0b8w4khcapnw6 was not found, please execute the statement first.
Listing 6
What?! The same message again? But the SQL_ID must be in the shared pool this time!
It’s so easy to fall into this trap again and again! It is just how SQL macros are supposed to work:
First run:
- the statement from listing 5 has been hard parsed
- the SQL Macro resolve_sqlmacro was executed but could not find the SQL_ID
- The query with the hard-coded message was constructed and returned to the main query
Second run:
- we run the test SQL statement from the listing 2, now we should have the SQL_ID there
- we re-run the “resolving” SQL from listing 5 again
- this SQL has been run and hard parsed during first run – no hard parse this time
- the SQL Macro resolve_sqlmacro was NOT executed at all
If we want to do really dynamic things, we have to work around this behavior (notice, I didn’t call it a problem or a limitation, because it is the way it works). And indeed, there is a brilliant solution that again can be found on Stew Ashtons’s Blog (proposed by Iudith Mentzel as he mentions). The idea is to use dbms_utility.invalidate call to invalidate the macro and place it in the calling SQL statement. The SQL macro will run at parse time, then it will be invalidated at run time of the statement which is not a problem at all. But it invalidates all cursors using this macro and will force a hard parse! Please refer Stew’s Blog for further details and the implementation of the function INVALIDATE_OBJECT which I will use without description here.
create or replace function resolve_sqlmacro (P_query in sys.odcivarchar2list)
return varchar2 sql_macro is
v_result varchar2(4000);
v_tracefile varchar2(100) ;
v_dump_trace_cmd varchar2(500);
v_sql_id varchar2(20);
v_cur sys_refcursor;
v_child number;
begin
v_sql_id := dbms_sql_translator.sql_id(p_query(1));
-- will not work for DML
-- open v_cur for p_query (1);
select max(child_number) into v_child from sys.v_$sql where sql_id = v_sql_id;
if v_child is not null then
v_dump_trace_cmd :=
replace(
replace(
replace( q'[
begin
DBMS_SQLDIAG.DUMP_TRACE( p_sql_id=>'#sql_id#'
, p_child_number=> '#child#'
, p_component=>'Compiler'
, p_file_id=>'#v_trace_identifier#');
end;]','#sql_id#',v_sql_id)
, '#v_trace_identifier#', 'SQM_'||to_char(sysdate, 'HH24MISS'))
, '#child#', v_child) ;
-- create a trace file
execute immediate v_dump_trace_cmd;
-- what is the name of the trace file?
select substr(regexp_substr(value, '/[^/]*$'),2)
into v_tracefile
from sys.V_$diag_info
where name = 'Default Trace File';
-- return the query to find the relevant line
v_result := q'[ select x.trcline from v$diag_trace_file_contents
match_recognize (
partition by trace_filename
order by line_number
measures payload as trcline
pattern (a b)
define a as (payload like 'Final query after%')
) x
where trace_filename = '#TRACEFILENAME#'
and 0 = (
select invalidate_object(']'
||$$PLSQL_UNIT_OWNER||''','''||$$PLSQL_UNIT||
''' ) from dual)';
return replace (v_result,'#TRACEFILENAME#', v_tracefile);
else
v_result := replace(q'[select 'The SQL_ID #sql_id# was not found, please execute the statement first.' trcline
where 0 = (
select invalidate_object(']'
||$$PLSQL_UNIT_OWNER||''','''||$$PLSQL_UNIT
||''' ) from dual)', '#sql_id#', v_sql_id );
return v_result;
end if;
end;
/
Function RESOLVE_SQLMACRO compiled
SQL> select *
2 from definer_user.resolve_sqlmacro(
3 sys.odcivarchar2list(
4 q'[select ename
5 , duration_years(hiredate, sysdate) as years
6 , duration_years(date '2000-01-01', sysdate) as years_y2k
7 from emp e]')) k;
TRCLINE
---------------------------------------------------------------------------
The SQL_ID 0b8w4khcapnw6 was not found, please execute the statement first.
SQL> select ename
2 , duration_years(hiredate, sysdate) as years
3 , duration_years(date '2000-01-01', sysdate) as years_y2k
4 from emp e;
ENAME YEARS YEARS_Y2K
---------- ---------- ----------
SMITH 42 23
ALLEN 42 23
...
SQL> select *
2 from definer_user.resolve_sqlmacro(
3 sys.odcivarchar2list(
4 q'[select ename
5 , duration_years(hiredate, sysdate) as years
6 , duration_years(date '2000-01-01', sysdate) as years_y2k
7 from emp e]')) k;
TRCLINE
-----------------------------------------------------------------------------------------------------------
SELECT "E"."ENAME" "ENAME",FLOOR(MONTHS_BETWEEN(SYSDATE@!,"E"."HIREDATE")/12)
"YEARS",FLOOR(MONTHS_BETWEEN(SYSDATE@!,TO_DATE(' 2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))/12) "YEARS_Y2K" FROM "SCOTT"."EMP" "E"
Listing 7
Finally! Now we can see the query after expansion of all SQL macros.
Was it worth it?
Well, I think it can be interesting to check how the actual SQL statement will look like. And we could demonstrate how one can automate it. In this respect it was already worth it. But using a SQL macro for this felt like overkill to me (passing parameters as collections, adding a function call to force a hard parse next time). I would just use a pipelined function like this:
create or replace function resolve_sqm (P_query in varchar2)
return sys.odcivarchar2list pipelined is
v_result varchar2(4000);
v_tracefile varchar2(100) ;
v_dump_trace_cmd varchar2(500);
v_sql_id varchar2(20);
v_cur sys_refcursor;
v_child number;
v_trcline varchar2(32000);
begin
v_sql_id := dbms_sql_translator.sql_id(p_query);
-- will not work for DML
-- open v_cur for p_query (1);
select max(child_number) into v_child from sys.v_$sql where sql_id = v_sql_id;
if v_child is not null then
v_dump_trace_cmd :=
replace(
replace(
replace( q'[
begin
DBMS_SQLDIAG.DUMP_TRACE( p_sql_id=>'#sql_id#'
, p_child_number=> '#child#'
, p_component=>'Compiler'
, p_file_id=>'#v_trace_identifier#');
end;]','#sql_id#',v_sql_id)
, '#v_trace_identifier#', 'SQM_'||to_char(sysdate, 'HH24MISS'))
, '#child#', v_child) ;
dbms_output.put_line (v_dump_trace_cmd);
-- create a trace file
execute immediate v_dump_trace_cmd;
-- what is the name of the trace file?
select substr(regexp_substr(value, '/[^/]*$'),2)
into v_tracefile
from sys.V_$diag_info
where name = 'Default Trace File';
-- return the query to find the relevant line
v_result := q'[ select x.trcline from v$diag_trace_file_contents
match_recognize (
partition by trace_filename
order by line_number
measures payload as trcline
pattern (a b)
define a as (payload like 'Final query after%')
) x
where trace_filename = '#TRACEFILENAME#'
]';
open v_cur for replace (v_result,'#TRACEFILENAME#', v_tracefile);
loop
fetch v_cur into v_trcline;
exit when v_cur%notfound;
pipe row(v_trcline);
end loop;
else
pipe row (replace(q'[The SQL_ID #sql_id# was not found, please execute the statement first.]', '#sql_id#', v_sql_id ));
end if;
end;
/
Listing 8
Having said that, there are still some points open for me.
- What else can we see in these trace files?
- Can it be useful for overall understanding how SQL macros work?
- What about SQL macros in DML statements? (That was the reason why I refrained from parsing the input SQL and expect the SQL_ID to be there, but it’s not that simple)
I plan to cover this in my next post.
Hi Andrej,
Wonderful, it was really worth to make this test 🙂
I just wonder what could be the reason that the effect of a scalar SQL macro cannot be seen by using
dbms_utility.expand_sql_text, aka the same as for table macros.
I also found some examples using a table SQL macro for which dbms_utility.expand_sql_text does show
the transformed SQL, but if we try to execute directly the resulting SQL we get a syntax error (some aliases introduced by the transformation are used out of their scope).
It looks to me that this entire SQL macro topic is not yet “completely mature”, there are still some bugs
that will probably be addressed in the upcoming versions, as feedback from developers will continue to come in.
The topic is however exciting, and I am glad to enjoy so valuable contributions from developers like you and Stew Ashton 🙂
Cheers & Best Regards,
Iudith Mentzel