Subscribe

Technology

Community

Features

Up Front

Comment

Share

Share

Application Developer

In-Memory Analytics Speeds Business Intelligence

Supercharge business intelligence with Oracle Database In-Memory.

By Mark Rittman Oracle ACE Director

January/February 2015

Oracle Database In-Memory adds columnar, in-memory processing to Oracle Database, improving the performance of analytical queries against large data sets. This database option provides an additional in-memory column storage format alongside the traditional disk-based row storage format.

When you enable in-memory storage for a database table, materialized view, or selection of columns, Oracle Database automatically creates an in-memory copy of the data and keeps it in sync with the row-format data, and it then uses the new query-optimized column format to satisfy business intelligence and analytic queries. Because no changes are needed in the application tier to take advantage of Oracle Database In-Memory, organizations using Oracle Business Intelligence solutions can easily take advantage of Oracle Database In-Memory without re-engineering their reports, data loading routines, or data warehouse designs.

In this article, I will load the BI AIRLINES data set that is part of the latest Oracle Business Intelligence Enterprise Edition 11g SampleApp (SampleApp v406 at oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html) into Oracle Database 12c Release 1 (12.1.0.2.0). I’ll create a simple repository and analysis using this data set and record the query execution plan and response time for the analysis before Oracle Database In-Memory is enabled. Then I’ll enable Oracle Database In-Memory, load the tables into memory, and run the tests again to demonstrate how this new database option can significantly improve the performance of these analytic queries.

If you want to run through the example in this article yourself, you will need to download and install the following software and configure a network connection between the two servers:

In addition, if you are running Oracle Business Intelligence on a UNIX or Linux platform, you will need a Microsoft Windows client environment to run the Oracle Business Intelligence administration tool to create the BI repository.

To save you from having to download the full SampleApp v406 VirtualBox image to obtain the BI AIRLINES data set, I’ve extracted it from the SampleApp v406 database and made it available as an Oracle Database export file, archived and compressed at bit.ly/flights_data.

Note that the download is 355 MB and that you will need about 3.5 GB of file system space on your database server to download and uncompress the download file and another 2.5 GB of space within the database to hold the imported data set.

To download and install the SampleApp v406 data set into your Oracle Database 12c Release 1 (12.1.0.2.0) installation, do the following:

  1. Download bit.ly/flights_data and copy the downloaded file to a directory on the database server, such as /home/oracle.
  2. Unpack the file, using the following command: 
    tar -zxvf flights_data.dmp.tar.gz

    You are left with a database export file called flights_data.dmp.
  3. Using SQL*Plus and connecting as the SYS user, run the following commands to create the user account that will hold the sample data and enable it to access a new dynamic performance view (which I’ll use later in the article to report on in-memory storage of the data): 
    sqlplus / as sysdba
    create user flights_data 
    identified by welcome1
    quota unlimited on users;
    grant connect, resource to 
    flights_data;
    grant select on v_$im_segments to 
    flights_data;
    exit
  4. From the command prompt and logged in to the server as the oracle user, use the IMP utility to import the contents of the database export file you downloaded into this new FLIGHTS_DATA schema, using the command below and the connection details for a DBA user, such as SYSTEM: 
    imp system/password 
    file=./flights_data.dmp 
    fromuser=BI_AIRLINES 
    touser=FLIGHTS_DATA
  5. Connect with SQL*Plus (as the FLIGHTS_DATA user), and verify that the sample database tables are present, as follows: 
    sqlplus flights_data/welcome1
    select table_name from user_tables;

Note: The previous steps assume that Oracle Database 12c Release 1 (12.1.0.2.0) is installed on a Linux server.

This article assumes that you’re reasonably familiar with the process of creating a BI repository and analysis, but if you need further information on the steps for performing these tasks, you can check out Oracle Fusion Middleware Metadata Repository Builder’s Guide for Oracle Business Intelligence Enterprise Edition and Oracle Fusion Middleware User’s Guide for Oracle Business Intelligence Enterprise Edition.

Creating the Repository

The steps in this section describe how to create the BI repository for testing performance before and after configuring Oracle Database In-Memory.

  1. From your Windows desktop, open the Oracle Business Intelligence administration tool and either open or connect to an existing repository or create a new one for this exercise.
  2. With this repository open for editing, select File -> Import Metadata to connect to the Oracle Database 12c instance and start importing the table metadata. When prompted, select and enter the following values, substituting the TNS service name for your own database, to connect to the schema containing the sample data: 
    Connection Type:	OCI 10g/11g
    Data Source Name:	<<your TNS service name, such as orcl_inmem>>
    User Name:	        flights_data
    Password:	        welcome1

    Click Next to continue.
  3. On the Create New Repository - Select Metadata Objects page, click the FLIGHTS_DATA schema in the left-hand panel and click the Import Selected button to select all the tables in the schema. In the Connection Pool - Connection Pool dialog box, leave all values at their default setting and click OK to close the dialog box. Then click Finish to complete the process and return to the main application window, where you should see entries for the four tables in the FLIGHTS_DATA schema displayed in the Physical panel.
  4. The tables within the FLIGHTS_DATA schema already have primary key and foreign key relationships defined for the fact and dimension tables. They are ready for you to drag and drop onto the Business Model and Mapping panel of the BI Administration tool to quickly create a business model. To do this, press Ctrl on your keyboard, click the FLIGHTS_DATA physical schema, and drag it onto the Business Model and Mapping panel.

