Skip to main content

Help! APEX is blowing up our memory!

That's the message I received from a DBA friend who ran into this problem at her customer site. What really happened was that a couple of times a week one of the APEX processes was killed by the operating system due to reaching the limit of 16Gb memory. 16Gb!
And this resulted in an ORA-04030 message in the Oracle Database.
So I went to that client site and we dove into the database log files, the APEX logging, the access log files and the error log files of the web server. Every time we noticed calls of WWW_FLOW_FILE_MGR.GET_FILE that seemed to be the problem. But the application wasn't huge, just 25 pages. Nothing complex, all pretty straight forward. So we started to look for the origin of those calls.
Just like in a lot of applications, this application also had a few images on the top of the screen (like Home, Logout, etc.). Just four or five on every page. However, these images were stored inside the APEX Repository, using the #WORKSPACE_IMAGES# substitution string. And that results in a call to WWW_FLOW_FILE_MGR.GET_FILE for every image. So that's five requests for every page, while it could - and should - be only one. And four executions of that procedure every time, while it could - and should - be zero. So I advised to move these images to the web server, so they can be cached and wouldn't result in hitting the database anymore.
But when this would be the cause of the problem, it made sense it would occur on peak times. But the problem didn't occur on peak times. The last one was that same morning around 4:30AM.
So we dove further and then we discovered that quite a few of the select statements in the application used #WORKSPACE_IMAGES# as well! So the procedure would be executed for every row returned by the select statement. So in some particular cases - just happening three times a week or so - the query would return 10,000 of rows. Or even millions. So the procedure would be executed that often. And the best thing is: the image that the select was trying to retrieve ... didn't even exists! So millions of calls to the database to retrieve ... nothing!
So changing the select statements and moving all images to the web server will reduce the number of calls to that procedure from millions (or 100 of millions) times a week to ... zero (?).
And the chances are the memory problem is gone to.

So when you run into these kind of problems were APEX seems to consume a lot of memory - or CPU for that matter - take a very good look to your own code first. The chances are that the resource consumption is not because APEX is misbehaving, but your code isn't optimal....
Post a Comment

Popular posts from this blog

Showing a success message after closing a modal dialog

APEX 5 comes with Modal Dialogs out of the box. Very neat. Especially for adding and changing data. And to minimise the number of time a user has to click, it could be useful to add a "Close Dialog" process after the actual data processing. When the data processing fails, the Dialog stays on top showing the error. When data processing runs fine, the Dialog is closed ... without any confirmation. And this might be scary for a shaky user.

So how can we provide the user some feedback? On Page 4 of the Sample Dialog Application you can see one solution: up on a Dialog Closed Event on the parent page it does a redirect to refresh the parent page appending the success message of the "Close Dialog" process. This has two drawbacks. First, it probably refreshes more than necessary. And second, if you're using multiple layers of dialogs (dialogs that open other dialogs) the message appears in the "parent dialog".
As an alternative you could follow these steps: 1…

A review of APEX World 2017 - Day 1

Last week the SS Rotterdam was the beautiful location of the largest gathering of APEX Developers worldwide. With around 380 (!) attendees a new high was set. And they came from all over the world : I spotted people from The Netherlands, Belgium, Switzerland, Austria, Croatia, Germany, Denmark, Norway, UK, Ireland and the USA. And I even might have missed one or two ….

The event started with a presentation by the “father of APEX”, Mike Hichwa, talking about "Oracle APEX Past, Present and Future”. Of course everyone is curious what the APEX future might bring: Friendly URL’s, automated testing, more JSON, concurrent APEX versions, third party Oauth 2 authentication (think Facebook, Google), APEX app diff and more, a lot more, REST capabilities. And now we have to wait for APEX 5.2 … and that might take a while! 
After this keynote, the conference split up in three tracks. After the coffee break I returned to to big theatre where Geertjan Wielenga talked about "Finally Javas…

It's happening again ... running for the ODTUG Board of Directors 😉

For the third time in a row I'll be running for ODTUG's Board of Directors. But after ending as a runner up twice, I am sure I'm going to make it this time! But not without your help!

My campaign statement this year is:
I have been attending and presenting at Kscope conferences since 2007. This not only resulted in a vast amount of knowledge, but also - and even more important - a huge number of friends from all over the globe.  I want to see ODTUG grow and spread this community feeling even more! 
My experience as an attendee, presenter and content lead has provided the basic foundation to be a director. Next to that, my personality and (global) network will be beneficial to the whole board and organization. 
Since March I have served on the Board of Directors in a limited term for a Director who stepped down due to a career change. This has allowed me to have unique insight of all the things that are going on in and around the ODTUG organization. As the train was already ro…