Limitations of Oracle Exadata Express X20

For my current customer in the agricultural business I have created an application to plan the growths of Chrysanthemum flowers. The current users were used to work in Excel so I had to build in all kind of Excel like features, like navigation with arrow keys and exotic layouts (everything is possible in Excel). The application makes extensive use of Ajax calls to provide an interactive user interface.

The interface cannot be called a typical APEX Universal Theme interface.
Our development enviroment was a virtual Windows server with an Oracle XE database. This environment provided a reasonable performance.

The Exadata Express Environment

The application should be deployed on an Exadata Express cloud server. After some struggles and hickups with the provisioning the management console of the Exadata is easy to use. With the right privileges a user has access to both the APEX administration environment and all workspaces.

Access from SQL Developer

It took some time to figure out how to get access with SQL Developer. You need to download a ZIP file with TNS configuration files and a keystore. During the download process you need to provide a password to protect the keystore. The download process was quite picky about the passwords provided, and a lot of passwords were rejected while they adhered to the password rules given. It took some time to find an appropriate password, and I still do not know what made the difference (I did not want to spend more time on it). The zip file can be referred to when defining the SQL Developer connections.

Migrating the database

The initial database was migrated using a script generated with SQL Developer. After that the base data was migrated with drag-and-drop from the development schema to the cloud schema.
Of course the migration of the APEX schema was a piece of cake. Just import the application and run.

Referencing the JS and CSS files

The only thing that needed to be changed was the reference to the JS and CSS files. In the development environment those files are placed on the webserver and referenced through virtual directories, like /assets/js and /assets/css.
As there is no webserver or OS access on Exadata the files can only be loaded into the workspace static files. And the reference to those files has the form #WORKSPACE_IMAGES#. And the references to the virtual directories were all over the place.
The solution for this was to edit the application export (do not do this at home or just if your know what you are doing). The references to virtual directories were replaced by references to substitution variables &JS_DIR. and &CSS_DIR.. Import the application in the development environment and define the substitution variables JS_DIR and CSS_DIR with values /assets/js/ and /assets/css/ (do not forget the last slash).
After import of the changed application on the Exadata instance all that should be done is change the value of the substitution parameters to #WORKSPACE_IMAGES.  And of course loading the files.

Loading the JS and CSS files

Some 25 JS and CSS files should be loaded into the database. Normally a lot of pointing and clicking. But luckily APEX can load and extract zip files. So zip all the files and load the ZIP file into the static workspace files. APEX will automatically unzip the files when you set Unzip File to Yes:

Running the application => Error

Now everything was ready to run the application. So one person started to test the application, and after half an hour suddenly the application issued an error message: 

Also the APEX Builder was not reachable. After an hour or so the application suddenly became available again. This happened a few times until I tried to connect using SQL Developer and got the message:

ORA-00018: Maximum number of sessions exceed.

Using this clue it turned out the the Exadata X20 has a MAX_OPEN_SESSIONS parameter set at 30. For a normal APEX application you can host a good number of users because they will not use these sessions simultaniously. But this application used a lot of Ajax calls. 

The user reported to have used a specific page just before the error occurred. And on this page changing the value of a select list fired a dynamic action with 5 refreshes. If you change that select list a few times fast, you hit the limit of 30 sessions on your own! So this page was rebuilt to fire a submit on change so that only one new page was requested instead of numerous refreshes. Less elegant but much more efficient. 

Performance

What was also surprising was, that the application performed less fast on the Exadata than on out development server. It is a heavy application with tough queries but I would have expected the Exadata to outrun Oracle XE on a virtual server. But no, overall the development server was 20-30% faster than the Exadata. 

Conclusion

The conclusion is that the Exadata X20 seemed to be too good to be true, and indeed it did not turn out to be the solution for this problem. The limited of the number of sessions proved absolutely too small for this application. Also the performance was not adequate for this type of application. The next Exadata offers also have a limited number of sessions, so my customer moved to Oracle DBAAS. 

Happy APEXing