Friday, February 26, 2010

Application Express 4.0 Early Adopter Phase II is available

The second phase of the Application Express 4.0 Early Adopter is now available. A new instance with the latest build of Application Express 4.0 is now available on

  1. This database was created with database character set AL32UTF8 (we're still a little confused by the Amazon Machine Images from Oracle aren't already AL32UTF8.
  2. The Websheets feature is now available. The security of Websheet applications still needs to be refined, but the functionality and ease of creation of these types of applications is amazing.
  3. The new tree region makes its debut.
  4. Collections now support NUMBER and DATE
  5. apex_collection.create_collection_from_query_b and apex_collection.create_collection_from_queryb2 support bind variables and row limits
  6. Debugging and logging has been completely rewritten - no longer is debug output emitted within the page, but it's collected and can be viewed later (and also queried via views).
  7. You won't be able to see this, but the tablespaces / data files of provisioned workspaces will now autoextend
  8. There have been many improvements in the Plug-In infrastructure, which Patrick will surely expand upon.
  9. A Migration Assistant has been created, which lets you upgrade all of the elements of your pre APEX 4.0 application in one place.
  10. Native validations and declarative validations have also been rewritten and improved (again, Patrick will expand upon this).
  11. All of the item types have been consolidated (so you no longer see 1,000 different Date Picker types in your item type select list, as well as other item types)
  12. A large number of bugs and usability issues have been corrected, thanks in a large part to the tremendous amount of feedback we received.
This isn't an exhaustive list. The updated list of new features will be available on

If you had used the first Application Express 4.0 Early Adopter instance, your workspace and schema have not been migrated to this instance. You will need to sign up for a workspace but you shouldn't have to take the survey again. The original Application Express 4.0 Early Adopter instance is available at, and it will be available for the next couple of weeks.

Thanks to everyone who has participated and provided feedback, as the feedback and suggestions have been invaluable.

Wednesday, February 24, 2010

FamZoo goes live!

Nine years ago, when Oracle started down the path of turning Application Express (nee HTML DB) into a real, supported product that was shipped with the Oracle database, we had the good fortune of being mentored and led by Bill Dwight. Bill was a hands-on Vice President in the Oracle Tools division and he led the development of JDeveloper, Developer, Forms, Designer, and iLearning. Bill had a lot of experience and success at Oracle, but also he had the right kind of savvy to guide us from a "project" to a product. He imparted a lot of sound advice that still helps us today.

Fast forward to today, and Bill Dwight is the founder and CEO of FamZoo, Inc. In a nutshell, FamZoo is "a family-friendly web site that helps parents teach their children the practical skills they’ll need to thrive in the real world." FamZoo the company, and FamZoo the Web site have been under development and in private beta for a few years, but as of January 21, 2010, FamZoo is now open for business to the public. As you navigate around the FamZoo site, you'll see the tell-tale signature of Application Express in the URL. There is an extensive FamZoo tour also available.

FamZoo has been an interesting case-study for Oracle Application Express, in that:
  1. They made the conscious decision to write the customer-facing portions of FamZoo in lovingly hand-crafted HTML / JavaScript / AJAX. According to Bill, this was important because "our consumer facing UI is very, very custom and particular - a key part of our brand."

  2. The internal administration and dashboard pages of FamZoo were written using the traditional wizards and declarative APEX infrastructure.

  3. Even with the very custom customer-facing UI, they still capitalize on other benefits of the Application Express framework, including security, session state, page templates, etc.

  4. With the exception of one local APEX instance for upgrade testing, the development and deployment of FamZoo has been done on commercial, hosted instances of Application Express from two different hosting providers. Not only was this extraordinarily cost effective, but it let the FamZoo team focus on building their business and their brand, and not have to worry about database and Web site administration, backups, power, etc.

The user interface of FamZoo is really superb and the overall flow and navigation of the site is elegant. To develop this kind of site takes a lot of time and effort and careful planning, regardless of the development framework that is chosen. I'm just happy they chose to go the Application Express route.

Monday, February 08, 2010

Making fast (again)

For the latter part of 2009, the performance on was lacking at times, to put it kindly. As evidenced by this long-running thread on the APEX discussion forum on OTN, performance issues started cropping up in August and things only got worse as the year went on. Ben Burrell, a very frequent contributor on the APEX discussion forum said it best with:

"The dedication and patience shown by the contributors to this forum is nothing short of incredible. Oracle should be magnificently proud of the APEX community - and reward it with a decent service."

I wholeheartedly agree! The least Oracle can do for the army of APEX experts who graciously give of their time to help others with Application Express is provide them a usable service. Additionally, for those users of Application Express who sign up for a test account on, a first impression is everything. Even if the cause of the issue is not "an APEX problem", per se, it will always have the perception that APEX is slow and cannot scale.

This has been a learning experience for me, and I'm simply here to report a number of the issues uncovered and the steps we took to remedy them.

1) KeepAlive - In November 2009, I was monitoring the database listener log files. There were a very large number of incoming connection requests to the database listener - in some instances, on the order of 30 connection requests per second. One of the most expensive actions you can take is to establish a new database session, hence, why session pooling is so commonly used. As it turns out, an administrator had turned off the KeepAlive setting in Oracle HTTP Server. Every new APEX page view request had to establish a new database session. For a site that gets millions of page views a day, this is not good. I had them turn KeepAlive back to On and with a KeepAliveTimeout of 6 seconds. As busy as is today, we now only average about one new database session request every 10 to 15 seconds.

