1. Operations Guide
  2. Using the ttIsql Utility
  3. Displaying Database Structures Using ttIsql

Displaying Database Structures Using ttIsql

There are several ttIsql commands that display information on database structures.

The most useful commands are summarized below:

  • describe - Displays information on database objects.

  • cachegroups - Displays the attributes of cache groups.

  • dssize - Reports the current sizes of the permanent and temporary database memory regions.

  • tablesize - Displays the size of tables that have been analyzed with the ttComputeTabSizes tool.

  • monitor - Displays a summary of the current state of the database.

Using the ttIsql describe Command

Use the describe command to display information on individual database objects. Displays parameters for prepared SQL statements and built-in procedures.

The argument to the describe command can be the name of a table, cache group, view, materialized view, sequence, synonym, a built-in procedure, a SQL statement or a command ID for a previously prepared SQL statement, a PL/SQL function, PL/SQL procedure or PL/SQL package.

The describe command requires a semicolon character to terminate the command. 

Command> CREATE TABLE t1 (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64));
Command> describe t1;

Table USER.T1:
  Columns:
   *KEY                            NUMBER NOT NULL
    VALUE                          CHAR (64)
1 table found.

(primary key columns are indicated with *)
Command> describe SELECT * FROM T1 WHERE KEY=?;

Prepared Statement:
  Parameters:
    Parameter 1                    NUMBER
  Columns:
    KEY NUMBER                     NOT NULL
    VALUE                          CHAR (64)
Command> describe ttOptUseIndex;

Procedure TTOPTUSEINDEX:
  Parameters:
    Parameter INDOPTION            VARCHAR (1024)
  Columns:
    (none)

1 procedure found.
Command>

Using the ttIsql cachegroups Command

The cachegroups command is used to provide detailed information on cache groups defined in the current database.

The attributes of the root and child tables defined in the cache group are displayed in addition to the WHERE clauses associated with the cache group. The argument to the cachegroups command is the name of the cache group that you want to display information for. 

Command> cachegroups;
Cache Group CACHEUSER.READCACHE:
 Cache Group Type: Read Only
 Autorefresh: Yes
 Autorefresh Mode: Incremental
 Autorefresh State: Paused
 Autorefresh Interval: 5 Seconds
 Autorefresh Status: ok
 Aging: No aging defined
 Root Table: SALES.READTAB
 Table Type: Read Only
Cache Group CACHEUSER.WRITECACHE:
 Cache Group Type: Asynchronous Writethrough (Dynamic)
 Autorefresh: No
 Aging: LRU on
 Root Table: SALES.WRITETAB
 Table Type: Propagate
2 cache groups found.

Using the ttIsql dssize Command

The dssize command is used to report the current memory status of the permanent and temporary memory regions as well as the maximum, allocated and in-use sizes for the database.

However, for TimesTen Scaleout, the dssize command only reports on the current memory status for the current element.

The dssize command reports the same information that is displayed in the SYS.V$MONITOR and SYS.GV$MONITOR system views.

The following example uses the k option to print the database size information in KB: 

Command> dssize k;
The following values are in KB:
 
  PERM_ALLOCATED_SIZE:      40960
  PERM_IN_USE_SIZE:         9742
  PERM_IN_USE_HIGH_WATER:   9742
  TEMP_ALLOCATED_SIZE:      32768
  TEMP_IN_USE_SIZE:         9442
  TEMP_IN_USE_HIGH_WATER:   9505

For more information on the dssize command, see ttIsql in the Oracle TimesTen In-Memory Database Reference.

Using the ttIsql tablesize Command

The tablesize command displays the detailed analysis of the amount of space used by a table.

Once you call the ttComputeTabSizes built-in procedure, which analyzes the table size of the indicated tables, the tablesize command displays the total size data for all analyzed tables.

Note:

See ttComputeTabSizes in the Oracle TimesTen In-Memory Database Reference.

Running the tablesize command with no arguments displays available sizing information for all tables that have had the ttComputeTabSizes computation run. When you provide a table as an argument, tablesize displays available sizing only for the indicated table.

