Apex page performance [message #565983] |
Mon, 10 September 2012 05:18  |
John Watson
Messages: 8805 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   |
 |
Littlefoot
Messages: 21760 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 #567467 is a reply to message #566038] |
Mon, 01 October 2012 13:01   |
andrew again
Messages: 2577 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   |
John Watson
Messages: 8805 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  |
andrew again
Messages: 2577 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?
|
|
|