{"id":1938,"date":"2024-01-03T10:30:20","date_gmt":"2024-01-03T08:30:20","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1938"},"modified":"2024-01-03T10:30:21","modified_gmt":"2024-01-03T08:30:21","slug":"sql-macros-what-is-in-the-cbo-trace","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/sql-macros-what-is-in-the-cbo-trace\/","title":{"rendered":"SQL Macros – what is in the CBO trace?"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n\n\n\n\n

Here are three SQL macros we will test with: two scalar ones and one table macro:<\/p>\n\n\n

\ncreate or replace function duration_years \n                    (p_date_from in date\n                   , p_date_to in date) \nreturn varchar2 sql_macro(scalar) as\nbegin\n   return q'[ floor(months_between (p_date_to, p_date_from)\/12) ]';\nend;\n\ncreate or replace FUNCTION upper_macro (p_param in varchar2) \nreturn varchar2 sql_macro(scalar) as\nbegin\n   return 'UPPER(p_param)';\nend;\n\ncreate or replace function top_n (p_tab in dbms_tf.table_t, p_limit in number\n\t\t\t  , p_order in dbms_tf.columns_t) \nreturn varchar2 sql_macro is \nv_order_list varchar2(2000);\nbegin\n  -- turn PL\/SQL table to comma separated list for ORDER BY clause\n  select listagg(replace(column_value,'"'),',') into v_order_list from table (p_order);\n\n  return 'select * from p_tab order by '||v_order_list||\n         ' fetch first p_limit rows only';\nend;\n\n<\/pre><\/div>\n\n\n
Listing 1<\/em><\/strong><\/p>\n\n\n\n
We’ll test with the following SQL: <\/p>\n\n\n
\nselect ename\n,      duration_years(hiredate, sysdate) as years2\n,      duration_years(date '2001-01-01', sysdate) as years_y2k1\n,      duration_years(:p_bind, sysdate) as years_bind1\n,      upper_macro(ename) as ename\n,      upper_macro('adams') as adams\nfrom   top_n(emp,10, columns(empno)) e;\n\n<\/pre><\/div>\n\n\n
Listing 2<\/em><\/strong><\/p>\n\n\n\n
Now we can resolve to the final SQL statement using the function introduced in the previous post<\/a>.<\/p>\n\n\n
\nselect *\nfrom   resolve_sqm(\n            q'[select ename\n               ,      duration_years(hiredate, sysdate) as years2\n               ,      duration_years(date '2001-01-01', sysdate) as years_y2k1\n               ,      duration_years(:p_bind, sysdate) as years_bind1\n               ,      upper_macro(ename) as ename\n               ,      upper_macro('adams') as adams\n               from   top_n(emp,10, columns(empno)) e]') k;\n\n-- final SQL (formatted)\nSELECT\n    "from$_subquery$_009"."ENAME"     "ENAME",\n    floor(months_between(sysdate@!, "from$_subquery$_009"."HIREDATE") \/ 12) "YEARS",\n    floor(months_between(sysdate@!,TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) \/ 12)  "YEARS_Y2K",\n    floor(months_between(sysdate@!, :b1) \/ 12)                              "YEARS_BIND",\n    upper("from$_subquery$_009"."ENAME")  "ENAME",\n    'ADAMS'                               "ADAMS"\nFROM\n    (\n        SELECT\n            "EMP"."EMPNO"    "EMPNO",\n            "EMP"."ENAME"    "ENAME",\n            "EMP"."JOB"      "JOB",\n            "EMP"."MGR"      "MGR",\n            "EMP"."HIREDATE" "HIREDATE",\n            "EMP"."SAL"      "SAL",\n            "EMP"."COMM"     "COMM",\n            "EMP"."DEPTNO"   "DEPTNO",\n            "EMP"."EMPNO"    "rowlimit_$_0",\n            ROW_NUMBER() OVER( ORDER BY "EMP"."EMPNO") "rowlimit_$$_rownumber"\n        FROM "EMP"\n    ) "from$_subquery$_009"\nWHERE "from$_subquery$_009"."rowlimit_$$_rownumber" <= 10\nORDER BY "from$_subquery$_009"."rowlimit_$_0"  \n<\/pre><\/div>\n\n\n
Listing 3<\/em><\/strong><\/p>\n\n\n\n
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.  <\/p>\n\n\n\n
Now let’s then look at the contents of the trace file:<\/p>\n\n\n
\nselect  payload \nfrom    sys.V_$diag_trace_file_contents\nwhere  trace_filename = 'FREE_ora_4911_SQM_205253.trc';   \n<\/pre><\/div>\n\n\n
The first section that has something to do with SQL macros looks like this:<\/p>\n\n\n
\nqksptfSQM_GetTxt(): Anonymous Block\n===================================\n                                                               \ndeclare                                                         \nt0 DBMS_TF.Table_t := DBMS_TF.Table_t(column => :0, table_schema_name => '"ONFTEST"', table_name => '"EMP"');\n                                                              \nbegin                                                           \n:macro_text := "TOP_N"(t0,10,:5 );                                          \nend; \n\nqksptfSQM_GetTxt(): Macro Text\n==============================\n\nSELECT * FROM p_tab ORDER BY EMPNO FETCH FIRST p_limit ROWS ONLY\n\nqksptfSQM_Template(): Template Text\n===================================\n\nwith  P_TAB as (select  \/*+ INLINE *\/ * from "ONFTEST"."EMP")\nselect "SYS__$".* \nfrom (select NULL "P_LIMIT" from SYS.DUAL) "TOP_N",\n     lateral(SELECT * FROM p_tab ORDER BY EMPNO FETCH FIRST p_limit ROWS ONLY) "SYS__$"\n\n<\/pre><\/div>\n\n\n
Listing 4<\/em><\/strong><\/p>\n\n\n\n
qksptfSQM_GetTxt(): Anonymous Block<\/h4>\n\n\n\n
Obviously, the anonymous PL\/SQL block is how Oracle calls the table SQL macro to get the SQL fragment back. The variable t0<\/strong> of the type DBMS_TF.TABLE_T<\/strong> is defined and populated. A bind variable :0 is of type DBMS_TF.TABLE_COLUMNS_T<\/strong> 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.<\/p>\n\n\n\n
qksptfSQM_GetTxt(): Macro Text. <\/h4>\n\n\n\n
We can see the SQL text returned from the macro function – no surprises here.<\/p>\n\n\n\n
qksptfSQM_Template(): Template Text<\/h4>\n\n\n\n
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!<\/p>\n\n\n\n
<\/p>\n\n\n