Oracle database 12c in-memory – part1 (unwanted behavior)

In my first post about the amazing in-memory feature (others are coming) I will talk about some “unwanted behavior” with the in-memory feature when choosing the best execution plan and the table was not yet populated in the in-memory column store.

Here is a quick and simple example :

My test database is of course 12.1.0.2

SQL> show parameter inm NAME                                 TYPE       VALUE ———————————— ———– —————————— _inmemory_enable_stat_alert         boolean     TRUE _inmemory_log_level                 integer     1 inmemory_clause_default             string inmemory_force                       string     DEFAULT inmemory_max_populate_servers       integer     1 inmemory_query                       string     ENABLE inmemory_size                       big integer 112M inmemory_trickle_repopulate_servers_ integer     1 percent optimizer_inmemory_aware            boolean       TRUE

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS               CON_ID ————————– ———– ———- ————————– ———- 1MB POOL                     82837504   82837504 OUT OF MEMORY                       0 64KB POOL                     16777216     458752 DONE                               0

I will begin by creating a sample table and collect stats :

create table TEST_INMEMORY as select * from dba_objects;

Check the execution plan for the flowing query.

select count(OBJECT_NAME) from TEST_INMEMORY; Plan d’exÚcution ———————————————————- Plan hash value: 1029766195 ———————————————————————————— | Id | Operation                   | Name                        | Rows | Bytes | Cost (%CPU)| Time     | ———————————————————————————— |   0 | SELECT STATEMENT   |                                   |       1   |     25   |   863     (1)| 00:00:01 | |   1 |   SORT AGGREGATE   |                                 |       1    |   25    |            |           | |   2 |     TABLE ACCESS FULL | TEST_INMEMORY |   183K| 4472K|     863   (1)| 00:00:01 | ————————————————————————————

Create an index on “object_name” and then check the execution plan for the flowing query.

select count(OBJECT_NAME) from TEST_INMEMORY;Plan d’exÚcution ———————————————————- Plan hash value: 71185874 ——————————————————————————————- | Id | Operation                       | Name                             | Rows   | Bytes | Cost (%CPU)| Time       | ——————————————————————————————- |   0 | SELECT STATEMENT     |                                         |       1    |   25   |   251   (1)| 00:00:01 | |   1 |   SORT AGGREGATE       |                                         |       1    |   25     |                  |         | |   2 |     INDEX FAST FULL SCAN| TEST_INMEMORY_IDX |   183K|   4472K|   251   (1)| 00:00:01 | ——————————————————————————————-

>So using index fast full scan is cheaper than a full table scan.

Suppose now that we mark the table for in-memory.

alter table TEST_INMEMORY inmemory;

And now execute our query :

Plan d’exÚcution ———————————————————- Plan hash value: 1029766195 ——————————————————————————————— | Id | Operation                                      | Name                     | Rows   | Bytes | Cost (%CPU)| Time       | ——————————————————————————————— |   0 | SELECT STATEMENT                       |                               |       1 |   25   |   44     (3) | 00:00:01 | |   1 |   SORT AGGREGATE                        |                               |       1 |     25    |                 |                | |   2 |     TABLE ACCESS INMEMORY FULL| TEST_INMEMORY |   183K|   4472K|   44   (3)| 00:00:01 | ———————————————————————————————

let’s check session stats : (I used Tanel Poder snapper for this)

