New IM (in-memory option) related Oracle’s diagnostic events

It’s a good news that a new feature (IM) goes with a new corresponding diagnostic event/component/s 🙂 .

SQL> oradebug doc component RDBMS.IM                                      

 

 IM			       in-memory ((null))
    IM_transaction	       IM transaction layer ((null))
      IM_Txn_PJ 	       IM Txn Private Journal (ktmpj)
      IM_Txn_SJ 	       IM Txn Shared Journal (ktmsj)
      IM_Txn_JS 	       IM Txn Journal Scan (ktmjs)
      IM_Txn_Conc	       IM Txn Concurrency (ktmc)
      IM_Txn_Blk	       IM Txn Block (ktmb)
      IM_Txn_Read	       IM Txn Read (ktmr)
    IM_space		       IM space layer ((null))
    IM_data		       IM data layer (kdm)
      IM_populate	       IM populating (kdml)
      IM_background	       IM background (kdmr)
      IM_scan		       IM scans ((null))
      IM_journal	       IM journal ((null))
      IM_dump		       IM dump ((null))
      IM_FS		       IM faststart ((null))
      IM_optimizer	       IM optimizer (kdmo)
Advertisements

How in-memory CU pruning works with a new attribute clustering clause

In the previous post I’ve shown how data ordering can potentially affect your  IM memory performance.

But actually you don’t need a perfect data ordering to provide effective CU pruning, it seems you just need a good data clustering to start gaining benefits from in-memory storage indexes.

Another new Oracle feature can help with that: attribute clustering clause 

Let’s do the similar experiment as in my previous post:

CREATE TABLE IDXTEST CLUSTERING BY LINEAR ORDER (id) YES ON DATA MOVEMENT
AS select (1e&pw) + rownum id, 1e4 id2, rpad('a',999,'a') filler 
from dual where rownum <2;
insert /*+ */ into IDXTEST select 1e&pw + trunc(rownum/50)*50+1 id, mod(rownum,20) id2, rpad('a',990,'b') || rownum filler 
from dual connect by level <=2e5 order by dbms_random.value; 
commit;
ALTER TABLE IDXTEST MOVE;

We populated our table with 200k data and then MOVE it to provide better clustering.
let’s put our table in the memory column store:

ALTER TABLE IDXTEST INMEMORY PRIORITY CRITICAL;
SELECT  /*+ dynamic_sampling(t 0) FULL(t)*/ count(*) from IDXTEST t WHERE id >:id2

to populate to populate in-memory store, let’s wait a couple of minutes.
and execute the same query again

PAUSE wait for inmemory segment to be populated  (select populate_status, bytes_not_populated,bytes,inmemory_size from v$im_segments))

SELECT  /*+ dynamic_sampling(t 0) FULL(t)*/ count(*) from IDXTEST t WHERE id >:id2

You would see a similar result:

SQL_ID  d1s2yfn2js4yh, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT  /*+ dynamic_sampling(t 0) FULL(t)*/ count(*) from IDXTEST t                                                                                                                                     
WHERE id >:id2                                                                                                                                                                                          
                                                                                                                                                                                                        
Plan hash value: 4867743                                                                                                                                                                                
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------                                                                                                                 
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                 
---------------------------------------------------------------------------------------                                                                                                                 
|   0 | SELECT STATEMENT            |         |       |       |     1 (100)|          |                                                                                                                 
|   1 |  SORT AGGREGATE             |         |     1 |     3 |            |          |                                                                                                                 
|*  2 |   TABLE ACCESS INMEMORY FULL| IDXTEST |     1 |     3 |     1   (0)| 00:00:01 |                                                                                                                 
---------------------------------------------------------------------------------------                                                                                                                 
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - inmemory("ID">:ID2)                                                                                                                                                                              
       filter("ID">:ID2)                                                                                                                                                                                
                                                                                                                                                                                                        

