ttSizeOra

The main features of ttSizeOra are:

Generates an accurate memory sizing estimate to support capacity planning before data is moved to TimesTen.
Bases the estimate on the mapping of Oracle data types to their corresponding TimesTen data types.
Optionally accounts for memory savings by mapping eligible Oracle NUMBER columns to native TimesTen integer data types.
Maps native Oracle JSON columns to the TimesTen JSON data type. The maximum supported TimesTen JSON size is 4,194,304 bytes.
Estimates JSON size using the measured average payload when available and uses the heuristic only when a measured average is unavailable.
Provides control over the threshold that determines whether variable-length columns are stored INLINE or NOT INLINE in TimesTen, allowing you to assess its impact on overall memory consumption.
Allows you to specify the TimesTen range index type (B-tree or T-tree) to use when estimating index sizes. The default index type is B-tree.
Analyzes the Oracle table data to determine the row count and the average utilization of variable-length columns.
Supports Oracle databases configured with MAX_STRING_SIZE=EXTENDED. Extended columns are validated individually.
Allows you to specify the row count and/or variable-length column utilization on the command line to override the automatically determined values, which may eliminate the need to analyze the data in the Oracle database.
Provides two levels of output detail.

Note:

To use this utility, you must ensure that the following prerequisites are met:

The utility supports Oracle Database 19c, 23ai, and 26ai releases. Oracle Database 23ai and later releases support the JSON data type. Older versions are not supported.
Supports on a wide range of platforms:
- Linux ARM 64-bit
- Linux x86 64-bit
The system on which you plan to run the utility must have a compatible Oracle Client installed (Oracle Instant Client is sufficient) and configured to connect to the Oracle database.
Before running the utility, ensure that your shell environment is properly configured, including variables such as PATH and LD_LIBRARY_PATH (or the equivalent on your platform).

Required Privilege

This utility requires the following privileges:

CREATE SESSION
SELECT privilege on the source tables
Access to the Oracle metadata views required by the utility, including ALL_TABLES, ALL_TAB_COLUMNS, ALL_INDEXES, and ALL_CONSTRAINTS.

Syntax

ttSizeOra [-h | -help | -?]  
  
ttSizeOra [-V | -version]  
  
ttSizeOra  -oraConn ouser[/opasswd][@oserver]  
           -tbl [owner.]tablename  
          [-rows r]  
          [-frac f]  
          [-typeMap | -noTypeMap]  
          [-inlineLimit n]  
          [-parallel dop]  
          [-btree | -ttree]  
          [-verbose]

Options

ttSizeOra has these options:

Option	Description
`-h \| -help \| -?`	Prints a usage message and exits.
`-V \| -version`	Prints the release number of `ttSizeOra` and exits.
`-oraConn ouser[/opasswd][@oserver]`	Specifies connection credentials for the source Oracle database. `ouser` - The Oracle username. `opasswd` - The password for `ouser`. If this is omitted, you will be prompted for the password. `oserver` - Identifies the source Oracle database server. This value can be a TNS name or an Easy Connect string in the format `hostname:portno/dbservice`. You can omit `oserver` when you connect locally to the default database.
`-tbl [owner.]tablename`	Specifies the Oracle database table for which sizing is estimated.
`-rows r`	Specifies the expected number of rows in the table. `r` must be greater than `0`. If this option is omitted, the utility uses the number of rows in the existing table to estimate the size, or `1` row if the table is empty.
`-frac f`	Specifies the estimated average fraction of `VARCHAR2`, `NVARCHAR2`, `VARBINARY`, or `JSON` column sizes that are being used. Specify f as a value from `0` through `1.0`, inclusive. When you omit `-frac`, `ttSizeOra` analyzes variable-length columns and uses the measured average sizes when available. For `JSON` columns, `ttSizeOra` calculates the measured average as: ROUND(AVG(LENGTH(json_column))) If no measured `JSON` average is available, `ttSizeOra` calculates the estimate as: ROUND(4,194,304 * frac) The default fraction is `0.5`, which produces a fallback estimate of `2,097,15`2 bytes. `ttSizeOra` uses the fallback estimate in the following cases: The table is empty. All `JSON` values are `NULL`. You explicitly specify `-frac`. The utility skips variable-length analysis.
`-typeMap \| -noTypeMap`	Controls whether to apply numeric type mapping when sizing. If you select type mapping, the utility maps columns with the `NUMBER` type, where possible, to the appropriate TimesTen native integer type. The default is `-noTypeMap`.
`-inlineLimit n`	Specifies the value used to determine whether to store variable-length columns inline or out of line. `n` - Stores `VARCHAR2` and `NVARCHAR2` columns with a maximum defined size greater than `n` bytes as `NOT INLINE`; otherwise, stores them as `INLINE`. The minimum allowed value is `0` (always store as `NOT INLINE`), the maximum is `32768`, and the default is `128`.
`-parallel dop`	Specifies the degree of parallelism used when querying the source database in the analysis phase. `dop` - Specifies the degree of parallelism to use for source table analysis scans. Specify a value from `0` to `128`. A value of `0` or `1` disables parallel scanning. By default, the utility uses the degree of parallelism defined in the source database.
`-btree \| -ttree`	Specifies whether to treat range indexes as `B-trees` or `T-trees`. The default is `B-trees`.
`-verbose`	Displays only basic sizing information if you do not specify this option. If you specify this option, the utility also displays details about type-mapped columns and sizing information for alternate index types.