2) robots.txt - The robots.txt file is supposed to be used by Web crawlers and search engines, although not all of them seem to observe it. This file enables a Web site administrator to control when Web crawlers are allowed to crawl a site and how frequently. The "window" of time that we permitted Web crawlers was too great - that's the last thing we want is to have some new user of Application Express competing for the same resources with some Web crawler which is hurling requests at our site. So I reduced the open window for Web crawlers:

3) Number of Database Access Descriptors - Since the days of the original '', the Database Access Descriptor used for the site was named 'otn'. Earlier this year, I wanted to have support the documented and standard Database Access Descriptor named 'apex', so we added that one to our configuration. Then, with the migration of AskTom to the instance, we created a third Database Access Descriptor named 'asktom' (otherwise, we would have broken any bookmarks to Tom's questions and answers).

The Web server software used by Oracle Global IT on this instance is Oracle Application Server 10.1.2. Unfortunately, Oracle HTTP Server and mod_plsql do not support true connection pooling in this version (see the documentation for the explanation). Each HTTP Server process would establish and maintain a database connection per DAD - that's three database connections times the number of HTTP Server processes. There are two Web servers that front-end - so 2 Web servers * 3 connections per Oracle HTTP Server process * 200 - 250 HTTP Server processes - and we easily had more than 1,000 database sessions during peak times. Even if only a handful of these sessions were active at any given time, the memory consumed by all of these sessions was too large. So large, that the system log was recording messages like "Out of Memory: Killed process 31088 (oracle)" every few minutes. Not good.

With the help of Kris Rice, we were able to remove the 'asktom' Database Access Descriptor and replace it with the following rewrite directives in httpd.conf:

RewriteEngine On
RewriteCond %{THE_REQUEST} ^.*/pls/asktom.* [NC]
RewriteRule ^.*/pls/asktom(.*)$ /pls/apex$1 [NC,QSA,PT]

With this one change, the database server wasn't swapping memory and the OS was not randomly killing processes because it was out of memory. The next day, Tom even commented how snappy it was.

4) File System Caching - The system administrators reviewed the network performance and I/O systems. They changed the "noac" and "actimeo" parameters so that the system would cache file system parameters, thus reducing the I/O on the system. Remember that this was a database with over 15,000 tablespaces and datafiles - that's a lot of potential I/O savings. Don't ask me for any more detail on this one, though - it's beyond my domain of expertise.

5) Purge of Stale Workspaces - There is no built-in workspace purging process in Application Express. The way the process has worked over the past 3 or 4 year is I personally would run a hand-crafted collection of custom PL/SQL programs which identified the workspaces that have not been touched in the last 45 days, notify the administrators of these workspaces via e-mail that their workspace was going to be purged, let them retain the workspace if they wished, and then after a two-week period - go through and purge the ones which hadn't been retained.

The problem with this solution is that would go through these peaks and valleys - dropping to 7,000 workspaces and then over time, creep up to 17,000 workspaces. I would manually run the purge process a year later, bring it back down to 8,000 workspaces, and then the process starts all over again. The system would be fastest after the purge and then slowly degrade over time.

I rewrote the purge process from scratch, but this time, I designed it to run perpetually (I have to give credit to my manager, Mike Hichwa, for this idea). The parameters are roughly the same - every night, it wakes up, computes who hasn't used their workspace in the past N days and isn't already on "the list", sends the administrators an e-mail, gives them the chance to retain their workspace, sends a reminder 3 days in advance of the purge, and and then after a total of 10 days, purges the workspace. Purging the workspace involves dropping the tablespace & data file, dropping the database user corresponding to the workspace schema, and deleting the workspace and all associated meta data. The great thing is this runs every day - I don't have to kick it off and monitor it, I just get daily e-mails about the status and statistics.

Since the first workspace was purged by this process on January 18, 2010, this perpetual purge process has been used to remove 5,998 workspaces and 5,860 tablespaces, reclaiming 64,531,415,040 bytes from the file system. And now is on auto-pilot.

My manager, Mike Hichwa, also suggested I simply fold this feature into Application Express 4.0 - so Application Express can become a self-maintaining system. I'm not convinced this feature would be used by that many customers outside of I tend to believe DBA's and our customers would rarely want to automatically drop data.

6) Memory - This past Saturday, February 6, 2010, the memory on was upgraded from 16 GB to 32 GB. Since then, the machine is really very, very fast. I've monitored the system load and it is consistently running at about 50% of maximum capacity, even during peak times.

Going forward, I am going to request that we upgrade the database to version Additionally, I am going to request an upgrade to Oracle HTTP Server which supports true connection pooling and which should dramatically reduce the number of overall database sessions to the database server, itself. If the upgrade to Oracle HTTP Server can't be done in a reasonable time frame, then I'm going to try to setup server-side pooling via Database Resident Connection Pooling. Even though I always advise against multiplexing multiplexed connections, I can't really describe the connection pooling on our version of Oracle HTTP Server today as "multiplexed."

If I were in charge of this site, I would have done these upgrades yesterday. But probably like the company you're at, our IT organization that actually runs this site has their software levels and it takes time to uptake new versions and roll it out. I'm not complaining, as I understand and appreciate this when you have to manage hundreds of databases and Web applications, as they do.

Contrary to some assertions, the performance of was of great concern to us and to me personally. However, you cannot fix a problem until you have diagnosed the actual cause of the issue. And unfortunately, that takes time. Additionally, some of these steps took a great deal of time to actually implement and test before rolling out on As much as I would have liked to flip the "fast=true" bit, that wasn't reality. This has been a learning process and one which will ultimately benefit the users of going forward as well as the product, Oracle Application Express. I am grateful to those customers and members of the APEX community for their patience during this time. To paraphrase Frank Costanza, "We're back, baby!"