Main | Why the In-Memory Column Store is not used (II) »
Tuesday
Feb172015

dramatic differences of in memory scanning performance on range queries

Given following two identical tables, on which run the same SQL,  replicated in memory with oracle in-memory option - one table created out of the other.
each tables covers 24m rows.
    
Same structure ...


SQL> desc T02

Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
TRID      NUMBER(12)                             
TRMONTH   CHAR(6)                                
TRDSYSTEM NUMBER(4)                              
TRDSTATE  CHAR(16)                               
TRDTIME   TIMESTAMP(6)                           
TRDDATE   DATE                                   
TRDAMOUNT NUMBER(8)    Y                         
TRDPRICE  NUMBER(12,3) Y                         
TRDDESC1  CHAR(64)                               
TRDDESC2  CHAR(64)                               
TRDDESC3  CHAR(64)                               

SQL>

 

Same data ...


SQL> SELECT * FROM T01 MINUS
  2  SELECT * FROM T02;

Es wurden keine Zeilen ausgewõhlt

Abgelaufen: 00:09:41.28

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 735239715

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |    24M|    11G|       |  2667K  (1)| 00:01:45 |
|   1 |  MINUS                       |       |       |       |       |            |       |
|   2 |   SORT UNIQUE                |       |    24M|  5859M|  6696M|  1333K  (1)| 00:00:53 |
|   3 |    TABLE ACCESS INMEMORY FULL| T01   |    24M|  5859M|       | 10061   (8)| 00:00:01 |
|   4 |   SORT UNIQUE                |       |    24M|  5859M|  6696M|  1333K  (1)| 00:00:53 |
|   5 |    TABLE ACCESS INMEMORY FULL| T02   |    24M|  5859M|       |  9599   (7)| 00:00:01 |
----------------------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
      12948  recursive calls
      13607  db block gets
         46  consistent gets
    1655305  physical reads
          0  redo size
        836  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          2  sorts (disk)
          0  rows processed

SQL>


the queries are very simple - not reasonable but simple ;)
But let's have a look on the in-memory performance - will it also be the same?

Query on Table T01

First query 0.12 sec !


SQL> SELECT /*+ FULL (T) */
  2   MAX(t.Trid)
  3  ,MIN(t.Trid)
  4  ,AVG(t.Trid)
  5    FROM T01 t
  6   WHERE t.Trid BETWEEN 70000 AND 70000 + 200;

MAX(T.TRID) MIN(T.TRID) AVG(T.TRID)
----------- ----------- -----------
      70200       70000       70100

Abgelaufen: 00:00:00.12

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 1887438862

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     6 |  9406   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| T01  |   202 |  1212 |  9406   (2)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - inmemory("T"."TRID"<=70200 AND "T"."TRID">=70000)
       filter("T"."TRID"<=70200 AND "T"."TRID">=70000)

for a table scan with 24m rows without using an index access this is a remarkable timing.
Now to the next query to Table T02 ...

Second query 0.02 sec !!!


SQL>
  1  SELECT /*+ FULL (T) */
  2   MAX(t.Trid)
  3  ,MIN(t.Trid)
  4  ,AVG(t.Trid)
  5    FROM T02 t
  6*  WHERE t.Trid BETWEEN 70000 AND 70000 + 200

MAX(T.TRID) MIN(T.TRID) AVG(T.TRID)
----------- ----------- -----------
      70200       70000       70100

Abgelaufen: 00:00:00.02

Plan hash value: 2537348092
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     6 |  9000   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| T02  |   202 |  1212 |  9000   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - inmemory("T"."TRID"<=70200 AND "T"."TRID">=70000)
       filter("T"."TRID"<=70200 AND "T"."TRID">=70000)

 

What? How can this be. Second Query on T02 was 600% faster!

And please do not comment to re-execute the query in T01; There is no caching here as all data is already in memory. 

In-Memory statistics - Key for understanding

Now looking on some in-memory stats enlightens Image on the picture

On table T01 IMCUs on that table were maximum 46 (IM scan segments minmax eligible) and all (IM scan CUs columns accessed) needed to be scanned and no one could be skipped (IM scan CUs pruned)

T01 : IM scan segments minmax eligible        46
T01 : IM scan CUs columns accessed            46
T01 : IM scan CUs pruned                             0
T01 : 00:00:00.12

On table T02 IMCUs on that table were maximum 90 (IM scan segments minmax eligible) and only 2 (IM scan CUs columns accessed) needed to be scanned and 88 could be skipped (IM scan CUs pruned)

T02 : IM scan segments minmax eligible        90
T02 : IM scan CUs columns accessed              2
T02 : IM scan CUs pruned                           88
T02 : 00:00:00.02

 The secret behind the timings is that T02 is a sorted data set by TRID and and table T01 sorted by TRDPRICE which causes TRID to be unsorted. Unsorted Colum store means to satisfy the range scan all IMCU may be scanned.

Conclusions/Findings

  • Soft-Partitioning - (INMEMORY FULL <> INMEMORY FULL)
    A columns store is not allocated as one piece of continues memory but allocated in IMCU's; Every IMCU covers a storage index. This is nothing more then a min/max  value of the column stored in the IMCU. So the wording index is miss-leading. I would prefer partition. Depending from the data distribution number acceess to IMCU can be reduced which performs best on range scans when the column is sorted. By design there is no sorting/real indexing of of the oreacle 12c in memory column store.
    So an INMEMORY FULL access must not be a complete scan over the whole column store but a partial much more effective one similar like accessing few partitions of a table.
  • Scan-speed
    It's hard to make assumption about the real scan speed. Because you never know how many IMCU's needs to access the data without checking teh sessiosn statistics. This also has an impact on performance prediction calculations and sizing of machines. Also be critical to published super fast benchmarks - not the number rows of a table counts but the number IMCU's accessed in the query.
  • Scalability and sorted column store
    If ideally a column store is sorted usually by incrimination of an integer id or by date values the performance of a range query may be consistent even the number rows in that table grows. In given example the access on T02 would not be slower with 240M rows compared to the 24M ones; But on T01 the unsorted one the access would get linear slower to the number of rows means instead of 0.12 sec it would need 1.2 sec which is a notifyable slow down for the user and compared to access on T02 factor 60!

 /Karl

 

References (2)

References allow you to track sources for this article, as well as articles that were written in response to this article.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>