When you move tables from an Oracle database to a TimesTen database, you observe some storage overhead because the two databases use different internal storage architectures.

Because memory capacity is typically more constrained than disk capacity, minimize memory usage, especially when working with large volumes of data.

When you move tables and their data from an Oracle database to a TimesTen database, you often use different data types for certain columns in TimesTen. In some cases, you must use different data types. ttSizeOra automatically applies the mandatory data type mappings when computing the sizing estimate.

In addition, ttSizeOra lets you optionally enable numeric type mapping to reduce memory usage by mapping NUMBER columns with a scale of zero to the appropriate TimesTen native integer type. TimesTen native integer types generally use less space and provide better performance than NUMBER types. Numeric type mapping is controlled by the –typeMap /-noTypeMap command line options. The default option is -noTypeMap.

If you specify the -verbose command-line option, the sizing report includes details of all type-mapped columns, including both mandatory and optional mappings. For details, see Data Type Mapping in the Getting Started with ttImportFromOracle Utility.

Example

Perform the following steps:

Determine the Oracle database connection information, including the user name, password, host name, port, and service name.
Ensure that the system where you plan to run ttSizeOra has a working Oracle Client installation (Oracle Instant Client is sufficient). Configure the client to connect to the target Oracle database, and verify the connection by using SQL*Plus.
Identify the tables for which you want to estimate the size.
Determine which sizing and reporting options you want to use. These options can vary by table.
Run ttSizeOra for each table and save the results to a file.
Review the output file and use the sizing estimates to determine the required TimesTen database size.

In this example, the Oracle database server uses the TNS name MYDB. The corresponding entry in the client tnsnames.ora file is as follows:

MYDB =  
(DESCRIPTION =  
(ADDRESS =   
(PROTOCOL = TCP)(HOST = ol8-odb1.oracle.net)(PORT = 1521))  
(CONNECT_DATA =  
(SERVER = DEDICATED)  
(SERVICE_NAME = orcl)  
)  
)

As with any Oracle application, set the TNS_ADMIN environment variable to the directory that contains the tnsnames.ora file before running the utility, if you use TNS name resolution to connect to the Oracle Database server.

ttSizeOra also supports the Oracle Easy Connect syntax (serverhostname:portno/dbservicename) for connecting to the Oracle database.

In this example, the Oracle database credentials are user scott and password test. User scott owns the following tables, indexes, and data:

