Hi ,
My Apex version is 4.2.0.00.27 running with APEX Listener 2.0.5 287.04.27 standalone on a Standard Edition Patchset 11.2.0.2.0.
There is a page designed by my developer which is taking 10+ minutes to run an interactive report . I am able to identify the issue is due to the face that in the pagination tab the value for "Max row Count"
was given as 200Million . After removing the pagination (making it to null) the report completes in seconds.
I see that APEX converts the Report SQL into
APEX converts the SQL to
select apxws_row_pk, <columns in the report>
count(*) over () as apxws_row_cnt
from (
select *
from
(select b.ROWID apxws_row_pk, b.*
from
(select *
from ( ACTUAL SQL ) r
where rownum <= to_number(:APXWS_MAX_ROW_CNT) order by "IN_TIME",apxws_row_pk
And this is when the problem starts and in DB I see CPU wait for this SQL . The page never works for any value of Row Count (200, 2000, 200000 ) it works only if it is Null and the SQL is not rewritten by APEX.
I am not sure what is the reason .
Few blogs that I referred but no luck.
http://oraclequirks.blogspot.com/2010/01/dispatcher-process-taking-99-of-cpu-on.html
http://www.talkapex.com/2010/10/apex-reports-no-limit-downloads.html
Thanks,
Karthick M