TimesTen and Memory allocation

In this post I would like to write about Columnar Compression in TimesTen but before that i should write some introduction about the TimesTen store method. As you know, TimesTen stores data in two different options:
  • IN_LINE - for column with a fixed length or columns with a variable length whose declared column length is <= 128 bytes (by default). This method is used by getting the best performance. 
  • OUT_OF_LINE - for columns whose declared column length is > 128 bytes (by default). Out-of-line columns are not stored contiguously with the row but are allocated. Accessing out-of-line columns is slightly slower than accessing in-line columns. LOB data types are stored out-of-line.
A little example (NLS_LANGTH_SEMANTIC=BYTE):

Command> create table t (in_line      varchar2 (50),
       >                 out_of_line  varchar2(129));
Command> desc t;

Table GENA.T:
  Columns:
    IN_LINE                         VARCHAR2 (50) INLINE
    OUT_OF_LINE                     VARCHAR2 (129) NOT INLINE

1 table found.
(primary key columns are indicated with *)

As you can see, the IN_LINE column stores date by using INLINE and the OUT_OF_LINE column stores date by using NOT INLINE. You can also implicitly specify the store method:

Command> create table t2 (in_line      varchar2 (50),
       >                  out_of_line  varchar2(129) inline);
Command> desc t2;

Table GENA.T2:
  Columns:
    IN_LINE                         VARCHAR2 (50) INLINE
    OUT_OF_LINE                     VARCHAR2 (129) INLINE

1 table found.
(primary key columns are indicated with *)
Command>

Now, let’s create a simple table:

Command> create table emp (id number not null,
       >                   creation date,
       >                   name_1   varchar2(40),
       >                   name_2   varchar2(40),
       >                   surname  varchar2(40),
       >                   dept_id  number);
Command> desc emp;

Table GENA.EMP:
  Columns:
    ID                              NUMBER NOT NULL
    CREATION                        DATE
    NAME_1                          VARCHAR2 (40) INLINE
    NAME_2                          VARCHAR2 (40) INLINE
    SURNAME                         VARCHAR2 (40) INLINE
    DEPT_ID                         NUMBER

1 table found.
(primary key columns are indicated with *)

What size does it have? We can use a new TimesTen feature for that. If you want to know what size your table has you should compute it by using ttComputeTabSizes('table_name') function and after that you will be able to see the table size in SYS.ALL_TAB_SIZES table or using tablesize table_name command:

Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   0
  NUM_USED_ROWS:        0
  NUM_FREE_ROWS:        0
  AVG_ROW_LEN:          Not computed
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          784
  LAST_UPDATED:         2012-01-19 19:37:07.000000

1 table found.
Command>

As you can see, there are no any data in EMP table. We see the metadata size (784 bytes) only, but we can get more information from data dictionary:

Command> VARIABLE TABNAME VARCHAR2(50) := 'EMP';
Command> SET AUTOVARIABLES ON;
Command> select COLNAME,
       >        COLLEN,
       >        INLINELEN
       >   from SYS.COLUMNS
       >  where id = ( select TBLID
       >                  from sys.tables
       >                 where tblname = :TABNAME);
< CREATION                       , 7, 7 >
< DEPT_ID                        , 22, 22 >
< ID                             , 22, 22 >
< NAME_1                         , 40, 44 >
< NAME_2                         , 40, 44 >
< SURNAME                        , 40, 44 >
6 rows found.
Command> select sum(COLLEN)    SUM_COL_LEN,
       >        sum(INLINELEN) SUM_INLINE_LEN
       >   from SYS.COLUMNS
       >  where id = ( select TBLID
       >                  from sys.tables
       >                 where tblname = :TABNAME);
< 171, 183 >
1 row found.
Command> select tblname, LENGTH from sys.tables where tblname = :TABNAME;
< EMP                            , 192 >
1 row found.
Command>

The data dictionary contains information about the EMP table. The SYS.COLUMN table contains columns information including data types, length of the column (SYS.COLUMN.COLLEN) and how many bytes a given column contributes to the inline width of a row (SYS.COLUMN.INLINELEN). Additionally, the SYS.TABLES table contains the length of inline portion of each row (SYS.TABLES.LENGTH). Now, let’s insert a row into the table:

Command> insert into emp values (1,sysdate,'gena', 'gena', 'gena', 1);
1 row inserted.
Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   52080
  NUM_USED_ROWS:        1
  NUM_FREE_ROWS:        255
  AVG_ROW_LEN:          206
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          52864
  LAST_UPDATED:         2012-01-19 19:37:54.000000

1 table found.
Command>

Let's analyze what we've got. First of all, the INLINE page was allocated for storing 256 rows and you've got information about already stored rows and about free rows (NUM_USED_ROWS = 1 and NUM_FREE_ROWS = 255). In this example all data are storing inside INLINE page that is why OUT_OF_LINE_BYTES = 0. Additionally we see the average row length (AVG_ROW_LEN = 206) and the total table size (TOTAL_BYTES = 52864).

Let's insert 255 rows into EMP table.

