About
ttsize is an utility used to estimate the table size.
More:
ttsize -help
Articles Related
Syntax
ttSize -tbl [<owner>.]<tableName> [-rows <rows>] [-frac <frac>] {<DSN> | -connstr <connStr>}
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).
Example
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
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
How to
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.