October | 2014 | ExaGridDba

By: Brian Fitzgerald

This article compares response time for Oracle Database 12.1.0.2 in memory database compares with response time for a bitmap index.

The configuration for this instance is:

[code language=”text”]

*.audit_file_dest=’/u01/app/oracle/admin/gp12102/adump’
*.audit_trail=’DB’
*.compatible=’12.1.0.2.0′
*.control_files=’+DATARDBMS10/GP12102/CONTROLFILE/current.300.860076553′,’+FRARDBMS10/GP12102/CONTROLFILE/current.272.860076555′
*.db_block_size=8192
*.db_create_file_dest=’+DATARDBMS10′
*.db_domain=”
*.db_name=’gp12102′
*.db_recovery_file_dest=’+FRARDBMS10′
*.db_recovery_file_dest_size=4560M
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=gp12102XDB)’
*.inmemory_size=9961472000
*.local_listener=’LISTENER_GP12102′
*.memory_max_target=0
*.memory_target=0
*.open_cursors=300
*.pga_aggregate_limit=8589934592
*.pga_aggregate_target=4294967296
*.processes=300
*.recyclebin=’OFF’
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=11744051200
*.sga_target=11744051200
*.undo_tablespace=’UNDOTBS1′
*.use_large_pages=’ONLY’

[/code]

First, let’s create a table with a billion rows

[code language=”sql”]
create table tmod (
n number,
m2 number,
m3 number,
m5 number,
m7 number,
m11 number,
m13 number,
m17 number,
m19 number,
m23 number,
m29 number
)
compress
inmemory;

declare
maxnumber number := 1000000000;
offset number;
mult number := 100000;
begin
for i in 0..maxnumber/mult-1
loop
offset := i * mult;
insert /*+ append */
into tmod (
n,
m2,
m3,
m5,
m7,
m11,
m13,
m17,
m19,
m23,
m29
)
select
level + offset n,
mod( level + offset, 2 ) n2,
mod( level + offset, 3 ) n3,
mod( level + offset, 5 ) n5,
mod( level + offset, 7 ) n7,
mod( level + offset, 11 ) n11,
mod( level + offset, 13 ) n13,
mod( level + offset, 17 ) n17,
mod( level + offset, 19 ) n19,
mod( level + offset, 23 ) n23,
mod( level + offset, 29 ) n29
from dual
connect by level <= mult
;

commit;
end loop;
end;
/

create bitmap index tmod_m2_idx on tmod( m2 );
create bitmap index tmod_m3_idx on tmod( m3 );
create bitmap index tmod_m5_idx on tmod( m5 );
create bitmap index tmod_m7_idx on tmod( m7 );
create bitmap index tmod_m11_idx on tmod( m11 );
create bitmap index tmod_m13_idx on tmod( m13 );
create bitmap index tmod_m17_idx on tmod( m17 );
create bitmap index tmod_m19_idx on tmod( m19 );
create bitmap index tmod_m23_idx on tmod( m23 );
create bitmap index tmod_m29_idx on tmod( m29 );

begin
dbms_stats.gather_table_stats
(
ownname => null,
tabname => ‘TMOD’
);
end;
/
alter table tmod inmemory priority critical;
alter table tmod inmemory memcompress for capacity high;
[/code]

[code language=”text”]
Table dropped.

Table created.

PL/SQL procedure successfully completed.

Index created.

PL/SQL procedure successfully completed.

Table altered.

Table altered.
[/code]

Check that the in-memory column store is populated.

[code language=”sql”]
set linesize 200
set trimspool on
column owner format a10
column segment_name format a30

select owner, segment_name,
bytes / 1024 / 1024 mb,
round ( ( bytes – bytes_not_populated ) / 1024 / 1024 ) mbpop,
round ( ( bytes – bytes_not_populated ) * 100 / bytes ) pctpop,
round ( ( inmemory_size ) / 1024 / 1024 ) mbinmem,
inmemory_priority pri,
inmemory_compression,
populate_status
from
gv$im_segments
;
[/code]

[code language=”text”]
OWNER SEGMENT_NAME MB MBPOP PCTPOP MBINMEM PRI INMEMORY_COMPRESS POPULATE_
———- —————————— ———- ———- ———- ———- ——– —————– ———
BRIAN TMOD 20608 20608 100 2731 NONE FOR CAPACITY HIGH COMPLETED
[/code]

100% populated!

