"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.
- 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.
- 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?
- 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.
- 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.
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.