Command> begin
       >   for i in 2 .. 256 loop
       >     insert into emp 
       >          values (i,
       >                  sysdate,
       >                  '1234567890123456789012345678901234567890',  
       >                  '1234567890123456789012345678901234567890',  
       >                  '1234567890123456789012345678901234567890', 
       >                  i);
       >   end loop;
       > end;
       > /
PL/SQL procedure successfully completed.

Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   52080
  NUM_USED_ROWS:        256
  NUM_FREE_ROWS:        0
  AVG_ROW_LEN:          206
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          52864
  LAST_UPDATED:         2012-01-24 15:49:40.000000

1 table found.
Command>

Nothing changes except NUM_USED_ROWS and NUM_FREE_ROWS parameters. Let's insert one more row.

Command> insert into emp values (257,sysdate,'gena', 'gena', 'gena', 1);
1 row inserted.
Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   104160
  NUM_USED_ROWS:        257
  NUM_FREE_ROWS:        255
  AVG_ROW_LEN:          204
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          104944
  LAST_UPDATED:         2012-01-24 15:50:29.000000

1 table found.
Command>

One more INLINE page was allocated for storing next 256 rows. Size the same (52080*2=104160). Metadata have the same size (784). But average row size was decrease till 204 bytes. What will happen if I delete the last row?

Command> delete from emp where id=257;
1 row deleted.
Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   52080
  NUM_USED_ROWS:        256
  NUM_FREE_ROWS:        0
  AVG_ROW_LEN:          206
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          52864
  LAST_UPDATED:         2012-01-24 18:00:57.000000

1 table found.
Command>

I deleted the single row in the new block that is why the block was deallocated. What If i deleted a row from first block?

Command> insert into emp values (257,sysdate,'gena', 'gena', 'gena', 1);
1 row inserted.
Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   104160
  NUM_USED_ROWS:        257
  NUM_FREE_ROWS:        255
  AVG_ROW_LEN:          204
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          104944
  LAST_UPDATED:         2012-01-24 18:07:47.000000

1 table found.
Command> delete from emp where id=1;
1 row deleted.
Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   104160
  NUM_USED_ROWS:        256
  NUM_FREE_ROWS:        256
  AVG_ROW_LEN:          204
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          104944
  LAST_UPDATED:         2012-01-24 18:08:02.000000

1 table found.
Command>

The INLINE_ALLOC_BYTES value didn't change (104160) because I deleted the row from first block. Let's delete more rows from first block.

Command> delete from emp where id between 3 and 256;
254 rows deleted.
Command> call ttComputeTabSizes('emp');
Command> tablesize emp;

Sizes of GENA.EMP:

  INLINE_ALLOC_BYTES:   104160
  NUM_USED_ROWS:        2
  NUM_FREE_ROWS:        510
  AVG_ROW_LEN:          204
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       784
  TOTAL_BYTES:          104944
  LAST_UPDATED:         2012-01-24 18:17:42.000000

1 table found.
Command>

TimesTen allocated two pages (104160 bytes) for storing only two rows. Of course the next inserted rows will be inserted into free space in the first block for avoiding fragmentation but you should know about it.

Conclusion

A lot of new features were introduced in new TimesTen version. One of them it is possibility to know the actual table size and this functionality is very easy for use I think.

TimesTen 11.2.2 is now available

TimesTen 11.2.2 is now available on OTN site. I was looking forward this release because a lot of new functionality was introduced in 11.2.2. I am particularly interested in the functionality which is related to Exalytics (TimesTen columnar compression, full LOB support, and etc.). There is not enough information about Exalitycs and there is only one man who has access to Exalytics outside of Oracle. That is Mark Rittman

Let's start. The most important  new functionality is as follows:

1. In-memory columnar compression of table

Actually, I can write nothing about it, because I don't know how it works :(, but I am going to find out and write about it ASAP.

2.  A global query (a query executed on multiple members of an Oracle In-Memory Database Cache grid) can reference more than one table.

I've already written here about Cache Grid and about impossibility to read the same data through different nodes. Additionally there were some restrictions for SQL (only one table can be used in SQL statement). In 11.2.2 this restriction was deleted. 

3. The ability to determine the current space usage of a table using the ttComputeTabSizes built-in procedure

A lot of customers ask: "How to get the actual size of a table in Timesten" (link). Now they have a tip for that :)

4. You can configure parallel propagation of changes in AWT cache tables to the corresponding Oracle tables.

One more replication feature was introduced. This functionality should increase a speed for propagation of changes to Oracle database. One partner has asked me about this functionality. I hope he is very happy now :)

5. Analytic functions

I think this is the most important feature that was introduced in this realise. When I worked in Oracle CIS, Andrey Pivovarov asked me to deliver presentation and demo about using TimesTen as a source for Oracle BI (11.1.05) on this event. I got a lot of questions about support analytic functions inside TimesTen from audience. Now as you can see, analytic functions are supporting in TimesTen.

6. Implicit data typed conversion

It can help developers because they can avoid error 2963 "Inconsistent datatypes" (example).

Resume

In my opinion, a lot of essential features were introduced in the new TimesTen release and I will update you about each of them in details very soon.