Skip to Main Content
  • Questions
  • In-memory on Partition Exchange loads

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: September 02, 2015 - 12:04 am UTC

Last updated: September 14, 2015 - 4:16 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Tom,

Can you help me to understand why the In-memory option doesn't work with Exchange partition loads ?

https://community.oracle.com/thread/3691663?sr=inbox

and Connor said...


Firstly, thanks for providing a simple, easy to use test case. Makes our lives so much easier.

Using that test case reveals something interesting:

SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1
  2     partition by list(id)
  3    ( partition p1 values (1) )
  4    as
  5   select a.*, 1 id
  6   from all_objects a;

Table created.

SQL> alter table t1 inmemory;

Table altered.

SQL> select count(*) from t1;

  COUNT(*)
----------
     90160

1 row selected.

SQL> select segment_name,partition_name,segment_type,
  2  inmemory_size,bytes_not_populated,populate_status
  3  from v$im_segments ;

SEGMENT_NAME                             PARTITION_NAME                 SEGMENT_TYPE       INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_
---------------------------------------- ------------------------------ ------------------ ------------- ------------------- ---------
T1                                       P1                             TABLE PARTITION          4325376                   0 COMPLETED

SQL> create table t2
  2  INMEMORY
  3   as
  4  select a.*,2 id
  5  from all_objects a;

Table created.

SQL> select segment_name,partition_name,segment_type,
  2    inmemory_size,bytes_not_populated,populate_status
  3  from v$im_segments ;

SEGMENT_NAME                             PARTITION_NAME                 SEGMENT_TYPE       INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_
---------------------------------------- ------------------------------ ------------------ ------------- ------------------- ---------
T1                                       P1                             TABLE PARTITION          4325376                   0 COMPLETED

1 row selected.

SQL> select count(*) from t2;

  COUNT(*)
----------
     90161

1 row selected.

SQL> select segment_name,partition_name,segment_type,
  2    inmemory_size,bytes_not_populated,populate_status
  3  from v$im_segments ;

SEGMENT_NAME                             PARTITION_NAME                 SEGMENT_TYPE       INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_
---------------------------------------- ------------------------------ ------------------ ------------- ------------------- ---------
T1                                       P1                             TABLE PARTITION          4325376                   0 COMPLETED
T2                                                                      TABLE                    4325376                   0 COMPLETED

2 rows selected.

SQL> alter table t1 add partition p2 values(2);

Table altered.

SQL> alter table t1  exchange partition p2 with table t2 ;

Table altered.

SQL> select count(*) from t1;

  COUNT(*)
----------
    180321

1 row selected.

SQL> select count(*) from t1 partition (p2);

  COUNT(*)
----------
     90161

1 row selected.

SQL> select count(*) from t2;

  COUNT(*)
----------
         0

1 row selected.

SQL> select segment_name,partition_name,segment_type,
  2   inmemory_size,bytes_not_populated,populate_status
  3  from v$im_segments ;

SEGMENT_NAME                             PARTITION_NAME                 SEGMENT_TYPE       INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_
---------------------------------------- ------------------------------ ------------------ ------------- ------------------- ---------
T1                                       P1                             TABLE PARTITION          4325376                   0 COMPLETED
T2                                                                      TABLE                    4325376                   0 COMPLETED

2 rows selected.



So as per your experience, it would appear the freshly exchanged partition is no longer in-memory. However, if we look both

1) the execution plan for accessing partition p2

SQL> set autotrace traceonly explain
SQL> select count(*) from t1 partition (p2);

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      |      | 90161 |    27   (0)| 00:00:01 |     2 |     2 |
|   3 |    TABLE ACCESS INMEMORY FULL| T1   | 90161 |    27   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------


and also, the runtime IO when we access the partition

SQL> set autotrace on stat
SQL> select count(*) from t1 partition (p2);

  COUNT(*)
----------
     90161

1 row selected.


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
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


I think we can safely say that the *functionality* is correct, ie, partition P2 is indeed a in-memory object.

Which might suggest a possible idiosyncracy in the way V$IM_SEGMENTS is reporting its results after a partition exchange. You might to pursue that via 'My Oracle Support'. I'm also asking some internals people about it as well.

Once again, thanks for providing a simple, easy to use test case.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

access to support.

Rajeshwaran, Jeyabal, September 03, 2015 - 1:35 pm UTC

Sorry I don't have access to support. would you mind in taking up this test case with "my oracle support". please let me know if you need any help.
Connor McDonald
September 04, 2015 - 3:14 am UTC

If our internals people get back to me, I'll add a followup to this question.

Thanks

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.