Toad World Blog

Oracle In-Memory's Performance Screams

Jun 7, 2017 2:56:07 PM by Bert Scalzo

Since the dawn of database technology the Achilles heel or key limiting factor has been disk IO. In order to address this issue traditional spinning disk drive vendors historically offered 10K and even 15K RPM models. But more speed was needed. Then came Solid State Disks or SSDs, where depending upon the underlying technology (SLC vs. MLC vs. TLC) the speed increase vs. spinning disk was from between 10X to 100X. However once again more speed was needed. Then hardware vendors offered SSD technology connected directly to the PCI express (PCIe) bus which offers yet another 2X to 4X improvement. Finally we now have the nonvolatile memory express (NVMe) host controller interface specification which from the ground up was designed for SSD technology. This technology offers yet another 2X to 4X speedup. Thus we now have IO technology that’s at least 40X faster than spinning disks. Nonetheless memory is still 100X faster than that – so in-memory database technology offers DBAs the currently fastest possible method for accessing data from within the database. But just how good is it really?


For testing I used a star schema design data warehouse. I kept it small so that I could guarantee that the entire database would fit within the memory I had available. My dimension tables ranged from 5,000 to 180,000 rows. My fact table was 34 million rows – tiny by todays data warehousing norms, but sufficient for testing purposes. The query was a typical multi dimension to fact join with inline views, subqueries, complex filters and group operations. I tested three scenarios:

  • Star Schema table design using traditional b-tree indexes with typical execution plan
  • Star Schema table design using bitmap indexes with star transformation execution plan
  • Star Schema table design using no indexes with all facts and dimensions 100% in-memory

In addition I performed these three test cases on both Oracle 12c R1 and 12c R2 since the latter includes significant improvements to in-memory. The results are shown below in Figure 1.

Figure 1: Performance Comparison Results

The results are quite astounding on multiple levels. Of course we see the major (6X in this case) benefit of using star transformation execution which relies upon bitmap instead of b-tree indexes. Now look at the in-memory results – we see yet another major reduction of 12X faster than the star transformation and an astounding 69X vs. the traditional design. But there’s still more – look now at Figure 2 comparing in-memory improvements for Oracle 12c R2.

Figure 2: Oracle 12c R2 in-memory Improvement

As you can see Oracle 12c R2 has delivered about 4.5X better performance than 12c R1. That translates into 54X faster than the star transformation and a flabbergasting 309X vs. the traditional design. Oh and by the way the in-memory stores data in columnar format so I also saw a 3.85X compression factor so that what took 1 GB just for the row format not including indexes took just 25 MB of memory. So even on this meager setup with just 4GB of the Oracle SGA allocated for in-memory I could easily handle over a billion row table. So Oracle in-memory is not just fast, it’s highly efficient as well.

Tags: Oracle

Bert Scalzo

Written by Bert Scalzo

Bert Scalzo is a renowned database expert, Oracle® ACE, author, senior product manager for IDERA's DBArtisan, and formerly a member of Dell Software’s TOAD dev team. With three decades of Oracle® database experience to draw on, Bert’s webcasts garner high attendance and participation rates. His work history includes time at both Oracle Education and Oracle Consulting. Bert holds several Oracle Masters certifications and has an extensive academic background that includes a BS, MS and Ph.D. in computer science, as well as an MBA, and insurance industry designations.

Bert is a highly sought-after speaker who has presented at numerous Oracle conferences and user groups, including OOW, ODTUG, IOUG, OAUG, RMOUG and many others. Bert enjoys sharing his vast knowledge on data modeling, database benchmarking, database tuning and optimization, "star schema" data warehouses, Linux® and VMware®.

As a prolific writer, Bert has produced educational articles, papers and blogs for such well-respected publications as the Oracle Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell Power Solutions Magazine, The LINUX Journal, LINUX.com, Oracle FAQ, Ask Toad and Toad World.

This popular author is known throughout the industry for his instructive books, which include:

  • Oracle DBA Guide to Data Warehousing and Star Schemas
  • TOAD Handbook (1st and 2nd Ed.)
  • TOAD Pocket Reference (2nd Ed.)
  • Database Benchmarking: Practical Methods for Oracle & SQL Server
  • Advanced Oracle Utilities: The Definitive Reference
  • Oracle on VMware: Expert Tips for Database Virtualization
  • Introduction to Oracle: Basic Skills for Any Oracle User
  • Introduction to SQL Server: Basic Skills for Any SQL Server User