In memory on Partition exchange loads

Here is a test case to demonstrate the behavior of Oracle in-memory database on partition exchanges loads. In short the functionality looks perfect, but the data dictionary v$im_segments doesn’t reflect the truth.

rajesh@ORA12C> select * from product_component_version;

PRODUCT                                  VERSION     STATUS

---------------------------------------- ----------- -----------------

NLSRTL                                   12.1.0.2.0  Production

Oracle Database 12c Enterprise Edition   12.1.0.2.0  64bit Production

PL/SQL                                   12.1.0.2.0  Production

TNS for 64-bit Windows:                  12.1.0.2.0  Production

4 rows selected.

rajesh@ORA12C>

rajesh@ORA12C> create table t1

  2  partition by list(x)

  3  ( partition p1 values(1) )

  4  as

  5  select a.*, 1 x

  6  from all_objects a;

Table created.

rajesh@ORA12C> alter table t1 inmemory;

Table altered.

rajesh@ORA12C> select count(*) from t1;

  COUNT(*)

----------

     90088

1 row selected.

rajesh@ORA12C>

rajesh@ORA12C> column owner format a10

rajesh@ORA12C> column segment_name format a10

rajesh@ORA12C> column partition_name format a10

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size

  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE

---------- ---------- ---------- -------------

RAJESH     T1         P1               4325376

1 row selected.

So far looks good. Now let us build a non-partitioned table ‘T2’ with In-memory options defined.

rajesh@ORA12C> create table t2

  2  inmemory as

  3  select a.*, 2 as x

  4  from all_objects a;

Table created.

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size

  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE

---------- ---------- ---------- -------------

RAJESH     T1         P1               4325376

1 row selected.

rajesh@ORA12C> select count(*) from t2;

  COUNT(*)

----------

     90089

1 row selected.

rajesh@ORA12C>

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size

  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE

---------- ---------- ---------- -------------

RAJESH     T2                          4325376

RAJESH     T1         P1               4325376

2 rows selected.

rajesh@ORA12C>

Now let us add a new partition P2 on table T1 and swap the segments with table T2.

rajesh@ORA12C> alter table t1 add partition p2 values(2);

Table altered.

rajesh@ORA12C>

rajesh@ORA12C> alter table t1

  2  exchange partition p2

  3  with table t2;

Table altered.

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size

  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE

---------- ---------- ---------- -------------

RAJESH     T2                          4325376

RAJESH     T1         P1               4325376

2 rows selected.

rajesh@ORA12C> select count(*) from t1;

  COUNT(*)

----------

    180177

1 row selected.

rajesh@ORA12C> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

rajesh@ORA12C>

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size

  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE

---------- ---------- ---------- -------------

RAJESH     T2                          4325376

RAJESH     T1         P1               4325376

2 rows selected.

The data in v$im_segments is not refresh with latest contents. (in this case it should be partition ‘P1’ and ‘P2’ in Table T1). Even after explicitly scanning partition P2, we don’t see the contents available.

rajesh@ORA12C> select count(*) from t1 partition(p2);

  COUNT(*)

----------

     90089

1 row selected.

rajesh@ORA12C> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size

  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE

---------- ---------- ---------- -------------

RAJESH     T2                          4325376

RAJESH     T1         P1               4325376

2 rows selected.

rajesh@ORA12C> select count(*) from t2;

  COUNT(*)

----------

         0

1 row selected.

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size

  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE

---------- ---------- ---------- -------------

RAJESH     T2                          4325376

RAJESH     T1         P1               4325376

2 rows selected.

rajesh@ORA12C>

With Auto trace in place we get this.

rajesh@ORA12C> set autotrace on explain statistics

rajesh@ORA12C> select count(*) from t1 partition(p2);

  COUNT(*)

----------

     90089

1 row selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 938476605

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |     1 |    27   (0)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE              |      |     1 |            |          |       |       |

|   2 |   PARTITION LIST SINGLE      |      | 90089 |    27   (0)| 00:00:01 |     2 |     2 |

|   3 |    TABLE ACCESS INMEMORY FULL| T1   | 90089 |    27   (0)| 00:00:01 |     2 |     2 |

---------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        544  bytes sent via SQL*Net to client

        552  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

rajesh@ORA12C> select /*+ no_inmemory */ count(*) from t1 where x = 2;

  COUNT(*)

----------

     90089

1 row selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 938476605

-----------------------------------------------------------------------------------------------

| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      |     1 |     3 |   434   (1)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE        |      |     1 |     3 |            |          |       |       |

|   2 |   PARTITION LIST SINGLE|      | 90089 |   263K|   434   (1)| 00:00:01 |     2 |     2 |

|   3 |    TABLE ACCESS FULL   | T1   | 90089 |   263K|   434   (1)| 00:00:01 |     2 |     2 |

-----------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1556  consistent gets

       1553  physical reads

          0  redo size

        544  bytes sent via SQL*Net to client

        552  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

rajesh@ORA12C>              

rajesh@ORA12C> set autotrace off

rajesh@ORA12C>

So this confirms that as part of partition exchange loads, partition P2 resides In-memory but v$im_segments doesn’t reflect them correctly.