by Jim Czuprynski
An experienced Oracle Database DBA provides his perspective on the latest incarnation of Oracle Database Exadata Express Cloud Service (aka Exadata Express).
Published November 2017
With some urging from my colleagues at the Oracle Developer Community, I've recently had an opportunity to revisit the features of Oracle Public Cloud's Exadata Express. Fortuitously I had just finished preparing a series of deeper explorations into the features of Oracle Database In-Memory in Oracle Database 12c Release 2, so I jumped at the chance.
I have to admit that my initial experiences with an Exadata Express In-Memory 50 GB (X50IM) configuration back in January 2017 hadn't exactly met my expectations. I'd found that the instance was somewhat less than stable and difficult to load data into; also, it appeared at the time that quite a few Oracle Exadata Database Machine (aka Oracle Exadata) features were disabled or simply unavailable. This time around, however, I worked closely with my colleague Todd Bottger from Oracle to traverse any rough spots and resolve any confusion over exactly what features were enabled and (more importantly) locate the corresponding documentation that described those necessary limitations.
In addition, I should point out that Exadata Express is distinct from Oracle services such as Oracle Database Exadata Cloud Service and Oracle Database Cloud Service. These related services provide more scalability and administrative control in exchange for being more expensive and not fully managed. On the other hand, Exadata Express is intended to be the entry-level service from Oracle providing low prices, reasonable ceilings on the specs, and certain functionality disabled as you might expect in a fully managed offering. Despite what it's missing compared to the other Oracle services, I must say Exadata Express is a compelling proposition for the price.
The goal for my experimentation was two-fold and simple: I wanted to be able to gauge the performance of some real-world decision support queries against Oracle Public Cloud to probe its capacity for responsiveness and ease of use; moreover, I wanted to explore just how close the Oracle Public Cloud version of Exadata hewed to what I was already familiar with in an on-premises configuration of Exadata. To do that, it was obvious that I needed a sufficiently sized database and a relatively complex workload. Fortunately, I've spent the last 18 months working closely with Dominic Giles from Oracle to add the Transaction Processing Performance Council's Decision Support (TPC-DS) query workload capabilities into the most recent version of Swingbench, a venerable (and free!) workload generation toolset.
I used the latest version of Swingbench (version 188.8.131.526) to generate a heavy decision support/analytic query workload against a TPC-DS schema that I'd preloaded with approximately 20 GB of data using the SQL*Loader feature of Oracle Database. After adding all necessary primary key and unique indexes, the entire schema consumed about 25 GB of space. Because the Exadata Express X50IM instance permits up to 50 GB of usable space, this strategy also allowed me to test Swingbench's TPC-DS batch transaction workload generation capabilities significantly beyond the initial test series I had deployed on a relatively under-powered Oracle VM VirtualBox instance on my private home office's database server.
Note: If you haven't taken a look at Swingbench lately, now might be a good time! I've worked with Dominic extensively over the past 18 months to expand its capabilities dramatically. It's downloadable directly from this site.
Just like any Oracle Database instance that's resident within Oracle Public Cloud, my Exadata Express X50IM instance was accessible directly in the cloud via any internet browser. I simply pointed my browser at the URL that was included in my subscription confirmation email—in this case,
https://myservices.em2.oraclecloud.com/mycloud/<the-oracle-cloud-id-domain>/faces/dashboard.jspx. Then I provided the appropriate Oracle Cloud username and password, and I opened the Exadata Express X50IM instance's main dashboard, which is shown in Figure 1.
Figure 1. Accessing the X50IM Exadata Express dashboard
Clicking the Exadata Express link displayed considerable detail about my Exadata Express X50IM service instance, as Figure 2 shows.
Figure 2. Exadata Express service instance details
Clicking the Service Instance URL link from this panel opened the Home page for my Exadata Express X50IM service instance (Figure 3).
Figure 3. Exadata Express service instance's Home page
From the Home page, I could tour my Exadata Express instance's features, or I could begin to immediately develop applications against the instance (Figure 4) using a plethora of tools, including Oracle Application Express (Oracle APEX) or the relatively new APIs for Oracle REST Data Services, as well as more-traditional access tools from Oracle such SQL*Plus, Oracle SQL Developer, and SQL Command Line (SQLcl).
Figure 4. Exadata Express service instance's application development page
Figure 5 shows the management console for my Exadata Express X50IM instance, and because I was focused on getting access to that environment immediately, I leveraged this page extensively. First, I activated client access to my instance, as shown in Figure 6.
Figure 5. Exadata Express service instance's management console
Figure 6. Enabling client access for an Exadata Express instance
Activating client access involves downloading a zip file that contains several files I used with my SQL client environment, as Figure 7 shows. I'll illustrate how to leverage these files in the next section.
Figure 7. Downloading client credentials for an Exadata Express instance
Remember that because an Exadata Express instance is essentially a single pluggable database (PDB), there's no root container (
CDB$ROOT) access at all; however, it's still possible to change the password for the PDB's "master"
PDB_ADMIN account. Figure 8 shows how I changed that password, and Figure 9 shows notification of the successful password change.
Figure 8. Changing the password for the PDB_ADMIN account
Figure 9. Confirmation of the PDB_ADMIN account's password change
Running my sample application workloads using Swingbench required me to connect to my Exadata Express X50IM instance via SQL*Plus as well as an interactive GUI-based tool such as Oracle SQL Developer. I accomplished this with a few simple modifications to my Oracle Database 12.1 SQL client's network configuration files (
TNSNAMES.ORA), as shown below.
# Entries added to SQLNET.ORA: WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY="C:\OraClient\instantclient_12_1"))) SSL_SERVER_DN_MATCH=yes # Entries added to TNSNAMES.ORA: EXAEXP = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCPS) (PORT = 1522) (HOST = dbaccess.em2.oraclecloudapps.com)) (CONNECT_DATA = (SERVICE_NAME = co2sbrvqkvf.nldc1.oraclecloud.com)) (SECURITY = (ssl_server_cert_dn="CN=dbaccess.em2.oraclecloudapps.com, O=Oracle Corporation,L=Redwood Shores,ST=California,C=US") ) )
Because connecting to an Exadata Express instance essentially involves the same method used to connect to an individual PDB via a service name, I didn't need to fumble with any private/public key infrastructure (PKI) issues that can sometimes be the bane of a newbie when connecting to an Oracle Public Cloud–resident database-as-a-service (DBaaS) database, especially when creating new tablespaces for the first time in a brand-new PDB.
Note: I've written about some tricks a cloud DBA might need to leverage when dealing with keystores in Oracle Public Cloud–resident DBaaS databases in my latest book.
During my extensive TPC-DS query test runs via Swingbench, I found that the Exadata Express X50IM instance was essentially always available, responsive—or to use a technical term, "snappy"—and simple to access using just about every method I tried, including SQL*Plus, Oracle SQL Developer, and even my old standby third-party RDBMS monitoring tool, PL/SQL Developer. But it's also important to understand that an Exadata Express instance's resources are limited via appropriate settings in a PDB Lockdown Profile as well as in Database Resource Manager profiles.
|Am I Empowered to...||With DBaaS||With Exadata Express|
|Create my own multitenant container databases (CDBs)?||Yes||No|
|Create my own PDBs?||Yes||No|
|Clone a PDB to the same or another CDB?||Yes||No|
|Add my own schemas?||Yes||Yes|
|Build my own tablespaces?||Yes||Yes|
|Modify memory pool sizes (for example, SGA_SIZE)?||Yes||No|
|Modify security settings (for example, keystores)?||Yes||No|
|Connect applications directly via EZCONNECT or TNSNAMES?||Yes||Yes|
|Connect directly as SYS?||Yes||No|
|Flush the CDB's buffer cache?||Yes||No|
|Build a PDB using Oracle Recovery Manager (Oracle RMAN) backups?||Yes||No|
|Elastically upsize or downsize CPUs, memory, and storage?||Yes||No|
|Experiment with system-wide impact of different initialization parameters?||Yes, via ALTER SYSTEM||Yes, for many (but not all) via ALTER SYSTEM SET command|
|Connect with Oracle Enterprise Manager Cloud Control for monitoring?||Yes, via Proxy Agent||No|
|Load data with SQL*Loader or Oracle SQL Developer?||Yes||Yes, but source files must reside on separate host (that is, load from another machine directly into Exadata Express)|
|Load data using the Oracle Database Data Pump feature's ||Yes||Yes, intracloud (that is, between Exadata Express or Schema Service instances) via buttons in service console UI|
|Export data using the Oracle Database Data Pump feature's ||Yes||Yes, intracloud (that is, between Exadata Express or Schema Service instances) via buttons in service console UI|
The biggest drawback I encountered during iterative testing with my Exadata Express X50IM configuration was the mandatory 30-minute maximum session timeout. It actually appears that the threshold extends to almost 45 minutes. However, many of my queries hammered the underlying PDB for an extended period of time—especially when I had deactivated Oracle Database In-Memory features to capture baseline statistics for some of the longest-running queries—so the best I was able to ascertain was that a query exceeded a run time of 2,700 seconds. This made it difficult to provide an accurate worst-case measurement for elapsed time, total wait time, and I/O resources consumed.
Interestingly, there also appears to be a maximum I/O limit in place based on a CDB-level Oracle Database Resource Manager plan, but it was hard for me to ascertain exactly what limits were in place. However, I was able to see the impact on my queries through the values recorded in the AVG_IO_THROTTLE, IOPS_THROTTLE_EXEMPT, and IOMBPS_THROTTLE_EXEMPT columns from views V$RSRCPDBMETRIC and DBA_HIST_RSRC_PDB_METRIC.
Even considering the limitations I've already noted, there are quite a few advantages to leveraging an Exadata Express X50IM configuration instead of an instance within a DBaaS configuration:
TNSNAMES.ORAfile. (EZCONNECT is supported as well.)
Given the limitations I've already mentioned, what use cases is Exadata Express most appropriate for? Here are some guidelines that I believe will help you ascertain its desirability for your use cases:
I'd be remiss if I didn't describe at least some of the results for the tests that I've already completed. My simplest test series compares execution time, CPU time, and I/O consumed for all of the TPC-DS queries with Oracle Database In-Memory completely disabled (by simply setting the INMEMORY_QUERY initialization parameter to DISABLE at the session level) versus with Oracle Database In-Memory enabled. The chart in Figure 10 shows that of the 14 TPC-DS queries that typically ran for more than 1,800 seconds, only three still exhibited no improvement in execution time. For those that did improve, the improvement was dramatic, including some whose performance improved by two or more orders of magnitude:
Figure 10. Initial test results: Elapsed time improvement for longest-running queries
I'm currently expanding my tests to include activation of in-memory expressions (IM expressions), join groups, and other features that are part of the latest improvements to Oracle Database In-Memory in Oracle Database 12c Release 2. I will be publishing these results in the next several months as part of my upcoming presentations at the 2018 HoTSoS Symposium, COLLABORATE 18, and Kscope18 conferences and other regional Oracle user groups in the US.
Though I've mentioned some of the unexpected limitations of my Exadata Express X50IM instance, I found them to be simply minor annoyances when compared to the power and hassle-free configuration that Exadata Express provided for my extended testing. I didn't encounter any connectivity issues during any of my test periods; the metrics returned were accurate and reflected what I could expect from a similar "bare metal" or virtualized on-premises environment; and I was able to determine with sufficient accuracy which parts of my application workload would benefit from the latest Oracle Database In-Memory features in Oracle Database 12c Release 2, including IM expressions, join groups, and Automatic Data Optimization.
I'm also looking forward to experimenting with one of the much larger Exadata Express images that became available after I started this article, for example, the X250, X500, X1000, and X1000IM images. I hear anyone can get up and running with them for free by signing up for Oracle's free $300 cloud credits promotion. I hope these images will soon allow me to explore the latest iteration of Oracle Database 18c and the power of Oracle Database In-Memory features on an even larger scale.
Jim Czuprynski has 35+ years of experience in information technology, serving diverse roles at several Fortune 1000 companies before becoming an Oracle Database DBA in 2001. He was awarded the status of Oracle ACE Director in March 2014 and is a sought-after public speaker on Oracle Database technology features, presenting topics at Oracle OpenWorld, Independent Oracle Users Group (IOUG) COLLABORATE, ODTUG Kaleidoscope, HoTSoS Symposium, Oracle Technology Network ACE Tours, and Oracle user group conferences around the world. He authored over 100 articles focused on facets of Oracle Database administration at databasejournal.com and ioug.org. He also coauthored four books on Oracle Database technology. His blog, Generally ... It Depends, contains his regular observations on all things Oracle.