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:
tar -zxvf flights_data.dmp.tar.gz
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
imp system/password
file=./flights_data.dmp
fromuser=BI_AIRLINES
touser=FLIGHTS_DATA
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 RepositoryThe steps in this section describe how to create the BI repository for testing performance before and after configuring Oracle Database In-Memory.
Connection Type: OCI 10g/11g
Data Source Name: <<your TNS service name, such as orcl_inmem>>
User Name: flights_data
Password: welcome1
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.
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 TablesTo 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:
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
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:
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:
sqlplus sys/password@orcl as sysdba
SHOW PARAMETER sga_target
ALTER SYSTEM SET sga_target = 8080M
SCOPE = spfile;
ALTER SYSTEM
SET inmemory_size = 1024M
SCOPE = spfile;
shutdown immediate;
startup;
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 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:
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;
SELECT table_name
, inmemory
, inmemory_priority
, inmemory_compression
FROM user_tables;
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;
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.
ConclusionOracle 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.
Photography by Ricardo Gomez, Unsplash