Oracle 12.1 provides an interesting performance enhancement, the in-memory column store, that can increase performance by reducing physical I/O load by making tables memory-resident (to simplify the description somewhat). Along with the in-memory access to table data Oracle has introduced a new optimization called “Vector Transformation, including Vector Optimization” for in-memory operations. Even when this new optimization isn’t in use there are new code paths to take advantage of this technology. As a result execution plans can change; let’s look at an example originally provided by Jonathan Lewis that illustrates this.
Two tables are created, neither one remarkable, an index is created on each table and extended statistics are generated:
SQL>
SQL> create table t1
2 nologging
3 as
4 with generator as (
5 select --+ materialize
6 rownum id
7 from dual
8 connect by
9 level <= 1e4
10 )
11 select
12 rownum id,
13 trunc((rownum - 1)/100) n1,
14 trunc((rownum - 1)/100) n2,
15 trunc(dbms_random.value(1,1e4)) rand,
16 cast(lpad(rownum,10,'0') as varchar2(10)) v1,
17 cast(lpad('x',100,'x') as varchar2(100)) padding
18 from
19 generator v1
20 ;
SQL>
SQL> create table t2
2 nologging
3 as
4 with generator as (
5 select --+ materialize
6 rownum id
7 from dual
8 connect by
9 level <= 1e4
10 )
11 select
12 rownum id,
13 trunc((rownum - 1)/100) n1,
14 trunc((rownum - 1)/100) n2,
15 trunc(dbms_random.value(1,1e4)) rand,
16 cast(lpad(rownum,10,'0') as varchar2(10)) v1,
17 cast(lpad('x',100,'x') as varchar2(100)) padding
18 from
19 generator v1,
20 generator v2
21 where
22 rownum <= 1e6
23 ;
SQL>
SQL>
SQL> create index t1_n1 on t1(n1) nologging;
SQL> create index t2_rand on t2(rand) nologging;
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname =>'T1',
5 method_opt => 'for columns (n1,n2) size 1'
6 );
7 end;
8 /
SQL>
Executing the following query and displaying the execution plan shows no surprises since neither table is in-memory:
SQL>
SQL> select
2 /*+
3 qb_name(main)
4 */
5 count(*)
6 from (
7 select
8 /*+ qb_name(inline) */
9 distinct t1.v1, t2.v1
10 from
11 t1,t2
12 where
13 t1.n1 = 50
14 and t1.n2 = 50
15 and t2.rand = t1.id
16 )
17 ;
9912
SQL>
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID a7gb3ncf922zz, child number 0
-------------------------------------
select /*+ qb_name(main) */
count(*) from ( select /*+ qb_name(inline)
*/ distinct t1.v1, t2.v1 from
t1,t2 where t1.n1 = 50 and t1.n2 =
50 and t2.rand = t1.id )
Plan hash value: 1718706536
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5127 (100)| |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 10001 | | 5127 (1)| 00:00:01 |
| 3 | HASH UNIQUE | | 10001 | 351K| 5127 (1)| 00:00:01 |
|* 4 | HASH JOIN | | 10001 | 351K| 5125 (1)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 100 | 2100 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_N1 | 100 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T2 | 1000K| 14M| 5113 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."RAND"="T1"."ID")
5 - filter("T1"."N2"=50)
6 - access("T1"."N1"=50)
SQL>
Having the extended statistics allowed the optimizer to estimate the correct cardinality between columns n1 and n2. Now things will get a bit interesting; table T2 is put in-memory:
SQL>
SQL> alter table t2 inmemory;
SQL>
The same query is executed again and the plan changes, even though the new vector transformation has not been applied:
SQL>
SQL> select
2 /*+
3 qb_name(main)
4 */
5 count(*)
6 from (
7 select
8 /*+ qb_name(inline) */
9 distinct t1.v1, t2.v1
10 from
11 t1,t2
12 where
13 t1.n1 = 50
14 and t1.n2 = 50
15 and t2.rand = t1.id
16 )
17 ;
9912
SQL>
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID a7gb3ncf922zz, child number 0
-------------------------------------
select /*+ qb_name(main) */
count(*) from ( select /*+ qb_name(inline)
*/ distinct t1.v1, t2.v1 from
t1,t2 where t1.n1 = 50 and t1.n2 =
50 and t2.rand = t1.id )
Plan hash value: 106371239
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 223 (100)| |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 10001 | | 223 (15)| 00:00:01 |
| 3 | HASH UNIQUE | | 10001 | 351K| 223 (15)| 00:00:01 |
|* 4 | HASH JOIN | | 10001 | 351K| 222 (14)| 00:00:01 |
| 5 | JOIN FILTER CREATE | :BF0000 | 100 | 2100 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 100 | 2100 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | T1_N1 | 100 | | 1 (0)| 00:00:01 |
| 8 | JOIN FILTER USE | :BF0000 | 1000K| 14M| 209 (11)| 00:00:01 |
|* 9 | TABLE ACCESS INMEMORY FULL | T2 | 1000K| 14M| 209 (11)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."RAND"="T1"."ID")
6 - filter("T1"."N2"=50)
7 - access("T1"."N1"=50)
9 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."RAND"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."RAND"))
SQL>
Notice that the cost of the tablescan was reduced considerably, from 5113 to 209; that isn’t the most interesting part of the plan, however, as a serial Bloom filter is used for the hash join, because of the in-memory code path. Normally a Bloom filter would not be effective; with the in-memory option a Bloom filter can use enhancements such as multiple, simultaneous probe access into table t2 (in this example) in addition to the benefits of avoiding disk access, disk buffer activity and row-by-row data retrieval. All of those avoidances reduce CPU time considerably when compared to a ‘regular’ table that is not in-memory.
The in-memory column store is a great addition to an already powerful database, made even better by new optimizer transformations that speed data access by providing execution paths not available for tables accessed from disk. It would be worth the time to ‘experiment’ with the in-memory option to see what benefits it might provide in your environment.
