Thursday, March 06, 2014

Finally...the official sizing guide for Oracle Application Express

The following question was recently posted on an internal mailing list:
"Is there a sizing/capacity/scalability guide available for APEX?"
I'm always fascinated by this question.  I appreciate the fact that this is a standard, acceptable practice in the industry, and people come to expect it.  How else could architects and planners appropriately allocate resources without some form of estimate?  This impacts capital expenditures and budgets and rack space and energy costs and support costs and human capital.  People seem to be looking for some simple formula like:
(X number of pages in an APEX application) * (Y number of concurrent users) = (W number of processors) + (Z number of GB of RAM)
Voila!  Plug that formula into your favorite spreadsheet and away you go.  Well....if I lured you in with the title of this blog post, I have to be honest - it's all fiction.  There is no such thing.  But why not?  There are a number of reasons.

  1. There is no such thing as a representative, typical application.  As I've often bloviated in the past, Oracle Application Express is as fast or as slow as you, the developer, make it.  The overhead associated with the APEX engine itself is fairly static (measured in hundredths of a second). If you have a query that takes 30 seconds to execute and you put this query in a report in an APEX application, you can expect the execution of that page to take just over 30 seconds per page view.

  2. What does "concurrent" mean?  Is that the total number of users in an hour?  Total number of users in a 5-minute interval?  Or is that the high-water mark of number of users all clicking the mouse or hitting the Enter key, all at the same time?

  3. What is the typical "think time" of an end user?  Effectively, resources are only being consumed when there is a request actively being processed by the APEX engine.  So while the end user is interpreting the results of a report or keying in data in a form, they aren't (typically) making any requests to the APEX engine.

  4. How much memory will be consumed by the typical page view?  Does your application allocate GB's of in-memory LOBs, per user per page view?  This would have a definite impact on scalability.
The total number of pages in an application has close to zero correlation to scalability and throughput.  You can have a 1,000-page application, each page with sub-second performance, which will be far more scalable than a 1-page application that consumes 15 seconds per page view.

As the Oracle Database Performance and Tuning Guide states, there are many variables involved in workload estimation, and it's typically done via either benchmarking or extrapolation from a similar system.  But what is "a similar system" for an APEX application?  Does a call-center application at one enterprise approximate the back-office order processing system at another company?

I can understand how a formula can be prepared for a COTS application.  If you're deploying Fusion Applications or the eBusiness Suite or JD Edwards or SAP, those applications are created, the business logic is written, the queries and transactions are crafted, and concurrency has been measured on representative systems for a given workload.  But I don't understand how someone can produce a sizing guide for any application development framework - Application Express, ADF, .NET, Java.  It's like asking "how scalable is C?"

An application that our team wrote and runs for Oracle is quite scalable (the oft-mentioned Aria People employee directory).  Yesterday (05-MAR), there were 2.1M page views on this system with a median page rendering time of 0.03 seconds from 45,314 distinct users.  The busiest hour saw 129,284 page views through the APEX engine (35.9 page views/second).  If another team within Oracle wrote this same system but didn't tune the SQL like we did, is that a reflection on the scalability of APEX?  And if the answer to that question is "no", then is the hardware configuration all that relevant?

Back in 2007, my manager Mike Hichwa took a draft note that I wrote and published an article for  Oracle Magazine entitled "Sizing up Performance".  There is a very simple formula which can be used to estimate the throughput of an APEX application.  This isn't going to help you determine how much hardware to buy or how to estimate the size of your VM, but it will help estimate (in back-of-the-napkin form) how scalable an existing APEX application will be on an existing system.

With all this said, we, on the Oracle Application Express team, have been deficient.  At a minimum, we should have a list of systems developed by our customers, with specific information about the hardware configuration, purpose of the system, and number of end-users served.  Maybe we should also obtain the level of expertise of the developers.  We will gather this information and publish it online (without specific customer names).  If nothing else, this can serve as the foundation for extrapolation by architects and designers.


Unknown said...

I'm not going to argue about your overall point, as I don't know how those estimates are developed for any application/framework/architecture, etc, nor how accurate they are. And to be honest I don't need these estimates personally, so I have no more interest beyond the fact that I use and enjoy APEX and want to see it gain adoption and grow.

-- but I would quibble with one thing you said:

"If you have a query that takes 30 seconds to execute and you put this query in a report in an APEX application, you can expect the execution of that page to take just over 30 seconds per page view."

That's every bit as true of our applications with a java user interface that connect to our oracle databases. There is nothing unique to APEX about that statement. So it would seem to be no more of a handicap to doing an APEX estimate than doing a Java one.

Joel R. Kallman said...


There is nothing to quibble about. You are precisely making my point. I talk about and blog about this topic incessantly (e.g.,

As you seem to clearly understand, APEX is simply a veneer over the database, and does nothing more than what you tell it. *However*, I've heard countless times about some APEX application being slow and the immediate stated conclusion from a customer or CIO is "APEX is slow" or "APEX can't scale". If I wrote a poor-performing ASP or Java or PHP application, would the immediate conclusion be "ASP is slow or Java is slow or PHP is slow"? No...seemingly never. But I've seen this conclusion falsely reached about APEX numerous times. It's baffling.

Again, there is nothing for you to argue or quibble about in my post. You're proving my point about APEX. And for that, I thank you.


Pratap said...

(X number of pages in an APEX application) * (Y number of concurrent users) = (W number of processors) + (Z number of GB of RAM)

I have 20 Pages in Apex application and 50 Concurrent users.

As per formula: 20*50 = 1000
I have a quadcore Processor which means 4 Processors and that means I need 996 Gigs of RAM to make Application run effectievly?

996 Gigs is very high value for 50 concurrent users accessing 20 page applications.

Joel R. Kallman said...

Hi Pratap,

Please read the full blog post.


Unknown said...

Like the idea and will be happy to contribute. Our experience over the last 12 years has been that Apex builds some of the most scalable apps I have ever seen. Multiple demos with competing technologies addressing same needs and same hardware proved the point. On top it was much quicker to develop (that's why we could develop a competing solution at risk).

I would also suggest a simple performance tuning report or guide; Often DBAs are not trained on Apex performance tuning and I don't know if the built-in advisors are "Apex Aware". I got tired of explaining that you do not go and tune the "f" function that is the core of all problems.

Personally, I create my own "advisor" in APEX during development time to tell me any query that uses more resources than most other queries. Even with no load, it results in removing problems later. I just did it last night on a Exadata Express instance.