—————————————————————————————————— SID, USERNAME , TYPE, STATISTIC                                                   ,         DELTA, —————————————————————————————————— 60, HATEM     , STAT, Requests to/from client                                   ,             3, 60, HATEM     , STAT, opened cursors cumulative                                ,             8, 60, HATEM     , STAT, user calls                                                 ,             4, 60, HATEM     , STAT, recursive calls                                             ,             9, 60, HATEM     , STAT, session logical reads                                     ,         3114, . . . 60, HATEM     , STAT, consistent gets                                          ,        3114, 60, HATEM     , STAT, consistent gets from cache                               ,        3114, 60, HATEM     , STAT, consistent gets pin                                       ,         3110, 60, HATEM       , STAT, consistent gets pin (fastpath)                           ,         3110, 60, HATEM     , STAT, consistent gets examination                               ,             4, 60, HATEM     , STAT, consistent gets examination (fastpath)                   ,             4, 60, HATEM     , STAT, logical read bytes from cache                               ,     25509888, 60, HATEM     , STAT, calls to kcmgcs                                             ,           14, 60, HATEM     , STAT, calls to get snapshot scn: kcmgss                         ,             8, 60, HATEM     , STAT, no work – consistent read gets                           ,         3098, 60, HATEM     , STAT, table scans (long tables)                                ,            1, 60, HATEM     , STAT, table scan rows gotten                                     ,       183255, 60, HATEM     , STAT, table scan disk non-IMC rows gotten                     ,       183255, 60, HATEM     , STAT, table scan blocks gotten                                   ,         3094, 60, HATEM     , STAT, index scans kdiixs1                                       ,             6, 60, HATEM     , STAT, IM populate segments requested                       ,             1, 60, HATEM     , STAT, IM scan segments disk                                     ,             1, 60, HATEM     , STAT, session cursor cache hits                                 ,             7, . . . 60, HATEM     , TIME, DB time                                                     ,         38804, 60, HATEM     , WAIT, SQL*Net message to client                                ,             7, 60, HATEM     , WAIT, SQL*Net message from client                               ,       5059941,

As we expect on the first execution the table columns are not yet populated in the column store so we will trigger it’s population “IM populate segments requested” . But wait we are doing a full table scan of the table(look at the statistics “table scans (long tables)” ” table scan rows gotten” etc ).So even when we know that using the index is faster we are doing a full table scan. Imagine also that you’re in-memory store is full (like in my case) so subsequent execution also will do full table scan if the table is still not populated.

The optimizer will choose the best execution plan but cannot know in advance if the table is populated or not. (There is no such info in optimizer trace)

UPDATE 18/11/2015 : After investigation the problem appeared to be related to “IM Quotient” statistics for more info check this blog post

===================================== Access path analysis for TEST_INMEMORY *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for TEST_INMEMORY[TEST_INMEMORY] SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE Table: TEST_INMEMORY Alias: TEST_INMEMORY Card: Original: 183187.000000 Rounded: 183187 Computed: 183187.000000 Non Adjusted: 183187.000000 Scan IO Cost (Disk) =   0.000000 Scan CPU Cost (Disk) =   0.000000 Scan IO Cost (IMC)   =   31.760000 (read invalid rows) + 0.500000 (read extent map) =   32.260000 Scan CPU Cost (IMC) =   45000.000000 (overhead) + 10000.000000 (traverse IMCU) + 0.000000 (min-max eval) (= 200.000000 (min-max eval per cu) * 0 (#min-max CUs)) + 0.000000 (decompression) (= 0.000000 (per IMCU) * 1 (#IMCUs) * 1.000000 (prune ratio)) + 0.000000 (pred eval) (= 0.000000 (per row) * 183187 (#IMCrows) * 1.000000 (prune ratio)) + 18318700.000000 (row stitch) (= 100.000000 (per col) * 1 (#cols) * 183187 (#IMCUs) * 1.000000 (prune ratio)) + 16486830.000000 (scan journal) (= 3600.000000 (per row) * 4580 (#journal rows)) =   34860530.000000 Total Scan IO Cost =     0.000000 (scan (Disk)) + 32.260000 (scan (IMC)) =   32.260000 Total Scan CPU Cost =   0.000000 (scan (Disk)) + 34860530.000000 (scan (IMC)) =   34860530.000000 Access Path: TableScan Cost: 33.137930   Resp: 33.137930 Degree: 0 Cost_io: 32.260000 Cost_cpu: 34860530 Resp_io: 32.260000 Resp_cpu: 34860530 Access Path: index (index (FFS)) Index: TEST_INMEMORY_IDX resc_io: 250.000000 resc_cpu: 28498558 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 250.717710   Resp: 250.717710 Degree: 1 Cost_io: 250.000000 Cost_cpu: 28498558 Resp_io: 250.000000 Resp_cpu: 28498558 ****** Costing Index TEST_INMEMORY_IDX Access Path: index (FullScan) Index: TEST_INMEMORY_IDX resc_io: 917.000000 resc_cpu: 43167760 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 Cost: 918.087140 Resp: 918.087140 Degree: 1 Best:: AccessPath: TableScan          Cost: 33.137930   Degree: 1 Resp: 33.137930 Card: 183187.000000 Bytes: 0.000000

I think that a feature like adaptive plan can be of use in these case. If the table is not populated then go for the best alternative plan. May be in the next patchset 🙂 .