CREATE TABLE PARENTTAB  
(  
P_PK          NUMBER(10,0) NOT NULL,  
P_C1          NUMBER,  
P_C2          DATE NOT NULL,  
P_C3          VARCHAR2(200),  
P_C4          NVARCHAR2(1000),  
CONSTRAINT CP_PK PRIMARY KEY ( P_PK )  
);  
CREATE UNIQUE INDEX P_IX1 ON PARENTTAB( P_C1 );  
CREATE INDEX P_IX2 ON PARENTTAB( P_C2 );  
INSERT INTO PARENTTAB VALUES(1, 10, '2012-12-01', 'ABCDE', 'ABCDE');  
INSERT INTO PARENTTAB VALUES(2, 20, '2012-12-02', NULL, 'A');  
INSERT INTO PARENTTAB VALUES(3, 30, '2012-12-03', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL);  
INSERT INTO PARENTTAB VALUES(4, 40, '2012-12-04', 'ABC', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'); 
INSERT INTO PARENTTAB VALUES(5, 50, '2012-12-05', 'ABCDE', NULL);   
CREATE TABLE CHILDTAB  
(  
C_PK          NUMBER(6,0) NOT NULL,  
C_FK          NUMBER(10,0),  
C_C1          VARCHAR2(255),  
CONSTRAINT CC_PK PRIMARY KEY ( C_PK ),  
CONSTRAINT CC_FK FOREIGN KEY ( C_FK)  
REFERENCES PARENTTAB( P_PK )  
);  
INSERT INTO CHILDTAB VALUES ( 1, 3, 'ZZZ');  
INSERT INTO CHILDTAB VALUES ( 2, 2, NULL );  
INSERT INTO CHILDTAB VALUES ( 3, 5, 'ABCDEFGHIJKLMNOPQRST' );

First, run ttSizeOra with the default options to analyze these two tables and generate sizing information.

$ ttSizeOra  –oraConn  scott/test@mydb –tbl parenttab  
Rows = 5    
Total in-line row bytes = 23595  
  
Out-of-line columns:  
  Column P_C3                            total      200    avg size     40  
  Column P_C4                            total      110    avg size     22  
  Total out-of-line column bytes = 310  
  
Indexes:  
  B-tree range index SCOTT.CP_PK adds 5170 bytes  
  B-tree range index SCOTT.P_IX1 adds 6194 bytes  
  B-tree range index SCOTT.P_IX2 adds 5170 bytes  
  Total index bytes = 16534  
  
Total = 40439  
  
$ ttSizeOra  –oraConn  scott/test@mydb –tbl childtab  
Rows = 3  
  
Total in-line row bytes = 14773  
  
Out-of-line columns:  
  Column C_C1                            total      144    avg size     48  
  Total out-of-line column bytes = 144  
  
Indexes:  
  B-tree range index SCOTT.CC_PK adds 5170 bytes  
  B-tree range index SCOTT.CC_FK adds 5170 bytes  
  Total index bytes = 10340  
  
Total = 25257

Next, rerun ttSizeOra with numeric type mapping enabled and T-tree indexes selected with detailed output.

$ ttSizeOra  –oraConn  scott/test@mydb –tbl parenttab –typemap –ttree -verbose  
Column type mappings:  
  P_PK: NUMBER(10,0) -> TT_BIGINT  
  
Rows = 5  
  
Total in-line row bytes = 23595  
  
Out-of-line columns:  
  Column P_C3                            total      200    avg size     40  
  Column P_C4                            total      110    avg size     22  
  Total out-of-line column bytes = 310  
  
Indexes:  
  T-tree range index SCOTT.CP_PK adds 4074 bytes  
      (as a B-tree index would be 5170 bytes)  
      (as a Hash index would be 2874 bytes)  
  T-tree range index SCOTT.P_IX1 adds 4074 bytes  
      (as a B-tree index would be 6194 bytes)  
 
 
      (as a Hash index would be 3114 bytes)  
  T-tree range index SCOTT.P_IX2 adds 4074 bytes  
      (as a B-tree index would be 5170 bytes)  
      (as a Hash index would be 2922 bytes)  
  Total index bytes = 12222  
  
Total = 36127  
  
$ ttSizeOra  –oraConn  scott/test@mydb –tbl childtab –typemap –ttree -verbose  
Column type mappings:  
  C_PK: NUMBER(6,0) -> TT_INTEGER  
  C_FK: NUMBER(10,0) -> TT_BIGINT  
  
Rows = 3  
  
Total in-line row bytes = 14773  
  
Out-of-line columns:  
  Column C_C1                            total      144    avg size     48  
  Total out-of-line column bytes = 144  
  
Indexes:  
  T-tree range index SCOTT.CC_PK adds 4074 bytes  
      (as a B-tree index would be 5170 bytes)  
      (as a Hash index would be 2874 bytes)  
  T-tree range index SCOTT.CC_FK adds 4074 bytes  
      (as a B-tree index would be 5170 bytes)  
      (as a Hash index would be 2922 bytes)  
  Total index bytes = 8148  
  
Total = 23065

Restrictions and Limitations

Oracle Database and TimesTen differ significantly in their architecture and capabilities. As a result, some Oracle Database DDL features cannot be mapped directly to TimesTen. When ttSizeOra encounters a feature that it cannot map appropriately, it makes reasonable assumptions, displays a warning describing those assumptions, and continues with the sizing estimate.

The following list highlights the limitations of ttSizeOra:

It sizes only tables, materialized views, unique constraints, primary key constraints, foreign key constraints, and indexes. It does not support any other database object types.
It sizes materialized views as tables.
Columns in Oracle database tables that use data types not supported by TimesTen (for example, user-defined types and XML types) are not included in the sizing estimate. The utility issues a warning and ignores these columns.
TimesTen JSON supports values up to 4 MiB, even though Oracle supports larger JSON values.
When a measured JSON average is unavailable, ttSizeOra uses a conservative fallback of 4,194,304 bytes multiplied by the specified fraction. The default fraction is 0.5, resulting in an estimate of 2,097,152 bytes. This fallback applies to empty tables, tables in which all JSON values are NULL, and invocations that explicitly specify -frac.
TimesTen does not support the TIMESTAMP WITH TIME ZONE data type. When ttSizeOra analyzes a column of this type, it cannot preserve the time zone information. The utility issues a warning and maps the column as a TIMESTAMP type.
It supports only regular and bitmap indexes. It ignores all other index types.
It includes only constraints that are in the ENABLED and VALIDATED state.
It includes only indexes that are in the VALID and VISIBLE state.
Oracle Database allows duplicate indexes (indexes defined on the same set of columns in the same order), but TimesTen does not. ttSizeOra ignores all except one of the duplicate indexes.