Within this new business model, expand the PERFORMANCE logical table and delete all the columns except for ARRDELAY, DEPDELAY, and FLIGHTS. Then double-click each of the remaining measures in turn, select the Aggregation tab from the Properties dialog box, and choose Count as the default aggregation type for FLIGHTS and Avg for ARRDELAY and DEPDELAY. Drag and drop the whole FLIGHTS_DATA business model onto the Presentation panel to create a new subject area for reporting, so that the new physical database, business model, and subject area look like Figure 1.


o15ba-f1

Figure 1: Sample data with a repository

Save your repository by selecting File -> Save, and if you are working with a repository online, check it for consistency errors before completing the save. If you are working with your repository offline, use the Fusion Middleware Control feature of Oracle Enterprise Manager to upload your repository to Oracle Business Intelligence Server and restart your business intelligence instance so that this repository is now active for users.

You should also ensure that query caching is disabled for your business intelligence instance. Log in to Fusion Middleware Control to check this setting, and disable caching if the feature is currently enabled. (If you do not disable caching, Oracle Business Intelligence Server will not resend a new physical SQL query to the database when you rerun your test report, serving the analysis results from the query cache instead.) If you are not sure how to check for or disable query caching, refer to Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition.

Building an Analysis Against Standard Data Warehouse Tables

To demonstrate the impact that in-memory, compressed column storage will have on the data set, I’ll start by creating an analysis against the data as it is currently stored in row format on disk.

Using a web browser, navigate to the Oracle Business Intelligence home page, at http://[server_name:port]/analytics (for example, http://obi11gserver:9704/analytics). Log in, and create the following new analysis, using the FLIGHTS_DATA subject area:

  1. For the analysis criteria, select the PERFORMANCE.FLIGHTS and PERFORMANCE.DEPDELAY measures and the OBIEE_GEO_ORIG.TR_AIRPORT_NAME and OBIEE_GEO_DEST.TR_STATE_NAME attribute columns.
  2. Create the following two filters for the analysis: 
    OBIEE_GEO_ORIG.AIRPORT is equal to/is in SFO; ORD; LAX; DEN
    OBIEE_GEO_DEST.TR_STATE_NAME is equal to / is in Florida; Texas
  3. For the analysis results, create a pivot table view with TR_AIRPORT_NAME as the rows and TR_STATE_NAME as the columns, so that the analysis summarizes the total flights and average flight delay in minutes for flights from these four airports to Florida and Texas, as shown in Figure 2.


o15ba-f2



Figure 2: The analysis used for testing

Make a note of how long the query takes to return data. On my installation of Oracle Database 12c, with 6 million flight records in the main fact table, results were returned to my browser in just under 10 seconds. This type of query is exactly the type that could benefit from Oracle Database In-Memory, which can improve the performance of the following types of database operations:

  • Queries that scan large numbers of rows, filtering values by using =, <, >, and IN operators
  • Queries that return just a small subset of rows from a table or a materialized view
  • Queries that join small tables to a large table
  • Queries that aggregate data
And these are exactly the types of queries that are generated by Oracle Business Intelligence solutions when displaying information on dashboards and in analyses. Now let’s see what’s involved in enabling my four tables for in-memory analysis with Oracle Database In-Memory. Enabling the In-Memory Option in Oracle Database 12c

Before you enable the four tables for in-memory storage analysis, either you or your database administrator will need to enable Oracle Database In-Memory by allocating an amount of memory for the in-memory column store out of the overall system global area (SGA) allocation. When making this allocation, you may also want to increase the overall size of the SGA to accommodate the memory allocated to the in-memory column store.

