Using listagg inside a PSQL block with nested select takes forever

[sda]

Developing a new script has shown abnormal execution time in a specific part.
The issue has been isolated so far with the following:

• pl/sql block
• listagg function
• nested sub-select
• tested version : QueryTool v25.0.118

Within a PSQL block, the execution run for an unknown time (tested over 6 minutes and running). The trace view shows heavy prints, which means the engine works.

declare
  l_html varchar2;
begin
  /* Build one big HTML table string using LISTAGG in a sub-select */
  select
      '<table border="1" style="border-collapse: collapse; width: 100%; margin-bottom: 20px;">'
    || '<tr style="background-color: #f2f2f2;">'
    || '<th>Month</th>'
    || '<th>Total Days</th>'
    || '<th>Weekdays</th>'
    || '<th>Weekends</th>'
    || '<th>First Day</th>'
    || '<th>Last Day (via subselect)</th>'
    || '</tr>'
    || (
         select listagg(
                  '<tr>'
                    || '<td>' || to_char(month_number) || '</td>'
                    || '<td style="text-align:center;">' || total_days    || '</td>'
                    || '<td style="text-align:center;">' || weekday_days  || '</td>'
                    || '<td style="text-align:center;">' || weekend_days  || '</td>'
                    || '<td>' || to_char(first_day, 'YYYY-MM-DD') || '</td>'
                    || '<td>' || to_char(last_day_via_sub, 'YYYY-MM-DD') || '</td>'
                  || '</tr>'
                  , ''
                ) within group (order by month_number)
         from (
               /* Dummy aggregation over CALENDAR@DataDictionary with subselects */
               select
                   c.month_number,
                   count(*)                                           as total_days,
                   -- Simple aggregates
                   sum(case when c.day_in_week between 2 and 6
                            then 1 else 0 end)                        as weekday_days,
                   sum(case when c.day_in_week in (1,7)
                            then 1 else 0 end)                        as weekend_days,
                   -- First day in the month
                   min(c.day_date)                                    as first_day,
                   -- Last day in the month, *computed via a correlated subselect*
                   max(
                     case
                       when c.day_date =
                            ( select max(d.day_date)
                              from calendar@DataDictionary d LIMIT 50
                            )
                       then c.day_date
                     end
                   )                                                  as last_day_via_sub
               from calendar@DataDictionary c
               where c.year_number in (2024,2025)          -- any year you like
               group by c.year_number, c.month_number
              )
       )
    || '</table>'
  into l_html
  from dual;
  /* Just to see the result */
  dbms_output.put_line(l_html);
end;

Without the PSQL block, the execution run in less than 2 seconds:

  /* Build one big HTML table string using LISTAGG in a sub-select */
  select
      '<table border="1" style="border-collapse: collapse; width: 100%; margin-bottom: 20px;">'
    || '<tr style="background-color: #f2f2f2;">'
    || '<th>Month</th>'
    || '<th>Total Days</th>'
    || '<th>Weekdays</th>'
    || '<th>Weekends</th>'
    || '<th>First Day</th>'
    || '<th>Last Day (via subselect)</th>'
    || '</tr>'
    || (
         select listagg(
                  '<tr>'
                    || '<td>' || to_char(month_number) || '</td>'
                    || '<td style="text-align:center;">' || total_days    || '</td>'
                    || '<td style="text-align:center;">' || weekday_days  || '</td>'
                    || '<td style="text-align:center;">' || weekend_days  || '</td>'
                    || '<td>' || to_char(first_day, 'YYYY-MM-DD') || '</td>'
                    || '<td>' || to_char(last_day_via_sub, 'YYYY-MM-DD') || '</td>'
                  || '</tr>'
                  , ''
                ) within group (order by month_number)
         from (
               /* Dummy aggregation over CALENDAR@DataDictionary with subselects */
               select
                   c.month_number,
                   count(*)                                           as total_days,
                   -- Simple aggregates
                   sum(case when c.day_in_week between 2 and 6
                            then 1 else 0 end)                        as weekday_days,
                   sum(case when c.day_in_week in (1,7)
                            then 1 else 0 end)                        as weekend_days,
                   -- First day in the month
                   min(c.day_date)                                    as first_day,
                   -- Last day in the month, *computed via a correlated subselect*
                   max(
                     case
                       when c.day_date =
                            ( select max(d.day_date)
                              from calendar@DataDictionary d LIMIT 50
                            )
                       then c.day_date
                     end
                   )                                                  as last_day_via_sub
               from calendar@DataDictionary c
               where c.year_number in (2024,2025)          -- any year you like
               group by c.year_number, c.month_number
              )
       )
    || '</table>'
  from dual;

[forums]

That is a nice question!

Although both statements will have identical outcome from the two variants of the query, the execution time can be completely different due to the different ways in which the optimization techniques can be applied currently.

