Adventures with Apex, part one: Boosting performance when using DBMS_EPG

We are currently developing a web application using Apex 3.1. There is a handful of developers on the team and we all work in the Apex Application Builder against a shared Oracle 11g database server, which has the Embedded PL/SQL Gateway (DBMS_EPG), rather than a full Apache (Oracle HTTP Server) setup, as per the default database install.

It did not take long before we noticed a significant variation in the time it took to serve Apex pages from the database. Most of the time, pages were served quickly enough, but at uneven intervals pages would just take forever to load. It seemed as if the underlying database or webserver/XDB session was timing out.

After some head-scratching, googling, and experimentation, the following two configuration changes fixed the problem. Page performance is now consistently excellent, and we do not experience timeouts anymore.

1. Increase the XDB session-timeout parameter (I changed it from the default of 6000 [1 minute] to a new value of 30000 [5 minutes].
2. Increase the shared_servers database parameter (I changed it from the default of 1 to a new value of 5).
   

Note: I changed both these settings at the same time, so I did not measure the effect of each individually. I suspect the shared_servers database setting is the most important of the two.

Detailed instructions follow:

Changing the session-timeout parameter in XDB

Connect as SYSDBA and run the following:

declare
  l_newconfig xmltype;
begin
  select updatexml (dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout/text()', 30000)
  into l_newconfig
  from dual;

  dbms_xdb.cfg_update (l_newconfig);

  commit;
end;
/

Note: The database must be restarted for the change to take effect. Issue "alter system register" for the new changes to take effect without restarting the database.

Changing the shared_servers database parameter

Connect as SYSDBA and run the following:

SQL> alter system set shared_servers = 5 scope=both;

System altered.

SQL> alter system register;

System altered.

This will create more background sessions to handle the Apex page requests served through XDB.