The syntax for tablesize is as follows: 

tablesize [[owner_name_pattern.]table_name_pattern]

The following example invokes the ttComputeTabSizes built-in procedure to calculate the table size of the employees table. Then, the tablesize command displays the sizing information gathered for the employees table. 

Command> call ttComputeTabSizes('employees');
Command> tablesize employees;
 
Sizes of USER1.EMPLOYEES:
 
  INLINE_ALLOC_BYTES:   60432
  NUM_USED_ROWS:        107
  NUM_FREE_ROWS:        149
  AVG_ROW_LEN:          236
  OUT_OF_LINE_BYTES:    0
  METADATA_BYTES:       1304
  TOTAL_BYTES:          61736
  LAST_UPDATED:         2011-06-29 12:55:28.000000
 
1 table found.

These values provide insights into overhead and how the total space is used for the table.

For example:

  • The NUM_FREE_ROWS value describes the number of rows allocated for the table, but not currently in use. Space occupied by free rows cannot be used by the system for storing other system objects or structures.

  • Use the TOTAL_BYTES value to calculate how much permanent space your table occupies.

  • LAST_UPDATED is the time of the last size computation. If you want a more recent computation, re-run ttComputeTabSizes and display the new output.

You can find a description for each calculated value in the SYS.ALL_TAB_SIZES section in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Using the ttIsql monitor Command

The monitor command displays all of the information provided by the dssize command plus additional statistics on the number of connections, checkpoints, lock timeouts, commits, rollback operations and other information collected since the last time the database was loaded into memory. 

Command> monitor; 
TIME_OF_1ST_CONNECT: Wed Apr 20 10:34:17 2011
DS_CONNECTS: 11 
DS_DISCONNECTS: 0 
DS_CHECKPOINTS: 0 
DS_CHECKPOINTS_FUZZY: 0 
DS_COMPACTS: 0 
PERM_ALLOCATED_SIZE: 40960 
PERM_IN_USE_SIZE: 5174 
PERM_IN_USE_HIGH_WATER: 5174 
TEMP_ALLOCATED_SIZE: 18432 
TEMP_IN_USE_SIZE: 4527 
TEMP_IN_USE_HIGH_WATER: 4527 
SYS18: 0 
TPL_FETCHES: 0 
TPL_EXECS: 0 
CACHE_HITS: 0 
PASSTHROUGH_COUNT: 0 
XACT_BEGINS: 2 
XACT_COMMITS: 1 
XACT_D_COMMITS: 0 
XACT_ROLLBACKS: 0 
LOG_FORCES: 0 
DEADLOCKS: 0 
LOCK_TIMEOUTS: 0 
LOCK_GRANTS_IMMED: 17 
LOCK_GRANTS_WAIT: 0 
SYS19: 0 
CMD_PREPARES: 1 
CMD_REPREPARES: 0 
CMD_TEMP_INDEXES: 0 
LAST_LOG_FILE: 0 
REPHOLD_LOG_FILE: -1 
REPHOLD_LOG_OFF: -1 
REP_XACT_COUNT: 0 
REP_CONFLICT_COUNT: 0 
REP_PEER_CONNECTIONS: 0 
REP_PEER_RETRIES: 0 
FIRST_LOG_FILE: 0 
LOG_BYTES_TO_LOG_BUFFER: 64 
LOG_FS_READS: 0 
LOG_FS_WRITES: 0 
LOG_BUFFER_WAITS: 0 
CHECKPOINT_BYTES_WRITTEN: 0 
CURSOR_OPENS: 1 
CURSOR_CLOSES: 1 
SYS3: 0 
SYS4: 0 
SYS5: 0 
SYS6: 0 
CHECKPOINT_BLOCKS_WRITTEN: 0 
CHECKPOINT_WRITES: 0 
REQUIRED_RECOVERY: 0 
SYS11: 0 
SYS12: 1 
TYPE_MODE: 0 
SYS13: 0 
SYS14: 0 
SYS15: 0 
SYS16: 0 
SYS17: 0 
SYS9: