APEX

Hi all,

We've got an AppEx app that contains a report that runs a query against a few views joined in the database. This page is taking a long time (>45 secs) to return, so we've reduced the page down a single report that just runs this one query and returns the row count, and it still takes the same long time. When running this exact same SQL query (which joins a few views and tables with various WHERE clause criteria) via SQL*Plus (with heading OFF and timing ON), the data returns in 2-3 seconds. Interestingly, when we run this same query in a - in PL/SQL as Native Dynamic SQL (using either a refCursor to loop through the results or a BULK COLLECT INTO clause to return the data to PL/SQL table), it returns in sub-seconds (.5 - .7). I'm sure caching is somehow playing into this, but more mysteriously, when then trying this same code as a cursor in PL/SQL, it once again take 46 seconds to complete.

This seems to imply that AppEx is acting like it's excuting the code behind the report as a generated PL/SQL cursor somehow, as opposed to NDS as one would assume...?

We've used the tip of adding the &p_trace=YES argument to the URL call and have managed to generate a 524 Meg trace file from that call,which we're trying to investigate with TKPROF but am wondering about any other tips folks might have. The disparity between the NDS (and straight SQL) performance and AppEx performance is most perplexing though...

Any thoughts/ideas most encouraged!

Jim C.