In the last 14 months I delivered a dozen of presentations covering the In-Memory Column Store. During many of them, I spent most of the time showing the audience several demos. The aim of this post is to share with you the scripts and a recording (MP4) of those demos.

Warning about Demos

The recordings show the results of running the scripts on an Exadata system. The performance figures are intended only to explain and compare different kinds of processing and to give you a feel for their impact. Since every system and every application has its own characteristics, the relevance of using each technique might be very different, depending on where it’s applied. Simply put, the scripts were engineered to clearly show specific behaviors.

In case you want to run the scripts, to setup the environment run imcs_prepare_schema.sql.

Population

This demo shows…

  • How to define that segments are eligible to be loaded in the in-memory area (ALTER TABLE statement)
  • How to explicitly and immediately trigger a population (DBMS_INMEMORY package)
  • How to check whether a population is running (GV$IM_USER_SEGMENTS view)
  • That the population is not a lightweight operation

Script: imcs_population.sql

FTS no Filter

This demo compares the performance of a full table scan executed in four different “configurations”:

  • No specific optimization (the performance strongly depends on disk I/O throughput that the database server supports; in the recording it’s a good one)
  • Table fully stored in the database buffer cache
  • Exadata offloading
  • Table fully stored in the in-memory area

Script: imcs_fts_no_filter.sql

FTS with Filter

This demo compares the performance of a full table scan applying a filter and executed in five different “configurations”:

  • No specific optimization (the performance strongly depends on disk I/O throughput that the database server supports; in the recording it’s a good one)
  • Table fully stored in the database buffer cache
  • Exadata offloading without storage indexes
  • Exadata offloading with storage indexes
  • Table fully stored in the in-memory area

Script: imcs_fts_filter.sql

FTS with Aggregation

This demo shows that if an aggregation is executed after a full table scan, the time needed to process the aggregation can also be larger than the time required to access the data. But, also in this case, the difference strongly depends on disk I/O throughput that the database server supports (in the recording it’s a good one).

Script: imcs_fts_aggregation.sql

Real Query

This demo compares the execution of a star query in three different “configurations”:

  • No specific optimization (the performance strongly depends on disk I/O throughput that the database server supports; in the recording it’s a good one)
  • All tables are fully stored in the in-memory area, vector transformation is not used
  • All tables are fully stored in the in-memory area, vector transformation is used

Script: imcs_real_query.sql

Distribution with RAC

This demo is based on the The Importance of the In-Memory DUPLICATE Clause for a RAC System post.

Script: imcs_duplicate.sql

Repopulation

This demo shows the performance impact of accessing data stored in the in-memory area that is not up-to-date. Note that in the demo 1% of the data is “stale” and that automatic repopulation is temporarily switched off.

Script: imcs_repopulation.sql