To check the size of your Oracle Database 12c Release 1 (12.1.0.2.0) SGA and then increase it by 1 GB and use this additional memory for the in-memory column store, follow these steps:

  1. Connect to the database containing the tables you want to enable for in-memory analysis as a user with SYSDBA privileges, such as SYS: 
    sqlplus sys/password@orcl as sysdba
  2. Enter the following command to display the current SGA_TARGET parameter value: 
    SHOW PARAMETER sga_target

    To increase the size of the SGA from 7,056 MB to 8,080 MB, for example (an increase of 1,024 MB, the amount of memory you are going to allocate to the in-memory column store), enter the following command to increase the overall SGA_TARGET value: 
    ALTER SYSTEM SET sga_target = 8080M 
    SCOPE = spfile;
  3. To now allocate 1,024 MB of memory to the in-memory column store, thereby enabling the in-memory feature when the database is restarted, enter the following command: 
    ALTER SYSTEM 
    SET inmemory_size = 1024M 
    SCOPE = spfile;
  4. To restart the database with the new SGA and in-memory column store values, run the following final commands: 
    shutdown immediate;
    startup;

    The database will restart and report the new sizes for the static pools within your SGA, including the in-memory column store you just enabled. Here’s an example of the report: 
    SQL> startup
    ORACLE instance started.
    Total System Global Area	
    7398752256 bytes
    Fixed Size         2941528 bytes
    Variable Size      1207963048 bytes
    Database Buffers   5100273664 bytes
    Redo Buffers       13832192 bytes
    In-Memory Area     1073741824 bytes
    Database mounted.
    Database opened.
You are now ready to enable the four tables in the FLIGHTS_DATA schema for in-memory storage and analysis. Configure Your Tables for In-Memory Storage, and Rerun the Query

You can select individual columns from a table or a materialized view to populate into the in-memory column store, and if your database object is partitioned, you can select all or some of the partitions to populate the store. In my example, though, I’ll copy the entire tables into memory and choose the HIGH population priority setting so that this copying is performed as soon as possible.

You can choose to store in-memory column store data uncompressed or with varying degrees of compression optimized for querying or capacity. In my example, I’ll leave compression at the default, MEMCOMPRESS FOR QUERY LOW—the compression setting that is most optimized for query performance.

To enable your tables for in-memory analysis and storage with these settings, do the following, using SQL*Plus:

  1. Connect to SQL*Plus as the FLIGHTS_DATA user, and enter the following commands: 
    ALTER TABLE obiee_geo_orig inmemory 
    priority high;
    ALTER TABLE obiee_geo_dest inmemory 
    priority high;
    ALTER TABLE unique_carriers inmemory 
    priority high;
    ALTER TABLE performance inmemory 
    priority high;
  2. To check the in-memory attributes for your tables, you can query new in-memory-related columns in the USER_TABLES view: 
    SELECT table_name
    ,      inmemory
    ,      inmemory_priority
    ,      inmemory_compression 
    FROM   user_tables;
  3. To check whether all or some of your tables have been successfully loaded into memory, you can query the new V$IM_SEGMENTS dynamic performance view: 
    SELECT v.owner
    ,      v.segment_name name
    ,      v.populate_status status
    ,      v.bytes
    ,      v.inmemory_size
    ,      v.bytes_not_populated 
    missing_bytes
    FROM v$im_segments v;

    Note that if you want to access this dynamic performance view as a user without the SYSDBA role, you will need to have been granted SELECT access on the underlying V$IM_SEGMENTS view by a user with the SYSDBA role, such as the SYS user.

Running this last query will probably show all the smaller-dimension tables already loaded into memory, but it may take a minute or so to completely load the PERFORMANCE table into memory. Oracle Database will then reload these four tables into memory whenever you restart the database or when a table is first scanned (if you set the population priority to NONE).

Now you can return to your Oracle Business Intelligence analysis and rerun the query with the underlying data in memory and stored in compressed format optimized for querying. Results should be returned much faster than when you first ran the query.

When I reran the query with the data in memory, it ran in about half the time it did before the data was in memory. My query ran in 5 seconds rather than the 10 seconds it took without the Oracle Database In-Memory option and all without any changes to the BI repository, reports, or underlying data model.

Conclusion

Oracle Database In-Memory stores your most important reporting data in optimized column-store memory that is automatically kept in sync with the row store data, and this in-memory column store can significantly improve the response time of the types of queries generated by Oracle Business Intelligence solutions. Oracle Database 12c automatically uses the in-memory column store when providing data for your reports, analyses, and dashboards, with no changes needed to the BI repository or your users’ reports.

Next Steps

LEARN more about
Oracle Business Intelligence Enterprise Edition 11g
Oracle Database 12c
Oracle Database In-Memory
Oracle Business Intelligence Cloud Service

READ more Rittman
Rittman Mead blog
Introduction to Oracle BI Cloud
Oracle Magazine business intelligence columns

DOWNLOAD
Oracle Database 12c
Oracle Business Intelligence Enterprise Edition 11g (11.1.1.7.0)



Photography by Ricardo Gomez, Unsplash

Mark Rittman

Mark Rittman is an Oracle ACE Director and independent analyst who has been working with Oracle Business Intelligence and big data technology for more than 15 years. 

Oracle

Integrated Cloud & Platform Services

Integrated Cloud and Platform Services