Let’s try it!

[code language=”sql”]
select
min(n) + 1 euclid
from tmod
where m2 = 0
and m3 = 0
and m5 = 0
and m7 = 0
and m11 = 0
and m13 = 0
and m17 = 0
and m19 = 0
and m23 = 0
and m29 != 0
[/code]

[code language=”text”]
EUCLID
———-
223092871

1 row selected.

Elapsed: 00:00:04.25

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID gbbjq4yhfhf2j, child number 0
————————————-
select min(n) + 1 euclid from tmod where m2 = 0 and m3 = 0 and m5 = 0
and m7 = 0 and m11 = 0 and m13 = 0 and m17 = 0 and m19 = 0 and m23 = 0
and m29 != 0

Plan hash value: 1674668081

—————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————–
| 0 | SELECT STATEMENT | | | | 35849 (100)| |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TMOD | 4 | 148 | 35849 (1)| 00:00:02 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP AND | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | TMOD_M23_IDX | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | TMOD_M19_IDX | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | TMOD_M17_IDX | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | TMOD_M11_IDX | | | | |
—————————————————————————————————–

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2’)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "TMOD"@"SEL$1" AND(("TMOD"."M23") ("TMOD"."M19") ("TMOD"."M17")
("TMOD"."M11")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TMOD"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————

2 – filter(("M13"=0 AND "M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
5 – access("M23"=0)
6 – access("M19"=0)
7 – access("M17"=0)
8 – access("M11"=0)

47 rows selected.

[/code]

The optimizer chose a bitmap index access path. There were 24 such test runs using bitmap indexes. Average 4.21s

Now let’s try the in-memory column store.

[code language=”sql”]
alter index tmod_m2_idx invisible;
alter index tmod_m3_idx invisible;
alter index tmod_m5_idx invisible;
alter index tmod_m7_idx invisible;
alter index tmod_m11_idx invisible;
alter index tmod_m13_idx invisible;
alter index tmod_m17_idx invisible;
alter index tmod_m19_idx invisible;
alter index tmod_m23_idx invisible;
alter index tmod_m29_idx invisible;
[/code]

[code language=”text”]Index altered.

Index altered.

Index altered.
[/code]

[code language=”text”]

EUCLID
———-
223092871

1 row selected.

Elapsed: 00:00:04.71

Plan hash value: 17535997

————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | | | 68899 (100)| |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS INMEMORY FULL| TMOD | 4 | 148 | 68899 (59)| 00:00:03 |
————————————————————————————

Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2’)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TMOD"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————

2 – inmemory(("M23"=0 AND "M19"=0 AND "M17"=0 AND "M13"=0 AND "M11"=0
AND "M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
filter(("M23"=0 AND "M19"=0 AND "M17"=0 AND "M13"=0 AND "M11"=0 AND
"M7"=0 AND "M5"=0 AND "M3"=0 AND "M2"=0 AND "M29"<>0))
38 rows selected.

altered.
[/code]

There were 9 such in-memory tests. 4.68s average.

Bitmap index was 9% faster!

Bitmap wins!

Notes:

1. In-memory population took 2 hours! For faster repopulation, try other memcompress options or no memcompress.

2. Repopulation happens after instance restart or alter table, so plan carefully! For higher availability, try RAC!

3. The first bitmap index test run after instance restart read the index from disk, took much longer, and was not considered in the bitmap average.

4. In-memory test runs during the repopulation runs ran much longer and were not considered in the in-memory average.

5. Note that the optimizer did not choose to use indexes tmod_m2_idx, tmod_m3_idx, tmod_m5_idx, and tmod_m7_idx. The indexes are not selective enough!

[code language=”text”]

TABLE_NAME COLUMN_NAME NUM_NULLS NUM_DISTINCT
—————————— —————————— ———- ————
TMOD N 0 1000000000
TMOD M2 0 2
TMOD M3 0 3
TMOD M5 0 5
TMOD M7 0 7
TMOD M11 0 11
TMOD M13 0 13
TMOD M17 0 17
TMOD M19 0 19
TMOD M23 0 23
TMOD M29 0 29
[/code]

6. The purpose of this blog posting was to compare the in-memory column store to bitmap indexes, not to calculate Euclid numbers. There are simpler ways to calculate Euclid numbers. 🙂

ExaGridDba

Database Administration

Monthly Archives: October 2014

12c in-memory column store vs. bitmap index: bitmap wins!