Timesten - ttSize

> Database > (TimesTen|TT) (In-Memory Database|IMDB)

1 - About

ttsize is an utility used to estimate the table size.

More:

ttsize -help
Advertising

3 - Syntax

ttSize -tbl [<owner>.]<tableName> [-rows <rows>] [-frac <frac>] {<DSN> | -connstr <connStr>}

4 - Oracle Database vs Timesten

  • Oracle rows are always variable length in storage whereas TimesTen rows are always fixed length in storage.
  • In Oracle, a column defined as NUMBER only occupies the space actually needed based on the value stored. In TimesTen a NUMBER column always occupies space to store the maximum possible precision and so occupies 22 bytes. You can reduce this by explicitly limiting it using NUMBER(n) or NUMBER(n,p).
  • NUMBER is relatively inefficient. Try using a native binary type (TT_INTEGER, TT_BIGINT, BINARY_DOUBLE depending on your data).

5 - Example

5.1 - With 100000 rows

ttsize -tbl SAF_ORMK_01M_T -rows 100000 "DSN=DI_TT_CACHE_STORE;UID=DI_OBIEE_AIRLINE_AGG;PWD=DI_OBIEE_AIRLINE_AGG";
or
ttsize -tbl DI_OBIEE_AIRLINE_AGG.SAF_ORMK_01M_T -rows 100000 DI_TT_CACHE_STORE;

Output:

Rows = 100000
Total in-line row bytes = 27262808
Total = 27262808

5.2 - With 200000 rows

ttsize -tbl DI_OBIEE_AIRLINE_AGG.SAF_ORMK_01M_T -rows 200000 DI_TT_CACHE_STORE;
Rows = 200000
Total in-line row bytes = 54518232
Total = 54518232

Difference = 27262808 * 2 = 54525616 - 54518232 = 7384 bit

Advertising

6 - How to

6.1 - use it in batch to estimate the size of a complete database

With the help of an Excelsheet, create a batch file ttsize.(sh|bat) such as:

ttsize -tbl MySchema.MyTable1 -rows 119245 MyDSN;
ttsize -tbl MySchema.MyTable2 -rows 13287 MyDSN;
..................

Execute it and redirect the standard output to a file

./ttsize.sh > ttsize.log

You will get something like that:

Rows = 119245
Total in-line row bytes = 32487608
Total = 32487608

Rows = 13287
Total in-line row bytes = 3638312
Total = 3638312

...............
...............

Then with the help of JEdit Search and Java Regular Expressions, we will extract only the total number.

where:

  • “Search for” contains:
(\nRows.*\n\nTotal in-line.*\n\n){1}(Out-of-line.*\n){0,1}(  Column.*\n){0,20}(  Total out-of-line.*\n\n){0,1}(Total = ){1}([0-9]*\n?){1}
  • “Replace with” contains:
_6

Output:

32487608
3638312
........

Import it back to an excelsheet in order to make your sum.

Advertising

7 - Documentation / Reference