The TimesTen ttIsql utility is a general tool for working with a TimesTen data source. The ttIsql command line interface is used to execute SQL statements and built-in ttIsql commands to perform various operations. Some common tasks that are typically accomplished using ttIsql include:
Database setup and maintenance. Creating tables and indexes, altering existing tables and updating table statistics can be performed quickly and easily using ttIsql.
Retrieval of information on database structures. The definitions for tables, indexes and cache groups can be retrieved using built-in ttIsql commands. In addition, the current size and state of the database can be displayed.
Optimizing database operations. The ttIsql utility can be used to alter and display query optimizer plans for the purpose of tuning SQL operations. The time required to execute various ODBC function calls can also be displayed.
The following sections describe how the ttIsql utility is used to perform these types of tasks:
For more information on TimesTen SQL and for a detailed description of all ttIsql commands see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.
The ttIsql utility can be used in two distinctly different ways: batch mode or interactive mode. When ttIsql is used in interactive mode, users type commands directly into ttIsql from the console. When ttIsql is used in batch mode, a prepared script of ttIsql commands is executed by specifying the name of the file containing the commands.
Batch mode is commonly used for the following types of tasks:
Performing periodic maintenance operations including the updating of table statistics, compacting the database and purging log files.
Initializing a database by creating tables, indexes and cache groups and then populating the tables with data.
Generating simple reports by executing common queries.
Interactive mode is suited for the following types of tasks:
Experimenting with TimesTen features, testing design alternatives and improving query performance.
Solving database problems by examining database statistics.
Any other database tasks that are not performed routinely.
By default, when starting ttIsql from the shell, ttIsql is in interactive mode. The ttIsql utility prompts you to type in a valid ttIsql built-in command or SQL statement by printing the Command> prompt:
C:\>ttIsql ttIsql (c) 1996-2009, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. Command>
Batch mode can be accessed in two different ways. The most common way is to specify the -f option on the ttIsql command line followed by the name of file to run.
For example, executing a file containing a CREATE TABLE statement will look like this:
C:\>ttIsql -f create.sql MY_DSN ttIsql (c) 1996-2009, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. Command> connect "DSN=MY_DSN" Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN; DRIVER=E:\WINNT\System32\TTdv1121.dll; (Default setting AutoCommit=1) Command> run "create.sql" CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)) Command> exit Disconnecting... Done. C:\>
The other way to use batch mode is to enter the run command directly from the interactive command prompt. The run command is followed by the name of the file containing ttIsql built-in commands and SQL statements to execute:
Command> run "create.sql"; CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)) Command>
The ttIsql utility can be customized to automatically execute a set of command line options every time a ttIsql session is started from the command prompt. This is accomplished by setting an environment variable called TTISQL to the value of the ttIsql command line that you prefer. A summary of ttIsql command line options is shown below. For a complete description of the ttIsql command line options, see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.
Usage: ttIsql [-h | -help | -helpcmds | -helpfull | -V]
[-connStr <connection_string>]
[-f <filename>]
[-v <verbosity>]
[-e <initialization_commands>]
[-interactive]
[-N <ncharencoding>]
[-wait]
The TTISQL environment variable has the same syntax requirements as the ttIsql command line. When ttIsql starts up it reads the value of the TTISQL environment variable and applies all options specified by the variable to the current ttIsql session. If a particular command line option is specified in both the TTISQL environment variable and the command line then the command line version will always take precedence.
The procedure for setting the value of an environment variable differs based on the platform and shell that ttIsql is started from. As an example, setting the TTISQL environment variable on Windows could look like this:
C:\>set TTISQL=-connStr "DSN=MY_DSN" -e "autocommit 0;dssize;"
In this example, ttIsql will automatically connect to a DSN called MY_DSN, turn off autocommit and display the size of the database as shown below:
C:\>ttIsql ttIsql (c) 1996-2009, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. Command> connect "DSN=MY_DSN"; Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\System32\TTdv1121.dll; (Default setting AutoCommit=1) Command> autocommit 0; Command> alltables; SYS.ACCESS$ SYS.ARGUMENT$ SYS.CACHE_GROUP SYS.COLUMNS SYS.COLUMN_HISTORY SYS.COL_STATS SYS.DEPENDENCY$ SYS.DIR$ SYS.DUAL SYS.ERROR$ SYS.IDL_CHAR$ SYS.IDL_SB4$ SYS.IDL_UB1$ SYS.IDL_UB2$ SYS.INDEXES SYS.MONITOR ... 59 tables found. Command>
You can customize the ttIsql command prompt by using the set command with the prompt attribute:
Command> set prompt MY_DSN; MY_DSN
You can specify a string format (%c) that returns the name of the current connection:
Command> set prompt %c; con1
If you want to embed spaces, you must quote the string:
Command> set prompt "MY_DSN %c> "; MY_DSN con1>
The ttIsql utility has an online version of command syntax definitions and descriptions for all built-in ttIsql commands. To access this online help from within ttIsql use the help command. To view a detailed description of any built-in ttIsql commands type the help command followed by one or more ttIsql commands to display help for. The example below displays the online description for the connect and disconnect commands.
Command> help connect disconnect Arguments in <> are required. Arguments in [] are optional. Command Usage: connect [DSN|connection_string] [as <connection_id>] Command Aliases: (none) Description: Connects to the data source specified by the optional DSN or connection string argument. If an argument is not given, then the DSN or connection string from the last successful connection is used. A connection ID may optionally be specified, for use in referring to the connection when multiple connections are enabled. The DSN is used as the default connection ID. If that ID is already in use, the connection will be assigned the ID "conN", where N is some number larger than 0. Requires an active connection: NO Requires autocommit turned off: NO Reports elapsed execution time: YES Works only with a TimesTen data source: NO Example: connect; -or- connect RunData; -or- connect "DSN=RunData"; -or- connect RunData as rundata1; Command Usage: disconnect [all] Command Aliases: (none) Description: Disconnects from the currently connected data source or all connections when the "all" argument is included. If a transaction is active when disconnecting then the transaction will be rolled back automatically. If a connection exists when executing the "bye", "quit" or "exit" commands then the "disconnect" command will be executed automatically. Requires an active connection: NO Requires autocommit turned off: NO Reports elapsed execution time: YES Works only with a TimesTen data source: NO Example: disconnect;
To view a short description of all ttIsql built-in commands type the help command without an argument. To view a detailed description of all built-in ttIsql commands type the help command followed by the all argument.
To view the list of attributes that can be set or shown by using ttIsql, enter:
Command> help attributes
On UNIX systems, you can use the 'editline' library to set up emacs (default) or vi bindings that enable you to scroll through previous ttIsql commands, as well as edit and resubmit them. This feature is not available or needed on Windows.
To disable the 'editline' feature in ttIsql, use the ttIsql command set editline off.
The set up and keystroke information is described for each type of editor:
To use the emacs binding, create a file ~/.editrc and put "bind" on the last line of the file, run ttIsql. The editline lib will print the current bindings.
The keystrokes when using ttIsql with the emacs binding are:
| Keystroke | Action |
|---|---|
| <Left-Arrow> | Move the insertion point left. Back up. |
| <Right-Arrow> | Move the insertion point right. Move forward. |
| <Up-Arrow> | Scroll to the command prior to the one being displayed. Places the cursor at the end of the line. |
| <Down-Arrow> | Scroll to a more recent command history item and put the cursor at the end of the line. |
| <Ctrl-A> | Move the insertion point to the beginning of the line. |
| <Ctrl-E> | Move the insertion point to the end of the line. |
| <Ctrl-K> | "Kill" (Save and erase) the characters on the command line from the current position to the end of the line. |
| <Ctrl-Y> | "Yank" (Restore) the characters previously saved and insert them at the current insertion point. |
| <Ctrl-F> | Forward char - move forward 1 (see Right Arrow) |
| <Ctrl-B> | Backward char - move back 1 (see Left Arrow) |
| <Ctrl-P> | Previous History (see Up Arrow) |
| <Ctrl-N> | Next History (see up Down Arrow) |
To use the vi bindings, create a file ${HOME}/.editrc and put "bind-v" in the file, run ttIsql. To get the current settings, create a file ${HOME}/.editrc and put "bind" on the last line of the file. When you execute ttIsql, the editline lib will print the current bindings.
The keystrokes when using ttIsql with the vi binding are:
| Keystroke | Action |
|---|---|
| <Left-Arrow>, h | Move the insertion point left (back up) |
| <Right-Arrow>, l | Move the insertion point right (forward) |
| <Up-Arrow>, k | Scroll to the prior command in the history and put the cursor at the end of the line. |
| <Down-Arrow>, j | Scroll to the next command in the history and put the cursor at the end of the line. |
| ESC | Vi Command mode |
| 0, $ | Move the insertion point to the beginning of the line, Move to end of the line. |
| i, I | Insert mode, Insert mode at beginning of the line |
| a, A | Add ("Insert after") mode, Append at end of line |
| R | Replace mode |
| C | Change to end of line |
| B | Move to previous word |
| e | Move to end of word |
| <Ctrl-P> | Previous History (see Up Arrow) |
| <Ctrl-N> | Next History (see up Down Arrow) |
The ttIsql utility stores a list of the last 100 commands executed within the current ttIsql session. The commands in this list can be viewed or executed again without having to type the entire command over. Both SQL statements and built-in ttIsql commands are stored in the history list. Use the history command ("h ") to view the list of previously executed commands. For example:
Command> h; 8 INSERT INTO T3 VALUES (3) 9 INSERT INTO T1 VALUES (4) 10 INSERT INTO T2 VALUES (5) 11 INSERT INTO T3 VALUES (6) 12 autocommit 0 13 showplan 14 SELECT * FROM T1, t2, t3 WHERE A=B AND B=C AND A=B 15 trytbllocks 0 16 tryserial 0 17 SELECT * FROM T1, t2, t3 WHERE A=B AND B=C AND A=B Command>
The history command displays the last 10 SQL statements or ttIsql built-in commands executed. To display more than that last 10 commands specify the maximum number to display as an argument to the history command.
Each entry in the history list is identified by a unique number. The ! character followed by the number of the command can be used to execute the command again. For example:
Command> Command> ! 12; autocommit 0 Command>
To execute the last command again simply type a sequence of two ! characters:
Command> !!; autocommit 0 Command>
To execute the last command that begins with a given string type the ! character followed by the first few letters of the command. For example:
Command> ! auto; autocommit 0 Command>
You can save the list of commands that ttIsql stores by using the savehistory command:
Command> savehistory history.txt;
If the output file already exists, use the -a option to append the new command history to the file or the -f option to overwrite the file. The next example shows how to append new command history to an existing file.
Command> savehistory -a history.txt;
You can clear the list of commands that ttIsql stores by using the clearhistory command:
Command> clearhistory;
The ttIsql utility supports the character sets listed in "Supported character sets" in the Oracle TimesTen In-Memory Database Reference. The ability of ttIsql to display characters depends on the native operating system locale settings of the terminal on which you are using ttIsql.
To override the locale-based output format, use the ncharencoding option or the -N option. The valid values for these options are LOCALE (the default) and ASCII. If you choose ASCII and ttIsql encounters a Unicode character, it displays it in escaped format.
You do not need to have an active connection to change the output method.
There are several ttIsql commands that display information on database structures. The most useful commands are summarized below:
dssize - Reports the current sizes of the permanent and temporary database partitions.
monitor - Displays a summary of the current state of the database.
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, view, materialized view, materialized view log, 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>
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: ORATT.READTAB Table Type: Read Only Cache Group CACHEUSER.WRITECACHE: Cache Group Type: Asynchronous Writethrough global (Dynamic) Autorefresh: No Aging: LRU on Root Table: ORATT.WRITETAB Table Type: Propagate 2 cache groups found.
The dssize command is used to report the current memory status of the permanent and temporary partitions as well as the maximum, allocated and in-use sizes for the database.
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, rollbacks and other information collected since the last time the database was loaded into memory.
Command> monitor; TIME_OF_1ST_CONNECT: Mon Feb 23 11:32:49 2009 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:
You can use ttIsql to list tables, indexes, views, sequences, synonyms, PL/SQL functions, procedures and packages in a database. Commands prefixed by all display all of this type of object. For example, the functions command lists PL/SQL functions that are owned by the user, whereas allfunctions lists all PL/SQL functions.
You can optionally specify patterns for object owners and object names.
Use these commands to list database objects:
tables and alltables - Lists tables
indexes and allindexes - Lists indexes
views and allviews - Lists views
sequences and allsequences - Lists sequences
synonyms and allsynonyms - Lists synonyms
functions and allfunctions - Lists PL/SQL functions
procedures and allprocedures - Lists PL/SQL procedures
packages and allpackages - Lists PL/SQL packages
Note:
For details on each of these commands, see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference.The following example demonstrates the procedures and allprocedures commands. User TERRY creates a procedure called proc1 while connected to myDSN. Note that a slash character (/) is entered on a new line following the PL/SQL statements.
The procedures command and the allprocedures command show that it is the only PL/SQL procedure in the database.
$ ttisql myDSN
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=myDSN";
Connection successful:
DSN=myDSN;UID=terry;DataStore=/scratch/terry/myDSN;DatabaseCharacter
Set=AL32UTF8;ConnectionCharacterSet=US7ASCII;PermSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> create or replace procedure proc1 as begin null; end;
> /
Procedure created.
Command> procedures;
TERRY.PROC1
1 procedure found.
Command> allprocedures;
TERRY.PROC1
1 procedure found.
Now connect to the same DSN as Pat and create a procedure called q. The allprocedures command shows the PL/SQL procedures created by Terry and pat.
$ ttisql "dsn=myDSN;uid=PAT"
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "dsn=myDSN;uid=PAT";
Connection successful: DSN=myDSN;UID=PAT;DataStore=/scratch/terry/myDSN;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=US7ASCII;PermSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> create or replace procedure q as begin null; end;
> /
Procedure created.
Command> procedures;
PAT.Q
1 procedure found.
Command> allprocedures;
TERRY.PROC1
PAT.Q
2 procedures found.
You can view and set connection attributes with the ttIsql show and set commands. For a list of the attributes that you can view and set with ttIsql, see "Connection Attributes" in Oracle TimesTen In-Memory Database Reference.
To view the setting for the Passthrough attribute, enter:
Command> show passthrough; PassThrough = 0
To change the Passthrough setting, enter:
Command> set passthrough 1;
The ttIsql utility has several built-in commands for managing transactions. These commands are summarized below:
autocommit - Turns on or off the autocommit feature. This can also be set as an attribute of the set command.
commitdurable - Commits the current transaction and ensures that the committed work will be recovered in case of database failure.
isolation - Changes the transaction isolation level. This can also be set as an attribute of the set command.
sqlquerytimeout - Specifies the number of seconds to wait for a SQL statement to execute before returning to the application. This can also be set as an attribute of the set command.
When starting ttIsql the autocommit feature is turned on by default. In this mode every SQL operation against the database is committed automatically. To turn the autocommit feature off execute the ttIsql autocommit command with an argument of 0.
When autocommit is turned off transactions must be committed or rolled back manually by executing the ttIsql commit, commitdurable or rollback commands. The commitdurable command ensures that the transaction's effect is preserved in case of database failure.
The ttIsql isolation command can be used to change the current connection's transaction isolation properties. The isolation can be changed only at the beginning of a transaction. The isolation command accepts one of the following constants: READ_COMMITTED and SERIALIZABLE. If the isolation command is modified without an argument then the current isolation level is reported.
The ttIsql sqlquerytimeout command sets the timeout period for SQL statements. If the execution time of a SQL statement exceeds the number of seconds set by the sqlquerytimeout command, the SQL statement is not executed and an 6111 error is generated. For details, see "Setting a timeout value for executing SQL statements" in the Oracle TimesTen In-Memory Database Java Developer's Guide and "Setting a timeout value for executing SQL statements" in the Oracle TimesTen In-Memory Database C Developer's Guide.
Note:
TimesTen rollback and query timeout features do not stop IMDB Cache operations that are being processed on Oracle. This includes passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating and dynamic loading.The following example demonstrates the common use of the ttIsql built-in transaction management commands.
E:\>ttIsql ttIsql (c) 1996-2009, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. Command> connect "DSN=MY_DSN"; Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\System32\ TTdv1121.dll; (Default setting AutoCommit=1) Command> autocommit 0; Command> CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)); Command> commit; Command> INSERT INTO LOOKUP VALUES (1, 'ABC'); 1 row inserted. Command> SELECT * FROM LOOKUP; < 1, ABC > 1 row found. Command> rollback; Command> SELECT * FROM LOOKUP; 0 rows found. Command> isolation; isolation = READ_COMMITTED Command> commitdurable; Command> sqlquerytimeout 10; Command> sqlquerytimeout; Query timeout = 10 seconds Command> disconnect; Disconnecting... Command> exit; Done. E:\>
Preparing a SQL statement just once and then executing it multiple times is much more efficient for TimesTen applications than re-preparing the statement each time it is to be executed. ttIsql has a set of built-in commands to work with prepared SQL statements. These commands are summarized below:
prepare - Prepares a SQL statement. Corresponds to a SQLPrepare ODBC call.
exec - Executes a previously prepared statement. Corresponds to a SQLExecute ODBC call.
execandfetch - Executes a previously prepared statement and fetches all result rows. Corresponds to a SQLExecute call followed by one or more calls to SQLFetch.
fetchall - Fetches all result rows for a previously executed statement. Corresponds to one or more SQLFetch calls.
fetchone - Fetches only one row for a previously executed statement. Corresponds to exactly one SQLFetch call.
close - Closes the result set cursor on a previously executed statement that generated a result set. Corresponds to a SQLFreeStmt call with the SQL_CLOSE option.
free - Closes a previously prepared statement. Corresponds to a SQLFreeStmt call with the SQL_DROP option.
describe - Describes the prepared statement including the input parameters and the result columns.
The ttIsql utility prepared statement commands also handle SQL statement parameter markers. When parameter markers are included in a prepared SQL statement, ttIsql will automatically prompt for the value of each parameter in the statement at execution time.
The example below uses the prepared statement commands of the ttIsql utility to prepare an INSERT statement into a table containing a NUMBER and a CHAR column. The statement is prepared and then executed twice with different values for each of the statement's two parameters. The ttIsql utility timing command is used to display the elapsed time required to executed the primary ODBC function call associated with each command.
Command> connect "DSN=MY_DSN"; Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER= E:\WINNT\Sys tem32\TTdv1121.dll; (Default setting AutoCommit=1) Command> timing 1; Command> create table t1 (key number not null primary key, value char(20)); Execution time (SQLExecute) = 0.007247 seconds. Command> prepare insert into t1 values (:f, :g); Execution time (SQLPrepare) = 0.000603 seconds. Command> exec; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/' to leave the remaining parameters unbound and execute the command. Enter Parameter 1 'F' (NUMBER) > 1; Enter Parameter 2 'G' (CHAR) > 'abc'; 1 row inserted. Execution time (SQLExecute) = 0.000454 seconds. Command> exec; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/' to leave the remaining parameters unbound and execute the help command. Enter Parameter 1 'F' (NUMBER) > 2; Enter Parameter 2 'G' (CHAR) > 'def'; 1 row inserted. Execution time (SQLExecute) = 0.000300 seconds. Command> free; Command> select * from t1; < 1, abc > < 2, def > 2 rows found. Execution time (SQLExecute + Fetch Loop) = 0.000226 seconds. Command> disconnect; Disconnecting... Execution time (SQLDisconnect) = 2.911396 seconds. Command>
In the example above, the prepare command is immediately followed by the SQL statement to prepare. Whenever a SQL statement is prepared in ttIsql, a unique command ID is assigned to the prepared statement. The ttIsql utility uses this ID to keep track of multiple prepared statements. A maximum of 256 prepared statements can exist in a ttIsql session simultaneously. When the free command is executed, the command ID is automatically disassociated from the prepared SQL statement.
To see the command IDs generated by ttIsql when using the prepared statement commands, set the verbosity level to 4 using the verbosity command before preparing the statement, or use the describe * command to list all prepared statements with their IDs.
Command IDs can be referenced explicitly when using ttIsql's prepared statement commands. For a complete description of the syntax of ttIsql's prepared statement commands see the "ttIsql" section in the Oracle TimesTen In-Memory Database Reference or type help at the ttIsql command prompt.
The example below prepares and executes a SELECT statement with a predicate containing one NUMBER parameter. The fetchone command is used to fetch the result row generated by the statement. The showplan command is used to display the execution plan used by the TimesTen query optimizer when the statement is executed. In addition, the verbosity level is set to 4 so that the command ID used by ttIsql to keep track of the prepared statement is displayed.
Command> connect "DSN=MY_DSN"; Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\Sys tem32\TTdv1121.dll; (Default setting AutoCommit=1) The command succeeded. Command> CREATE TABLE T1 (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64)); The command succeeded. Command> INSERT INTO T1 VALUES (1, 'abc'); 1 row inserted. The command succeeded. Command> autocommit 0; The command succeeded. Command> showplan 1; The command succeeded. Command> verbosity 4; The command succeeded. Command> prepare SELECT * FROM T1 WHERE KEY=?; Assigning new prepared command id = 0. Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkHashScan TBLNAME: T1 IXNAME: T1 PRED: T1.KEY = qmark_1 OTHERPRED: <NULL> The command succeeded. Command> exec; Executing prepared command id = 0. Type '?;' for help on entering parameter values. Type '*;' to abort the parameter entry process. Enter Parameter 1 (NUMBER) >1; The command succeeded. Command> fetchone; Fetching prepared command id = 0. < 1, abc > 1 row found. The command succeeded. Command> close; Closing prepared command id = 0. The command succeeded. Command> free; Freeing prepared command id = 0. The command succeeded. Command> commit; The command succeeded. Command> disconnect; Disconnecting... The command succeeded. Command>
Note:
For information about usingttIsql with PL/SQL host variables, see "Introduction to PL/SQL in the TimesTen Database" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.You can create and execute PL/SQL blocks from the ttIsql command line.
Set serveroutput on to display results generated from the PL/SQL block:
Command> set serveroutput on
Create an anonymous block that puts a text line in the output buffer. Note that the block must be terminated with a slash (/).
Command> BEGIN
> DBMS_OUTPUT.put_line(
> 'Welcome!');
> END;
> /
Welcome!
PL/SQL procedure successfully completed.
Command>
See the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for more examples.
You can pass data back to applications from PL/SQL by using OUT parameters. This example returns information about how full a TimesTen database is.
Create the tt_space_info PL/SQL procedure and use SQL to provide values for the permpct, permmaxpct, temppct, and tempmaxpct parameters.
Command> CREATE OR REPLACE PROCEDURE tt_space_info
> (permpct OUT PLS_INTEGER,
> permmaxpct OUT PLS_INTEGER,
> temppct OUT PLS_INTEGER,
> tempmaxpct OUT PLS_INTEGER) AS
> monitor sys.monitor%ROWTYPE;
> BEGIN
> SELECT * INTO monitor FROM sys.monitor;
> permpct := monitor.perm_in_use_size * 100 /
monitor.perm_allocated_size;
> permmaxpct := monitor.perm_in_use_high_water * 100 /
monitor.perm_allocated_size;
> temppct := monitor.temp_in_use_size * 100 /
monitor.temp_allocated_size;
> tempmaxpct := monitor.temp_in_use_high_water * 100 /
monitor.temp_allocated_size;
> END;
>/
Procedure created.
Declare the variables and call tt_space_info. The parameter values are passed back to ttIsql so they can be printed:
Command> VAR permpct NUMBER
Command> VAR permpctmax NUMBER
Command> VAR temppct NUMBER
Command> VAR temppctmax NUMBER
Command> BEGIN
> tt_space_info(:permpct, :permpctmax, :temppct, :temppctmax);
> END;
>/
PL/SQL procedure successfully completed.
Command> PRINT permpct;
PERMPCT : 4
Command> PRINT permpctmax;
PERMPCTMAX : 4
Command> PRINT temppct;
TEMPPCT : 11
Command> PRINT temppctmax;
TEMPPCTMAX : 11
You can also pass back a statement handle that can be executed by a PL/SQL statement with an OUT refcursor parameter. The PL/SQL statement can choose the query associated with the cursor. The following example opens a refcursor, which randomly chooses between ascending or descending order.
Command> VARIABLE ref REFCURSOR;
Command> BEGIN
> IF (mod(dbms_random.random(), 2) = 0) THEN
> open :ref for select object_name from SYS.ALL_OBJECTS order by 1 asc;
> ELSE
> open :ref for select object_name from SYS.ALL_OBJECTS order by 1 desc;
> end if;
> END;
> /
PL/SQL procedure successfully completed.
To fetch the result set from the refcursor, use the PRINT command:
Command> PRINT ref REF : < ACCESS$ > < ALL_ARGUMENTS > < ALL_COL_PRIVS > < ALL_DEPENDENCIES > ... 143 rows found.
Or if the result set was ordered in descending order, the following would print:
Command> PRINT ref REF : < XLASUBSCRIPTIONS > < WARNING_SETTINGS$ > < VIEWS > ... 143 rows found.
The following sections describe how to view the query optimizer plans, commands in the SQL command cache, or query plans for commands in the SQL command cache:
The built-in showplan command is used to display the query optimizer plans used by the TimesTen Data Manager for executing queries. In addition, ttIsql contains built-in query optimizer hint commands for altering the query optimizer plan. By using the showplan command in conjunction with the built-in commands summarized below, the optimum execution plan can be designed. For detailed information on the TimesTen query optimizer see "The TimesTen Query Optimizer".
optprofile - Displays the current optimizer hint settings and join order.
trytmphash - Enables or disables the use of temporary hash indexes.
trytmptable - Enables or disables the use of an intermediate results table.
trytmpttree - Enables or disables the use of temporary range indexes.
When using the showplan command and the query optimizer hint commands the autocommit feature must be turned off. Use ttIsql's autocommit built-in command to turn autocommit off.
The example below shows how these commands can be used to change the query optimizer execution plan.
Command> CREATE TABLE T1 (A NUMBER); Command> CREATE TABLE T2 (B NUMBER); Command> CREATE TABLE T3 (C NUMBER); Command> Command> INSERT INTO T1 VALUES (3); 1 row inserted. Command> INSERT INTO T2 VALUES (3); 1 row inserted. Command> INSERT INTO T3 VALUES (3); 1 row inserted. Command> INSERT INTO T1 VALUES (4); 1 row inserted. Command> INSERT INTO T2 VALUES (5); 1 row inserted. Command> INSERT INTO T3 VALUES (6); 1 row inserted. Command> Command> autocommit 0; Command> showplan; Command> SELECT * FROM T1, T2, T3 WHERE A=B AND B=C AND A=B; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: TblLkSerialScan TBLNAME: T1 IXNAME: <NULL> PRED: <NULL> OTHERPRED: <NULL> STEP: 2 LEVEL: 3 OPERATION: TblLkSerialScan TBLNAME: T2 IXNAME: <NULL> PRED: <NULL> OTHERPRED: T1.A = T2.B AND T1.A = T2.B STEP: 3 LEVEL: 2 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> PRED: <NULL> OTHERPRED: <NULL> STEP: 4 LEVEL: 2 OPERATION: TblLkSerialScan TBLNAME: T3 IXNAME: <NULL> PRED: <NULL> OTHERPRED: T2.B = T3.C STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> PRED: <NULL> OTHERPRED: <NULL> < 3, 3, 3 > 1 row found. Command> trytbllocks 0; Command> tryserial 0; Command> SELECT * FROM T1, t2, t3 WHERE A=B AND B=C AND A=B; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: TmpTtreeScan TBLNAME: T1 IXNAME: <NULL> PRED: <NULL> OTHERPRED: <NULL> STEP: 2 LEVEL: 3 OPERATION: TmpTtreeScan TBLNAME: T2 IXNAME: <NULL> PRED: T2.B >= T1.A OTHERPRED: <NULL> STEP: 3 LEVEL: 2 OPERATION: MergeJoin TBLNAME: <NULL> IXNAME: <NULL> PRED: T1.A = T2.B AND T1.A = T2.B OTHERPRED: <NULL> STEP: 4 LEVEL: 2 OPERATION: TmpTtreeScan TBLNAME: T3 IXNAME: <NULL> PRED: <NULL> OTHERPRED: T2.B = T3.C STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> PRED: <NULL> OTHERPRED: <NULL> < 3, 3, 3 > 1 row found. Command>
In this example a query against three tables is executed and the query optimizer plan is displayed. The first version of the query simply uses the query optimizer's default execution plan. However, in the second version the trytbllocks and tryserial built-in hint commands have been used to alter the query optimizer's plan. Instead of using serial scans and nested loop joins the second version of the query uses temporary index scans and merge joins.
In this way the showplan command in conjunction with ttIsql's built-in query optimizer hint commands can be used to quickly determine which execution plan should be used to meet application requirements.
The following sections describe how to view commands and their explain plans:
The ttIsql cmdcache command invokes the ttSqlCmdCacheInfo built-in procedure to display the contents of the TimesTen SQL Command Cache. See "Displaying commands stored in the SQL Command Cache" for full details on this procedure.
If you execute the cmdcache command without parameters, the full SQL Command Cache contents are displayed. Identical to the ttSqlCmdCacheInfo built-in procedure, you can provide a command ID to specify a specific command to be displayed.
In addition, the ttIsql cmdcache command can filter the results so that only those commands that match a particular owner or query text are displayed.
The syntax for the cmdcache command is as follows:
cmdcache [[by {sqlcmdid | querytext | owner}] <query_substring>
If you provide the owner parameter, the results are filtered by the owner, identified by the <query_substring>, displayed within each returned command. If you provide the querytext parameter, the results are filtered so that all queries are displayed that contain the substring provided within the <query_substring>. If only the <query_substring> is provided, such as cmdcache <query_substring>, the command assumes to filter the query text by the <query_substring>.
The ttIsql explain command displays the query plan for an individual SQL command.
If you provide a command ID from the SQL Command Cache, the explain command invokes the ttSqlCmdQueryPlan built-in procedure to display the query plan for an individual command in the TimesTen SQL Command Cache. If you want the explain plan displayed in a formatted method, execute the explain command instead of calling the ttSqlCmdQueryPlan built-in process. Both provide the same information, but the ttSqlCmdQueryPlan built-in provides the data in a raw data format. See "Viewing query plans associated with commands stored in the SQL Command Cache" for full details on the ttSqlCmdQueryPlan procedure.
If you provide a SQL statement or the history item number, then the explain command executes the SQL statements necessary to display the explain plan for that SQL statement.
The syntax for the explain command is as follows:
explain [plan for] {[<Connid>.]<ttisqlcmdid> | sqlcmdid <sqlcmdid> | <sqlstmt> | !<historyitem>}
Identical to the ttSqlCmdQueryPlan built-in procedure, you can provide a command ID to specify a specific command to be displayed. The command ID can be retrieved with the cmdcache command, as described in "View commands in the SQL Command Cache".
The following example provides an explain plan for command ID 38001456:
Command> explain sqlcmdid 38001456; Query Optimizer Plan: Query Text: select * from all_objects where object_name = 'DBMS_OUTPUT' STEP: 1 LEVEL: 12 OPERATION: TblLkTtreeScan TABLENAME: OBJ$ TABLEOWNERNAME: SYS INDEXNAME: USER$.I_OBJ INDEXEDPRED: NONINDEXEDPRED: (RTRIM( NAME )) = DBMS_OUTPUT;NOT( 10 = TYPE#) ;( FLAGS ^ 128 = 0) ; STEP: 2 LEVEL: 12 OPERATION: RowLkTtreeScan TABLENAME: OBJAUTH$ TABLEOWNERNAME: SYS INDEXNAME: OBJAUTH$.I_OBJAUTH1 INDEXEDPRED: ( (GRANTEE#=1 ) OR (GRANTEE#=10 ) ) AND ( (PRIVILEGE#=8 ) ) NONINDEXEDPRED: OBJ# = OBJ#; STEP: 3 LEVEL: 11 OPERATION: NestedLoop(Left OuterJoin) TABLENAME: TABLEOWNERNAME: INDEXNAME: INDEXEDPRED: NONINDEXEDPRED: ... STEP: 21 LEVEL: 1 OPERATION: Project TABLENAME: TABLEOWNERNAME: INDEXNAME: INDEXEDPRED: NONINDEXEDPRED: Command>
In addition, the ttIsql explain command can generate an explain plan for any SQL query you provide. For example, the following shows the explain plan for SQL query "select * from dual;"
Command> explain select * from dual; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkSerialScan TBLNAME: DUAL IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL>
You can also retrieve explain plans based upon the command history. The following example shows how you explain a previously executed SQL statement using the history command ID:
Command> select * from all_objects where object_name = 'DBMS_OUTPUT'; < SYS, DBMS_OUTPUT, <NULL>, 241, <NULL>, PACKAGE, 2009-10-13 10:41:11, 2009-10-13 10:41:11, 2009-10-13:10:41:11, VALID, N, N, N, 1, <NULL> > < PUBLIC, DBMS_OUTPUT, <NULL>, 242, <NULL>, SYNONYM, 2009-10-13 10:41:11, 2009-10-13 10:41:11, 2009-10-13:10:41:11, INVALID, N, N, N, 1, <NULL> > < SYS, DBMS_OUTPUT, <NULL>, 243, <NULL>, PACKAGE BODY, 2009-10-13 10:41:11, 2009-10-13 10:41:11, 2009-10-13:10:41:11, VALID, N, N, N, 2, <NULL> > 3 rows found. Command> history; 1 connect "DSN=cache"; 2 help cmdcache; 3 cmdcache; 4 explain select * from dual; 5 select * from all_objects where object_name = 'DBMS_OUTPUT'; Command> explain !5; Query Optimizer Plan: STEP: 1 LEVEL: 10 OPERATION: TblLkTtreeScan TBLNAME: SYS.OBJ$ IXNAME: USER$.I_OBJ INDEXED CONDITION: <NULL> NOT INDEXED: O.FLAGS & 128 = 0 AND CAST(RTRIM (O.NAME) AS VARCHAR2(30 BYTE) INLINE) = 'DBMS_OUTPUT' AND O.TYPE# <> 10 STEP: 2 LEVEL: 10 OPERATION: RowLkTtreeScan TBLNAME: SYS.OBJAUTH$ IXNAME: OBJAUTH$.I_OBJAUTH1 INDEXED CONDITION: (OA.GRANTEE# = 1 OR OA.GRANTEE# = 10) AND OA.PRIVILEGE# = 8 NOT INDEXED: OA.OBJ# = O.OBJ# STEP: 3 LEVEL: 9 OPERATION: NestedLoop(Left OuterJoin) TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 4 LEVEL: 9 OPERATION: TblLkTtreeScan TBLNAME: SYS.OBJAUTH$ IXNAME: OBJAUTH$.I_OBJAUTH1 INDEXED CONDITION: (OBJAUTH$.GRANTEE# = 1 OR OBJAUTH$.GRANTEE# = 10) AND (OBJAUTH$.PRIVILEGE# = 2 OR OBJAUTH$.PRIVILEGE# = 3 OR OBJAUTH$.PRIVILEGE# = 4 OR OBJAUTH$.PRIVILEGE# = 5 OR OBJAUTH$.PRIVILEGE# = 8) NOT INDEXED: O.OBJ# = OBJAUTH$.OBJ# ... STEP: 19 LEVEL: 1 OPERATION: NestedLoop(Left OuterJoin) TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: O.OWNER# = 1 OR (O.TYPE# IN (7,8,9) AND (NOT( ISNULLROW (SYS.OBJAUTH$.ROWID)) OR NOT( ISNULLROW (SYS.SYSAUTH$.ROWID)))) OR (O.TYPE# IN (1,2,3,4,5) AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 6 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 11 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# NOT IN (7,8,9,11) AND NOT( ISNULLROW (SYS.OBJAUTH$.ROWID))) OR (O.TYPE# = 28 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR (O.TYPE# = 23 AND NOT( ISNULLROW (SYS.SYSAUTH$.ROWID))) OR O.OWNER# = 10
Information on the time required to execute common ODBC function calls can be displayed by using the ttIsql timing command. When the timing feature is enabled many built-in ttIsql commands will report the elapsed execution time associated with the primary ODBC function call corresponding to the ttIsql command that is executed.
For example, when executing the ttIsql connect command several ODBC function calls are executed, however, the primary ODBC function call associated with connect is SQLDriverConnect and this is the function call that is timed and reported as shown below.
Command> timing 1; Command> connect "DSN=MY_DSN"; Connection successful: DSN=MY_DSN;DataStore=E:\ds\MY_DSN;DRIVER=E:\WINNT\System32\ TTdv1121.dll; (Default setting AutoCommit=1) Execution time (SQLDriverConnect) = 1.2626 seconds. Command>
In the example above, the SQLDriverConnect call took about 1.26 seconds to execute.
When using the timing command to measure queries, the time required to execute the query plus the time required to fetch the query results is measured. To avoid measuring the time to format and print query results to the display, set the verbosity level to 0 before executing the query.
Command> timing 1; Command> verbosity 0; Command> SELECT * FROM T1; Execution time (SQLExecute + FetchLoop) = 0.064210 seconds. Command>
You can use the xlabookmarkdelete command to both check the status of the current XLA bookmarks and delete them. This command requires XLA privilege or object ownership.
For example, when running the XLA application, 'xlaSimple', you can check the bookmark status by entering:
Command> xlabookmarkdelete; XLA Bookmark: xlaSimple Read Log File: 0 Read Offset: 630000 Purge Log File: 0 Purge Offset: 629960 PID: 2808 In Use: No 1 bookmark found.
To delete the bookmark, enter:
Command> xlabookmarkdelete xlaSimple; Command>