21 rows selected.

         0 IM scan CUs no memcompress                                                                                                                                                                   
         0 IM scan CUs memcompress for dml                                                                                                                                                              
         4 IM scan CUs memcompress for query low                                                                                                                                                        
         0 IM scan CUs memcompress for query high                                                                                                                                                       
         0 IM scan CUs memcompress for capacity low                                                                                                                                                     
         0 IM scan CUs memcompress for capacity high                                                                                                                                                    
         0 IM scan segments disk                                                                                                                                                                        
         0 IM scan CUs columns decompressed                                                                                                                                                             
         4 IM scan CUs predicates received                                                                                                                                                              
         4 IM scan CUs predicates applied                                                                                                                                                               
         4 IM scan CUs predicates optimized                                                                                                                                                             
         4 IM scan CUs pruned                                                                                                                                                                           
         4 IM scan segments minmax eligible                                                                                                                                                             

So result is even better comparing the previous post.

a small funny bug of CTAS syntax with clustering clause.

CREATE TABLE TEST_A CLUSTERING BY LINEAR ORDER (id)
AS select 2 id from dual where rownum <2

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

CREATE TABLE TEST_A CLUSTERING BY LINEAR ORDER (id)
AS select 1 id from dual where rownum

Table created
CREATE TABLE TEST_B
AS select 2 id from dual where rownum

Table created

I.e. create table as select (with clustering) :

works for value 1
doesn’t work for value 2

and work for usual CTAS (in both cases)

Oracle 12c In-memory and IM scan CUs pruned mechanics

Recently I’ve learnt about one interesting optimization technique used by Oracle in-memory database engine, i.e. CU (compression units) pruning.
(see http://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database2).
It looks similar to the trick used by Exadata storage indexes (or zone maps) to skip(prune) big data ranges by storing the min and max values of some columns
( see https://richardfoote.wordpress.com/2014/10/30/12-1-0-2-introduction-to-zone-maps-part-ii-changes/).

And I’ve almost immediate come up with idea that it also can suffer from “bad” data clustering in the same way as described by Richard Foote in his marvelous blog.

So I decided to check it and here is the result.

I’ve create a table as

CREATE TABLE IDXTEST
AS select (1e&pw) id, 1e4 id2, rpad('a',999,'a') filler
from dual where rownum <2;

ALTER TABLE IDXTEST INMEMORY PRIORITY CRITICAL;

and populated it in 2 ways:

1st (good clustering):

insert into IDXTEST select 1e&pw + trunc(rownum/1)+1 id, mod(rownum,20) id2, rpad('a',990,'b') || rownum filler
from dual connect by level <=2e5;
commit;

2nd (bad clustering):

insert into IDXTEST select 1e&pw + trunc(rownum/1)+1 id, mod(rownum,20) id2, rpad('a',990,'b') || rownum filler
from dual connect by level <=2e5 order by dbms_random.value;
commit;

I used the following query to do “in-memory” scans:
VARIABLE id2 number
EXEC :id2 :=(1e&pw.)+ 1.5e5

SELECT /*+ dynamic_sampling(t 0) FULL(t)*/ count(*) from IDXTEST t
WHERE id >:id2;

You need to run it twice:
1st time to populate in memory column store
and 2nd time to do “pure” in-memory scan.
(You need to wait 1-2 minutes before run the query 2nd time to finish population of the in memory column store, you can monitor the population process as
SELECT populate_status, bytes_not_populated,bytes,inmemory_size from v$im_segments)

and here is the corresponding stats

1st(good clustering) 2nd(bad clustering)
2 IM scan CUs pruned 0 IM scan CUs pruned
5 IM scan segments minmax eligible 5 IM scan segments minmax eligible

Moralite’: it’s still very important how your data is physically ordered/clustered even if you read it from memory.

PS
I had a small hope that Oracle in-memory engine could reorder data during populating column store but it seems to expensive operation to be true.