Tuesday, 29 May 2018

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


2 comments:

Fateh said...

Cannot Agree more,

I spent too much time trying and measuring the performance of X500.
I could be very wrong, but I think that Oracle is missing the point here. I have considered the cloud for two reasons:
1- Availability: But every now and then I get service maintenance email from Oracle Cloud.
2- Scalability: Oracle DB cloud does not offer auto scale performance.

* There is remarkable latency when accessing the cloud dashboard/admin from UAE so I cannot expect much from the service itself.
* I think Exadata is mainly a marketing term. It does not have many features of the enterprise edition especially data redaction.
I am going to run Oracle DB on Intel CUP 3.5 MGHz and 170,000 IOPS SSD soon, and if I get a chance, I will update you about the performance.

To close, my main point is the Offering, I am not speaking about DB itself.
Just have a look at what Google Cloud offers:
https://cloud.google.com/spanner/
https://cloud.google.com

Fateh said...

In the Oracle Cloud conference, the presenter started explaining the cloud concept and showed the major services that are running on the cloud e.g, Youtube, FB, Twitter, Netflex ....
I asked him which of them uses Oracle cloud?
I think that you know the answer.