Home » Developer & Programmer » Application Express & MOD_PLSQL » Apex page performance (11.1.0.7)
Apex page performance [message #565983] Mon, 10 September 2012 05:18 Go to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
This application has one report that is slow, nearly 80 seconds. It returns about 100 rows of 50 columns. The query itself runs in 1 second. Tracing the session identifies this anonymous block as the slow part:
declare
 rc__ number;
 simple_list__ owa_util.vc_arr;
 complex_list__ owa_util.vc_arr;
begin
 owa.init_cgi_env(:n__,:nm__,:v__);
 htp.HTBUF_LEN := 63;
 null;
 null;
 simple_list__(1) := 'sys.%';
 simple_list__(2) := 'dbms\_%';
 simple_list__(3) := 'utl\_%';
 simple_list__(4) := 'owa\_%';
 simple_list__(5) := 'owa.%';
 simple_list__(6) := 'htp.%';
 simple_list__(7) := 'htf.%';
 simple_list__(8) := 'wpg_docload.%';
 if ((wwv_flow_epg_include_modules.authorize('f') = false) or (owa_match.match_pattern(p_string => 'f'
   /* */,p_simple_pattern =>
   simple_list__
   ,p_complex_pattern =>
   complex_list__
   ,p_use_special_chars =>
   false)))
 then
  rc__ := 2;
 else
  null;
  null;
  f(p=>:p,p_trace=>:p_trace);
  if (wpg_docload.is_file_download) then
   rc__ := 1;
   wpg_docload.get_download_file(:doc_info);
   null;
   null;
   null;
   commit;
  else
   rc__ := 0;
   null;
   null;
   null;
   commit;
   owa.get_page(:data__,:ndata__);
  end if;
 end if;
 :rc__ := rc__;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1     75.95      76.21         39       1335         41           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1     75.95      76.21         39       1335         41           1
I've been trying to work out what is happening. I think (and I'm open to correction on this - my pl/sql is poor, my Apex non-existant) the first IF will always drop through to the ELSE, because of the hard coded literals 'f'.
In the ELSE, there is another IF which will also drop through to ELSE, because (as far as I can tell) wpg_docload.is_file_download also returns false. So the only candidate for slow execution is the call to owa.get_page which I can't find documented. Is it documented? Where??

Does anyone have any advice on what to do to speed this up? If this is the part that renders the 100 rows for display, is 75 seconds of CPU normal?

Thank you for any insight.
Re: Apex page performance [message #565986 is a reply to message #565983] Mon, 10 September 2012 05:37 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's some information on OTN Apex forum (but I'm not sure you'll benefit from it).

Also, see what Joel Kallman says here (hint: Monitor Activity).

But, as query itself takes only 1 second, I wonder what you'll find out.


Oh well, it appears that "owa.get_page" is the keyword for performance issues all over the OTN Apex forum. Basically, the answer seems to be the same all over again: it is Apex page processing and there's nothing you can do about it, but find your own (PL/)SQL which is to be blamed.

[Updated on: Mon, 10 September 2012 05:42]

Report message to a moderator

Re: Apex page performance [message #566002 is a reply to message #565986] Mon, 10 September 2012 06:35 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Thanks for replying. I'll take a look at those references. Probably have to raise a TAR.
Re: Apex page performance [message #566038 is a reply to message #566002] Mon, 10 September 2012 15:17 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you ever receive the answer, please, let us know the outcome.
Re: Apex page performance [message #567467 is a reply to message #566038] Mon, 01 October 2012 13:01 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
Things I try when I have page performance issues (and the "equivalent" query runs fine when tested in isolation):
1) When your query uses DB links, try forcing the query to the remote DB (select /*+ DRIVING_SITE(x) */...).
2) Replace any refs to v('...') or ITEMS with literals to see if the they are the cause. Dont use v('...') refs in your queries.
3) Try adding all_rows hint. This has resolved several of my performance issues.

I'm a big fan of populating collections (on page load) when I have slow queries, and then letting partial-page-refresh reports query the collection. Cacheing in this way can make pagination fast. You do need to consider growth of the underlying collection tables, so I try to keep query resultsets < 10k rows or so. Populate your collection with the result of a big cartesian join once and you've in trouble...
Re: Apex page performance [message #567468 is a reply to message #567467] Mon, 01 October 2012 13:36 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying, but I am sure beyond all reasonable doubt that the problem is not to do with the query, but is in the block I pasted above. Following more Googling, it seems likely to me that the slow part of the code is the call to the F function. If I understand it correctly, F is the function that renders the page. I could be wrong on this. So I think that something (the data, perhaps?) is causing the rendering to take a ludicrous amount of CPU.
Any advice (or corrections) will be gratefully received.
Re: Apex page performance [message #567470 is a reply to message #567468] Mon, 01 October 2012 15:37 Go to previous message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
Yes, f is the main procedure (it's the f in an Apex URL like .../apex/f?p=100:1:0). As far as I'm aware, Apex still uses one DB session for requests (POSTs and GETs etc) and another for rendering the result (you can easily verify this by setting some package state during submit processing, and then try to access that state during page rendering. The state you set during POST/GET will be gone during page rendering). This might mean that if you are setting trace on (with your own alter session set sql_trace = true in Apex), that you're only seeing either the GET/POST, or the page rendering.

Have you tried pulling an AWR report for the period to see what else shows up?
Previous Topic: Update statement not working with Row selector
Next Topic: Special Character issue for inserting into Db table from Apex form
Goto Forum:
  


Current Time: Wed Oct 01 09:36:01 CDT 2014

Total time taken to generate the page: 0.05349 seconds