The subselect on calendar@DataDictionary is not materialized when executed within PSQL, whereas it is when executed from a separate SQL context.

Given that some day this question might change into a Wiki for people trying to understand the engine, an elaborate discussion of the background, alternative techniques to resolve the issue and possible future enhancements is given.

Computationally Expensive Subselect

First note that the max(d.day_date) processes close to 37.000 rows and is not very cheap when executed repeatedly, since:

select max(d.day_date) 
from   calendar@datadictionary d
limit  50

is always functionally identical and in practical cases performance wise identical too to:

select max(d.day_date) 
from   calendar@datadictionary d

since max returns exactly one row. In case the maximum of the first 50 rows is desired, please use:

select max(d.day_date)
from   ( select day_date from calendar@datadictionary limit 50 ) d

The execution of the max statement therefore always evaluates close to 37.000 rows in the calendar.

Quick Workaround

The workaround is to replace the following query:

max
( case
  when c.day_date 
  = 
  ( select max(d.day_date)
    from   calendar@DataDictionary d LIMIT 50
  )
  then c.day_date
  end
) 

by either one of materialization:

max
( case
  when c.day_date 
  = (trunc(sysdateutc) - 30) /* Or some other precalculated date. */
  then c.day_date
  end
) 

or a more labourous restructure with moving elements to an inline view and various other changes:

from   ( select max(d.day_date) day_date_max from calendar@DataDictionary d ) d
...
max
( case
  when c.day_date 
  = 
  d.day_date_max
  then c.day_date
  end
) 

or using a low_cost hint:

max
( case
  when c.day_date 
  = 
  ( select /*+ low_cost */ max(d.day_date)
    from calendar@DataDictionary d LIMIT 50
  )
  then c.day_date
  end
) 

The PSQL block then finishes within approximately 1.7 seconds (using low_cost hint), 0.4 seconds (using constant) and the second one somewhere in between (untested).

Background

The evaluation of an Invantive UniversalSQL statement located within a PSQL block differs in details on the evaluation when executed separately using either a separate statement or an execute immediate in a PSQL block.

For all scenarios, the outcome is identical, but only for most scenarios, the impact is non-detectable. Scenarios like the PSQL-block above can trigger significantly longer runtimes.

The current version (internally formally named “V2”, but by some named the first engine version because the original version was very limited) generates machine code for execution. This machine code has sometimes less optimal execution characteristics than separate SQL statement execution.

For instance (and typically the most frequently occurring one similar to the one above), a PSQL block with hundreds of statement is parsed and compiled exactly one time, whereas each separate SQL statement goes through a separate parse phase.

The optimizer will first evaluate the following construct to a constant value before executing the rest of the statement in the scenario above when the statement is executed as a separate SQL-statement (“constant folding”). For PSQL execution, the compiler currently assumes that the (anonymous) PSQL-block:

• can be executed multiple times, and/or
• that the SQL-statement contained can be contained in a loop and/or
• there is a delay between the parse phase and execution of the query such as a previous query in the block, a preceding dbms_lock.sleep, or delayed execution using a procedure.

The outcome of the max(d.day_date) might vary between the parse and execution time, so execution is delayed till the last possible moment and sent by the PSQL-engine to the SQL-engine for parsing and execution at that time without replacing the subquery by a constant. Note that when a SQL-statement is executed multiple times in a loop, the SQL-statement is only parsed once and the machine code is re-executed multiple times.

Performance Implications of Sub selects

Even worse, the max(d.day_date) may be calculated repeatedly since such sub-selects although supported are often re-evaluated numerous times during the execution of a SQL statement. The use of sub-selects is common on SQL Server / Sybase, but is not recommended on Invantive UniversalSQL due to the different set of optimization techniques used.

No changes are planned in this area. Within the Invantive UniversalSQL optimizer, uncorrelated sub-selects are regarded as an anti-pattern. Much like the historical ‘goto considered harmful’, they complicate control-flow analysis, obstruct early evaluation and limit opportunities for plan simplification. Although valid SQL, they reduce predictability of execution and, in practice, can signal a shortcut that masks insufficient understanding of set-based logic and query structure.

The recommended alternative for replacing (uncorrelated) sub-selects is the second technique shown above.

Possible Optimizations

Ofcourse, there are scenarios possible where the engine could optimize the logic within the current engine technology, but for reasons of labor time involved, small gains and to keep the complex logic predictable and explainable this has not been done sofar.

Bind Variables and Performance

Similar performance issues on PSQL can arise in cases when the optimizer would use a different execution path for different values of bind variables. In general, the use of execute immediate or other approaches can be necessary for environments that process large volumes of data. These cases are however even more rare than the one described above.

Recommended Solution

The best solution is to materialize the outcome of the subquery max(d.day_date) in PSQL-logic, such as by:

