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.