Hello
I have a page on Apex 24.2.7 in OCI (database 19c) that takes 20s+ to parse for the first time on each day (across all users), the following executions of the page on the same day are very fast.
I assume it is related to hard parsing, but the query is not that complex at all (selecting from one table with 2 subqueries in the SELECT clause). I can't reproduce this issue in SQLPlus for example. The application has lots of similar queries against the same tables without similar issues.
Not a big issue as only happens once a day, but still I'd like to understand what is going on. See below sample timings of when this happens (sorry for the formatting!)
Any ideas what could be causing this or how to start to investigate?
Thanks
Luis
Elapsed/Execution/Message/Level
1.35334/0.01841/>>> APEX_APPLICATION_PAGE_REGIONS Available Letters/3
1.37174/0.00042/>>>> APEX_APPLICATION_PAGE_RPT_COLS PARAMS/3
1.37217/0.00006/>>>>> APEX_APPLICATION_AUTHORIZATION Developer/3
1.37222/0.00004/... passed (using cache)/4
1.37226/0.00004/<<<<< APEX_APPLICATION_AUTHORIZATION Developer/3
1.3723/0.00141/<<<< APEX_APPLICATION_PAGE_RPT_COLS PARAMS/3
1.37371/0.00008/Generated Component SQL Query:/4
1.37379/27.7905/select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
from (select "CODE","NAME","TEMPLATE_FILENAME","GENERATE_BTN","FILES","PARAMS"
from(select /*+ qb_name(apex$inner) */d."CODE",d."NAME",d."TEMPLATE_FILENAME",d."GENERATE_BTN",d."FILES",d."PARAMS"
...
)d
)i
)i where 1=1/4
29.16429/0.14417/rows loop: 150 row(s)/4
29.30846/0.0004/<<< APEX_APPLICATION_PAGE_REGIONS Available Letters/4