What You See Is What You Get Element

Oracle Database Exadata Express Cloud Service: A Timely Revisit

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


Table of Contents
The Test Plan
Accessing the Exadata Express Instance
Establishing Browserless Connections to an Exadata Express Instance
Exadata Express: Notable Limitations
Benefits and Advantages
Appropriate Use Cases
Positive Impact of Oracle Database In-Memory: Some Initial Dramatic Test Results
Conclusion: Exadata Express Was Worth a Second Look
About the Author

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 Test Plan

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 2.6.0.1046) 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.

Accessing the Exadata Express Instance

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.

Exadata Express Cloud Fig1

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.

Exadata Express Cloud Fig2

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).

Exadata Express Cloud Fig3

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).

Exadata Express Cloud Fig4

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.

Exadata Express Cloud Fig5

Figure 5. Exadata Express service instance's management console

Exadata Express Cloud Fig6

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.

Exadata Express Cloud Fig7

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.

Exadata Express Cloud Fig8

Figure 8. Changing the password for the PDB_ADMIN account

Exadata Express Cloud Fig9

Figure 9. Confirmation of the PDB_ADMIN account's password change

Establishing Browserless Connections to an Exadata Express Instance

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 (SQLNET.ORA and 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.

Exadata Express: Notable Limitations

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.

The complete list of restrictions can be found here and here, but I've summarized in Table 1 most of the ones that Oracle Database DBAs will be concerned about.

Table 1. DBaaS versus Exadata Express: Direct Comparison
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 impdp command? 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 expdp command? 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.

Benefits and Advantages

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:

  • Because every possible licensing option is included within an Exadata Express X50IM configuration, I don't have to worry about whether I've accidentally incurred a potential additional licensing fee, and because the Oracle Database In-Memory feature set is one of the most expensive licensable options, that helps prevent exhausting my cloud credits prematurely.
  • The service instance is always up. I don't have to stop and restart the service instance to attempt to conserve resources.
  • It's easy to connect an application to it. I only need to add a few entries into a host's SQLNET.ORA file and (if required) TNSNAMES.ORA file. (EZCONNECT is supported as well.)
  • It's completely configured. I don't need to mess with any INMEMORY parameters to set up and size Oracle Database In-Memory, the system global area (SGA), and the program global area (PGA); partitioning is already fully enabled; and I have only one PDB to worry about. (Note that for an Exadata Express X50IM image, SGA_SIZE, PGA_AGGREGATE_TARGET, and INMEMORY_SIZE are locked in at 10 GB, 5 GB, and 5 GB, respectively.)
  • I don't have to mess around with database backup and recovery. Regular Oracle RMAN backups already are performed for me by Oracle's cloud operations team.
  • Starting with the latest release, direct path loads are fully supported. I loaded 25 GB worth of TPC-DS data over my meager home office internet bandwidth in under four hours—and that includes index creation and statistics regathering.

Appropriate Use Cases

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:

  • Your applications need a reliable, well-performing service instance with an adequate amount of resources (compute, memory, and space).
  • You have a respectable (that is, 1 TB or less) amount of data to store in an Oracle Database 12c Release 2 database, and that data won't require an excessive number of indexes to retrieve and process data effectively.
  • Your application workloads are read-only or read-mostly and focus on leveraging analytical processing—especially real-time analytics, predictive analytics, or prescriptive analytics—that can take advantage of Oracle Database In-Memory's columnar storage and advanced query processing features.
  • You have neither the ability nor the desire to tune your database instance aggressively—as long as you have sufficient power to accomplish what needs to be done, no instance tuning is necessary. And that means you don't need to engage an expensive Oracle Database DBA to assist you in your deployment efforts.
  • Best of all, because you don't need an Oracle Database DBA, human resources are now free to pursue what they really should be doing: helping application developers build better systems from the ground up by choosing the right object structures; writing the best SQL and PL/SQL possible for higher maintainability and performance; and ensuring maximum recoverability of data while improving service-level-agreements for mission-critical systems.

Positive Impact of Oracle Database In-Memory: Some Initial Dramatic Test Results

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:

Exadata Express Cloud Fig10

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.

Conclusion: Exadata Express Was Worth a Second Look

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.

About the Author

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.