Performance on Classic Reports with LOVs

Just an observation made yesterday that I thougt was worth everone's consideration.

I created a classic report on a reference table with a touch over 1000 rows, with pagination set at 150 rows per page.

That’s all I had on the page.

The table had it's own set of lookups for a couple of columns, so I assigned two LOVs I already defined in Shared Components.

Classic Report LOV

Next minute, I had a simple reference page taking 10 seconds to load.

This is information from apex_workspace_activity_logs, showing results where no LOV was applied, two LOVs, and just one. I later found the number of rows shown in the pagination set also varied the result.

As I removed one of the LOVs, I quickly realised this was the problem.

I ran the page in debug mode, to see if something crazy was happening as it constructed the query.
When I used debug = YES, it pinned all the effort onto the one line item - but not the query itself.
rows loop: 150 row(s)

Looking again using LEVEL9, I could see that every row took a bit of work, not just the query.
In fact each row had three debug line items referencing my LOV lookup SQL.

begin begin SELECT name display_value, id return_value  
bulk collect into wwv_flow_utilities.g_display,wwv_flow_utilities.g_value 
FROM  my_secondary_ref 
WHERE SYSDATE BETWEEN eff_start AND COALESCE(eff_end, SYSDATE)
ORDER by 1
; end;
end;

Debug chart

Apparently these are only melded into the SQL for IR/IG, not Classic Reports. Context switching kills the page instead - well, at least 149 extra executions of the one statement.

I ran another test so I could check v$sqlarea, and sure enough, there are far more executions of this lookup tha necessary - unnecessarily churning the CPU.

v$sql_area status

I’ve tended to embed these in my queries anyway, often as some form of scalar subqery.
This is a habit I started as a Oracle Forms developer, since Post-Query lookups made repeated network calls that just slowed the application down.

select sm.*
  ,(select name
    from my_lookup m
    where sm.id = m.id) my_desc
from some_model sm

This makes me really wonder how much of our existing page generation time is spent on this work?
Even on small classic report regions. This all adds up.
If only there was a way I could find all occurences of LOVs used in classic reports... wait a minute! I can query the APEX dictionary! ;p

select application_id, pagE_id, page_name, region_name
  ,(select maximum_rows_to_query 
    from apex_application_page_regions r
    where r.region_id = c.region_id) nbr_rows
  , column_alias, heading 
  , named_list_of_values, inline_list_of_values
  , column_is_hidden, condition_type
  ,build_option, column_comment
from apex_application_page_rpt_cols c
where application_id = 102
and display_as_code = 'TEXT_FROM_LOV'
order by nbr_rows desc

It's a shame such a nify declarative feature impacts performance this way.