Taking a Look at the Oracle Database 12c In-Memory Option
The In-Memory Option for Oracle Database 12c became available a few weeks ago with the 12.1.0.2 database patchset, adding column-store and in-memory capabilities to the Oracle Database. Unlike pure in-memory databases such as Oracle TimesTen, the in-memory option adds an in-memory column-store feature to the regular row-based storage in the Oracle database, creating in-memory copies of selected row-store tables in a compressed column-based storage format, with the whole process being automatic and and enabled by issuing just a couple of commands - to the point where my feedback at the end of beta testing was that it was “almost boring” - said slightly tongue-in-cheek...
But of course adding in-memory capabilities to the Oracle database is anything but boring - whilst TimesTen on Exalytics has given us the ability to store aggregate tables, and “hot data” tables in a dedicated in-memory database co-located with the BI mid-tier, TimesTen is another set of skills to learn and another product to manage. Moreover, you’ve got to somehow get the data you want to aggregate or otherwise cache out of your data warehouse and into TimesTen, and then you’ve got the even more difficult task of keeping that TimesTen dataset in-sync with your main data warehouse data. Most importantly though, even with the 2TB and 4TB versions of Exalytics many data warehouses won’t fit entirely into memory, so you’re going to end-up with some of your data in TimesTen and some in your data warehouse, and with only the TimesTen dataset benefiting from in-memory speeds.
So what if you could enable the in-memory option on your data warehouse, and then just run it all in-memory there? To see how this worked, I thought it’d be interesting to take the flight delays dataset that comes with the latest OBIEE 11g v406 SampleApp, and copy that across to an Oracle 12.1.0.2.0 database to test it out with the in-memory option. This dataset comes with a set of dashboard pages and analyses and contains around 6m rows of data, and in SampleApp is accelerated by an aggregate schema, also stored in Oracle, that generally reduces query times down to just a few seconds.
So what I thought would be interesting to check would be first, whether enabling the in-memory option sped-up the dashboards, and second, whether having the in-memory option removed the need for the aggregate tables altogether. I was also interested to see whether putting the aggregate tables into memory improved their access time significantly, as anecdotally I’d heard that there wasn’t much clock-time difference between accessing aggregates in a TimesTen database vs. just creating them in the same data warehouse database as the source data. To set this all up then I created the new 12.1.0.2.0 database,. exported the BI_AIRLINES and BI_AIRLINES_AGG schemas out of the 12c database that comes with SampleApp v406, and then imported them into the same schema names in the new 12.1.0.2.0 instance, on the new VM (rather than on the SampleApp VM), and then amended the connection pool details in the SampleApp RPD to point to this new, external Oracle 12.1.0.2.0 database instance. The 12.1.0.2.0 database at this point had the following memory allocation:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 6325010432 bytes Fixed Size 2938448 bytes Variable Size 1207962032 bytes Database Buffers 5100273664 bytes Redo Buffers 13836288 bytes Database mounted. Database opened.
So I don’t end-up comparing the larger database instance on my 12.1.0.2.0 VM with the smaller one the airlines data came from on the VM, I created my baseline response time measurements on this new VM and with none of the in-memory features enabled, and ran some of the dashboard pages and clicked-on some of the prompt values - and the response time wasn’t bad, with just the odd analysis that took an excessive time to return. Mostly though, the results for each dashboard came back straight-away, what you’d expect when summary tables have been deployed to speed-up reporting.
Looking at the SQL being generated by the BI Server, you can quickly see why this is the case; the BI Server is using aggregate navigation and actually querying the BI_AIRLINES_AGG schema to return the aggregated results the dashboard, as you can see from one of the SQL statements retrieved from the NQQuery.log file below:
select sum(T255906.Dep_Delay_00039B26) as c1,
sum(T255906.Arr_Delay_00039B22) as c2,
sum(T255906.Z_of_Fligh00039B28) as c3,
substr(T255216.Carrier00039BA9 , 1, 25) as c4,
T255216.Carrier00039BA9 as c5,
T255216.Carrier_Co00039BAA as c6
from
BI_AIRLINES_AGG.SA_16_Dest00039D06 T255357,
BI_AIRLINES_AGG.SA_Time_Mo00039CFB T255737,
BI_AIRLINES_AGG.SA_31_Carr00039CEB T255216,
BI_AIRLINES_AGG.FACT_AGG_OR_06M T255906
where ( T255216.Carrier_Co00039BAA = T255906.Carrier_Co00039BAA and T255357.Dest_Airpo00039C2A = T255906.Dest_Airpo00039C2A and T255737.Dep_Month00039C07 = T255906.Dep_Month00039C07 and substr(T255216.Carrier00039BA9 , 1, 25) = 'SunFlower Airlines' and (T255357.Dest_Regio00039C31 in ('Midwest Region', 'Northeast Region', 'South Region', 'West Region')) and T255737.Month_of_Y00039C0F between 3 and 12 )
group by T255216.Carrier00039BA9, T255216.Carrier_Co00039BAA
order by c5, c6, c4
OBIEE will always use these aggregate tables if they’re available in the repository, so to enable me to test the reports with these aggregates disabled I borrowed the technique Robin introduced in his blog on testing aggregate navigation, and added a request variable prompt to the dashboard page that allows me to pass an INACTIVE_SCHEMAS value to the logical SQL queries issued for the dashboard analyses, and therefore tell the BI Server to ignore the aggregate schema and just use the detail-level BI_AIRLINES schema instead.
I therefore test the dashboard again, this time with the BI_AIRLINES_AGG schema disabled; this time, every dashboard took a while to display properly, with every analysis showing the “spinning clock” for twenty, thirty seconds a time. Comparing the response times to the ones a moment ago when aggregates were enabled, and you can see the difference.
So let’s set-up the in-memory option and see how it affects these two scenarios. The in-memory option for Oracle Database 12c 12.1.0.2.0 is installed by default as part of the core database, but doesn’t start working until you set a value for the INMEMORY_SIZE initialisation parameter - as you can see from the SQL*Plus output below, it’s currently set to zero, effectively disabling the feature:
SQL> conn / as sysdba Connected. SQL> show parameter INMEMORY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 percent optimizer_inmemory_aware boolean TRUE
The memory Oracle allocates to the in-memory option, via this initialisation parameter, comes out of the SGA and therefore before I set the INMEMORY_SIZE parameter, it’d be a good idea to increase the SGA_TARGET value to accommodate the 1G I’m then going to assign to INMEMORY_SIZE. I do both from the SQL*Plus command-prompt and then bounce the database to bring the new values into use:
SQL> alter system set inmemory_size = 1G scope=spfile; System altered. SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 6032M SQL> alter system set sga_target = 7032M scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 7381975040 bytes Fixed Size 2941480 bytes Variable Size 1207963096 bytes Database Buffers 5083496448 bytes Redo Buffers 13832192 bytes In-Memory Area 1073741824 bytes Database mounted. Database opened.
Now we’re at the point where we can enable the tables for in-memory access. Tables to be copied into memory can either be created like that at the start, or you can ALTER TABLE them afterwards and specify that they go into memory (or you can configure the in-memory settings at the tablespace level, or even specify particular columns or partitions to go into memory). The underlying data still gets stored row-wise on disk, but enabling a table for in-memory access tells the Oracle database to create column-store in-memory representations of the table and direct suitable queries to those copies, all the time in the background keeping the copy in-sync with the row-store base data.
I’ll start by enabling all of the BI_AIRLINES schema for in-memory access, as the biggest pay-off would be if then meant we didn’t need to maintain the aggregate tables. After running a SELECT table_name FROM user_tables to list out the table names, I then run a script to enable all the tables for in-memory access, with the in-memory copies being populated immediately:
alter table AIRCRAFT_GROUP inmemory priority high; alter table AIRCRAFT_TYPE inmemory priority high; alter table AIRLINES_USER_DATA inmemory priority high; alter table AIRLINE_ID inmemory priority high; alter table CANCELLATION inmemory priority high; alter table CARRIER_GROUP_NEW inmemory priority high; alter table CARRIER_REGION inmemory priority high; alter table DEPARBLK inmemory priority high; alter table DISTANCE_GROUP_250 inmemory priority high; alter table DOMESTIC_SEGMENT inmemory priority high; alter table OBIEE_COUNTY_HIER inmemory priority high; alter table OBIEE_GEO_AIRPORT_BRIDGE inmemory priority high; alter table OBIEE_GEO_ORIG inmemory priority high; alter table OBIEE_ROUTE inmemory priority high; alter table OBIEE_TIME_DAY_D inmemory priority high; alter table OBIEE_TIME_MTH_D inmemory priority high; alter table ONTIME_DELAY_GROUPS inmemory priority high; alter table PERFORMANCE inmemory priority high; alter table PERFORMANCE_ENDECA_MV inmemory priority high; alter table ROUTES_FOR_LINKS inmemory priority high; alter table SCHEDULES inmemory priority high; alter table SERVICE_CLASS inmemory priority high; alter table UNIQUE_CARRIERS inmemory priority high;
Looking at USER_TABLES from this schema, I can see all of the tables I selected now marked for in-memory access, for immediate loading:
SQL> @display_table_inmem_details.sql SQL> select table_name 2 , inmemory 3 , inmemory_priority 4 from user_tables 5 / TABLE_NAME INMEMORY INMEMORY -------------------- -------- -------- AIRCRAFT_GROUP ENABLED HIGH UNIQUE_CARRIERS ENABLED HIGH SERVICE_CLASS ENABLED HIGH SCHEDULES ENABLED HIGH ROUTES_FOR_LINKS ENABLED HIGH PERFORMANCE ENABLED HIGH ONTIME_DELAY_GROUPS ENABLED HIGH OBIEE_TIME_MTH_D ENABLED HIGH OBIEE_TIME_DAY_D ENABLED HIGH OBIEE_ROUTE ENABLED HIGH OBIEE_GEO_ORIG ENABLED HIGH TABLE_NAME INMEMORY INMEMORY -------------------- -------- -------- OBIEE_GEO_AIRPORT_BR ENABLED HIGH IDGE OBIEE_COUNTY_HIER ENABLED HIGH DOMESTIC_SEGMENT ENABLED HIGH DISTANCE_GROUP_250 ENABLED HIGH DEPARBLK ENABLED HIGH CARRIER_REGION ENABLED HIGH CARRIER_GROUP_NEW ENABLED HIGH CANCELLATION ENABLED HIGH AIRLINE_ID ENABLED HIGH TABLE_NAME INMEMORY INMEMORY -------------------- -------- -------- AIRLINES_USER_DATA ENABLED HIGH AIRLINES_PBLOB$ DISABLED AIRLINES_PART$ DISABLED AIRLINES_NODE_TZ$ DISABLED AIRLINES_NODE$ DISABLED AIRLINES_LINK_TZ$ DISABLED AIRLINES_LINK_SCH$ DISABLED AIRLINES_LINK$ DISABLED AIRLINES_AIRPORT_TZ$ DISABLED AIRCRAFT_TYPE ENABLED HIGH 30 rows selected.
And I can track the progress of the tables being copied into memory using the V$IM_SEGMENTS v$ view, like this:
SQL> @display_im_segments.sql
SQL> set echo on
SQL> set pages 200
SQL> col owner for a20
SQL> col name for a20
SQL> col status for a10
SQL> select v.owner
2 , v.segment_name name
3 , v.populate_status status
4 from v$im_segments v;
OWNER NAME STATUS
-------------------- -------------------- ----------
BI_AIRLINES OBIEE_COUNTY_HIER COMPLETED
BI_AIRLINES PERFORMANCE STARTED
BI_AIRLINES UNIQUE_CARRIERS COMPLETED
BI_AIRLINES AIRLINES_LINK_TZ$ COMPLETED
BI_AIRLINES OBIEE_TIME_MTH_D COMPLETED
BI_AIRLINES AIRLINES_LINK_SCH$ COMPLETED
BI_AIRLINES OBIEE_ROUTE COMPLETED
BI_AIRLINES DOMESTIC_SEGMENT COMPLETED
BI_AIRLINES AIRLINES_LINK$ COMPLETED
BI_AIRLINES AIRLINE_ID COMPLETED
BI_AIRLINES OBIEE_GEO_ORIG COMPLETED
BI_AIRLINES AIRLINES_NODE$ COMPLETED
BI_AIRLINES OBIEE_GEO_AIRPORT_BR COMPLETED
IDGE
BI_AIRLINES AIRLINES_NODE_TZ$ COMPLETED
BI_AIRLINES OBIEE_TIME_DAY_D COMPLETED
Note that most of the tables went into memory immediately, but one (PERFORMANCE) is taking a while because it’s so big. Also note that not all tables are listed in the v$ view yet, as the database hasn’t got around to adding them into memory yet, or it might choose not to populate them if it feels the memory could be used better elsewhere. You can alter the priority of these in-memory copy processes if you want, and decide whether the copying happens immediately, or when the table is first scanned (accessed).
Running the dashboards again, with the request variable prompt set to disallow the aggregate schema, gave me the response times below - the in-memory queries were much faster than the row-based non in-memory ones, but they weren’t down to the response time of the dashboards right at the start, where all data is stored row-wise but we’ve got aggregate tables to speed up the queries (note I’ve reordered the bars so the non in-memory queries with no aggregate tables are on the left of each series, as the slowest of all approaches)
Taking a look at one of the physical SQL queries for a cross-tab (hierarchical columns) analysis, you can see that in-memory table access is happening:
WITH
SAWITH0 AS (select sum(T233937.ACTUALELAPSEDTIME) as c1,
sum(T233937.ARRDELAYMINUTES) as c2,
concat(concat(T233484.AIRPORT, ' - '), substr(T233484.TR_AIRPORT_NAME , instr(T233484.TR_AIRPORT_NAME , ': ') + 2 , 50)) as c3,
T233820.D_NAME as c4,
T233820.R_NAME as c5
from
BI_AIRLINES.OBIEE_GEO_AIRPORT_BRIDGE T233484 /* 10 GEO_AIRPORT_ORIGIN */ ,
BI_AIRLINES.OBIEE_COUNTY_HIER T233820 /* 11 COUNTY_HIER_ORIGIN */ ,
BI_AIRLINES.OBIEE_TIME_MTH_D T233732 /* 41 TIME MONTH */ ,
BI_AIRLINES.PERFORMANCE T233937 /* 00 PERFORMANCE FULL */
where ( T233484.AIRPORT = T233937.ORIGIN and T233484.STCTY_FIPS = T233820.SC_CODE and T233732.Cal_Month = T233937.MONTH and T233732.Cal_Qtr = T233937.QUARTER and T233732.Cal_Year = T233937.YEAR and T233732.Cal_Month between 6 and 12 and T233937.MONTH between 6 and 12 )
group by T233820.D_NAME, T233820.R_NAME, concat(concat(T233484.AIRPORT, ' - '), substr(T233484.TR_AIRPORT_NAME , instr(T233484.TR_AIRPORT_NAME , ': ') + 2 , 50))),
SAWITH1 AS (select sum(T233609.PASSENGERS) as c1,
T233820.R_NAME as c2,
T233820.D_NAME as c3,
concat(concat(T233484.AIRPORT, ' - '), substr(T233484.TR_AIRPORT_NAME , instr(T233484.TR_AIRPORT_NAME , ': ') + 2 , 50)) as c4,
sum(T233609.PASSENGERS_MILES) as c5
from
BI_AIRLINES.OBIEE_GEO_AIRPORT_BRIDGE T233484 /* 10 GEO_AIRPORT_ORIGIN */ ,
BI_AIRLINES.OBIEE_COUNTY_HIER T233820 /* 11 COUNTY_HIER_ORIGIN */ ,
BI_AIRLINES.OBIEE_TIME_MTH_D T233732 /* 41 TIME MONTH */ ,
BI_AIRLINES.DOMESTIC_SEGMENT T233609 /* 01 DOMESTIC Segment */
where ( T233484.AIRPORT = T233609.ORIGIN and T233484.STCTY_FIPS = T233820.SC_CODE and T233609.MONTH = T233732.Cal_Month and T233609.QUARTER = T233732.Cal_Qtr and T233609.YEAR = T233732.Cal_Year and T233609.MONTH between 6 and 12 and T233732.Cal_Month between 6 and 12 )
group by T233820.D_NAME, T233820.R_NAME, concat(concat(T233484.AIRPORT, ' - '), substr(T233484.TR_AIRPORT_NAME , instr(T233484.TR_AIRPORT_NAME , ': ') + 2 , 50)))
select 2 as c1,
case when D1.c3 is not null then D1.c3 when D2.c4 is not null then D2.c4 end as c2,
case when D1.c4 is not null then D1.c4 when D2.c3 is not null then D2.c3 end as c3,
case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end as c4,
'All Orig Airports' as c5,
1 as c6,
case when D1.c4 is not null then D1.c4 when D2.c3 is not null then D2.c3 end as c7,
case when D1.c5 is not null then D1.c5 when D2.c2 is not null then D2.c2 end as c8,
cast(D1.c2 as DOUBLE PRECISION ) / nullif( D1.c1, 0) * 100 as c9,
D2.c5 as c10,
D2.c1 as c14
from
SAWITH0 D1 full outer join SAWITH1 D2 On D1.c3 = D2.c4 and SYS_OP_MAP_NONNULL(D1.c4) = SYS_OP_MAP_NONNULL(D2.c3)
order by c4, c3, c2
SQL> @complex_query_explain.sql
Explained.
SQL> set lines 200
SQL> set pages 0
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3097908901
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2673K| 1392M| | 112K (1)| 00:00:05 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10006 | 2673K| 1392M| | 112K (1)| 00:00:05 | Q1,06 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 2673K| 1392M| 1492M| 112K (1)| 00:00:05 | Q1,06 | PCWP | |
| 4 | PX RECEIVE | | 2673K| 1392M| | 26819 (1)| 00:00:02 | Q1,06 | PCWP | |
| 5 | PX SEND RANGE | :TQ10005 | 2673K| 1392M| | 26819 (1)| 00:00:02 | Q1,05 | P->P | RANGE |
| 6 | VIEW | VW_FOJ_0 | 2673K| 1392M| | 26819 (1)| 00:00:02 | Q1,05 | PCWP | |
|* 7 | HASH JOIN FULL OUTER BUFFERED | | 2673K| 1392M| | 26819 (1)| 00:00:02 | Q1,05 | PCWP | |
| 8 | PX RECEIVE | | 136 | 37128 | | 144 (7)| 00:00:01 | Q1,05 | PCWP | |
| 9 | PX SEND HASH | :TQ10003 | 136 | 37128 | | 144 (7)| 00:00:01 | Q1,03 | S->P | HASH |
| 10 | PX SELECTOR | | | | | | | Q1,03 | SCWC | |
| 11 | VIEW | | 136 | 37128 | | 144 (7)| 00:00:01 | Q1,03 | SCWC | |
| 12 | HASH GROUP BY | | 136 | 17408 | | 144 (7)| 00:00:01 | Q1,03 | SCWC | |
|* 13 | HASH JOIN | | 136 | 17408 | | 143 (6)| 00:00:01 | Q1,03 | SCWC | |
| 14 | JOIN FILTER CREATE | :BF0000 | 136 | 11288 | | 142 (6)| 00:00:01 | Q1,03 | PCWP | |
|* 15 | HASH JOIN | | 136 | 11288 | | 142 (6)| 00:00:01 | Q1,03 | SCWC | |
| 16 | JOIN FILTER CREATE | :BF0001 | 136 | 4896 | | 142 (6)| 00:00:01 | Q1,03 | PCWP | |
|* 17 | HASH JOIN | | 136 | 4896 | | 142 (6)| 00:00:01 | Q1,03 | SCWC | |
|* 18 | TABLE ACCESS INMEMORY FULL | OBIEE_TIME_MTH_D | 161 | 1610 | | 1 (0)| 00:00:01 | Q1,03 | SCWP | |
|* 19 | TABLE ACCESS INMEMORY FULL | DOMESTIC_SEGMENT | 771 | 20046 | | 141 (6)| 00:00:01 | Q1,03 | SCWP | |
| 20 | JOIN FILTER USE | :BF0001 | 1787 | 83989 | | 1 (0)| 00:00:01 | Q1,03 | PCWP | |
|* 21 | TABLE ACCESS INMEMORY FULL | OBIEE_GEO_AIRPORT_BRIDGE | 1787 | 83989 | | 1 (0)| 00:00:01 | Q1,03 | SCWP | |
| 22 | JOIN FILTER USE | :BF0000 | 3221 | 141K| | 1 (0)| 00:00:01 | Q1,03 | PCWP | |
|* 23 | TABLE ACCESS INMEMORY FULL | OBIEE_COUNTY_HIER | 3221 | 141K| | 1 (0)| 00:00:01 | Q1,03 | SCWP | |
| 24 | PX RECEIVE | | 2255K| 587M| | 26673 (1)| 00:00:02 | Q1,05 | PCWP | |
| 25 | PX SEND HASH | :TQ10004 | 2255K| 587M| | 26673 (1)| 00:00:02 | Q1,04 | P->P | HASH |
| 26 | VIEW | | 2255K| 587M| | 26673 (1)| 00:00:02 | Q1,04 | PCWP | |
| 27 | HASH GROUP BY | | 2255K| 264M| 489M| 26673 (1)| 00:00:02 | Q1,04 | PCWP | |
| 28 | PX RECEIVE | | 2255K| 264M| | 26673 (1)| 00:00:02 | Q1,04 | PCWP | |
| 29 | PX SEND HASH | :TQ10002 | 2255K| 264M| | 26673 (1)| 00:00:02 | Q1,02 | P->P | HASH |
| 30 | HASH GROUP BY | | 2255K| 264M| 489M| 26673 (1)| 00:00:02 | Q1,02 | PCWP | |
|* 31 | HASH JOIN | | 3761K| 441M| | 841 (9)| 00:00:01 | Q1,02 | PCWP | |
| 32 | PX RECEIVE | | 161 | 1610 | | 1 (0)| 00:00:01 | Q1,02 | PCWP | |
| 33 | PX SEND BROADCAST | :TQ10000 | 161 | 1610 | | 1 (0)| 00:00:01 | Q1,00 | S->P | BROADCAST |
| 34 | PX SELECTOR | | | | | | | Q1,00 | SCWC | |
|* 35 | TABLE ACCESS INMEMORY FULL | OBIEE_TIME_MTH_D | 161 | 1610 | | 1 (0)| 00:00:01 | Q1,00 | SCWP | |
|* 36 | HASH JOIN | | 3773K| 406M| | 838 (9)| 00:00:01 | Q1,02 | PCWP | |
| 37 | PX RECEIVE | | 1787 | 160K| | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 38 | PX SEND BROADCAST | :TQ10001 | 1787 | 160K| | 2 (0)| 00:00:01 | Q1,01 | S->P | BROADCAST |
| 39 | PX SELECTOR | | | | | | | Q1,01 | SCWC | |
|* 40 | HASH JOIN | | 1787 | 160K| | 2 (0)| 00:00:01 | Q1,01 | SCWC | |
| 41 | TABLE ACCESS INMEMORY FULL| OBIEE_GEO_AIRPORT_BRIDGE | 1787 | 83989 | | 1 (0)| 00:00:01 | Q1,01 | SCWP | |
| 42 | TABLE ACCESS INMEMORY FULL| OBIEE_COUNTY_HIER | 3221 | 141K| | 1 (0)| 00:00:01 | Q1,01 | SCWP | |
| 43 | PX BLOCK ITERATOR | | 3773K| 75M| | 834 (9)| 00:00:01 | Q1,02 | PCWC | |
|* 44 | TABLE ACCESS INMEMORY FULL | PERFORMANCE | 3773K| 75M| | 834 (9)| 00:00:01 | Q1,02 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("D1"."C3"="D2"."C4" AND SYS_OP_MAP_NONNULL("D1"."C4")=SYS_OP_MAP_NONNULL("D2"."C3"))
13 - access("T233484"."STCTY_FIPS"="T233820"."SC_CODE")
15 - access("T233484"."AIRPORT"="T233609"."ORIGIN")
17 - access("T233732"."CAL_MONTH"=TO_NUMBER("T233609"."MONTH") AND "T233732"."CAL_QTR"=TO_NUMBER("T233609"."QUARTER") AND
"T233732"."CAL_YEAR"=TO_NUMBER("T233609"."YEAR"))
18 - inmemory("T233732"."CAL_MONTH">=6 AND "T233732"."CAL_MONTH"<=12)
filter("T233732"."CAL_MONTH">=6 AND "T233732"."CAL_MONTH"<=12)
19 - inmemory(TO_NUMBER("T233609"."MONTH")>=6 AND TO_NUMBER("T233609"."MONTH")<=12)
filter(TO_NUMBER("T233609"."MONTH")>=6 AND TO_NUMBER("T233609"."MONTH")<=12)
21 - inmemory(SYS_OP_BLOOM_FILTER(:BF0001,"T233484"."AIRPORT"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"T233484"."AIRPORT"))
23 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"T233820"."SC_CODE"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"T233820"."SC_CODE"))
31 - access("T233732"."CAL_MONTH"="T233937"."MONTH" AND "T233732"."CAL_QTR"="T233937"."QUARTER" AND "T233732"."CAL_YEAR"="T233937"."YEAR")
35 - inmemory("T233732"."CAL_MONTH">=6 AND "T233732"."CAL_MONTH"<=12)
filter("T233732"."CAL_MONTH">=6 AND "T233732"."CAL_MONTH"<=12)
36 - access("T233484"."AIRPORT"="T233937"."ORIGIN")
40 - access("T233484"."STCTY_FIPS"="T233820"."SC_CODE")
44 - inmemory("T233937"."MONTH">=6 AND "T233937"."MONTH"<=12)
filter("T233937"."MONTH">=6 AND "T233937"."MONTH"<=12)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 2 because of table property
80 rows selected.
Indeed, looking at the queries the BI Server is sending to the database it’s not too surprising the in-memory difference in this case wasn’t too dramatic. According to the docs (emphasis mine):
"Storing a database object in the IM column store can improve performance significantly for the following types of operations performed on the database object
The IM column store does not improve performance for the following types of operations:
- A query that scans a large number of rows and applies filters that use operators such as the following: =, <, >, and IN
- A query that selects a small number of columns from a table or materialized view with a large number of columns, such as a query that selects five columns from a table with 100 columns
- A query that joins a small table to a large table
- A query that aggregates data
- Queries with complex predicates
- Queries that select a large number of columns
- Queries that return a large number of rows
- Queries with multiple large table joins”
and our query certainly has complex predicates, returns a fair few rows, has large and lots of joins etc. Taking a more simple query that you’d likely write yourself if querying a data warehouse, you can see the in-memory table access being used again but a much simpler, an cheaper explain plan:
SQL> @inmem_explain.sql
SQL> set echo on
SQL> explain plan for
2 select /*+ INMEMORY */ sum(T233937.ACTUALELAPSEDTIME) as c1,
3 sum(T233937.WEATHERDELAY) as c2,
4 sum(T233937.SECURITYDELAY) as c3,
5 sum(T233937.NASDELAY) as c4,
6 sum(T233937.LATEAIRCRAFTDELAY) as c5,
7 sum(T233937.ARRDELAYMINUTES) as c6,
8 sum(T233937.CARRIERDELAY) as c7,
9 sum(nvl(casewhen T233937.CANCELLED < 1 then T233937.FLIGHTS end , 0)) as c8,
10 T233820.D_NAME as c9
11 from
12 BI_AIRLINES.OBIEE_GEO_AIRPORT_BRIDGE T233484 /* 10 GEO_AIRPORT_ORIGIN */ ,
13 BI_AIRLINES.OBIEE_COUNTY_HIER T233820 /* 11 COUNTY_HIER_ORIGIN */ ,
14 BI_AIRLINES.OBIEE_GEO_AIRPORT_BRIDGE T233497 /* 12 GEO_AIPORT_DEST */ ,
15 BI_AIRLINES.OBIEE_COUNTY_HIER T233831 /* 13 COUNTY_HIER_DEST */ ,
16 BI_AIRLINES.OBIEE_TIME_MTH_D T233732 /* 41 TIME MONTH */ ,
17 BI_AIRLINES.DISTANCE_GROUP_250 T233594 /* 19 DISTANCE_GROUP_250 */ ,
18 BI_AIRLINES.PERFORMANCE T233937 /* 00 PERFORMANCE FULL */
19 where ( T233484.AIRPORT = T233937.ORIGIN and T233484.STCTY_FIPS = T233820.SC_CODE and T233497.AIRPORT = T233937.DEST and T233497.STCTY_FIPS = T233831.SC_CODE and T233594.DESCRIPTION = '1000-1249 Miles' and T233594.CODE = T233937.DISTANCEGROUP and T233732.Cal_Month = T233937.MONTH and T233732.Cal_Qtr = T233937.QUARTER and T233732.Cal_Year = T233937.YEAR and T233831.R_NAME = 'Northeast Region' and T233732.Cal_Month between 6 and 12 and T233937.MONTH between 6 and 12 )
20 group by T233820.D_NAME
21 order by c9
22 /
Explained.
SQL> set lines 300
SQL> set pages 0
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3055743864
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1314 | 883 (13)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10006 | 9 | 1314 | 883 (13)| 00:00:01 | Q1,06 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 9 | 1314 | 883 (13)| 00:00:01 | Q1,06 | PCWP | |
| 4 | PX RECEIVE | | 9 | 1314 | 883 (13)| 00:00:01 | Q1,06 | PCWP | |
| 5 | PX SEND RANGE | :TQ10005 | 9 | 1314 | 883 (13)| 00:00:01 | Q1,05 | P->P | RANGE |
| 6 | HASH GROUP BY | | 9 | 1314 | 883 (13)| 00:00:01 | Q1,05 | PCWP | |
|* 7 | HASH JOIN | | 60775 | 8665K| 882 (13)| 00:00:01 | Q1,05 | PCWP | |
| 8 | PX RECEIVE | | 3221 | 99851 | 1 (0)| 00:00:01 | Q1,05 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 3221 | 99851 | 1 (0)| 00:00:01 | Q1,00 | S->P | BROADCAST |
| 10 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
| 11 | TABLE ACCESS INMEMORY FULL | OBIEE_COUNTY_HIER | 3221 | 99851 | 1 (0)| 00:00:01 | Q1,00 | SCWP | |
|* 12 | HASH JOIN | | 60775 | 6825K| 881 (13)| 00:00:01 | Q1,05 | PCWP | |
| 13 | PX RECEIVE | | 1787 | 17870 | 1 (0)| 00:00:01 | Q1,05 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 1787 | 17870 | 1 (0)| 00:00:01 | Q1,01 | S->P | BROADCAST |
| 15 | PX SELECTOR | | | | | | Q1,01 | SCWC | |
| 16 | TABLE ACCESS INMEMORY FULL | OBIEE_GEO_AIRPORT_BRIDGE | 1787 | 17870 | 1 (0)| 00:00:01 | Q1,01 | SCWP | |
|* 17 | HASH JOIN | | 60775 | 6231K| 880 (13)| 00:00:01 | Q1,05 | PCWP | |
| 18 | PX RECEIVE | | 161 | 1610 | 1 (0)| 00:00:01 | Q1,05 | PCWP | |
| 19 | PX SEND BROADCAST | :TQ10002 | 161 | 1610 | 1 (0)| 00:00:01 | Q1,02 | S->P | BROADCAST |
| 20 | PX SELECTOR | | | | | | Q1,02 | SCWC | |
|* 21 | TABLE ACCESS INMEMORY FULL | OBIEE_TIME_MTH_D | 161 | 1610 | 1 (0)| 00:00:01 | Q1,02 | SCWP | |
|* 22 | HASH JOIN | | 60964 | 5655K| 879 (13)| 00:00:01 | Q1,05 | PCWP | |
| 23 | JOIN FILTER CREATE | :BF0000 | 1 | 19 | 1 (0)| 00:00:01 | Q1,05 | PCWP | |
| 24 | PX RECEIVE | | 1 | 19 | 1 (0)| 00:00:01 | Q1,05 | PCWP | |
| 25 | PX SEND BROADCAST | :TQ10003 | 1 | 19 | 1 (0)| 00:00:01 | Q1,03 | S->P | BROADCAST |
| 26 | PX SELECTOR | | | | | | Q1,03 | SCWC | |
|* 27 | TABLE ACCESS INMEMORY FULL | DISTANCE_GROUP_250 | 1 | 19 | 1 (0)| 00:00:01 | Q1,03 | SCWP | |
|* 28 | HASH JOIN | | 670K| 48M| 878 (13)| 00:00:01 | Q1,05 | PCWP | |
| 29 | JOIN FILTER CREATE | :BF0001 | 318 | 9540 | 2 (0)| 00:00:01 | Q1,05 | PCWP | |
| 30 | PX RECEIVE | | 318 | 9540 | 2 (0)| 00:00:01 | Q1,05 | PCWP | |
| 31 | PX SEND BROADCAST | :TQ10004 | 318 | 9540 | 2 (0)| 00:00:01 | Q1,04 | S->P | BROADCAST |
| 32 | PX SELECTOR | | | | | | Q1,04 | SCWC | |
|* 33 | HASH JOIN | | 318 | 9540 | 2 (0)| 00:00:01 | Q1,04 | SCWC | |
| 34 | JOIN FILTER CREATE | :BF0002 | 217 | 4340 | 1 (0)| 00:00:01 | Q1,04 | PCWP | |
|* 35 | TABLE ACCESS INMEMORY FULL| OBIEE_COUNTY_HIER | 217 | 4340 | 1 (0)| 00:00:01 | Q1,04 | SCWP | |
| 36 | JOIN FILTER USE | :BF0002 | 1787 | 17870 | 1 (0)| 00:00:01 | Q1,04 | PCWP | |
|* 37 | TABLE ACCESS INMEMORY FULL| OBIEE_GEO_AIRPORT_BRIDGE | 1787 | 17870 | 1 (0)| 00:00:01 | Q1,04 | SCWP | |
| 38 | JOIN FILTER USE | :BF0000 | 3773K| 165M| 874 (13)| 00:00:01 | Q1,05 | PCWP | |
| 39 | JOIN FILTER USE | :BF0001 | 3773K| 165M| 874 (13)| 00:00:01 | Q1,05 | PCWP | |
| 40 | PX BLOCK ITERATOR | | 3773K| 165M| 874 (13)| 00:00:01 | Q1,05 | PCWC | |
|* 41 | TABLE ACCESS INMEMORY FULL | PERFORMANCE | 3773K| 165M| 874 (13)| 00:00:01 | Q1,05 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T233484"."STCTY_FIPS"="T233820"."SC_CODE")
12 - access("T233484"."AIRPORT"="T233937"."ORIGIN")
17 - access("T233732"."CAL_MONTH"="T233937"."MONTH" AND "T233732"."CAL_QTR"="T233937"."QUARTER" AND "T233732"."CAL_YEAR"="T233937"."YEAR")
21 - inmemory("T233732"."CAL_MONTH">=6 AND "T233732"."CAL_MONTH"<=12)
filter("T233732"."CAL_MONTH">=6 AND "T233732"."CAL_MONTH"<=12)
22 - access("T233594"."CODE"="T233937"."DISTANCEGROUP")
27 - inmemory("T233594"."DESCRIPTION"='1000-1249 Miles')
filter("T233594"."DESCRIPTION"='1000-1249 Miles')
28 - access("T233497"."AIRPORT"="T233937"."DEST")
33 - access("T233497"."STCTY_FIPS"="T233831"."SC_CODE")
35 - inmemory("T233831"."R_NAME"='Northeast Region')
filter("T233831"."R_NAME"='Northeast Region')
37 - inmemory(SYS_OP_BLOOM_FILTER(:BF0002,"T233497"."STCTY_FIPS"))
filter(SYS_OP_BLOOM_FILTER(:BF0002,"T233497"."STCTY_FIPS"))
41 - inmemory("T233937"."MONTH">=6 AND "T233937"."MONTH"<=12 AND SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0001,"T233937"."DEST"),SYS
_OP_BLOOM_FILTER(:BF0000,"T233937"."DISTANCEGROUP")))
filter("T233937"."MONTH">=6 AND "T233937"."MONTH"<=12 AND SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0001,"T233937"."DEST"),SYS_O
P_BLOOM_FILTER(:BF0000,"T233937"."DISTANCEGROUP")))
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 2 because of table property
75 rows selected.
If I then turn-off the in-memory feature and regenerate the execution plan, you can see without in-memory the plan is around 5x as expensive:
SQL> alter system set INMEMORY_SIZE = 0 scope = spfile;
System altered.
SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SQL> rollback;
Rollback complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 7398752256 bytes
Fixed Size 2941528 bytes
Variable Size 1056968104 bytes
Database Buffers 6325010432 bytes
Redo Buffers 13832192 bytes
Database mounted.
Database opened.
SQL> @noinmem_explain.sql
Explained.
SQL> set lines 300
SQL> set pages 0
SQL> select * from table(dbms_xplan.display);
Plan hash value: 2990499928
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1341 | 4086 (1)| 00:00:01 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_275335 | | | | | | | |
|* 3 | HASH JOIN | | 318 | 9540 | 22 (0)| 00:00:01 | | | |
|* 4 | TABLE ACCESS FULL | OBIEE_COUNTY_HIER | 217 | 4340 | 13 (0)| 00:00:01 | | | |
| 5 | TABLE ACCESS FULL | OBIEE_GEO_AIRPORT_BRIDGE | 1787 | 17870 | 9 (0)| 00:00:01 | | | |
| 6 | PX COORDINATOR | | | | | | | | |
| 7 | PX SEND QC (ORDER) | :TQ10008 | 9 | 1341 | 4086 (1)| 00:00:01 | Q1,08 | P->S | QC (ORDER) |
| 8 | SORT GROUP BY | | 9 | 1341 | 4086 (1)| 00:00:01 | Q1,08 | PCWP | |
| 9 | PX RECEIVE | | 9 | 1341 | 4086 (1)| 00:00:01 | Q1,08 | PCWP | |
| 10 | PX SEND RANGE | :TQ10007 | 9 | 1341 | 4086 (1)| 00:00:01 | Q1,07 | P->P | RANGE |
| 11 | HASH GROUP BY | | 9 | 1341 | 4086 (1)| 00:00:01 | Q1,07 | PCWP | |
|* 12 | HASH JOIN | | 281 | 41869 | 4085 (1)| 00:00:01 | Q1,07 | PCWP | |
| 13 | PX RECEIVE | | 281 | 33158 | 4072 (1)| 00:00:01 | Q1,07 | PCWP | |
| 14 | PX SEND HYBRID HASH | :TQ10005 | 281 | 33158 | 4072 (1)| 00:00:01 | Q1,05 | P->P | HYBRID HASH|
| 15 | STATISTICS COLLECTOR | | | | | | Q1,05 | PCWC | |
|* 16 | HASH JOIN BUFFERED | | 281 | 33158 | 4072 (1)| 00:00:01 | Q1,05 | PCWP | |
| 17 | VIEW | VW_GBC_29 | 281 | 30348 | 4063 (1)| 00:00:01 | Q1,05 | PCWP | |
| 18 | HASH GROUP BY | | 281 | 79523 | 4041 (1)| 00:00:01 | Q1,05 | PCWP | |
| 19 | PX RECEIVE | | 281 | 79523 | 4041 (1)| 00:00:01 | Q1,05 | PCWP | |
| 20 | PX SEND HASH | :TQ10003 | 281 | 79523 | 4041 (1)| 00:00:01 | Q1,03 | P->P | HASH |
| 21 | HASH GROUP BY | | 281 | 79523 | 4041 (1)| 00:00:01 | Q1,03 | PCWP | |
|* 22 | HASH JOIN | | 60853 | 16M| 4039 (1)| 00:00:01 | Q1,03 | PCWP | |
| 23 | BUFFER SORT | | | | | | Q1,03 | PCWC | |
| 24 | PX RECEIVE | | 318 | 1272 | 2 (0)| 00:00:01 | Q1,03 | PCWP | |
| 25 | PX SEND BROADCAST | :TQ10000 | 318 | 1272 | 2 (0)| 00:00:01 | | S->P | BROADCAST |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_275335 | 318 | 1272 | 2 (0)| 00:00:01 | | | |
|* 27 | HASH JOIN | | 60853 | 16M| 4037 (1)| 00:00:01 | Q1,03 | PCWP | |
| 28 | PX RECEIVE | | 160 | 4640 | 4 (0)| 00:00:01 | Q1,03 | PCWP | |
| 29 | PX SEND BROADCAST | :TQ10002 | 160 | 4640 | 4 (0)| 00:00:01 | Q1,02 | S->P | BROADCAST |
| 30 | PX SELECTOR | | | | | | Q1,02 | SCWC | |
| 31 | MERGE JOIN CARTESIAN | | 160 | 4640 | 4 (0)| 00:00:01 | Q1,02 | SCWC | |
|* 32 | VIEW | index$_join$_006 | 1 | 19 | 2 (0)| 00:00:01 | Q1,02 | SCWC | |
|* 33 | HASH JOIN | | | | | | Q1,02 | SCWC | |
| 34 | BITMAP CONVERSION TO ROWIDS | | 1 | 19 | 1 (0)| 00:00:01 | Q1,02 | SCWC | |
|* 35 | BITMAP INDEX SINGLE VALUE | M_INDEX32 | | | | | Q1,02 | SCWP | |
| 36 | BITMAP CONVERSION TO ROWIDS | | 1 | 19 | 1 (0)| 00:00:01 | Q1,02 | SCWC | |
| 37 | BITMAP INDEX FULL SCAN | INDEX4 | | | | | Q1,02 | SCWP | |
| 38 | BUFFER SORT | | 161 | 1610 | 2 (0)| 00:00:01 | Q1,02 | SCWC | |
| 39 | BITMAP CONVERSION TO ROWIDS | | 161 | 1610 | 2 (0)| 00:00:01 | Q1,02 | SCWC | |
|* 40 | BITMAP INDEX FAST FULL SCAN | M_INDEX28 | | | | | Q1,02 | SCWP | |
|* 41 | VIEW | VW_ST_167D3604 | 61043 | 14M| 4033 (1)| 00:00:01 | Q1,03 | PCWP | |
| 42 | NESTED LOOPS | | 61043 | 4768K| 4029 (1)| 00:00:01 | Q1,03 | PCWP | |
| 43 | BUFFER SORT | | | | | | Q1,03 | PCWC | |
| 44 | PX RECEIVE | | | | | | Q1,03 | PCWP | |
| 45 | PX SEND HASH (BLOCK ADDRESS) | :TQ10001 | | | | | | S->P | HASH (BLOCK|
| 46 | BITMAP CONVERSION TO ROWIDS | | 61042 | 1311K| 365 (1)| 00:00:01 | | | |
| 47 | BITMAP AND | | | | | | | | |
| 48 | BITMAP MERGE | | | | | | | | |
| 49 | BITMAP KEY ITERATION | | | | | | | | |
|* 50 | VIEW | index$_join$_255 | 1 | 19 | 2 (0)| 00:00:01 | | | |
|* 51 | HASH JOIN | | | | | | | | |
| 52 | BITMAP CONVERSION TO ROWIDS| | 1 | 19 | 1 (0)| 00:00:01 | | | |
|* 53 | BITMAP INDEX SINGLE VALUE | M_INDEX32 | | | | | | | |
| 54 | BITMAP CONVERSION TO ROWIDS| | 1 | 19 | 1 (0)| 00:00:01 | | | |
| 55 | BITMAP INDEX FULL SCAN | INDEX4 | | | | | | | |
|* 56 | BITMAP INDEX RANGE SCAN | PERF_DISTANCEGRP | | | | | | | |
| 57 | BITMAP MERGE | | | | | | | | |
| 58 | BITMAP KEY ITERATION | | | | | | | | |
| 59 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_275335 | 318 | 1272 | 2 (0)| 00:00:01 | | | |
|* 60 | BITMAP INDEX RANGE SCAN | PERF_DEST | | | | | | | |
| 61 | BITMAP MERGE | | | | | | | | |
|* 62 | BITMAP INDEX RANGE SCAN | PERF_MONTH | | | | | | | |
| 63 | TABLE ACCESS BY USER ROWID | PERFORMANCE | 1 | 58 | 3669 (1)| 00:00:01 | Q1,03 | PCWP | |
| 64 | PX RECEIVE | | 1787 | 17870 | 9 (0)| 00:00:01 | Q1,05 | PCWP | |
| 65 | PX SEND BROADCAST | :TQ10004 | 1787 | 17870 | 9 (0)| 00:00:01 | Q1,04 | S->P | BROADCAST |
| 66 | PX SELECTOR | | | | | | Q1,04 | SCWC | |
| 67 | TABLE ACCESS FULL | OBIEE_GEO_AIRPORT_BRIDGE | 1787 | 17870 | 9 (0)| 00:00:01 | Q1,04 | SCWP | |
| 68 | PX RECEIVE | | 3221 | 99851 | 13 (0)| 00:00:01 | Q1,07 | PCWP | |
| 69 | PX SEND HYBRID HASH | :TQ10006 | 3221 | 99851 | 13 (0)| 00:00:01 | Q1,06 | S->P | HYBRID HASH|
| 70 | PX SELECTOR | | | | | | Q1,06 | SCWC | |
| 71 | TABLE ACCESS FULL | OBIEE_COUNTY_HIER | 3221 | 99851 | 13 (0)| 00:00:01 | Q1,06 | SCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T233497"."STCTY_FIPS"="T233831"."SC_CODE")
4 - filter("T233831"."R_NAME"='Northeast Region')
12 - access("T233484"."STCTY_FIPS"="T233820"."SC_CODE")
16 - access("T233484"."AIRPORT"="ITEM_1")
22 - access("C0"="ITEM_1")
27 - access("T233732"."CAL_YEAR"="ITEM_5" AND "T233732"."CAL_QTR"="ITEM_4" AND "T233732"."CAL_MONTH"="ITEM_3" AND "T233594"."CODE"="ITEM_2")
32 - filter("T233594"."DESCRIPTION"='1000-1249 Miles')
33 - access(ROWID=ROWID)
35 - access("T233594"."DESCRIPTION"='1000-1249 Miles')
40 - filter("T233732"."CAL_MONTH">=6 AND "T233732"."CAL_MONTH"<=12)
41 - filter("ITEM_3"<=12 AND "ITEM_3">=6)
50 - filter("T233594"."DESCRIPTION"='1000-1249 Miles')
51 - access(ROWID=ROWID)
53 - access("T233594"."DESCRIPTION"='1000-1249 Miles')
56 - access("T233937"."DISTANCEGROUP"="T233594"."CODE")
60 - access("T233937"."DEST"="C0")
62 - access("T233937"."MONTH">=6 AND "T233937"."MONTH"<=12)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 2 because of table property
- star transformation used for this statement
105 rows selected.
Running the actual queries in this case gives me a wall-time of around 4 seconds for the in-memory version, and 14 seconds when in-memory query is disabled ... but the response time isn’t anywhere near the initial run where we had data stored row-wise but with aggregate tables, so let’s finish-off the testing by putting the aggregate table in-memory too, and see if that makes a difference.
And of course, it absolutely flew:
So to conclude from my look at the Oracle Database 12c In-Memory option with OBIEE11g v406 SampleApp, I’d say the following based on my initial tests:
- For BI-type reporting where you’re typically summarising lots of data, the in-memory option doesn’t remove the need for aggregate tables - you’ll still benefit significantly from having them, in my observation
- Where the in-memory option does benefit you is when you’re querying the detail-level data - it helps with aggregation but it’s main strength is fast filtering against subsets of columns
- Some of the more complex SQL queries issued by OBIEE’s BI Server, for example when creating lots of subtotals and totals against a dataset, reduce the effectiveness of the in-memory option - you’ll get the biggest speed improvement, at least at the moment, with queries with simpler predicates and not so complex joins
There might be more to the aggregation story in the end, though. Looking at the Oracle Database 12c In-Memory Option Technical White Paper, the in-memory option should in-fact help with aggregation through a new optimiser transformation called “vector group by”, a transformation that’s likened to a star transformation that uses CPU-efficient algorithms and a multi-dimensional array created on-the-fly in the PGA called an “in-memory accumulator”.
In fact, what we’ve heard is that many of the old Oracle OLAP team have moved over to the in-memory option team and were responsible for this feature, so I’ll be taking a closer look at in-memory aggregation in this new feature over the next few months. In my examples though, I didn’t see any examples of vector group by in the query execution plans, so I’m assuming either conditions weren’t right for it, or like star transformations there's some combination of setting and query factors that need to be in place before it’ll appear in the execution plan (and queries presumably run that much faster).
For now though - that’s my first run-through of the 12c In-Memory Option in the context of a typical BI workload. Check back later in the year for more in-memory option postings, including hopefully something more on the in-memory aggregation feature.