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 thettComputeTabSizes
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-runttComputeTabSizes
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: