During some ongoing recent research into Database In-Memory features in Oracle Database 12c Release 2 for COLLABORATE18, I recently had a chance to dive deeply into the features of Oracle Public Cloud’s Oracle Database Exadata Express Cloud Service (also known as Exadata Express).
My initial experiences with an Exadata Express In-Memory 50 GB (X50IM) configuration back in January 2017 hadn’t exactly met my expectations; the instance was somewhat less than stable and difficult to load data into, and at the time quite it appeared that at least some Oracle Exadata Database Machine features were either disabled or simply unavailable. This time around, however, I worked closely with my colleague and Oracle Product Manager Todd Bottger to determine exactly what features were enabled and – just as important - locate the corresponding documentation that described any apparent limitations.
One key point before I continue, Exadata Express is quite 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. Conversely, Exadata Express is intended as an entry-level service from Oracle that proves low prices, reasonable ceilings on the specifications, and certain functionality that you might expect in a fully managed offering disabled. 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 126.96.36.1996) 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.
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
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.
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 DBaaS database, especially when creating new tablepsaces 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: PDB Me to Oracle Cloud Pocket Solutions Guide.
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 Exadata Express
Create my own multitenant container databases (CDBs)?
Create my own PDBs?
Clone a PDB to the same or another CDB?
Add my own schemas?
Build my own tablespaces?
Modify memory pool sizes (for example, SGA_SIZE)?
Modify security settings (for example, keystores)?
Connect applications directly via EZCONNECT or TNSNAMES?
Connect directly as SYS?
Flush the CDB’s buffer cache?
Build a PDB using Oracle Recovery Manager (Oracle RMAN) backups?
Elastically upsize or downsize CPUs, memory, and storage?
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
Load data with SQL*Loader or Oracle SQL Developer?
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, 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, 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 accidently incurred a potential additional licensing fee, and because the 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 DBIM, 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 Release2 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 DBIM: 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 DBIM completely disabled (by simply setting the INMEMORY_QUERY initialization parameter to DISABLE at the session level) versus with DBIM 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.
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.