declare
  l_max datetime;
begin
  select max(d.day_date)
  into   l_max
  from   calendar@datadictionary
  ;
  select ...
  from   ...
  where /* use somewhere */ t.c = l_max
  ;
end;

This moves the evaluation of the max outside of the Invantive UniversalSQL-statement. The 37.000 rows in calendar@datadictionary are evaluated once and the outcome is reused in the next SQL-query as a value bound to a variable.

Alternative Solution

The use of the low_cost hint is a less elegant way to achieve the same. The consistent difference in execution time between materialization and the hint signals they have some different execution path.

From Invantive UniversalSQL Grammar (current) :

The low_cost-hint specifies that the select with the hint must be considered a select with low execution costs. Low execution costs trigger early evaluation during parsing. By default, select statements using solely in memory storage, dummy and data dictionary are considered low cost and evaluated early. The evaluation of all others is delayed as long as possible.

The use of the low_cost-hint is recommended when the select is used with a ‘in ( select … )’ syntax and the developer knows beforehand that it will evaluate fast to values and that the use of these values will allow the use of server-side filtering for the outer select.

Both approaches are different. The materialization is calculated during execution time, whereas low cost is calculated during the parse phase (which can include some execution elements). In an anonymous PSQL-block, the parse phase and execution phase are closely tied together.

The low cost approach is not always suitable and can lead to side effects, which are predictable but can be hard to grasp the logic of. Therefore, the use of materialization is recommended where possible.

Possible Bug

The query on calendar@datadictionary should according to documentation have evaluated already using the low cost approach. This will be reviewed by an analist and either documentation or logic will be changed.

Future

In 2021 or 2022 a project was started to improve the parse and execution logic of SQL and PSQL-statements by splitting phases further. This project has been canceled after some time due to the costs for completion, other priorities and infrequent occurrence of the issue described in this topic. In the future, the project might be reinitiated using the original design but this is not scheduled for the foreseeable future, as such performance features are only found in a few competing US products and the European market currently shows little desire to switch to European products.

[sda]

Thanks, in my case the real script is like this:

    dbms_output.put_line('Building email body HTML for ' || recipient_rec.site || '...');
      -- Build site summary table
      select '<table border="1" style="border-collapse: collapse; width: 100%; margin-bottom: 20px;">'
        || '<tr style="background-color: #f2f2f2;"><th style="padding: 8px; border: 1px solid #ddd;">Site</th>'
        || '<th style="padding: 8px; border: 1px solid #ddd;">Total Devices</th>'
        || '<th style="padding: 8px; border: 1px solid #ddd;">Online</th>'
        || '<th style="padding: 8px; border: 1px solid #ddd;">Offline</th>'
        || '<th style="padding: 8px; border: 1px solid #ddd;">Oldest Offline (days)</th>'
        || '<th style="padding: 8px; border: 1px solid #ddd;">Newest Offline (days)</th></tr>' 
        || (select listagg('<tr><td style="padding: 8px; border: 1px solid #ddd;">' || site 
            || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || total_devices 
            || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center; color: green; font-weight: bold;">' || online_count 
            || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center; color: red; font-weight: bold;">' || offline_count 
            || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || oldest_offline 
            || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || newest_offline 
            || '</td></tr>', '') within group (order by site)
            from (select site, count(*) as total_devices, 
                  sum(case when last_update_utc >= (sysdate - (select parameter_value from script_parameters@inmemorystorage where parameter_name = 'outdated_threshold_days')) then 1 else 0 end) as online_count,
                  sum(case when last_update_utc < (sysdate - (select parameter_value from script_parameters@inmemorystorage where parameter_name = 'outdated_threshold_days')) then 1 else 0 end) as offline_count,
                  max(case when last_update_utc < (sysdate - (select parameter_value from script_parameters@inmemorystorage where parameter_name = 'outdated_threshold_days')) then round((sysdate - last_update_utc), 2) else 0 end) as oldest_offline,
                  min(case when last_update_utc < (sysdate - (select parameter_value from script_parameters@inmemorystorage where parameter_name = 'outdated_threshold_days')) then round((sysdate - last_update_utc), 2) else 999 end) as newest_offline
                  from results_last@inmemorystorage where last_update_utc is not null and (recipient_rec.site = 'global' or site = recipient_rec.site) group by site)) 
        || '</table>' into l_site_summary_table
      from dual;
      dbms_output.put_line('summary table built');

[forums]

Quickest solution is probably to use:

... ( select /*+ low_cost */ parameter_value 
      from   script_parameters@inmemorystorage 
      where parameter_name = 'outdated_threshold_days'
    )

since settings typically don’t change during execution. Or use PSQL as indicated above.

Please note that sysdate is not UTC; sysdateutc is UTC.