Oracle® TimesTen In-Memory Database Reference 11g Release 2 (11.2.2) E21643-24 |
|
|
View PDF |
TimesTen built-in procedures extend standard ODBC and JDBC functionality. You can invoke these procedures using the ODBC or JDBC procedure call interface. The procedure takes the position of the SQL statement, as illustrated in the following examples.
The following ODBC SQLExecDirect
call invokes the ttOpsSetFlag
built-in procedure to tell the optimizer that it should not generate temporary hash indexes when preparing commands:
SQLExecDirect (hstmt, (SQLCHAR*) "{CALL ttOptSetFlag ('TmpHash', 0)}", SQL_NTS);
This is the equivalent JDBC call:
CallableStatement cstmt = con.prepareCall ("{CALL ttOptSetFlag ('TmpHash', 0)}"); cstmt.execute();
TimesTen built-in procedures can also be called from PL/SQL using the EXECUTE IMMEDIATE
statement with CALL
, as illustrated in the following example. See the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for more details on this statement.
For example, to call the built-in procedure ttConfiguration
, create a PL/SQL record type and then SELECT INTO
that record type. Because ttConfiguration
returns multiple rows, use BULK COLLECT
.
Command> DECLARE > TYPE ttConfig_record IS RECORD > (name varchar2(255), value varchar2 (255)); > TYPE ttConfig_table IS TABLE OF ttConfig_record; > v_ttConfigs ttConfig_table; > BEGIN > EXECUTE IMMEDIATE 'CALL ttConfiguration' > BULK COLLECT into v_ttConfigs; > DBMS_OUTPUT.PUT_LINE ('Name: ' || v_ttConfigs(1).name > || ' Value: ' || v_ttConfigs(1).value); > end; > / Name: CacheGridEnable Value: 0 PL/SQL procedure successfully completed.
Note:
String parameter values for built-in procedures must be single-quoted as indicated in these examples, unless the value isNULL
.This procedure sets the Least Recently Used (LRU) aging attributes on all regular tables that have been defined with an LRU aging policy. For cache tables, the aging policy is defined on the root table but applies to all tables in the cache group. The aging policy is defined on tables when they are created or altered, using the CREATE TABLE
or ALTER TABLE
SQL statements.
The LRU aging feature helps applications maintain the usage size of the database under a specified threshold by removing the least recently used data.
Data is removed if the database space in-use exceeds the specified threshold values. For cache groups, aging is defined at the root table for the entire cache instance. LRU aging cannot be specified on a cache group with the AUTOREFRESH
attribute, unless the cache group is dynamic. For explicitly loaded cache groups with the AUTOREFRESH
attribute, use time-based aging.
This procedure requires no privilege to query the current values. It requires the ADMIN
privilege to change the current values.
ttAgingLRUConfig
has these optional parameters:
Parameter | Type | Description |
---|---|---|
lowUsageThreshold |
BINARY_FLOAT |
Sets, displays or resets the low end of percentage of database PermSize , specified in decimals. The bottom of the threshold range in which LRU aging should be deactivated. Default is 80 percent. |
highUsageThreshold |
BINARY_FLOAT |
Sets, displays or resets the high end of percentage of database PermSize , specified in decimals. The top of the threshold range in which LRU aging should be activated. Default is 90 percent. |
agingCycle |
TT_INTEGER |
Sets, displays or resets the number of minutes between aging cycles, specified in minutes. Default is 1 minute. If you use this procedure to change the aging cycle, the cycle is reset based on the time that this procedure is called. For example, if you call this procedure at 12:00 p.m. and specify a cycle of 15 minutes, aging occurs at 12:15, 12:30, 12:45, and so on.
If the cycle is set to a value of |
ttAgingLRUConfig
returns these results:
Column | Type | Description |
---|---|---|
lowUsageThreshold |
BINARY_FLOAT NOT NULL |
The current setting for the low end of percentage of database PermSize , specified in decimals. |
highUsageThreshold |
BINARY_FLOAT NOT NULL |
The current setting for the high end of percentage of database PermSize , specified in decimals. |
agingCycle |
TT_INTEGER NOT NULL |
The current setting for the number of minutes between aging cycles, specified in minutes. |
To set the aging threshold to a low of 75 percent and a high of 95 percent and the aging cycle to 5 minutes, use:
CALL ttAgingLRUConfig (.75, .90, 5); <.7500000, .9000000, 5>
To display the current LRU aging policy for all tables that defined with an LRU aging policy, call ttAgingLRUConfig
without any parameters:
Call ttAgingLRUConfig();
If the tables are defined with the default thresholds and aging cycle, the procedure returns:
<.8000000, .9000000, 1> 1 row found.
To change the low usage threshold to 60 percent, the aging cycle to 5 minutes and to retain the previous high usage threshold, use:
Call ttAgingLRUConfig (60,,5); < .6000000, .9000000, 5 > 1 row found.
The values of this procedure are persistent, even across system failures.
If no parameters are supplied, this procedure only returns the current LRU aging attribute settings.
This procedure starts the aging process, regardless of the value of the aging cycle. The aging process begins right after the procedure is called unless there is an aging process in progress. In that case, the new aging process begins when the aging process that was in process at the time the built-in was called has completed.
Aging occurs only once when you call this procedure. This procedure does not change any aging attributes. The previous aging state is unchanged. For example, if aging state is OFF
when you call ttAgingScheduleNow
, the aging process starts. When aging is complete, if your aging state is OFF
, aging does not continue. To continue aging, you must call ttAgingScheduleNow
again or change the aging state to ON
, in which case aging occurs next based on the value of the aging cycle.
For tables with aging ON
, the aging cycle is reset to the time when ttAgingScheduleNow
was called. For example, if you call this procedure at 12:00 p.m. and the aging cycle is 15 minutes, aging occurs immediately and again at 12:15, 12:30, 12:45, and so on.
If used in an external scheduler, such as a cron
job, or executed manually, this procedure starts the aging process at the time the procedure is executed, if there is no aging process in progress, or as soon as the current aging process has completed. In the case that you want aging to occur only when the external scheduler executes the ttAgingScheduleNow
procedure or you call it manually, set the aging state to OFF
.
Aging is performed by a background thread that wakes up every second to check if any work must be done. Calling ttAgingScheduleNow
only guarantees that the aging thread works on the specified tables within the next second, at best. If the aging thread is working on a different table at the time the built-in procedure is called, it may take some time to reach the specified table. The rows are visible until the aging thread commits the delete.
This procedure requires the DELETE
privilege on the table being aged, or the DELETE
ANY TABLE
privilege when you do not specify a table.
ttAgingScheduleNow
has the parameter:
Parameter | Type | Description |
---|---|---|
tblname |
TT_CHAR (61) |
The name of the table on which to start the aging process.
If Using a synonym to specify a table name is not supported. |
To schedule aging on all tables, including tables defined with both LRU aging and time-based aging, call ttAgingScheduleNow
without any parameter values:
CALL ttAgingScheduleNow ();
This examples creates the table agingex
with time-based aging policy and the aging state set to OFF
. ttAgingScheduleNow
is called, using the ttIsql
utility, to start the aging process once. Rows are deleted from the table. After ttAgingScheduleNow
is called, the aging state remains OFF
. To continue aging, alter the table and set the aging state to OFF
.
Command> CREATE TABLE agingex (col1 TT_INTEGER PRIMARY KEY NOT NULL, ts TIMESTAMP NOT NULL) AGING USE ts LIFETIME 1 MINUTES CYCLE 30 MINUTES OFF; Command> DESCRIBE agingex; Table TTUSER.AGINGEX: Columns: *COL1 TT_INTEGER NOT NULL TS TIMESTAMP (6) NOT NULL Aging use TS lifetime 1 minute cycle 30 minutes off 1 table found. (primary key columns are indicated with *) Command> INSERT INTO agingex VALUES (1, SYSDATE); 1 row inserted. Command> INSERT INTO agingex VALUES (2, SYSDATE); 1 row inserted. Command> SELECT * FROM agingex;
< 1, 2011-03-25 13:06:29.000000 > < 2, 2011-03-25 13:06:42.000000 > 2 rows found. Command> CALL ttAgingScheduleNow ('agingex'); Command> SELECT * FROM agingex; 0 rows found.
This procedure sets application-defined context for the next update record (either an UPDATE
or commit) to pass application specific data to XLA readers.
ttApplicationContext
has the parameter:
Parameter | Type | Description |
---|---|---|
cmd |
VARBINARY(16384) NOT NULL |
Context information to be passed to the XLA readers. |
This procedure returns a single row with information about the current or last backup of the database. If a backup is in progress, this information represents the current backup. If no backup is in progress, this information represents the last backup taken.
If no backup has been taken on the database since the last first-connect, the status field is 0
and the rest of the columns are NULL
.
ttBackupStatus
returns the results:
Column | Type | Description |
---|---|---|
status |
TT_INTEGER NOT NULL |
An INTEGER code representing the current progress of a backup or the completion status of the last backup. Values are:
|
destination |
TT_INTEGER |
The type of backup taken. The value is NULL when no backup has been taken on the database. Value is one of:
|
backupType |
TT_INTEGER |
Backup type, either full or incremental. The value is NULL when no backup has been taken on the database. Value is one of:
|
startTime |
TT_TIMESTAMP |
Time when the backup was started. The value is NULL when no backup has been taken on the database. |
endTime |
TT_TIMESTAMP |
Time when the backup completed. If NULL and startTime is non-NULL , a backup is currently in progress. |
backupLFN |
TT_INTEGER |
The transaction log file number of the backup point. The value is NULL when no backup has been taken on the database. |
backupLFO |
TT_BIGINT |
The transaction log file offset of the backup point. The value is NULL when no backup has been taken on the database. |
error |
TT_INTEGER |
If a backup fails, this column indicates the reason for the failure. The value is one of the TimesTen error numbers. The value is NULL when no backup has been taken on the database. |
processId |
TT_INTEGER |
The ID of the process or daemon performing the backup (if known). |
CALL ttBackupStatus (); < 2, 2, 1, 2005-08-12 13:10:32.587557, 2005-08-12 13:10:33.193269, 1, 1531840, 0, 6968 > 1 row found.
This procedure provides information about perm blocks and the amount of block-level fragmentation in a database.
ttBlockInfo
returns the result set:
Column | Type | Description |
---|---|---|
TotalBlocks |
TT_BIGINT NOT NULL |
Total number of blocks in the database. |
FreeBlocks |
TT_BIGINT NOT NULL |
Total number of free blocks in the database. |
FreeBytes |
TT_BIGINT NOT NULL |
Total size of the free blocks. |
LargestFree |
TT_BIGINT NOT NULL |
Size of the largest free block. |
This procedure returns information about the TimesTen transaction log. Records in the transaction log are identified by pairs of integers:
A transaction log file number.
An offset in that transaction log file.
Transaction log file numbers correspond to the file system names given to transaction log files. For example, the transaction log file SalesData.log29
has the transaction log file number 29
.
Three log records are identified in the result row of ttBookmark
:
The identity of the most recently written log record.
The identity of the log record most recently forced to the disk.
The replication bookmark. The replication bookmark is the oldest log record that represents an update not yet replicated to another system.
ttBookmark
returns the result set:
Column | Type | Description |
---|---|---|
writeLFN |
TT_INTEGER |
Last written transaction log file. |
writeLFO |
TT_BIGINT |
Last written offset in transaction log file. |
forceLFN |
TT_INTEGER |
Last transaction log file forced to disk. |
forceLFO |
TT_BIGINT |
Offset of last transaction log file forced to disk. |
holdLFN |
TT_INTEGER |
Replication bookmark transaction log file. |
holdLFO |
TT_BIGINT |
Replication bookmark log offset. |
The ttCacheAllowFlushAwtSet
built-in procedure enables you to execute a FLUSH CACHE GROUP
statement against an AWT cache group and should only be used in a specific recovery scenario, as described in “When there is unsynchronized data in the cache groups” section in the Oracle TimesTen Application-Tier Database Cache User's Guide.
Set auto commit to off before executing the ttCacheAllowFlushAwtSet
built-in procedure when setting the enableFlush
parameter to 1; otherwise, this parameter automatically resets to 0 directly after executing the built-in procedure. Then, perform a commit after you execute the FLUSH CACHE GROUP
statement and execute the ttCacheAllowFlushAwtSet
built-in procedure to reset the enableFlush
parameter back to 0.
ttCacheAllowFlushAwtSet
has the parameters:
Parameter | Type | Description |
---|---|---|
enableFlush |
TT_INTEGER |
0 - The user is prevented from executing a FLUSH CACHE GROUP statement against an AWT cache group, which is the intended restriction.
|
The following example shows how to execute the ttCacheAllowFlushAwtSet
built-in procedure to first allow and then disallow a FLUSH CACHE GROUP
statement to be executed against the marketbasket
AWT cache group.
Command> set autocommit off; Command> CALL ttCacheAllowFlushAwtSet(1); Command> FLUSH CACHE GROUP marketbasket; Command> CALL ttCacheAllowFlushAwtSet(0); Command> COMMIT;
The ttCacheAutorefIntervalStatsGet
built-in procedures returns statistical information about the last 10 autorefresh cycles for a particular autorefresh interval.
ttCacheAutorefIntervalStatsGet
has the parameters:
Parameter | Type | Description |
---|---|---|
autoRefInterval |
TT_BIGINT NOT NULL |
The autorefreshInterval designates the cache group (the one with this autorefresh interval value) on which to gather statistics.
The integer value for the autorefresh interval (in milliseconds) is the same value that was originally specified when the autorefresh cache group was created to indicate how often autorefresh is scheduled. |
isStatic |
TT_INTEGER |
Indicates if you are to retrieve information on static or dynamic cache groups with the interval value:
The default is static. |
ttCacheAutorefIntervalStatsGet
returns statistical information about the last 10 autorefresh cycles for a particular autorefresh interval:
Column | Type | Description |
---|---|---|
autorefInterval |
TT_BIGINT |
Autorefresh interval in milliseconds. |
isStatic |
TT_INTEGER |
Indicates that the information is for static or dynamic cache groups with the interval value:
|
autorefNumber |
TT_BIGINT |
Autorefresh number. |
startTimestamp |
TT_TIMESTAMP |
Autorefresh start time. |
selectLimit |
TT_BIGINT |
Select row limit set for incremental autorefresh cache group. |
numRows |
TT_BIGINT |
Number of rows refreshed. |
numOps |
TT_BIGINT |
Number of SQL operations executed. |
numCommits |
TT_BIGINT |
Number of commits. |
commitBufSize |
TT_BIGINT |
Maximum commit buffer size in bytes. |
commitBufMaxReached |
TT_BIGINT |
Amount of memory used for commit processing in bytes. |
commitBufNumOverflows |
TT_BIGINT |
Number of times the commit buffer overflowed for each transaction. |
totalNumRows |
TT_BIGINT |
Number of rows refreshed since the autorefresh thread was started. |
totalNumOps |
TT_BIGINT |
Number of SQL operations were executed since the autorefresh thread was started. |
totalNumCommits |
TT_BIGINT |
Number of commits since the autorefresh thread was started. |
totalNumRollbacks |
TT_BIGINT |
Number of rollbacks since the autorefresh thread started |
totalNumSnapshotOld |
TT_BIGINT |
Number of "Snapshot too old" errors received since the autorefresh thread started |
The following example shows how to execute ttCacheAutorefIntervalStatsGet
built-in procedure to retrieve statistics for autorefresh cache groups that have been defined as static and have the interval of seven seconds:
Command> call ttCacheAutorefIntervalStatsGet(7000,1); < 7000, 1, 41, 2013-04-25 15:17:00.000000, 0, 0, 0, 1, 0, 0, <NULL>, 132121, 132121, 13, 21, 0, 0, 0, 0 > < 7000, 1, 40, 2013-04-25 15:16:53.000000, 0, 0, 0, 1, 0, 0, <NULL>, 132121, 132121, 12, 21, 0, 0, 0, 0 > < 7000, 1, 39, 2013-04-25 15:16:46.000000, 0, 0, 0, 1, 0, 0, <NULL>, 132121, 132121, 11, 21, 0, 0, 0, 0 > < 7000, 1, 38, 2013-04-25 15:16:39.000000, 0, 0, 0, 1, 0, 0, <NULL>, 132121, 132121, 10, 21, 0, 0, 0, 0 > < 7000, 1, 37, 2013-04-25 15:16:32.000000, 0, 6305, 6305, 1, 0, 131072, <NULL>, 132121, 132121, 9, 21, 0, 0, 0, 0 > < 7000, 1, 36, 2013-04-25 15:16:24.000000, 0, 15616, 15616, 1, 0, 131072, <NULL>, 125816, 125816, 8, 21, 0, 0, 0, 0 > < 7000, 1, 35, 2013-04-25 15:16:17.000000, 0, 18176, 18176, 1, 0, 131072, <NULL>, 110200, 110200, 7, 21, 0, 0, 0, 0 > < 7000, 1, 34, 2013-04-25 15:16:10.000000, 0, 14336, 14336, 1, 0, 131072, <NULL>, 92024, 92024, 6, 21, 0, 0, 0, 0 > < 7000, 1, 33, 2013-04-25 15:16:03.000000, 0, 15360, 15360, 1, 0, 131072, <NULL>, 77688, 77688, 5, 21, 0, 0, 0, 0 > < 7000, 1, 32, 2013-04-25 15:15:56.000000, 0, 11520, 11520, 1, 0, 131072, <NULL>, 62328, 62328, 4, 21, 0, 0, 0, 0 > 10 rows found.
ttCacheAutorefreshSelectLimit
ttCacheAutorefreshXactLimit
This procedure starts an immediate autorefresh on the set of cache groups that are associated by sharing the same autorefresh interval with the specified cache group. This set of associated cache groups would normally be refreshed together automatically. The effect on the autorefresh process is the same as that of adding a new cache group with the same refresh interval as that of the specified cache group. This procedure is useful if updates have occurred on the Oracle database and you would like to refresh them on the cache group before the next scheduled autorefresh.
If there is an existing transaction with locks on table objects that belong to the set of cache groups to be autorefreshed, this procedure returns an error without taking any action. This procedure establishes a condition that requires that you commit or rollback before you can perform other work in the session.
ttCacheAutorefresh
has the parameters:
Parameter | Type | Description |
---|---|---|
cgOwner |
VARCHAR2 (30) |
Name of the cache group owner. |
cgName |
VARCHAR2 (30) NOT NULL |
Name of the cache group. |
synchronous |
TT_INTEGER |
Species whether data is updated on synchronously or asynchronously.
|
This example autorefreshes the testcache
cache group and all cache groups with the same autorefresh interval. The procedure returns synchronously.
Command> call ttcacheautorefresh('user1','testcache', 1);
The specified cache group AUTOREFRESH
state must be ON
. While, other associated cache groups can be in any state, they are not refreshed if they are not in the autorefresh ON
state.An autorefresh of the specified associated cache groups cannot be in progress.You cannot call this procedure on the standby node of an active standby pair.
This procedure is available only for TimesTen Cache.
The ttCacheAutorefreshLogDefrag
built-in procedure compact the trigger log space for a cache autorefresh table.
For usage details, see “Defragmenting change log tables in the tablespace” in the Oracle TimesTen Application-Tier Database Cache User's Guide.
ttCacheAutorefreshLogDefrag
has the parameters:
Parameter | Type | Description |
---|---|---|
action |
VARCHAR (50) NOT NULL |
Acceptable values are:
NOTE: The reclaim phase takes a lock on the trigger log table for a brief moment. This can suspend the workload from writing into the base table. |
In this example, the call compacts or defragments only the trigger log space.
Command> call ttCacheAutorefreshLogDefrag('CompactOnly');
This procedure returns information about the last ten autorefresh transactions on the specified cache group. This information is only available when the AUTOREFRESH
state is ON
or PAUSED
, and the cache agent is running.
The information returned by this built-in procedure is reset whenever:
The cache agent is restarted
The state is set to OFF
and then back to ON
or PAUSED
The cache group is dropped and recreated
ttCacheAutorefreshStatsGet
has the parameters:
Parameter | Type | Description |
---|---|---|
cgOwner |
VARCHAR2 (30) |
Name of the cache group owner. |
cgName |
VARCHAR2 (30) NOT NULL |
Name of the cache group for which autorefresh information should be returned. |
The ttCacheAutorefreshStatsGet
built-in procedure returns only a subset of column information for a cache group with autorefresh mode FULL
. A column value of 0
returns for information that is not available.
ttCacheAutorefreshStatsGet
returns the results:
Column name | Column type | Description | Returned for full autorefresh |
---|---|---|---|
cgId |
TT_BIGINT |
The cache group ID. | Y |
startTimestamp |
TT_TIMESTAMP |
Timestamp when autorefresh started for this interval. See "Notes" below. | Y |
cacheAgentUpTime |
TT_BIGINT |
Number of cache agent clock ticks in milliseconds at the time the autorefresh transaction started for this interval. This value is cumulative and is reset when the cache agent process starts. See "Notes" below. | Y |
autorefNumber |
TT_BIGINT |
Autorefresh number for a cache group indicates the number of times this cache group has been incrementally refreshed since the cache agent started. This number is initialized to 0 when the cache agent is started. |
Y |
autorefDuration |
TT_BIGINT |
The number of milliseconds spent in this autorefresh transaction. | Y |
autorefNumRows |
TT_BIGINT |
The number of rows autorefreshed in this autorefresh. This includes all rows, including those in the root table and the child tables.
If there are cache groups with multiple tables, child table rows get updated multiple times. Therefore, the number of rows autorefreshed may be more than the number of rows updated on the Oracle database. |
N |
numOracleBytes |
TT_BIGINT |
The number of bytes transferred from the Oracle database in this autorefresh transaction. | N |
autorefNumRootTblRows |
TT_BIGINT |
The number of root table rows autorefreshed in this autorefresh transaction. | Y |
autorefQueryExecDuration |
TT_BIGINT |
The duration in milliseconds that it takes for the autorefresh query to execute on the Oracle database. | N |
autorefQueryFetchDuration |
TT_BIGINT |
The duration in milliseconds that it takes for the autorefresh query to fetch rows from the Oracle database. | N |
autorefTtApplyDuration |
TT_BIGINT |
The duration in milliseconds that it takes for TimesTen to apply the autorefresh. | N |
totalNumRows |
TT_BIGINT |
The total number of rows autorefreshed since the cache agent started.
The total number of rows autorefreshed may not be the same as number of rows updated on the Oracle database. This is because of a delay in marking the log; some updates may get autorefreshed and counted multiple times. |
N |
totalNumOracleBytes |
TT_BIGINT |
The total number of bytes transferred from the Oracle database since the cache agent started. | N |
totalNumRootTblRows |
TT_BIGINT |
The total number of root table rows autorefreshed since the cache agent started. | Y |
totalDuration |
TT_BIGINT |
The total autorefresh duration in milliseconds since the cache agent started. | Y |
status |
VARCHAR2 (128) |
A string description of the status of the current autorefresh. See "Notes" below. Supported values for this field are:
|
Y |
numlogrows |
TT_BIGINT |
Number of rows fetched from the Oracle database in this autorefresh. | Y |
totalnumlogrows |
TT_BIGINT |
The cumulative number of rows fetched from the Oracle database in this autorefresh. | Y |
autorefLogFragmentationPct |
TT_BIGINT |
A low-water mark for table usage by percentage. If less than the specified percent of the table is used, the table is compacted. | Y |
autorefLogFragmentationTs |
TT_TIMESTAMP |
The timestamp when the last utilization/ fragmentation ratio was calculated | Y |
autorefLogDefragGcnt |
TT_BIGINT |
The number of times the table has been compacted. | Y |
In this example, testcache
is a READONLY
cache group with one table and an incremental autorefresh interval of 10 seconds.
Command> call ttcacheautorefreshstatsget('user1','testcache'); < 1164260, 2011-07-23 15:43:52.000000, 850280, 44, 0, 75464, 528255, 75464, 310, 110, 6800, 1890912, 12439795, 1890912, 160020, InProgress, 2, 74 > < 1164260, 2011-07-23 15:43:33.000000, 831700, 43, 13550, 108544, 759808, 108544, 1030, 230, 12290, 1815448, 11911540, 1815448, 160020, Complete, 2, 72 > < 1164260, 2011-07-23 15:43:12.000000, 810230, 42, 17040, 115712, 809984, 115712, 610, 330, 16090, 1706904, 11151732, 1706904, 146470, Complete, 2, 70> < 1164260, 2011-07-23 15:42:52.000000, 790190, 41, 14300, 94208, 659456, 94208,560, 320, 13410, 1591192, 10341748, 1591192, 129430, Complete, 2, 68 > < 1164260, 2011-07-23 15:42:32.000000, 770180, 40, 12080, 99328, 695296, 99328,450, 290, 11340, 1496984, 9682292, 1496984, 115130, Complete, 2, 66 > < 1164260, 2011-07-23 15:42:12.000000, 750130, 39, 10380, 86016, 598368, 86016,430, 230, 9720, 1397656, 8986996, 1397656, 103050, Complete, 2, 64 > < 1164260, 2011-07-23 15:41:52.000000, 730130, 38, 13530, 112640, 700768, 112640, 530, 220, 12780, 1311640, 8388628, 1311640, 92670, Complete, 2, 62 > < 1164260, 2011-07-23 15:41:32.000000, 710120, 37, 9370, 56320, 326810, 56320, 310, 160, 8900, 1199000, 7687860, 1199000, 79140, Complete, 2, 60 > < 1164260, 2011-07-23 15:41:22.000000, 700120, 36, 2120, 10240, 50330, 10240, 50, 200, 1870, 1142680, 7361050, 1142680, 69770, Complete, 2, 58 > < 1164260, 2011-07-23 15:41:12.000000, 690110, 35, 0, 0, 0, 0, 0, 0, 0, 1132440, 7310720, 1132440, 67650, Complete, 2, 56 > 10 rows found.
Most of the column values reported above are collected at the cache group level. For example, autorefDuration
and autorefNumRows
only include information for the specified cache group. Exceptions to this rule are column values cacheAgentUpTime, startTimestamp
and autorefreshStatus
. These values are reported at the autorefresh interval level.
StartTimestamp
is taken at the beginning of the autorefresh for the autorefresh interval. A cache group enters the in progress
state as soon as the autorefresh for the interval starts. It is not marked complete
until the autorefresh for all cache groups in the interval are complete.
This procedure is available only for TimesTen Cache.
Configuring the incremental autorefresh to join the Oracle database base table with a limited number of rows from the autorefresh change log table is known as configuring a select limit. This is accomplished with the ttCacheAutorefreshSelectLimit
built-in procedure.
ttCacheAutorefreshSelectLimit
has the parameters:
Parameter | Type | Description |
---|---|---|
autorefreshInterval |
TT_VARCHAR(30) NOT NULL |
The autorefreshInterval designates the cache group (the one with this autorefresh interval value) on which to apply the value .
The integer value for the autorefresh interval (in milliseconds) is the same value that was originally specified when the autorefresh cache group was created to indicate how often autorefresh is scheduled. |
value |
TT_VARCHAR(30) |
The value denotes a limit of the number of rows to select from the autorefresh change log file to apply to the cached table. These changes are applied incrementally until all the rows in the autorefresh change log table have been applied.
If the value changes, it takes effect at the start of the next autorefresh cycle. The
|
ttCacheAutorefreshSelectLimit
returns the select limit value that has been set for a particular autorefresh interval:
Column | Type | Description |
---|---|---|
autorefreshInterval |
TT_VARCHAR(30) |
The autorefreshInterval that designates the cache group (the one with this autorefresh interval value). |
value |
TT_VARCHAR(30) |
The current value that shows the number of rows that is selected from the autorefresh change log file to apply to the cached table. |
You can show the current setting by either providing a NULL
value or no parameter. The following example shows the setting for incremental autorefresh cache groups with an interval value of 7 seconds.
Command> call ttCacheAutorefreshSelectLimit('7000', NULL); < 7000, 2000 > 1 row found. Command> call ttCacheAutorefreshSelectLimit('7000'); < 7000, 2000 > 1 row found.
The following example set a select limit to 2000 rows for incremental autorefresh cache groups with an interval value of 7 seconds.
Command> call ttCacheAutorefreshSelectLimit('7000', '2000'); < 7000, 2000 > 1 row found.
This procedure is available only for TimesTen Cache.
The ttCacheAutotrefreshSelectLimit
built-in procedure can set a select limit only on an interval that is defined for a single cache group that contains one table, where the cache group is defined as a static read-only cache group with incremental autorefresh.
The setting for ttCacheAutorefreshSelectLimit
is not replicated or duplicated. The user must execute the built-in on both the active and standby nodes.
The settings do not reset if you drop all cache groups for the interval.
The ttMigrate
, ttBackup
, and ttRestore
built-in procedures do not preserve the setting of ttCacheAutorefreshSelectLimit
.
If you alter the cache group autorefresh interval, it does not modify what was set previously through execution of ttCacheAutorefreshSelectLimit
for the cache group. You can only alter the select limit for the cache group with the ttCacheAutorefreshSelectLimit
built-in procedure.
ttCacheAutorefIntervalStatsGet
This procedure starts an immediate autorefresh on single table cache groups within a specified autorefresh interval and commits after the specified number of operations.
This procedure is useful if updates have occurred on the Oracle database and you want to refresh them on the cache group before the next scheduled autorefresh.
To modify the reclaim buffer size, use the ttDBConfig
built-in procedure.
ttCacheAutorefreshXactLimit
has the parameters:
Parameter | Type | Description |
---|---|---|
intervalValue |
VARCHAR2 (50) NOT NULL |
Indicates the interval at which the autorefresh cache groups are defined to occur in units of milliseconds. IntervalValue is an integer value in milliseconds that was specified when the autorefresh cache group was created on how often autorefresh is scheduled. |
value |
VARCHAR2 (200) |
The Value can be one of the following:
|
ttCacheAutorefreshXactLimit
returns the results:
Column | Type | Description |
---|---|---|
intervalValue |
VARCHAR2 (50) NOT NULL |
The interval at which the autorefresh cache groups are defined to occur in units of milliseconds. |
value |
VARCHAR2 (200) |
The Value can be one of the following:
|
The following example sets up the transaction limit to commit after every 256 operations for all incremental autorefresh read-only cache groups that are defined with an interval value of 10 seconds.
call ttCacheAutorefreshXactLimit('10000', 'ON');
After the month end process has completed and the incremental autorefresh read-only cache groups are refreshed, disable the transaction limit for incremental autorefresh read-only cache groups that are defined with the interval value of 10 seconds.
call ttCacheAutorefreshXactLimit('10000', 'OFF');
To enable the transaction limit for incremental autorefresh read-only cache groups to commit after every 2000 operations, provide 2000 as the value as follows:
call ttCacheAutorefreshXactLimit('10000', '2000');
This procedure is available only for TimesTen Cache. This built-in procedure only applies for static read-only cache groups with incremental autorefresh.
While autorefresh is in-progress and is being applied in several small transactions, transactional consistency cannot be maintained. Once the autorefresh cycle has completed, the data is transactional consistent.
The setting for ttCacheAutorefreshXactLimit
is not replicated or duplicated. The user must execute the built-in procedure on both the active and standby nodes.
The settings do not reset if you drop all cache groups for the interval.
The ttMigrate
, ttBackup
, and ttRestore
built-in procedures do not preserve the setting of ttCacheAutorefreshXactLimit
.
If you alter the cache group autorefresh interval, it does not modify the setting of ttCacheAutorefreshXactLimit
.
ttCacheAutorefIntervalStatsGet
This procedure enables monitoring to determine the amount of time spent in each component of the workflow of an AWT cache group. To display the monitoring results, use the ttRepAdmin
utility with the -awtmoninfo
and -showstatus
commands.
If the replication agent is restarted, monitoring is turned off. Setting the monitoring state to OFF
resets the internal counters of the monitoring tool.
Run this procedure on the replication node that is replicating AWT changes to the Oracle database. If the active standby pair is functioning normally, the node replicating AWT changes is the standby. If the active is operating standalone, the node replicating AWT changes is the active.
If a failure occurs on the node where the active database resides, the standby node becomes the new active node. In that case you would run this procedure on the new active node.
ttCacheAWTMonitorConfig
has the optional parameters:
Parameter | Type | Description |
---|---|---|
state |
TT_CHAR(10) |
Enables and disables AWT monitoring. Its value can be ON or OFF . Default is OFF |
samplingRate |
TT_INTEGER |
Positive integer that specifies the frequency with which the AWT workflow is sampled. If samplingRate is set to 1 , every AWT operation is monitored. Greater values indicate less frequent sampling. The value recommended for accuracy and performance is 16 . If state is set to ON , the default for samplingRate is 16. If state is set to OFF , the default for samplingRate is 0 . |
ttCacheAWTMonitorConfig
returns the following result if you do not specify any parameters. It returns an error if the replication agent is not running or if an AWT cache group has not been created.
Column | Type | Description |
---|---|---|
state |
TTVARCHAR (10) NOT NULL |
Current state of AWT monitoring. The value can be ON or OFF . |
AWTSamplingFactor |
TT_INTEGER NOT NULL |
Positive integer that specifies the frequency with which the AWT workflow is sampled. |
Retrieve the current state and sampling factor when monitoring is disabled.
Command> CALL ttCacheAWTMonitorConfig; < OFF, 0 > 1 row found.
Enable monitoring and set the sampling frequency to 16.
Command> CALL ttCacheAWTMonitorConfig ('ON', 16); < ON, 16 > 1 row found.
This procedure returns the current transaction log file threshold for databases that include AWT cache groups.
ttCacheAWTThresholdGet
returns the result:
Column | Type | Description |
---|---|---|
threshold |
TT_INTEGER NOT NULL |
The number of transaction log files for all AWT cache groups associated with the database. If the result is 0 , there is no set limit. |
This procedure sets the threshold for the number of transaction log files that can accumulate before AWT is considered either terminated or too far behind to catch up. This setting applies to all subscribers to the database. When the threshold is exceeded, updates are no longer sent to the Oracle database. If no threshold is set then the default is zero.
Using this built-in procedure, the threshold can be set after an AWT cache group has been created.
This setting can be overwritten by a CREATE REPLICATION
statement that resets the Log Failure Threshold for the database.
ttCacheAWTThresholdSet
has the parameter:
Parameter | Type | Description |
---|---|---|
threshold |
TT_INTEGER |
Specifies the number of transaction log files for all AWT cache groups associated with the database. If the threshold is NULL , the log failure threshold is set to zero. |
To set the threshold to allow 12 transaction log files to accumulate, use:
CALL ttCacheAWTThresholdSet(12);
This procedure is available for TimesTen Cache.
The user is responsible to recover when the threshold is exceeded.
The ttCacheCheck
built-in procedure performs a check for missing constraints for cached tables on the Oracle database.
Any unique index, unique constraint, or foreign key constraint on columns in Oracle Database tables that are to be cached should also be created on asynchronous writethrough cache tables within TimesTen. If you have not created these constraints on the AWT cache tables and you have configured the cache group for parallel propagation, TimesTen serializes any transactions with DML operations to those tables with missing constraints.
This procedure provides information about missing constraints and the tables marked for serialized propagation.
Call ttCacheCheck
to manually check for missing constraints, under these conditions:
After completing a series of DROP CACHE GROUP
statements.
After creating or dropping a unique index or foreign key on the Oracle database.
To determine why some transactions are being serialized.
This procedure updates system tables to indicate if DML executed against a table should or should not be serialized, therefore you must commit or roll back after the ttCacheCheck
built-in completes.
For more details on parallel propagation, see “Configuring parallel propagation to Oracle Database tables” in the Oracle TimesTen Application-Tier Database Cache User's Guide.
ttCacheCheck
has these parameters:
Parameter | Type | Description |
---|---|---|
operation |
TT_VARCHAR(30) |
Specifies the constraint to be checked. Legal values are:
|
cgOwner |
TT_VARCHAR(30) |
Specifies the owner of the cached Oracle database table.
If If both |
cgName |
TT_VARCHAR(30) |
Specifies the name of the cached Oracle database table.
If If both |
ttCacheCheck
returns the result set:
Column | Type | Value |
---|---|---|
cgOwner |
TT_VARCHAR(30) NOT NULL |
The owner of the cache group. |
cgName |
TT_VARCHAR(30) NOT NULL |
The name of the cache group. |
tblOwner |
TT_VARCHAR(30) |
The owner of the table. |
tblName |
TT_VARCHAR(30) |
The name of the table. |
objectType |
TT_VARCHAR(15) |
The type of Oracle object: unique index, constraint or foreign key. |
objectOwner |
TT_VARCHAR(30) |
The owner of the Oracle object. |
objectName |
TT_VARCHAR(30) |
The object name. |
msgType |
TT_SMALLINT NOT NULL |
The type of message:
|
msg |
TT_VARCHAR(100000) NOT NULL |
Message describing the issue. |
objectDesc |
VARCHAR2(200000) |
A description of the object. If the object is AWT checking, the description is the SQL statement that describes the object. |
The following example determines if there are any missing constraints for the cache group update_orders
that is owned by cacheuser
. A result set is returned that includes the warning message. The ordertab
table in the update_orders
cache group is marked for serially propagated transactions.
Command> call ttCacheCheck( NULL, 'cacheuser', 'update_orders'); < CACHEUSER, UPDATE_ORDERS, CACHEUSER, ORDERTAB, Foreign Key, CACHEUSER, CUST_FK, 1, Transactions updating this table will be serialized to Oracle because: The missing foreign key connects two AWT cache groups., table CACHEUSER.ORDERTAB constraint CACHEUSER.CUST_FK foreign key(CUSTID) references CACHEUSER.ACTIVE_CUSTOMER(CUSTID) > 1 row found.
For all cache groups that cache data from the same Oracle instance, this procedure specifies a timeout value and recovery policies in the case that the Oracle database server is unreachable and the cache agent or database is considered terminated.
The automatic refresh state of the database and cache groups can be determined from the procedure ttCacheDbCgStatus
.
ttCacheConfig
has these parameters:
Parameter | Type | Description |
---|---|---|
Param |
VARCHAR2(50) NOT NULL |
Specifies the parameter to be set by Value :
|
tblOwner |
VARCHAR2(30) |
Specifies the owner of the cached Oracle database table.
This parameter is required if A synonym cannot be used to specify a table name. |
tblName |
VARCHAR2(30) |
Specifies the name of the cached Oracle database table.
This parameter is required if Using a synonym to specify a table name is not supported. |
Value |
VARCHAR2(200) |
Specifies the value to be set for Param .
Or Specifies the value to be set by
|
ttCacheConfig
returns no results when an application uses it to set parameter values. When it is used to return parameter settings, ttCacheConfig
returns the following results.
Column | Type | Value |
---|---|---|
Param |
VARCHAR2(50) |
Parameter name:
|
tblOwner |
VARCHAR2(30) |
Owner of the cached Oracle database table. |
tblName |
VARCHAR2(30) |
Name of the cached Oracle database table.
Using a synonym to specify a table name is not supported. |
Value |
VARCHAR2(200) |
Specifies the value set for Param .
|
To set the cache agent timeout to 600 seconds (10 minutes), enter:
CALL ttCacheConfig('AgentTimeout',,,'600');
To determine the current cache agent timeout setting, enter:
CALL ttCacheConfig('AgentTimeout'); < AgentTimeout, <NULL>, <NULL>, 600 > 1 row found.
To set the recovery method to Manual
for cache groups whose automatic refresh status is dead
, enter:
CALL ttCacheconfig('DeadDbRecovery',,,'Manual');
Configure the TimesTen Cache to prevent an automatic full refresh and receive an Oracle database error when there is an update on a cached Oracle database table while the cache administration user's tablespace is full. The Oracle database table is terry.customer
.
CALL ttCacheConfig('TblSpaceFullRecovery','terry','customer','None');
To determine the current setting for TblSpaceFullRecovery
on the terry.customer
cached Oracle database table, enter:
CALL ttCacheConfig('TblSpaceFullRecovery','terry','customer'); < TblSpaceFullRecovery, TERRY, CUSTOMER, none > 1 row found.
To configure a warning to be returned when the cache administration user's tablespace is 85 percent full and an update operation occurs on the cached Oracle database table, enter:
CALL ttCacheConfig('AutoRefreshLogTblSpaceUsagePCT',,,'85');
When working in an Oracle RAC environment, the following shows how to retrieve the value of the failover timeout:
Command> call ttCacheConfig('AgentFailoverTimeout'); < AgentFailoverTimeout, <NULL>, <NULL>, 4 > 1 row found. .
The following sets the failover timeout to 5 minutes:
Command> call ttCacheConfig('AgentFailoverTimeout',,,5); < AgentFailoverTimeout, <NULL>, <NULL>, 5 > 1 row found. Command>
This procedure is available only for TimesTen Cache.
You must call the ttCacheConfig
built-in procedure from every node in a cache grid or a active standby pair.
ttCacheDbCgStatus
ttCachePolicyGet
ttCachePolicySet
ttCacheStart
ttCacheStop
ttCacheUidGet
ttCacheUidPwdSet
This procedure returns the automatic refresh status of the database and the specified cache group. If you do not specify any values for the parameters, the procedure returns the automatic refresh status for the database.
ttCacheDbCgStatus
has these optional parameters:
Parameter | Type | Description |
---|---|---|
cgOwner |
VARCHAR2(30) |
Specifies the user name of the cache group owner. |
cgName |
VARCHAR2(30) |
Specifies the cache group name. |
ttCacheDbCgStatus
returns the result:
Column | Type | Value |
---|---|---|
dbStatus |
VARCHAR2(20) |
Specifies the autorefresh status of all the cache groups in the database. The status is one of:
|
cgStatus |
VARCHAR2(20) |
Specifies the autorefresh status of the specified cache group. The status is one of:
|
This example shows that the automatic refresh status of the database is alive
. The automatic refresh status of the cache group is ok
.
CALL ttCacheDbCgStatus ('terry', 'cgemployees'); < alive, ok > 1 row found.
To determine the automatic refresh status of the database, call ttCacheDbCgStatus
with no parameters:
CALL ttCacheDbCgStatus; < dead, <NULL> > 1 row found.
This procedure enables or disables tracking of DDL statements issued on cached Oracle database tables. By default, DDL statements are not tracked.
DDL tracking saves the change history for all the cached Oracle database tables. One DDL tracking table is created to store DDL statements issued on any cached Oracle database table. You can use this information to diagnose autorefresh problems.
See “Tracking DDL statements issued on cached Oracle Database tables” in Oracle TimesTen Application-Tier Database Cache User's Guide.
ttCacheDDLTrackingConfig
has the parameter:
Parameter | Type | Description |
---|---|---|
trackingStatus |
TT_VARCHAR(10) |
Specifies whether DDL statements issued on cached Oracle database tables are tracked. Valid values are:
|
This procedure returns the current policy used to determine when the TimesTen cache agent for the connected database should run. The policy can be either always
or manual
.
ttCachePolicyGet
returns the result:
Column | Type | Value |
---|---|---|
cachePolicy |
TT_VARCHAR(10) |
Specifies the policy used to determine when the TimesTen cache agent for the database should run. Valid values are:
|
The procedure defines the policy used to determine when the TimesTen cache agent for the connected database should run. The policy can be either always
or manual
.
ttCachePolicySet
has these parameters:
Parameter | Type | Description |
---|---|---|
cachePolicy |
TT_VARCHAR(10) NOT NULL |
Specifies the policy used to determine when the TimesTen cache agent for the database should run. Valid values are:
|
To set the policy for TimesTen cache agent to always
, use:
CALL ttCachePolicySet ('always');
This procedure is available only for TimesTen Cache.
If you attempt to start the TimesTen cache agent (by changing the policy from manual
to always
) for a database with a relative path, TimesTen looks for the database relative to where TimesTen Data Manager is running, and fails. For example, on Windows, if you specify the path for the database as DataStore=./payroll
and attempt to start the TimesTen cache agent with this built-in procedure, the agent is not started because TimesTen Data Manager looks for the database in the install_dir
\srv
directory. On UNIX, TimesTen Data Manager looks in the /var/TimesTen/
instance
directory.
Successfully setting the policy to always
automatically starts the cache agent if it was stopped.
This procedure enables you to disable propagation of committed updates (the result of executing DML statements) within the current transaction to the Oracle database. Any updates from executing DML statements after the flag is set to zero are never propagated to the back-end Oracle database. Thus, these updates exist only on the TimesTen database. You can then re-enable propagation for DML statements by resetting the flag.
ttCachePropagateFlagSet
has the parameter:
Parameter | Type | Description |
---|---|---|
CommitsOn |
TT_INTEGER NOT NULL |
If 0 , sets a flag to stop updates from being sent to the Oracle database. The flag remains set until the end of the transaction or until the procedure is set to 1 .
If |
This procedure is available only for TimesTen Cache.
If the value of ttCachePropagateFlagSet
is reenabled several times during a single transaction, the transaction is only partially propagated to the Oracle database.
ttCachePropagateFlagSet
is the only built-in procedure that applications can use in the same transaction as any of the other cache group operation, such as FLUSH
, LOAD
, REFRESH
and UNLOAD
.
The propagate flag is reset after a commit or rollback.
When using this procedure, it is important to turn off AutoCommit, otherwise after the procedure is called the transaction ends and propagation to the Oracle database is turned back on.
This example sets autocommit
off to prevent the propagation flag from toggling from off to on after a commit. Calls the ttCachePropagateFlagSet
to turn off propagation. A row is inserted into the TimesTen Cache detail table for oratt.writetab
. Then, propagation is re-enabled by calling the ttCachePropagateFlagSet
built-in procedure and setting the flag to one.
Command> set autocommit off; Command> call ttCachePropagateFlagSet(0); Command> INSERT INTO oratt.writetab VALUES (103, 'Agent'); 1 row inserted. Command> COMMIT; Command> SELECT * FROM oratt.writetab; < 100, Oracle > < 101, TimesTen > < 102, Cache > < 103, Agent > 4 rows found. Command> call ttCachePropagateFlagSet(1);
When you select all rows on the Oracle database, the row inserted when propagation was turned off is not present in the oratt.writetab
table on Oracle.
Command> set passthrough 3; Command> SELECT * FROM oratt.writetab; < 100, Oracle > < 101, TimesTen > < 102, Cache > 3 rows found.
This procedure generates the Oracle SQL statements to install or uninstall Oracle database objects for:
Read-only cache groups
User managed cache groups with incremental autorefresh
Asynchronous writethrough (AWT) cache groups
This is useful when the user creating the cache group does not have adequate privilege to write on the Oracle database. The Oracle DBA can then use the script generated by this built-in procedure to create the Oracle database objects.
ttCacheSqlGet
has these parameters:
Parameter | Type | Description |
---|---|---|
feature_name |
TT_VARCHAR (100) |
Can be specified as INCREMENTAL_AUTOREFRESH or ASYNCHRONOUS_WRITETHROUGH . |
cache_group_name |
TT_VARCHAR (100) |
The name of the cache group. Specify NULL when installing objects for asynchronous writethrough cache groups or to uninstall all Oracle database objects in the autorefresh user's account. |
install_flag |
TT_INTEGER NOT NULL |
If install_flag is 1, ttCacheSqlGet returns Oracle SQL to install the autorefresh or asynchronous writethrough Oracle database objects.
If |
ttCacheSqlGet
returns the result set:
Column | Type | Description |
---|---|---|
retval |
TT_VARCHAR (4096) NOT NULL |
The Oracle SQL statement to uninstall or install autorefresh or asynchronous writethrough Oracle database objects. |
continueFlag |
TT_SMALLINT NOT NULL |
nonzero only if the Oracle SQL statement in the retval result column exceeds 4096 bytes and must be continued into the next result row. |
CALL ttCacheSqlGet('INCREMENTAL_AUTOREFRESH', 'westernCustomers', 1);
To remove all Oracle database objects in the autorefresh user's account, use:
CALL ttCacheSqlGet('INCREMENTAL_AUTOREFRESH', NULL, 0);
This procedure is available only for TimesTen Cache.
Each returned retval
field contains a separate Oracle SQL statement that may be directly executed on the Oracle database. A row may end in the middle of a statement, as indicated by the continueFlag
field. In this case, the statement must be concatenated with the previous row to produce a usable SQL statement.
The script output of this procedure is not compatible with Oracle's SQL*Plus utility. However, you can use the ttIsql
cachesqlget
command to generate a script that is compatible with the SQL*Plus utility.
You can specify NULL
for the cache_group_name
option to generate Oracle SQL to clean up Oracle database objects after a database has been destroyed by the ttDestroy
utility.
This procedure is available only for TimesTen Cache.
The cache administration user ID and password must be set with the ttCacheUidPwdSet
built-in procedure before starting the cache agent when there are or might be autorefresh or asynchronous writethrough cache groups in the database.
If you attempt to start the TimesTen cache agent (by changing the policy from manual to always) for a database with a relative path, TimesTen looks for the database relative to where the TimesTen Data Manager is running, and fails. For example, on Windows, if you specify the path for the database as DataStore=./payroll
and attempt to start the TimesTen cache agent with this built-in procedure, the agent is not started because TimesTen Data Manager looks for the database in the \srv
directory. On UNIX, the TimesTen Data Manager looks in the /var/TimesTen/
instance
directory.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies database-level locking (LockLevel=1
).
ttCacheStop
has the parameter:
Parameter | Type | Description |
---|---|---|
timeout |
TT_INTEGER |
Specifies that the TimesTen daemon should stop the cache agent if it does not stop within timeout seconds. If set to 0 , the daemon waits forever for the cache agent. The default value is 100 . |
This procedure is available only for TimesTen Cache.
Do not shut down the cache agent immediately after dropping or altering a cache group. Instead, wait for at least two minutes. Otherwise, the cache agent may not get a chance to clean up the Oracle database objects that were used by the AUTOREFRESH
feature.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies database-level locking (LockLevel=1
).
This procedure returns the cache administration user ID for the database. If the cache administration user ID and password have not been set for the database with the ttCacheUidPwdSet
built-in procedure, ttCacheUidGet
returns NULL
.
ttCacheUidGet
returns the results:
Column | Type | Description |
---|---|---|
UID |
TT_VARCHAR (30) |
The current cache administration user ID, used for autorefresh and asynchronous writethrough cache groups. |
This procedure sets the cache administration user ID and password. You only need to specify the cache administration user ID and password once for each new database. The cache administration password can be changed at any time.
ttCacheUidPwdSet
has these parameters:
Parameter | Type | Description |
---|---|---|
UID |
TT_VARCHAR (30) |
The cache administration user ID, used for autorefresh and asynchronous writethrough cache groups. |
PWD |
TT_VARCHAR (30) |
The password for the cache administration user. |
This procedure cannot be called from a Client/Server connection.
This procedure is available only for TimesTen Cache.
For all levels of DDLReplicationLevel
, you can set the cache administration user ID and password while the cache or replication agents are running. For more details on changing the cache administration user ID or password, see “Changing cache user names and passwords” in the Oracle TimesTen Application-Tier Database Cache User's Guide.
The cache administration user ID cannot be reset while there are cache groups on the database. The cache administration password can be changed at any time.
This procedure performs a nonblocking checkpoint operation. For information on blocking checkpoints, see "ttCkptBlocking". A checkpoint operation makes a record of the current state of the database on disk and to purge transaction log files. A nonblocking checkpoint does not require any locks on the database.
Applications should checkpoint databases periodically either by setting the background checkpointing attributes (CkptFrequency
and CkptLogVolume
) or by explicitly calling this procedure. Applications can call this procedure asynchronously to any other application running on the database.
By default, TimesTen performs background checkpoints at regular intervals.
In the case that your application attempts to perform a checkpoint operation while a backup is in process, the backup waits until the checkpoint finishes. Regardless of whether the checkpoint is a background checkpoint or an application-requested checkpoint, the behavior is:
If a backup or checkpoint is running and you try to do a backup, it waits for the running backup or checkpoint to finish.
If a backup or checkpoint is running and you try to do a checkpoint, it does not wait. It returns an error immediately.
To turn off background checkpointing, set CkptFrequency=0
and CkptLogVolume=0
.
When a database crashes, and the checkpoints on disk are nonblocking checkpoints, TimesTen uses the log to recover.
ttCkpt
has these optional parameters:
Parameter | Type | Description |
---|---|---|
timeout |
TT_INTEGER |
The time (in seconds) that ttCkpt should wait to get a database lock before timing out. The value of timeout can be between 0 and one million, inclusively. If not specified, the checkpoint never times out. |
retries |
TT_INTEGER |
The number of times that ttCkpt should attempt to get a database lock, if timeouts occur. The value of retries can be between 0 and 10 , inclusive. If not specified, defaults to zero. |
For a description of checkpoints, see “Transaction Management” in Oracle TimesTen In-Memory Database Operations Guide.
This procedure performs a blocking checkpoint operation. A checkpoint operation makes a record of the current state of the database on disk, and to purge transaction log files. This checkpoint requires exclusive access to the database, and so may cause other applications to be blocked from the database while the checkpoint is in progress.
When this procedure is called, TimesTen performs a blocking checkpoint when the current transaction is committed or rolled back. If, at that time, other transactions are in progress, the checkpointing connection waits until the other transactions have committed or rolled back. While the checkpoint connection is waiting, any other new transactions that should start will form a queue behind the checkpointing transaction. As a result, if any transaction is long-running, it may cause many other transactions to be held up. So, use this blocking checkpoint with caution. To perform a nonblocking checkpoint, use the ttCkpt
procedure.
No log is needed to recover when blocking checkpoints are used. TimesTen uses the log, if present, to bring the database up to date after recovery.
ttCkptBlocking
has these optional parameters:
Parameter | Type | Description |
---|---|---|
timeout |
TT_INTEGER |
The time (in seconds) that ttCkptBlocking should wait to get a database lock before timing out. The value of timeout can be between 0 and one million, inclusively. If not specified, the checkpoint never times out. |
retries |
TT_INTEGER |
The number of times that ttCkptBlocking should attempt to get a database lock, if timeouts occur. The value of retries can be between 0 and 10 , inclusive. If not specified, defaults to zero. |
Because the checkpoint takes place at commit or rollback, the call to ttCkptBlocking
always succeed. At commit or rollback, any problems with the checkpoint operation, such as a lack of disk space or a timeout, result in a warning being returned to the application. Checkpoint problems are not reflected as errors, since the commit or rollback of which they are a part can succeed even if the checkpoint fails. Warnings are reflected in ODBC with the return code SQL_SUCCESS_WITH_INFO
.
For more information on checkpoints, see “Transaction Management" in Oracle TimesTen In-Memory Database Operations Guide.
This procedure reconfigures the background checkpointer dynamically or returns the currently active settings of the configuration parameters. Changes made using ttCkptConfig
become effective immediately. Thus, changes to ckptRate
can take effect on a checkpoint that is currently in progress.
Changes made to the background checkpointer using ttCkptConfig
are persistent. Subsequent loads of the database retain the new settings, unless the CkptFrequency
and CkptLogVolume
connection attributes are specified in the DSN or connection string, in which case the attribute values are used instead.
This procedure requires no privilege to query the current values. It requires the ADMIN
privilege to change the current values.
ttCkptConfig
has these parameters:
Parameter | Type | Description |
---|---|---|
ckptFrequency |
TT_INTEGER |
Checkpoint frequency in seconds. Values from 0 to MAXINT are allowed. A value of 0 means that checkpoint frequency is not considered when scheduling checkpoints. |
ckptLogVolume |
TT_INTEGER |
Log volume between checkpoints in megabytes. Values from 0 to MAXINT are allowed. A value of 0 means that checkpoint log volume is not considered when scheduling checkpoints. |
ckptRate |
TT_INTEGER |
Specifies the rate in MB per second at which a checkpoint should be written to disk.
A value of |
ttCkptConfig
returns the following results.
Column | Type | Description |
---|---|---|
ckptFrequency |
TT_INTEGER NOT NULL |
Currently active setting for checkpoint frequency in seconds. |
ckptLogVolume |
TT_INTEGER NOT NULL |
Currently active setting for log volume between checkpoints in MB. |
ckptRate |
TT_INTEGER NOT NULL |
Current rate at which TimesTen writes checkpoints to disk. |
To view the current settings of the background checkpointer configuration parameters, use:
CALL ttCkptConfig; < 600, 32, 0 > 1 row found.
To stop the background checkpointer from initiating checkpoints unless the log reaches its limit, use:
CALL ttCkptConfig(0); < 0, 32, 0 > 1 row found.
To stop the background checkpointer from initiating checkpoints, use:
CALL ttCkptConfig(0, 0); < 0, 0, 0 > 1 row found.
To set the background checkpointer configuration to initiate a checkpoint every 600 seconds or to checkpoint when the log reaches 32 MB (whichever comes first), use:
CALL ttCkptConfig(600, 32); < 600, 32, 0 > 1 row found.
By default, TimesTen performs background checkpoints at regular intervals.
In the case that your application attempts to perform a checkpoint operation while a backup is in process, the backup waits until the checkpoint finishes. Regardless of whether the checkpoint is a background checkpoint or an application-requested checkpoint, the behavior is:
If a backup or checkpoint is running and you try to do a backup, it waits for the running backup or checkpoint to finish.
If a backup or checkpoint is running and you try to do a checkpoint, it does not wait. It returns an error immediately.
To turn off background checkpointing, set CkptFrequency=0
and CkptLogVolume=0
.
This procedure returns information about the last eight checkpoints of any type taken by any agent.
ttCkptHistory
returns the result set:
Column | Type | Description |
---|---|---|
startTime |
TT_TIMESTAMP NOT NULL |
Time when the checkpoint was begun. |
endTime |
TT_TIMESTAMP |
Time when the checkpoint completed. |
type |
TT_CHAR (16) NOT NULL |
The type of checkpoint taken. Value is one of:
|
status |
TT_CHAR (16) NOT NULL |
Result status of the checkpoint operation. Value is one of:
In
|
initiator |
TT_CHAR (16) NOT NULL |
The source of the checkpoint request. Value is one of:
|
error |
TT_INTEGER |
If a checkpoint fails, this column indicates the reason for the failure. The value is one of the TimesTen error numbers. |
ckptFileNum |
TT_INTEGER NOT NULL |
The database file number used by the checkpoint. This corresponds to the number in the checkpoint file extension datastore .ds0 or datastore .ds1. |
ckptLFN |
TT_INTEGER |
The transaction log file number of the checkpoint log record. |
ckptLFO |
TT_BIGINT |
The transaction log file offset of the checkpoint log record. |
blksTotal |
TT_BIGINT |
The number of permanent blocks currently allocated in the database. These blocks are subject to consideration for checkpointing. |
bytesTotal |
TT_BIGINT |
The number of bytes occupied by blksTotal . |
blksInUse |
TT_BIGINT |
Of blksTotal , the number of blocks currently in use. |
bytesInUse |
TT_BIGINT |
The number of bytes occupied by blksInUse . |
blksDirty |
TT_BIGINT |
The number of dirty blocks written by this checkpoint. |
bytesDirty |
TT_BIGINT |
The number of bytes occupied by blksDirty . |
bytesWritten |
TT_BIGINT |
The total number of bytes written by this checkpoint. |
Percent_Complete |
TT_INTEGER |
If there is an in-progress checkpoint, indicates the percentage of the checkpoint that has been completed. If no checkpoint is in-progress, the value is NULL . The returned value is calculated by comparing the block ID of the last-written block against the database's PermSize . The value does not necessarily indicate the precise time remaining to complete the checkpoint, although it does give some indication of the remaining time needed to complete the disk write. The field shows only the progress of the writing of dirty blocks and does not include additional bookkeeping at the end of the checkpoint. The value is non-NULL if you call this procedure while a checkpoint is in progress. |
ckptVNo |
TT_INTEGER NOT NULL |
The checkpoint sequence number that is incremented for each checkpoint. |
This example shows a checkpoint in progress:
Call ttckpthistory; < 2011-04-14 16:56:34.169520, <NULL>, Fuzzy , In Progress , User , <NULL>, 0, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, 13, 6 > < 2011-04-14 16:55:47.703199, 2011-04-14 16:55:48.188764, Fuzzy , Completed , Checkpointer , <NULL>, 1, 0, 8964304, 294, 33554432, 291, 5677288, 27, 1019512, 1065408, <NULL>, 5 > < 2011-04-14 16:54:47.106110, 2011-04-14 16:54:47.723379, Static , Completed , Subdaemon , <NULL>, 0, 0, 8960328, 294, 33554432, 291, 5677288, 256, 33157172, 5321548, <NULL>, 4 > < 2011-04-14 16:54:41.633792, 2011-04-14 16:54:42.568469, Blocking , Completed , User , <NULL>, 1, 0, 8958160, 294, 33554432, 291, 5677288, 31, 1162112, 6604976, <NULL>, 3 > < 2011-04-14 16:54:37.438827, 2011-04-14 16:54:37.977301, Static , Completed , User , <NULL>, 0, 0, 1611984, 93, 33554432, 92, 1853848, 93, 33554432, 1854052, <NULL>, 2 > < 2011-04-14 16:54:36.861728, 2011-04-14 16:54:37.438376, Static , Completed , User , <NULL>, 1, 0, 1609936, 93, 33554432, 92, 1853848, 93, 33554432, 1854052, <NULL>, 1 > 6 rows found.
This example shows that an error occurred during the most recent checkpoint attempt:
call ttckpthistory; < 2011-04-14 16:57:14.476860, 2011-04-14 16:57:14.477957, Fuzzy , Failed , User , 847, 1, <NULL>, <NULL>, 0, 0, 0, 0, 0, 0, 0, <NULL>, 7 > < 2011-04-14 16:56:34.169520, 2011-04-14 16:56:59.715451, Fuzzy , Completed , User , <NULL>, 0, 0, 8966472, 294, 33554432, 291, 5677288, 5, 522000, 532928, <NULL>, 6 > < 2011-04-14 16:55:47.703199, 2011-04-14 16:55:48.188764, Fuzzy , Completed , Checkpointer , <NULL>, 1, 0, 8964304, 294, 33554432, 291, 5677288, 27, 1019512, 1065408, <NULL>, 5 > < 2011-04-14 16:54:47.106110, 2011-04-14 16:54:47.723379, Static , Completed , Subdaemon , <NULL>, 0, 0, 8960328, 294, 33554432, 291, 5677288, 256, 33157172, 5321548, <NULL>, 4 > < 2011-04-14 16:54:41.633792, 2011-04-14 16:54:42.568469, Blocking , Completed , User , <NULL>, 1, 0, 8958160, 294, 33554432, 291, 5677288, 31, 1162112, 6604976, <NULL>, 3 > < 2011-04-14 16:54:37.438827, 2011-04-14 16:54:37.977301, Static , Completed , User , <NULL>, 0, 0, 1611984, 93, 33554432, 92, 1853848, 93, 33554432, 1854052, <NULL>, 2 > < 2011-04-14 16:54:36.861728, 2011-04-14 16:54:37.438376, Static , Completed , User , <NULL>, 1, 0, 1609936, 93, 33554432, 92, 1853848, 93, 33554432, 1854052, <NULL>, 1 > 7 rows found.
Results are ordered by start time, with the most recent first.
A failed row is overwritten by the next checkpoint attempt.
This built-in procedure returns the number of commit buffer overflows and the high watermark for memory used by transaction reclaim records during transaction commit process.
The information provided by the results of this procedure call is useful information when you want to explicitly set the maximum size of commit buffer, using the CommitBufferSizeMax
connection attribute or the ALTER SESSION
SQL statement, described in Oracle TimesTen In-Memory Database SQL Reference. This procedure helps you choose the right size for the reclaim buffer, based on the number of overflows and the maximum memory used by the reclaim records.
If there are buffer overflows, you may consider increasing the commit buffer maximum size. If there are no overflows and the highest amount of memory usage is well under the commit buffer maximum size, you may consider decreasing the maximum size.
For more information on reclaim operations, including details about setting the commit buffer size, see "Transaction reclaim operations" in the Oracle TimesTen In-Memory Database Operations Guide.
ttCommitBufferStats
returns these results:
Column | Type | Description |
---|---|---|
overflows |
TT_BIGINT NOT NULL |
Total number of commit buffer overflows. |
maxReached |
TT_BIGINT NOT NULL |
The currently used maximum for the transaction commit buffer in bytes. |
This shows the result for a session where there have been no commit buffer overflows and the transaction commit buffer is set to 500 MB.
Command> ALTER SESSION SET COMMIT_BUFFER_SIZE_MAX = 500; Session altered. Command> CALL ttCommitBufferStats( ); < 0, 524288000 > 1 row found
For a session where there have been 10 commit buffer overflows and the transaction commit buffer is set to 2 MB, the output of this procedure is:
Command> ALTER SESSION SET COMMIT_BUFFER_SIZE_MAX = 2; Session altered. Command> CALL ttCommitBufferStats( ); < 0, 2097152 > 1 row found
When you call the built-in procedure ttCommitBufferStatsReset
, the commit buffer statistics are expressed in bytes. However, the ttConfiguration
output and the value set by the connection attribute CommitBufferSizeMax
are expressed in MB.
The ttCommitBufferStatsReset
procedure resets transaction commit buffer statistics to 0
. This is useful, for example, if you have set a new value for the commit buffer maximum size and want to restart the statistics.
For more information on reclaim operations, including details about setting the commit buffer size, see "Transaction reclaim operations" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure compacts both the permanent and temporary data partitions of the database.
ttCompact
merges adjacent blocks of free space, but does not move any items that are allocated. Therefore, fragmentation that is caused by small unallocated blocks of memory surrounded by allocated blocks of memory is not eliminated by using ttCompact
.'
This procedure is supported for backward compatibility. New applications should not call it.
This procedure is similar to ttCompact
, except that ttCompactTS
may be used to compact a small fraction of the database, while ttCompact
compacts the entire database. ttCompactTS
is a time-sliced version of ttCompact
. ttCompactTS
iterates through all the blocks in the database compacting the quantum specified each time. When a sweep is completed, the value of the DS_COMPACTS
field in the MONITOR
table is incremented.
This procedure is supported for backward compatibility. New applications should not call it.
ttCompactTS
has the parameter:
Parameter | Type | Description |
---|---|---|
quantum |
TT_INTEGER NOT NULL |
A nonzero positive integer that specifies the number of data blocks a ttCompactTS should compact. Each quantum corresponds to one data block. |
The ttComputeTabSizes
built-in procedure refreshes table size statistics stored in TimesTen system tables. After calling this built-in procedure, you can review the statistics updates by querying the DBA_TAB_SIZES
, USER_TAB_SIZES
or ALL_TAB_SIZES
view.
This procedure computes the different types of storage allocated for the specified table, such as the amount of storage allocated for inline row storage, out-of-line buffers and system usage. If no table is specified, the procedure computes the sizes for all tables on which the user has SELECT
privileges.The execution of this built-in behaves like a DDL statement: the transaction commits just before the procedure begins and commits again upon its successful termination.
ttComputeTabSizes
has the parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) |
Name of an application table. Can include the table owner. If a value of NULL or an empty string is provided, updates the statistics for all the current tables.
The type of tables that can be estimated are:
|
includeOutOfLine |
TT_INTEGER |
0 (no) or 1 (yes). Default is 1 (yes).
If value is Avoiding the computation of out-of-line values significantly decreases the latency of this procedure. |
To compute the size of my_table
without including out-of-line columns, use:
CALL ttComputeTabSizes ('my_table', 0);
The built-in procedure enables concurrent insertions while ttComputeTabSizes
is executing. For this reason, the size computed by ttComputeTabSizes
for each table is any value between the minimum size of the table during the computation and the maximum size of the table during the computation. For example, if the size of a table is 250 MB when ttComputeTabSizes
is executed, and a transaction running concurrently raises the size of the table to 300 MB, ttComputeTabSizes
estimates a value between 250 and 300 MB.
The ttConfiguration
built-in procedure returns the values for most, but not all, connection attributes for the current database connection. Specifically, the ttConfiguration
built-in procedure returns the values for these connection attributes:
CacheAwtMethod
CacheAwtParallelism
CacheGridEnable
CacheGridMsgWait
CkptFrequency
CkptLogVolume
CkptRate
CkptReadThreads
CommitBufferSizeMax
ConnectionCharacterSet
ConnectionName
Connections
DDLCommitBehavior
DDLReplicationAction
DDLReplicationLevel
DataBaseCharacterSet
DataStore
DynamicLoadEnable
DuplicateBindMode
DurableCommits
DynamicLoadErrorMode
Isolation
RangeIndexType
LockLevel
LockWait
LogAutoTruncate
LogBufMB
LogBufParallelism
LogDir
LogFileSize
LogFlushMethod
LogPurge
Logging
MemoryLock
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
NLS_SORT
OracleNetServiceName
PLSCOPE_SETTINGS
PLSQL
PLSQL_CCFLAGS
PLSQL_CODE_TYPE
PLSQL_CONN_MEM_LIMIT
PLSQL_MEMORY_ADDRESS
PLSQL_MEMORY_SIZE
PLSQL_OPTIMIZE_LEVEL
PLSQL_TIMEOUT
PassThrough
PermSize
PermWarnThreshold
Preallocate
PrivateCommands
QueryThreshold
RACCallback
ReceiverThreads
RecoveryThreads
ReplicationApplyOrdering
ReplicationParallelism
ReplicationTrack
SQLQueryTimeout
TempSize
TempWarnThreshold
Temporary
TypeMode
UID
ttConfiguration
has the optional parameter:
Parameter | Type | Description |
---|---|---|
paramName |
TT_VARCHAR (30) |
The name of a connection attribute for which you want this procedure to return the value. |
ttConfiguration
returns the result set:
Column | Type | Description |
---|---|---|
paramName |
TT_VARCHAR (30) NOT NULL |
The names of the connection attributes specified in the connection string, returned in alphabetical order. |
paramValue |
TT_VARCHAR (1024) |
The values of the connection attributes specified in the connection string. |
To see the value of the QueryThreshold
connection attribute, use
CALL ttConfiguration('querythreshold'); <QueryThreshold, 0> 1 row found
To see the values of all attributes, use:
CALL ttConfiguration(); < CacheGridEnable, 1 > < CacheGridMsgWait, 60 > < CkptFrequency, 600 > < CkptLogVolume, 0 > . . .
The values of client driver attributes are not returned by this procedure.
The values of other attributes, such as ForceConnect
, may not be returned by this procedure, as well.
This procedure returns the context value of the current connection as a BINARY(8)
value. You can use the context to correlate a unique connection to a database from the list of connections presented by the ttStatus
utility and the ttDataStoreStatus
built-in procedure.
ttContext
returns the result set:
Column | Type | Description |
---|---|---|
context |
BINARY(8) |
Current connection context value. |
The context value numbers are unique only within a process. The context value number is not unique within the entire database. Therefore you may see the same context value number for different processes.
This procedure returns the list of processes connected to a database. If the dataStore
parameter is specified as NULL
, then the status of all active databases is returned.
The result set is similar to the printed output of the ttStatus
utility.
ttDataStoreStatus
has the parameter:
Parameter | Type | Description |
---|---|---|
dataStore |
TT_VARCHAR (256) |
Full path name of desired database or NULL for all databases. |
ttDataStoreStatus
returns the result set:
Column | Type | Description |
---|---|---|
dataStore |
TT_VARCHAR (256) NOT NULL |
Full path name of database. |
PID |
TT_INTEGER NOT NULL |
Process ID. |
Context |
BINARY(8) NOT NULL |
Context value of connection. |
conType |
TT_CHAR (16) NOT NULL |
Type of process connected. The result can be one of the following:
|
ShmID |
TT_VARCHAR (260) NOT NULL |
A printable version of the shared memory ID that the database occupies. |
connection_Name |
TT_CHAR (30) NOT NULL |
The symbolic name of the database connection. |
connID |
TT_INTEGER NOT NULL |
The numeric ID of the database connection. |
The ttDBConfig
built-in enables users to set or view the value of a TimesTen database system parameter.
ttDBConfig
has the parameters:
Parameter | Type | Description |
---|---|---|
param |
VARCHAR2(30) NOT NULL |
.A system parameter for which you either want to set a value or to see the current value. Accepted values for this argument are:
|
value |
VARCHAR2(200) |
The value for the system parameter.
If you do not specify a value, this procedure returns the current value of the specified parameter. |
These name/value pairs can be returned in the result set:
Name | Value | Description |
---|---|---|
CacheParAwtBatchSize |
Number of rows in a batch | Configures a threshold value for the number of rows included in a single batch. Once the maximum number of rows is reached, TimesTen includes the rest of the rows in the transaction (TimesTen does not break up any transactions), but does not add any more transactions to the batch.
NOTE: You should not change the value of this parameter unless advised by Oracle TimesTen technical support. |
CacheAwtMethod |
0 - SQL Array execution method
|
Determines whether PL/SQL execution method or SQL array execution method is used for AWT propagation to apply changes to the Oracle database server.
See the description of the If set with this built-in procedure, overrides the value of the value of the connection attribute value. |
CacheAgentCommitBufSize |
Size in MB | Specifies the reclaim buffer maximum size for the cache agent. The cache agent periodically checks to see if the value has changed. The size cannot be greater than the temporary partition size.
For more details, see "Improving performance when reclaiming memory during autorefresh operations" in the Oracle TimesTen Application-Tier Database Cache User's Guide. |
ParReplMaxDrift |
Number of seconds | Specifies the number of seconds of drift to allow between the parallel replication tracks. When you use automatic parallel replication and disable commit dependencies, some of the tracks may move ahead of the others. Once this threshold is passed, TimesTen synchronizes all replication tracks so that they catch up to each other. By default, this is set to zero, which means that checking for drift between tracks is disabled.
For more details, see "Configuring automatic parallel replication with disabled commit dependencies" in the Oracle TimesTen In-Memory Database Replication Guide |
RepAgentCommitBufSize |
Size in MB | Specifies the reclaim buffer maximum size for the replication agent. The replication agent periodically checks to see if the value has changed. The size cannot be greater than the temporary partition size.
For more details, see "Improving performance when reclaiming memory during autorefresh operations" in the Oracle TimesTen Application-Tier Database Cache User's Guide. |
ttDBConfig
returns the result set:
Column | Type | Description |
---|---|---|
param |
VARCHAR2(30) |
The name of the system parameter. |
value |
VARCHAR2(200) |
Displays the current value of the specified parameter. |
To retrieve the current value of the CacheParAwtBatchSize
, use:
CALL ttDBConfig('CacheParAwtBatchSize'); <CACHEPARAWTBATCHSIZE, 125> 1 row found.
To set the value of the RepAgentCommitBufSize
to 50 MB, use:
CALL ttDBConfig('RepAgentCommitBufSize', '50'); <REPAGENTCOMMITBUFSIZE, 50> 1 row found.
To set the current value of the CacheAgentCommitBufSize
to 100, use:
Command> call ttDBConfig('CacheAgentCommitBufSize', '100'); < CACHEAGENTCOMMITBUFSIZE, 100 > 1 row found.
After using this built-in procedure to set a parameter value, initiate a checkpoint to ensure the persistence of the parameter change. See details about the ttCheckpoint
procedure in "Checkpoint operations" in the Oracle TimesTen In-Memory Database Operations Guide. For details about the checkpoint built-in procedure, see "ttCkpt" in this chapter.
Changes to parameter values made by ttDbConfig
cannot be rolled back.
The ttDbWriteConcurrencyModeGet
built-in returns information about the write concurrency mode of the database and the status of write concurrency mode operations and transitions.
ttDbWriteConcurrencyModeGet
returns the result set:
Column | Type | Description |
---|---|---|
ts |
TIMESTAMP NOT NULL |
Time at which the status information was collected. |
mode |
TT_INTEGER NOT NULL |
The write concurrency mode:
|
operation |
VARCHAR2 (50) |
The transition status of the write concurrency mode. Either:
|
status |
VARCHAR2 (100) NOT NULL |
The status of the write concurrency mode transition. Either:
|
msg |
VARCHAR2 (5000) |
NULL or a status explanation message. |
The following example shows how to determine if your database is optimized for concurrent write operations:
Command> CALL ttDbWriteConcurrencyModeGet(); < 2013-09-23 13:48:21.207599, 1, <NULL>, COMPLETE, <NULL> > 1 row found.
The results indicate that at approximately 1:48 pm on September 23, 2013 the database was optimized for concurrent write operations. The mode was not in transition.
The ttDbWriteConcurrencyModeSet
built-in enables control over read optimization during periods of concurrent write operations.
Set the mode to one (1) to enable the enhanced write concurrency mode and disable read optimization. Set the mode to zero (0) to disable the enhanced write concurrency mode and re-enable read optimization.When the mode is set to one (1
), all transaction and statement table lock hints are suppressed. This affects hint-triggered Sn
table locks for SELECT
statements and subqueries and also hint-triggered W
table locks for DML statements. Suppression of the table lock hint also suppresses other table-lock hint driven execution plans such as star joins. Regardless of the mode setting, table locks that are not triggered by table-lock hints are not affected.
ttDbWriteConcurrencyModeSet
has these parameters:
Parameter | Type | Description |
---|---|---|
mode |
TT_INTEGER NOT NULL |
The write concurrency mode:
|
wait |
TT_INTEGER NOT NULL |
0 - Return immediately after starting mode transition.
|
The following example shows how to enable standard optimization techniques and return immediately after starting the operation:
Command> CALL ttDbWriteConcurrencyModeSet(0,0);
When the mode is set to one (1), all transaction and statement table lock hints are suppressed. This affects hint-triggered Sn
table locks for SELECT
statements and subqueries and also hint-triggered W
table locks for DML statements. Suppression of the table lock hint also suppresses other table-lock hint driven execution plans such as star joins. Regardless of the mode setting, table locks that are not triggered by table-lock hints are not affected.
This procedure specifies that the current transaction should be made durable when it is committed. It only has an effect if the application is connected to the database with DurableCommits
disabled.
Calling ttDurableCommit
also makes durable the current transaction and any previously committed delayed durability transactions. There is no effect on other transactions that are committed after calling ttDurableCommit
. ttDurableCommit
does not commit transactions. The application must do the commit, for example with a call to SQLTransact
.
Some controllers or drivers may only write data into cache memory in the controller or may write to disk some time after the operating system is told that the write is done. In these cases, a power failure may mean that some information you thought was durably committed does not survive the power failure. To avoid this loss of data, configure your disk to write all the way to the recording media before reporting completion or you can use an Uninterruptable Power Supply (UPS).
This procedure attaches a grid member to an existing local cache grid. A grid member can be a standalone TimesTen database or a TimesTen active standby pair.
If a member is an active standby pair, both nodes of the pair must attach to the grid. When calling the ttGridAttach
built-in procedure from each node of the active standby pair, specify the IP address or host name of both nodes.
This procedure starts the cache agent if it is not already running. This procedure cannot be used remotely.
For a standalone TimesTen database:
ttGridAttach(currentNode, 'name1', IPAddr1, port1)
For a node of an active standby pair:
ttGridAttach(currentNode, 'name1', IPAddr1, port1 'name2', IPAddr2, port2)
ttGridAttach
has the parameters:
Parameter | Type | Description |
---|---|---|
currentNode |
TT_INTEGER NOT NULL |
The node number for the master database. Valid values for this parameter are:
|
name1 |
TT_VARCAR (30) |
Fully qualified name that uniquely identifies the grid member for the active master database. |
IPAddr1 |
TT_VARCHAR (128) NOT NULL |
IP address of the node where the active master database resides. |
port1 |
TT_INTEGER NOT NULL |
Port number for the cache agent process of the active master database or a standalone database. |
name2 |
TT_VARCAR (30) |
Fully qualified name that uniquely identifies the grid member for the standby master database. |
IPAddr2 |
TT_VARCHAR (128) |
IP address of the node where the standby master database resides. |
port2 |
TT_INTEGER |
Port number for the cache agent process of the standby master database. |
To attach to a standalone TimesTen database to a grid:
CALL ttGridAttach (1, 'alone2','sys2',5002);
To attach an active master database to a grid:
CALL ttGridAttach(1,'cacheact','sys1',5003,'cachestand','sys2',5004);
To attach a standby master database to a grid:
CALL ttGridAttach(2,'cacheact','sys1',5003,'cachestand','sys2',5004);
The only difference between the calls for attaching the active and the standby master stores is the node number.
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachList
ttGridDetachAll
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure checks if the number of rows in global cache groups match number of rows in the ownership tables. Call this procedure only when the cache grid is quiet.
ttGridCheckOwner
has the optional parameters:
Parameter | Type | Description |
---|---|---|
cvName |
TT_VARCHAR (30 ) |
The name of the cache group to be checked. If NULL , all cache groups owned by the owner, if one is specified, are checked. If NULL , and no owner is specified, all cache groups are checked. |
cvOwner |
TT_VARCHAR (30) |
The owner of the cache group to be checked. If NULL , all cache groups are checked. |
To get information on the mygroup
cache group, owned by user terry
, use:
CALL ttGridCheckOwner ('mygroup', 'terry');
To get information on all cache groups, use:
CALL ttGridCheckOwner();
ttGridAttach
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure creates a cache grid. Run this procedure only one time to create a grid. You can run it from any standalone database or from the active or standby master database in an active standby pair.
You must commit after calling this procedure if AUTOCOMMIT=0
.
ttGridCreate
has the parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) NOT NULL |
Specifies the name of the grid. |
CALL ttGridCreate ('mygrid');
ttGridAttach
ttGridCheckOwner
ttGridDestroy
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure destroys a cache grid by removing all cache grid objects stored on the Oracle database.
By default, this built-in procedure does not destroy the grid if there are still attached members or existing cache groups.
Before destroying a cache grid, detach all the TimesTen databases from the cache grid. To force the grid to be destroyed, supply a value of 1
as an argument to the force
parameter.
ttGridDestroy
has the parameters:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) NOT NULL |
The fully qualified name of the grid to be destroyed. |
force |
TT_INTEGER |
This optional parameter forces the cache grid to be destroyed even if there are still grid members attached to the cache grid or if it still contains cache groups. Valid value is 1 . |
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure detaches a node from a cache grid.
Use this procedure before destroying a cache grid. You cannot destroy a cache grid if there are any nodes attached to the cache grid.
ttGridDetach
has the optional parameters:
Parameter | Type | Description |
---|---|---|
nodeMemberName |
TT_VARCHAR (200) |
Specifies the node to detach from the grid.
Each node of an active standby pair must be detached separately. |
force |
TT_INTEGER |
Forces a node to be detached without checking whether it is terminated. Valid value is 1 . |
oraclePropWaitSec |
TT_INTEGER |
Specifies the number of seconds to wait for all transactions to propagate to the Oracle database before detaching the node. A value of -1 indicates to wait forever. If no value is specified, ttGridDetach waits 1 second. |
To detach the current node from the grid, use:
CALL ttGridDetach();
To detach the remote node TTGRID_alone2_2
from the grid, use:
CALL ttGridDetach('TTGRID_alone2_2',1);
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDetachAll
ttGridDetachList
ttGridDestroy
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure detaches all attached members from the grid. A grid member can be a standalone TimesTen database or a TimesTen active standby pair.
This procedure starts the cache agent if it is not already running.
ttGridDetachAll
has the optional parameter:
Parameter | Type | Description |
---|---|---|
oraclePropWaitSec |
TT_INTEGER |
Specifies the number of seconds to wait for all transactions to propagate to the Oracle database before detaching all nodes. A value of -1 indicates to wait forever. If no value is specified, ttGridDetachAll waits 1 second. |
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachList
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure detaches the nodes in the list. It is useful for remote nodes, because they are unavailable.
ttGridDetachList
has the parameters:
Parameter | Type | Description |
---|---|---|
nodeMemberName |
TT_VARCHAR (8192) NOT NULL |
The fully qualified name of the node to be removed. |
force |
TT_INTEGER |
This optional parameter forces nodes to be detached without checking whether they are terminated. Valid value is 1. |
oraclePropWaitSec |
TT_INTEGER |
The optional parameter specifies the number of seconds to wait for all transactions to propagate to the Oracle database before detaching the listed nodes. A value of -1 indicates to wait forever. If no value is specified, ttGridDetachList waits 1 second. |
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDetach
ttGridDetachAll
ttGridDestroy
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure is similar to the ttGridAttach
built-in procedure. Call this procedure instead to attach the first member of the grid in the case that all attached members of the grid have died.
Call this procedure only from one member. Ensure that the cache agents of all other members are stopped before calling this built-in procedure or the procedure fails.
This procedure starts the cache agent if it is not already running. This procedure cannot be used remotely.
For a standalone TimesTen database:
ttGridFirstMemberAttach(currentNode, 'name1', IPAddr1, port1)
For a node of an active standby pair:
ttGridFirstMemberAttach(currentNode, 'name1', IPAddr1, port1 'name2', IPAddr2, port2)
ttGridFirstMemberAttach
has the parameters:
Parameter | Type | Description |
---|---|---|
currentNode |
TT_INTEGER NOT NULL |
The node number for the master database. Valid values for this parameter are:
|
name1 |
TT_VARCHAR (30) |
Fully qualified name that uniquely identifies the grid member for the active master database. |
IPAddr1 |
TT_VARCHAR (128) NOT NULL |
IP address of the node where the active master database resides. |
port1 |
TT_INTEGER NOT NULL |
Port number for the cache agent process of the active master database or a standalone database. |
name2 |
TT_VARCAR (30) |
Fully qualified name that uniquely identifies the grid member for the standby master database. |
IPAddr2 |
TT_VARCHAR (128) |
IP address of the node where the standby master database resides. |
port2 |
TT_INTEGER |
Port number for the cache agent process of the standby master database. |
To attach to a standalone TimesTen database to a grid:
CALL ttGridFirstMemberAttach (1, 'alone2','sys2',5002);
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachList
ttGridDetachAll
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure resumes operations that were blocked after a call to ttGridGlobalCGSuspend
.
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
This procedure temporarily blocks dynamic loading and deleting cache instances for global cache groups. Use the ttGridGlobalCGResume
procedure to re-enable these actions.
ttGridGlobalCGSuspend
has the parameter:
Parameter | Type | Description |
---|---|---|
wait |
TT_INTEGER |
The number of seconds that the command waits for a pending delete to be propagated to the Oracle database or a pending transparent load operation to complete before returning.TimesTen returns an error if either the pending delete or the pending transparent load operation cannot complete in the specified time.
If no value is specified, there is no wait interval. |
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGResume
ttGridNameSet
ttGridNodeStatus
ttGridInfo
has the optional parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) |
If gridName is specified, displays information about the specified grid. Otherwise, displays information about all grids. |
ttGridInfo
returns information about the cache grid.
Column | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) |
The name of the grid specified |
cacheAdminID |
TT_VARCHAR (30)
|
The cache administration user ID associated with the grid. |
platform |
TT_VARCHAR (100) |
The operating system platform on which the grid is operating.
The platform value is displayed as:
For example:
|
major1 |
TT_VARCHAR (10) |
The first number of the major TimesTen release associated with the grid. For example, 11 , if the release is 11.2.2. |
major2 |
TT_VARCHAR (10) |
The second number of the major TimesTen release associated with the grid. For example, 2 , if the release is 11.2.2. |
major3 |
TT_VARCHAR (10) |
The third number of the major TimesTen release associated with the grid. For example, 2 , if the release is 11.2.2. |
To get information on the mygrid
cache grid, use:
CALL ttGridInfo ('mygrid'); < MYGRID, CACHEUSER, Linux Intel x86, 32-bit, 11, 2, 2 >
To get information on all grids, use:
CALL ttGridInfo();
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
ttGridNodeStatus
ttGridNameSet
has the parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) |
Associates the TimesTen database that calls the procedure with the grid specified by gridName . |
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNodeStatus
This procedure returns information about all members of the specified cache grid. If no grid name is specified, then it displays information about all members of all cache grids associated with the Oracle database.
ttGridNodeStatus
has the optional parameter:
Parameter | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) |
If gridName is specified, displays information about all members of the named grid. Otherwise, displays information about all grids. |
ttGridNodeStatus
returns the results:
Column | Type | Description |
---|---|---|
gridName |
TT_VARCHAR (30) |
The name of the grid. |
nodeID |
TT_INTEGER NOT NULL |
The unique ID of the grid node. |
activeNode |
TT_INTEGER NOT NULL |
The number of the node on which the active master database or a standalone database currently resides. |
node1Attached |
CHAR (1) NOT NULL |
Indicates if the active node is attached to the grid:
|
Host1 |
TT_VARCHAR (200) NOT NULL |
The host name where the active database is located. |
memberName1 |
TT_VARCHAR (200) NOT NULL |
The unique member name for the standalone database or active standby database. |
IPaddr1 |
TT_VARCHAR (128) NOT NULL |
The IP address where the active master or standalone database is located. |
port1 |
TT_INTEGER NOT NULL |
The port number for the cache agent process of the active master or standalone database. |
node2Attached |
CHAR (1) |
Indicates if the standby node is attached to the grid:
|
host2 |
TT_VARCHAR (200) |
The host name where the standby master database is located. |
memberName2 |
TT_VARCHAR (200) |
The unique member name for the standalone database or active standby database. |
IPaddr2 |
TT_VARCHAR (128) |
The IP address where the standby master database is located. |
port2 |
TT_INTEGER |
The port number for the cache agent process of the standby master database. |
For a grid member that is a standalone database, the number of columns in the result set is fewer than for a member that is an active standby pair.
If ttgrid
is the only cache grid in the database, display information about its members:
Command> call ttGridNodeStatus; < TTGRID, 1, 1, T, sys1, TTGRID_alone1_1, 140.87.0.201, 5001, <NULL>, <NULL>,<NULL>, <NULL>, <NULL> > < TTGRID, 2, 1, T, sys2, TTGRID_alone2_2, 140.87.0.202, 5002, <NULL>, <NULL>,<NULL>, <NULL>, <NULL> > < TTGRID, 3, 1, T, sys3, TTGRID_cacheact_3A, 140.87.0.203, 5003, T, sys4, TTGRID_cachestand_3B, 140.87.0.204, 5004 >
ttGridAttach
ttGridCheckOwner
ttGridCreate
ttGridDestroy
ttGridDetach
ttGridDetachAll
ttGridDetachList
ttGridGlobalCGResume
ttGridGlobalCGSuspend
ttGridNameSet
This procedure returns the name of the current local host for the database. The value returned is only for the current session. It is not a systemwide setting and does not persist after the current session has been disconnected.
Use this procedure to check whether a particular store name in a scheme refers to the current host. This can be helpful when configuring replication schemes.
ttHostNameGet
returns the result:
Column | Type | Description |
---|---|---|
hostName |
TT_VARCHAR (200) |
The current default local host setting for the database. If a default has not been supplied then the current host name is returned. |
ttHostNameSet
This procedure specifies the name of the default local host for the current database. The value is only used in the current session, it is not a systemwide setting and does not persist after the current session has been disconnected.
To configure master/subscriber relationships and replication object permissions correctly, Replication DDL processing relies on being able to determine whether a host name used in a replication scheme refers to the computer on which the script is currently being run. This procedure enables an application to set a default host name for the current session that Replication DDL processing uses whenever there is a need to establish the name of the current host.
ttHostNameSet
has the parameter:
Parameter | Type | Description |
---|---|---|
hostName |
TT_VARCHAR (200) |
The required default name for the local computer. To clear the default value, specify NULL . |
The legal value of hostName
can be any host name or IP address string except 'localhost
', '127.0.0.1
' or '::1
'. You cannot set the default host name to a value that is different from a local host name used in an existing replication scheme.
ttHostNameGet
This procedure drops existing capture data for either the current connection or for the database. Subsequent calls to ttIndexAdviceCaptureOutput
at that level return no rows.
This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure requires no privileges to drop a connection level capture.
This procedure requires ADMIN
privileges to drop a database level capture.
ttIndexAdviceCaptureDrop
has this optional parameter:
Parameter | Type | Description |
---|---|---|
captureLevel |
TT_INTEGER |
Legal values for the capture level are:
|
To drop both connection level and database level captures, invoke the command twice, once for each capture level.
It is an error to call this command while a capture is in progress at the level you are attempting to drop.
ttIndexAdviceCaptureEnd
ttIndexAdviceCaptureInfoGet
ttIndexAdviceCaptureOutput
ttIndexAdviceCaptureStart
This procedure ends either an active connection level capture from the current connection or an active database level capture.
This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure requires no privilege to end a connection level capture.
This procedure requires ADMIN
privileges to end a database level capture.
ttIndexAdviceCaptureEnd
has this optional parameter:
Parameter | Type | Description |
---|---|---|
captureLevel |
TT_INTEGER |
Legal values for the capture level are:
|
The following example ends the collection for the connection level capture:
Call ttIndexAdviceCaptureEnd(0)
To end both connection level and database level captures, invoke the command twice, once for each capture level.
It is an error to call this procedure without first starting a capture at the specified level by calling the ttIndexAdviceCaptureStart
procedure.
ttIndexAdviceCaptureDrop
ttIndexAdviceCaptureInfoGet
ttIndexAdviceCaptureOutput
ttIndexAdviceCaptureStart
This procedure returns a row for each active capture. A capture is active if it has started capturing index advice or if it has stopped capturing index advice, but the capture data is still available.
One row relates to a connection level capture, if one exists. Another row relates to a database level capture, if one exists. At most there is one connection level and one database capture.
If no capture is in progress or no data exists, this procedure does not return any rows.
This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure requires no privilege to get information on a connection level capture.
This procedure requires ADMIN
privileges to get information on a database level capture.
ttIndexAdviceCaptureInfoGet
returns the result set:
Columns | Type | Description |
---|---|---|
captureState |
TT_INTEGER NOT NULL |
The state of the capture:
|
connID |
TT_INTEGER |
The connection ID of the connection that initiated the last capture, or the current capture if one is in progress.
This row is not returned if no capture has been initiated. |
captureLevel |
TT_INTEGER |
The level of the most recent capture.
This row is not returned if no capture has been initiated. |
captureMode |
TT_INTEGER |
The mode of the most recent capture.
This row is not returned if no capture has been initiated. |
numPrepared |
TT_INTEGER |
The number of prepared statements during the capture period.
This value is |
numExecuted |
TT_INTEGER |
The number of executed statements during the capture period.
This value is |
captureStartTime |
TT_TIMESTAMP |
The time stamp taken at the start of the capture period.
This row is not returned if no capture has been initiated. |
captureEndTime |
TT_TIMESTAMP |
The time stamp taken at the end of the capture period.
This value is |
This example shows capture information for a completed connection level capture for 363 prepared statements and 369 executed statements:
Command> CALL ttIndexAdviceCaptureInfoGet(); < 0, 1, 0, 0, 363, 369, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 > 1 row found.
If there is an active database level capture and you call this procedure on a connection that does not have ADMIN
privilege, TimesTen returns an error.
ttIndexAdviceCaptureDrop
ttIndexAdviceCaptureEnd
ttIndexAdviceCaptureOutput
ttIndexAdviceCaptureStart
This built-in returns a list of index recommendations from the last recorded capture at the specified level. It also returns an executable CREATE INDEX
SQL statement for creating the recommended index.
This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.
For a connection level capture, run this procedure in the same connection that initiated the capture. For a database level capture, run this procedure in a connection with ADMIN
privileges.
This procedure requires no privilege to get output on a connection level capture.
This procedure requires ADMIN
privileges to get output on a database level capture.
ttIndexAdviceCaptureOutput
has this optional parameter:
Parameter | Type | Description |
---|---|---|
captureLevel |
TT_INTEGER |
Legal values for the capture level are:
|
ttIndexAdviceCaptureOutput
returns the result set:
Column | Type | Description |
---|---|---|
stmtCount |
TT_INTEGER |
The number of statements in the captured workload that would have benefited from this index if it were present. |
createStmt |
TT_VARCHAR (8300) NOT NULL |
The executable statement that can create the recommended index. |
The following example provides the CREATE INDEX
statement for an index called PURCHASE_i1
on the HR.PURCHASE
table. There are four distinct statements that would benefit from the index in this SQL workload.
CALL ttIndexAdviceCaptureOutput(); < 4, create index PURCHASE_i1 on HR.PURCHASE(AMOUNT); > 1 row found.
ttIndexAdviceCaptureDrop
ttIndexAdviceCaptureEnd
ttIndexAdviceCaptureInfoGet
ttIndexAdviceCaptureStart
This procedure enables index advice capture. It is recommended that statistics be updated before you call this procedure, using ttOptEstimateStats
and setting the 'invalidate'
parameter set to 'yes'
. Updating the statistics in this way ensures statistics are up to date and forces statements to be re-prepared during the capture. To set statistics to known values instead, call ttOptSetTblStats
with the 'invalidate'
parameter set to 'yes'
.
This procedure and the procedures related to it are referred to as the Index Advisor. For details on using these procedures, see "Using the Index Advisor to recommend indexes" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure requires no privilege to start a connection level capture.
This procedure requires ADMIN
privileges to start a database level capture.
ttIndexAdviceCaptureStart
has these optional parameters:
Parameter | Type | Description |
---|---|---|
captureLevel |
TT_INTEGER |
Legal values for the capture level are:
|
captureMode |
TT_INTEGER |
Legal values for the capture mode are:
|
The following example starts a collection for the Index Advisor at the connection-level.
Call ttIndexAdviceCaptureStart(0,0);
It is an error to call this procedure if index advice is already being captured at the level specified by the captureLevel
parameter or at the connection level if no level is specified. Connection level captures can be issued concurrently on independent connections without conflict. Outstanding connection level captures that are in progress when a database level capture begins complete as intended.
ttIndexAdviceCaptureDrop
ttIndexAdviceCaptureEnd
ttIndexAdviceCaptureInfoGet
ttIndexAdviceCaptureOutput
This procedure takes a TimesTen table name, an Oracle SELECT
statement and the number of threads for parallel load. It executes the query on the Oracle database and loads the result set into the specified TimesTen table.
No character set conversion is performed when loading data from an Oracle database into a TimesTen table. The TimesTen database and the Oracle database must use the same character set.
The procedure requires the connection attribute UID
, the connection attribute OraclePWD
and the connection attribute OracleNetServiceName
to be specified. You must commit after calling this procedure.
For more details and usage information, see "Loading data from an Oracle database into a TimesTen table" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure requires INSERT
privileges to the table to be loaded.The session must have all the required privileges to execute the query on the Oracle database.
ttLoadFromOracle
has these parameters:
Parameter | Type | Description |
---|---|---|
tblOwner |
TT_CHAR (30) |
TimesTen table owner (optional). If not provided, the connection ID is used. |
tblName |
TT_CHAR (30) NOT NULL |
Name of the table to be loaded with data from the Oracle database. You can use the built-in procedure ttTableSchemaFromOraQueryGet to get a schema with which to build the table, if one does not already exist.
The specified TimesTen table cannot be a system table, a synonym, a view, a materialized view or a detail table of a materialized view, a global temporary table or a cache group table. |
Query |
TT_VARCHAR (409600) NOT NULL |
A SELECT query on an Oracle database to derive the table column definition.
The query on an Oracle database cannot have any parameter bindings. Provide any expressions in the |
numThreads |
TT_INTEGER |
Number of threads for parallel load (optional). If NULL , defaults to four.
Provides parallel loading for tables. Specifies the number of loading threads to run concurrently. One thread performs the bulk fetch from Oracle and the other threads perform the inserts into TimesTen. Each thread uses its own connection or transaction. The minimum value for NumThreads is 2. The maximum value is 10. If you specify a value greater than 10, TimesTen assigns the value 10. |
ttLoadFromOracle
returns the result set:
Column | Type | Description |
---|---|---|
numRows |
TT_BIGINT NOT NULL |
A single number indicating the number of rows loaded. |
The following example selects information about employees from the Oracle database HR
.EMPLOYEES
table and loads it into the TimesTen HR.EMPLOYEES
table. In this example information was found for 107 employees.
Command> CALL ttLoadFromOracle ('HR','EMPLOYEES', 'SELECT * FROM HR.EMPLOYEES'); < 107 > 1 row found.
TimesTen does not empty the table before the load. The target table does not require a primary key.TimesTen returns an error if the query output cannot be converted to rows in the target table due to a mismatch of column types or number of columns.Loading data into TimesTen LOB columns is not supported. If the query on the Oracle database has LOB output, it is mapped to a VAR type.
The load process does not check that the column data types and sizes in the TimesTen table match the data types and sizes of the result set. Instead, the insert is attempted and if the column data types cannot be mapped or the Oracle Database data from the SQL query exceeds the TimesTen column size, TimesTen returns an error. LOB columns are truncated to 4 MB.
When a table is altered to add columns, secondary partitions are added. Loading a table with multiple partitions is not supported by ttLoadFromOracle
.
Changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection. Applications can change the lock level again by calling ttLockLevel
again. The initial value depends on the LockLevel
connection attribute. See "LockLevel" for full details of the different locking levels.
ttLockLevel
has the parameter:
Parameter | Type | Description |
---|---|---|
lockLevel |
TT_CHAR (20) NOT NULL |
Locking level for the connection. |
The value of lockLevel
may be one of two case-insensitive strings:
Row
: Locking should be set to row-level locking.
DS
: Locking should be set to database-level locking.
This procedure does not affect the current transaction.
Row-level locking is required when caching tables from an Oracle database.
This procedure must be called from within a transaction. It has the effect of setting the locking level for subsequent transactions for the connection that invoked it. The new lock level does not affect the current transaction. It takes effect at the beginning of the next transaction.
This procedure enables an application to change the lock timeout interval of the current connection. The change takes effect immediately and applies to all subsequent statements in the current transaction and all subsequent transactions on the connection.
The lock wait interval is the number of seconds to wait for a lock when there is contention on it. You can also indicate a fraction of a second.
Lock wait intervals are imprecise, and may be exceeded, generally by no more than 100 milliseconds, due to the scheduling of the agent that detects timeouts. This imprecision does not apply to zero second timeouts, which are always reported immediately.
Cache grid uses message wait time with lock wait time. When using cache grid, lock wait times are approximately half the value you have specified. If your applications require the full lock wait time, specify twice the desired seconds.
ttLockWait
has the required parameters:
Parameter | Type | Description |
---|---|---|
seconds |
NUMBER (8,1) NOT NULL |
Number of seconds to wait for a lock when there is contention on it. You can also specify fractions of a second. Valid values are 0.0 to 1000000.0 inclusive. |
To indicate a six second lock wait, use:
CALL ttLockWait (6);
To indicate a tenth of a second lock wait, use:
CALL ttLockWait (0.1);
When a lock is not immediately available to a TimesTen transaction, it waits a predetermined amount of time to try to get the lock. After that it times out the lock request and returns error TT6003
to the application. By default, TimesTen uses a value of 10
seconds for lock timeouts. If a value of 0
is specified, transactions do not wait for any unavailable locks.
This procedure returns information about transaction log holds, including those created on behalf of incremental backups, replication peers, active standby pairs (and any subscribers), AWT cache groups, persistent XLA subscribers, XA, long-running transactions and checkpoints. This procedure can help diagnose situations where it appears that checkpoint operations are not purging all unneeded transaction log files.
Applications should monitor log holds and the accumulation of log files. For more information, see "Show replicated log holds" in the Oracle TimesTen In-Memory Database Replication Guide and "Monitoring accumulation of transaction log files" in the Oracle TimesTen In-Memory Database Operations Guide.
ttLogHolds
returns the result set:
Column | Type | Description |
---|---|---|
HoldLFN |
TT_INTEGER NOT NULL |
Returns the transaction log file number of the hold. |
HoldLFO |
TT_BIGINT NOT NULL |
Returns the transaction log file offset of the hold. |
type |
TT_CHAR (30) NOT NULL |
Returns the type of hold, one of:
|
description |
TT_VARCHAR (1024) NOT NULL |
Describes the type-specific object for which the hold was created. Each description corresponds with the Type returned. Descriptions are one of:
|
CALL ttLogHolds(); < 0, 1148544, Long-Running XA Transaction , 0x1-476c6f62616c-5861637431 > < 0, 1149752, Long-Running Transaction, 4.2 > < 0, 1149992, Checkpoint , sample.ds1 > < 0, 1150168, Checkpoint , sample.ds0 >
The following example shows the output of ttLogHolds
built-in procedure for an active standby pair replication scheme, where the active master is master1
and the standby master is master2
with a single subscriber, subscriber1
.
Command> call ttLogHolds; < 0, 3569664, Checkpoint , master1.ds0 > < 0, 15742976, Checkpoint , master1.ds1 > < 0, 16351496, Replication , ADC6160529:SUBSCRIBER1 > < 0, 16351640, Replication , ADC6160529:MASTER2 > 4 rows found.
The following example shows the progress of the asynchronous propagation for an AWT cache group to the Oracle database. The description field contains "_ORACLE
" to identify the transaction log hold for the AWT cache group propagation.
Command> call ttLogHolds(); < 0, 18958336, Checkpoint , cachealone1.ds0 > < 0, 19048448, Checkpoint , cachealone1.ds1 > < 0, 19050904, Replication , ADC6160529:_ORACLE > 3 rows found.
This procedures set the value of PERM_IN_USE_HIGH_WATER
column in the MONITOR
table to the current value of PERM_IN_USE_SIZE
connection attribute and sets the value of the TEMP_IN_USE_HIGH_WATER
column in the MONITOR
table to the current value of TEMP_IN_USE_SIZE
connection attribute. These columns are useful for sizing databases during application development and deployment.
This procedure clears the statistics for the specified table, causing the TimesTen query optimizer to use estimates or default values for subsequent queries involving the table. The procedure is useful if statistics are assumed to be out of date and an application wants to use built-in default values. This procedure removes all rows from the TBL_STATS
and COL_STATS
system tables that pertain to the specified tables. See "SYS.TBL_STATS" and "SYS.COL_STATS" in Oracle TimesTen In-Memory Database System Tables and Views Reference.
This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName
is not specified, because the procedure operates on the current user's tables if tblName
is not specified.
This procedure requires the ALTER ANY TABLE
privilege if user is not the table owner.
ttOptClearStats
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) |
Name of an application table. Can include table owner. If tblName is the empty string or is not specified, statistics are cleared for all the current user's tables in the database.
Using a synonym to specify a table name is not supported. |
invalidate |
TT_INTEGER |
0 (no) or 1 (yes). Default is 0 .
If If |
CALL ttOptClearStats ( 'SALLY.ACCTS', 1 );
Clears the statistics for the SALLY.ACCTS
table and reprepares all commands that affect the ACCTS
table.
CALL ttOptClearStats();
Clears the statistics for all the current user's tables and reprepares all commands that affect these tables.
CALL ttOptClearStats('', 0);
Clears the statistics for all the current user's tables without repreparing commands that reference these tables.
This built-in procedure either forces a recompilation should a dependent command be invoked again, or removes such command from the cache and it must be re-prepared by the user.
Scenarios in which you may want to call this procedure include:
After all needed statistics have been collected.
When table cardinalities have been changed significantly.
The procedure either marks a command as needing recompilation or as invalidated.
Neither option stops execution of a command.
ttOptCmdCacheInvalidate
has these parameters:
Parameter | Type | Description |
---|---|---|
tblname |
TT_CHAR(61) |
The name of the table for which the dependent commands should be invalidated or recompiled. |
invalidate |
TT_INTEGER |
Forces recompilation or invalidates the dependent commands.
|
To recompile dependent commands on the table tab1, use:
CALL ttOptCmdCacheInvalidate ('tab1', 1);
To invalidate the dependent commands on table tab1, use:.
CALL ttOptCmdCacheInvalidate ('tab1', 2);
The ttOptEstimateStats
procedure updates the statistics for the specified table. This procedure estimates statistics by looking at a random sample of the rows in the specified table(s). The sample size is the number of rows specified (if sampleStr
has the form '
n
ROWS'
) or a percentage of the total number of rows (if sampleStr
has the form '
p
PERCENT
'
).
The procedure operates on all tables of the current user if tblName
is not specified.
This procedure requires no privilege if the user is the table owner, or if tblName
is not specified.This procedure requires the ALTER ANY TABLE
privilege if the user is not the table owner.
ttOptEstimateStats
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) |
Name of an application table. Can include table owner. If tblName is an empty string, statistics are estimated for all the current user's tables in the database.
Using a synonym to specify a table name is not supported. |
invalidate |
TT_INTEGER |
0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared.
The |
sampleStr |
TT_VARCHAR (255) NOT NULL |
String of the form 'n ROWS ', where n is an INTEGER greater than zero; or 'p PERCENT ', where p is a floating point number between 0.0 and 100.0 inclusive. |
CALL ttOptEstimateStats ( 'ACCTS', 1, '5 PERCENT' ); CALL ttOptEstimateStats ( 'ACCTS', 1, '75 ROWS' );
The TimesTen statistics include the number of rows in each table, the number of unique values in each column, and the minimum and maximum values in each column. TimesTen assumes a uniform distribution of column values.
This procedure only runs faster than ttOptUpdateStats
when you sample less than 50 percent of the rows in the table.
Estimates are not computed on columns that are longer than 2,048 bytes, and statistics for these columns are not updated. To update statistics on columns longer than 2,048 bytes, use the ttOptUpdateStats
built-in procedure. (For varying length columns, this procedure updates statistics only if the column has a maximum length of 2,048 bytes or less.)
If a very small value is chosen for the sampleStr
parameter, this procedure runs quickly but may result in suboptimal execution plans. For "good" distributions of data, a 10 percent selection is a good choice for computing statistics quickly without sacrificing plan accuracy. If the number of rows specified is sufficiently large or the table in question is sufficiently small, to improve performance TimesTen computes exact statistics on all columns that have a length of 2,048 bytes or less. For example, the only difference between
ttOptEstimateStats ('ACCTS', 1, '100 PERCENT' )
and
ttOptUpdateStats( 'ACCTS', 1 )
is that the former does not compute statistics for long columns.
The statistics are stored in the TBL_STATS
and COL_STATS
system tables.
For performance reasons, ttOptEstimateStats
does not hold a lock on tables or rows when computing statistics. Computing statistics can still slow performance. Estimating statistics generally provides better performance than computing exact statistics.
If you estimate or update statistics with an empty table list, statistics on system tables are updated also, if you have privileges to update the system tables.
ttOptGetColStats
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR ( 61) |
Name of the table whose statistics are to be returned. If NULL is passed, then values for all tables are returned.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR (30) |
Name of the column for which statistics should be returned. If NULL is passed, statistics for all columns in the specified table are returned. |
ttOptGetColStats
returns the result set:
Column | Type | Description |
---|---|---|
tblName |
TT_CHAR (30) |
Name of the table.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR (30) |
Name of the column. |
stats |
TT_VARCHAR (409600) NOT NULL |
Statistics in text form. |
CALL ttOptGetColStats (); < T1 , X1, (2, 10, 10, 100 (,4, 40, 10 ,1, 10, 5) , (4, 20, 20 ,11, 20, 15) )>
This procedure returns the optimizer flag settings for the current transaction. The results are returned as a result set that can be retrieved using the ODBC SQLFetch
function or the JDBC ResultSet.get
XXX
()
method, just like the result of a SQL SELECT
statement. Applications can request the value of a specific optimizer flag by passing the flag name to ttOptGetFlag
. Alternatively, applications can request the values of all the optimizer flags by passing NULL
. The optimizer flags and their meanings are described under the ttOptSetFlag
built-in procedure.
ttOptGetFlag
has the parameter:
Parameter | Type | Description |
---|---|---|
flagName |
TT_CHAR (32) |
Name of the flag whose value is to be returned. If NULL is passed, the values of all flags are returned. |
ttOptGetFlag
returns the result set:
Column | Type | Description |
---|---|---|
flagName |
TT_VARCHAR (32) NOT NULL |
Name of the flag. See "ttOptSetFlag" for a description of possible flag values. |
value |
TT_INTEGER NOT NULL |
Current flag value, either 0 or 1 . |
This procedure returns the size of the free list of SQL compiled command cache. To reset the size of the cache, use ttOptSetMaxPriCmdFreeListCnt
for materialized views and ttOptSetMaxCmdFreeListCnt
for regular tables.
ttOptGetMaxCmdFreeListCnt
returns the results.
Column | Type | Description |
---|---|---|
retVal |
TT_VARCHAR (200) NOT NULL |
The size of the SQL compiled command cache. |
This procedure returns a single-row result set containing the join order for the current transaction. This result set can be retrieved using the ODBC SQLFetch
function or the JDBC ResultSet.get
XXX
()
method, just like the result of a SQL SELECT
statement. Join orders are described under the ttOptSetOrder
built-in procedure.
ttOptGetOrder
returns the result set:
Column | Type | Description |
---|---|---|
joinOrder |
TT_VARCHAR(1024) NOT NULL |
Optimizer join order for the current transaction. |
This procedure modifies the statistics for the specified columns with interval information. This procedure enables an application to set statistics manually rather than have TimesTen automatically compute them. This feature is useful for preparing commands before the data has been inserted or for seeing how table characteristics can affect the choice of execution plan. This procedure modifies the relevant row(s) in the COL_STATS
system table. Modifying interval statistics for a column that is not currently indexed has no effect.
Because this procedure can be used before any data is in the table, the values specified do not need to bear any relation to the actual values, although some basic validity checking is performed.
This procedure requires no privilege (if owner) or ALTER ANY TABLE
privilege (if not owner).
ttOptSetColIntvlStats
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL |
Name of an application table. Can include table owner. Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR(30) NOT NULL |
Name of a column in that table. |
invalidate |
TT_INTEGER |
0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed. This includes commands prepared by other users. If invalidate is 0 , the statistics are not considered to have been modified and existing commands are not reprepared. |
stats |
VARBINARY (409600) NOT NULL |
Sets stats for the column, using the format:
The modal value ( Because this parameter is a compound structure it cannot be parameterized using ODBC functions or described using the |
To set the following statistics for column t1.x1
:
Two intervals
Integer type
10 rows with null value
10 unique value
100 rows
Interval 1
(4 unique values besides the most frequently occurring value, 40 rows with values other than most frequently occurring value, 10 rows with most frequently occurring value, min = 1
, max = 10
, mod = 5)
Interval 2
(4 unique values besides the most frequently occurring value, 20 rows with values other than most frequently occurring, 20 rows with most frequently occurring value, min = 11
, max = 20
, mod = 15
)
Use the statement:
CALL ttOptSetColIntvlStats('t1', 'x1', 1, (2, 10, 10, 100, (4, 40, 10, 1, 10, 5), (4, 20, 20, 11, 20, 15)));
You must specify the minimum and maximum values in the interval as VARBINARY
. NULL
values are not permitted as minimum or maximum values. The value is stored in the platform-specific endian format.
This procedure modifies the statistics for the specified columns. This procedure enables an application to set statistics manually rather than have TimesTen automatically compute them. This feature is useful for preparing commands before the data has been inserted or for seeing how table characteristics can affect the choice of execution plan. This procedure modifies the relevant row(s) in the COL_STATS
system table.
Because this procedure can be used before the table is populated with data, the values specified do not need to bear any relation to the actual values, although some basic validity checking is performed.
This procedure requires no privilege (if owner) or ALTER ANY TABLE
privilege (if not owner).
ttOptSetColStats
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL |
Name of an application table. Can include table owner.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR(30) NOT NULL |
Name of a column in that table. |
num_Uniq |
TT_INTEGER NOT NULL |
Number of unique values in the column. |
minVal |
VARBINARY(1024) NOT NULL |
Minimum value in the column (possibly truncated). |
maxVal |
VARBINARY(1024) NOT NULL |
Maximum value in the column (possibly truncated). |
invalidate |
TT_INTEGER |
0 (no) or 1 (yes). If invalidate is 1 , all commands that reference the affected tables are automatically prepared again when re-executed. This includes commands prepared by other users. If invalidate is 0 , the statistics are not considered to have been modified and existing commands are not reprepared. |
num_Null |
TT_INTEGER |
Indicates the total number of NULL s in the column. |
You must specify the minimum and maximum values as VARBINARY
. NULL
values are not permitted as minimum or maximum values. The value is stored in the platform-specific endian format.
The statistics are treated as a single interval of column values that are uniformly distributed between the minimum value and the maximum value.
This procedure enables applications to alter the generation of execution plans by the TimesTen query optimizer. It sets flags to enable or disable the use of various access methods. The changes made by this call take effect during preparation of statements and affect all subsequent calls to the ODBC functions SQLPrepare
and SQLExecDirect
or the JDBC methods Connection.prepareCall
and Statement.execute
in the current transaction. All optimizer flags are reset to their default values when the transaction has been committed or rolled back. If optimizer flags are set while AutoCommit
is on, they are ignored.
ttOptSetFlag
has these parameters:
Parameter | Type | Description |
---|---|---|
optFlag |
TT_CHAR(32) NOT NULL |
Name of optimizer flag. |
optVal |
TT_INTEGER NOT NULL |
The value of the optimizer flag. The value is generally 0 (disable/disallow) or 1 (enable/allow), except as described under "Optimizer flags" below. |
When setting the optimizer flags, use the following character strings, which are not case sensitive:
Flag | Description |
---|---|
BranchAndBound |
Enables or disables branch and bound optimization. If enabled, TimesTen calculates the maximum cost of the query plan during a "zero phase," at the very beginning of the optimization process. If disabled, TimesTen does not perform this cost analysis. |
DynamicLoadEnable |
Enables or disables dynamic load of data from an Oracle database to a TimesTen dynamic cache group. By default, dynamic load of data from an Oracle database is enabled. |
DynamicLoadErrorMode |
Enables or disables dynamic load error mode. It controls output of error messages upon failure of a transparent load operation on a TimesTen dynamic cache group. Disabled by default. |
FirstRow |
Enables or disables first row optimization in a SELECT , UPDATE or DELETE statement . If the SQL keyword FIRST is used in the SQL statement, it takes precedence over this optimizer hint. The FIRST keyword enables first row optimization. |
ForceCompile |
Enables or disables forced compilation. If enabled, TimesTen recompiles the query and regenerates the query plan each time. If disabled, TimesTen does not compile the query plan even if it is available. |
GenPlan |
Enables or disables the creation of entries in the PLAN table for the rest of the transaction.
For an example, see "Instruct TimesTen to store the plan in the system PLAN table" in Oracle TimesTen In-Memory Database Operations Guide. |
GlobalLocalJoin |
Enables or disables global execution of SELECT and UNLOAD CACHE GROUP statements that contain a join in a grid. By default, these statements are executed locally.
See Oracle TimesTen Application-Tier Database Cache User's Guide for more details. |
GlobalProcessing |
Enables or disables global execution of SELECT and UNLOAD CACHE GROUP statements in a grid. By default, these statements are executed locally.
See Oracle TimesTen Application-Tier Database Cache User's Guide for more details. |
Hash |
Enables or disables the use of existing hash indexes in indexed table scans. |
HashGb |
Enables or disables the use of hash groups. |
IndexedOR |
Enables or disables serialized table scans. If disabled, TimesTen uses serialized table scans for IN... list conditions, else TimesTen uses multiple index scans for an OR condition. |
MergeJoin |
Enables or disables the use of merge joins. |
NestedLoop |
Refers to a common way of joining two tables. |
NoRemRowIdOpt |
Enables or disables internal generation of RowID s. If enabled, RowID s are not internally generated for optimization purposes. If disabled, RowID s may be internally generated, even if the row is not in the SELECT list. |
PassThrough |
Temporarily changes the pass through level for TimesTen Cache applications. The pass through level can be set at any time and takes effect immediately. Legal values for this flag are:
|
Range |
Enables or disables the use of existing range indexes in indexed table scans. |
Rowid |
Enables or disables the use of Row IDs. |
RowLock |
Allows or disallows the optimizer to consider using row locks. |
Scan |
Refers to full table scans. |
ShowJoinOrder |
Shows the join order of the tables in an optimizer scan. |
TblLock |
Enables or disables the optimizer to consider using table locks. |
TmpHash |
Enables or disables the use of a temporary hash scan. This is an index that is created during execution for use in evaluating the statement. Though index creation is time-consuming, it can save time when evaluating join predicates. |
TmpRange |
Performs a temporary range scan. Can also be used so that values are sorted for a merge join. Though index creation is time-consuming, it can save time when evaluating join predicates. |
TmpTable |
Stores intermediate results into a temporary table. This operation is sometimes chosen to avoid repeated evaluation of predicates in join queries or sometimes just to allow faster scans of intermediate results in joins. |
UseBoyerMooreStringSearch |
Enables or disables the Boyer-Moore string search algorithm. If enabled, Boyer-Moore string search algorithm is enabled. This can improve performance of LIKE operations. |
In addition, you can use the string AllFlags
to refer to all optimizer flags, and the string Default
to refer to the default flags. Default
excludes the GenPlan
flag but includes all other optimizer flags.
The value of each flag can be 1 or 0:
If 1, the operation is enabled
If 0, the operation is disabled unless absolutely necessary
Initially, all the flag values except GenPlan
are 1 (all operations are permitted).
For example, an application can prevent the optimizer from choosing a plan that stores intermediate results:
ttOptSetFlag ( 'TmpTable', 0 )
Similarly, an application can specify a preference for MergeJoin
:
ttOptSetFlag ( 'MergeJoin', 0 )
In the second example, the optimizer may still choose a nested loop join if a merge join is impossible (for example, if there is no merge-join predicate). Similarly, the optimizer may occasionally not be able to satisfy an application request to avoid table scans (when the Scan
flag is set to 0
).
You cannot specify that a particular operation is prohibited only at a certain step of a plan or that a particular join method always be done between two specific tables. Similarly, there is no way to specify that certain indexes be used or that a hash index be used to evaluate a specific predicate. Each operation is either fully permitted or fully restricted.
When a command is prepared, the current optimizer flags, index hints and join order are maintained in the structure of the compiled form of the command and are used if the command is ever reprepared by the system. See "The TimesTen Query Optimizer" in Oracle TimesTen In-Memory Database Operations Guide for an example of reprepared statements.
If both RowLock
and TblLock
are disabled, TimesTen uses row-locking. If both RowLock
and TblLock
are enabled, TimesTen uses the locking scheme that is most likely to have better performance:
TblLock status | RowLock status | Effect on the optimizer |
---|---|---|
Disabled | Disabled | Use row-level locking. |
Enabled | Disabled | Use table-level locking. |
Disabled | Enabled | Use row-level locking. |
Enabled | Enabled | Optimizer chooses row-level or table-level locking. |
In general, table-level locking is useful when a query accesses a significant portion of the rows of a table or when there are very few concurrent transactions accessing the table.
You can also set the join order using statement level optimizer hints in certain SQL statements. For details, see "Statement level optimizer hints" in the Oracle TimesTen In-Memory Database SQL Reference. Specifically, see the table, "Differences between statement level and transaction level optimizer hints" to understand the behavior of each style of hint.
This procedure sets the maximum count of the free list of SQL compiled commands for regular tables. To get the current setting use the ttOptGetMaxCmdFreeListCnt
procedure.
ttOptSetMaxCmdFreeListCnt
has the required parameter:
Parameter | Type | Description |
---|---|---|
maxCnt |
TT_INTEGER NOT NULL |
The max number of free SQL compiled commands for regular tables. |
This procedure sets the maximum count of the free list of SQL compiled commands that perform materialized view maintenance.
When this command is set, freeable materialized view compiled commands are counted separately from those of regular tables. If this command is not set, materialized view compiled commands are counted as regular commands.
ttOptSetMaxPriCmdFreeListCnt
has the required parameter:
Parameter | Type | Description |
---|---|---|
maxCnt |
TT_INTEGER NOT NULL |
The size of the SQL compiled command cache. |
This procedure specifies the order in which tables should be joined by the optimizer. The character string is a list of table names or table correlation names referenced in the query or a subquery, separated by spaces (not commas). The table listed first is scanned first by the plan. (It is outermost in a nested loop join, for example.) A correlation name is a shortcut or alias for a qualified table name. AutoCommit
must be set to OFF
when running this built-in procedure.
ttOptSetOrder
has the required parameter:
Parameter | Type | Description |
---|---|---|
join_Order |
TT_VARCHAR(1024) |
List of space-separated table or table correlation names. If an owner is required to distinguish the table name, use a table correlation name. If the joinOrder is not specified the query optimizer reverts to its default behavior. |
CALL ttOptSetOrder ('EMPS DEPTS ACCTS');
If an application makes the call:
call ttOptSetOrder('ORDERS CUSTOMERS');
The optimizer scans the ORDERS
table before scanning the CUSTOMERS
when evaluating the following query that lists all the customers who have at least one unshipped order:
SELECT CUSTOMERS.NAME FROM CUSTOMERS WHERE EXISTS (SELECT 1 FROM ORDERS WHERE CUSTOMERS.ID = ORDERS.CUSTID AND ORDER.STATUS ='UN-SHIPPED');
Consider an application that makes the following call.
ttOptSetOrder('DEPTS EMPS ACCTS');
The optimizer is prevented from executing a join between DEPTS
and ACCTS
when evaluating the number of employees working on a specific account:
SELECT COUNT(DISTINCT EMPS.ID) FROM ACCTS, DEPTS, EMPS WHERE ACCTS.DEPTS = DEPTS.ID AND EMPS.DEPTS = DEPTS.ID AND ACCTS.NUM = :AcctNum
If the application does not reset the join order and tries to prepare a command that does not reference each of the three tables (and no others), the optimizer issues warning number 965. The specified join order is not applicable. TimesTen considers valid join orders and ignores the specified join order when preparing the command.
A table alias name for a derived table is not supported in the join order. If you specify a table alias name, TimesTen returns the warning message 965 that indicates the order cannot be honored.
The string length is limited to 1,024 bytes. If a string exceeds this length, it is truncated and a warning is issued.
When correlation names referenced in subqueries are in included in the order, TimesTen may internally change the isolation mode.
When a command is prepared, the current optimizer flags, index hints, and join order are maintained in the structure of the compiled form of the command and are used if the command is ever reprepared by the system. See "The TimesTen Query Optimizer" in Oracle TimesTen In-Memory Database Operations Guide for an example of reprepared statements.
The changes made by this call take effect immediately and affect all subsequent calls to the ODBC function SQLPrepare
or the JDBC method Connection.prepareCall
in the current transaction. The query optimizer reverts to its default behavior for subsequent transactions.
The tables referenced by a query must exactly match the names given if the join order is to be used (the comparisons are not case sensitive). A complete ordering must be specified; there is no mechanism for specifying partial orders. If the query has a subquery then the join order should also reference the correlation names in the subquery. In essence, the join order should reference all the correlation names referenced in the query. The TimesTen optimizer internally implements a subquery as a special kind of join query with a GROUP BY
. For the join order to be applicable it should reference all the correlation names. If there is a discrepancy, Times issues a warning and ignores the specified join order completely.
You can also set the join order using statement level optimizer hints in certain SQL statements. For details, see "Statement level optimizer hints" in the Oracle TimesTen In-Memory Database SQL Reference. Specifically, see the section, "Differences between statement level and transaction level optimizer hints" to understand the behavior of each style of hint.
This procedure modifies the statistics for the specified table. This procedure enables an application to set statistics explicitly rather than have TimesTen automatically compute them.
This procedure requires no privilege (if owner) or ALTER ANY TABLE
privilege (if not owner).
ttOptSetTblStats
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL |
Name of an application table. Can include table owner.
Using a synonym to specify a table name is not supported. |
num_Rows |
TT_INTEGER NOT NULL |
Number of rows in the table. |
invalidate |
TT_INTEGER |
0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0 , the statistics are not considered to have been modified and existing commands are not reprepared. |
This feature is useful for preparing commands before the data has been inserted or for seeing how table size can affect the choice of an execution plan. Because the command can be used before any data is in the table, the values specified do not need to bear any relation to the actual values. This procedure modifies the relevant row(s) in the TBL_STATS
system table. See "SYS.TBL_STATS
" in Oracle TimesTen In-Memory Database System Tables and Views Reference.
This procedure returns the join order of the last prepared or executed SQL statement (SELECT
, UPDATE
, DELETE
, and INSERT SELECT
) in the current transaction. For a join order to be collected, use ttOptSetFlag
('ShowJoinOrder', 1)
or set the ttIsql
ShowJoinOrder
command to ON
(1) first in the same transaction. AUTOCOMMIT
must be off when using either of these commands. The join order is represented by the order of the table names.
ttOptShowJoinOrder
returns the result:
Column | Type | Description |
---|---|---|
joinOrder |
TT VARCHAR (4096) NOT NULL |
Table names, including owner name quantifiers and correlation name for each table if specified. Table names are returned in parentheses.
Using a synonym to specify a table name is not supported. |
>AUTOCOMMIT 0; > CALL ttOptSetFlag ('ShowJoinOrder', 1); >PREPARE SELECT * FROM t1; >CALL ttOptShowJoinOrder(); >( T1 )
You must call ttOptSetFlag
('ShowJoinOrder', 1)
or set the ttIsql
ShowJoinOrder
command to ON
(1
) before using this procedure.
This procedure works within one transaction and is not persistent across transactions.
This procedure returns the set of statements required to restore the table statistics to the current state. If no table is specified, it returns the set of statements required to restore the table statistics for all user tables that the calling user has permission to access.
ttOptStatsExport
has the parameter:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) NOT NULL |
Name of the table whose statistics are to be returned. If NULL is passed, then values for all tables are returned.
Using a synonym to specify a table name is not supported. |
ttOptStatsExport
returns the result set:
Column | Type | Description |
---|---|---|
stmt |
TT_VARCHAR (8300) NOT NULL |
The set of statements required to restore the table(s) statistics to the current state. |
This procedure updates the statistics for the specified table. TimesTen looks at the data in the table and updates the TBL_STATS
and COL_STATS
system tables. If the table is large, this process can take some time. Statistics are not computed automatically as rows are updated; an application must compute them explicitly by calling this procedure.
The procedure operates on all tables of the current user if tblName
is not specified.
This procedure requires no privilege if the user is the table owner, or if tblName
is not specified.This procedure requires the ALTER ANY TABLE
privilege if the user is not the table owner.
ttOptUpdateStats
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) |
Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the statistics for all the current user's tables are updated.
Using a synonym to specify a table name is not supported. |
invalidate |
TT_INTEGER |
0 (no) or 1 (yes). If invalidate is 1, marks all commands for reprepare on next execution except ALTER TABLE DROP TABLE , and the ALTER TABLE ADD COLUMN FOR SELECT * FROM TABLE statements. These exceptions require manual reprepare. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared.
The |
option |
TT_INTEGER |
Specifies whether to collect complete interval statistics information. Valid values for this option are:
1 - Do not collect complete interval statistics. Only single interval statistics are collected. The See the notes below for more information. |
CALL ttOptUpdateStats ( 'ACCTS', 1 );
Updates the ACCTS
table and causes all commands that reference the ACCTS
table to be re-prepared when they are next executed.
CALL ttOptUpdateStats('', 1);
Updates all the current user's tables and causes commands on those tables to be reprepared when they are next executed.
CALL ttOptUpdateStats('ACCTS', 0, 1);
Forces single interval statistics to be collected.
If the table name specified is an empty string, statistics are updated for all the current user's tables.
When complete interval statistics are collected, the total number of rows in the table is divided into 20 or less intervals and the distribution of each interval is recorded in the statistics. The new statistics contain the information:
Number of intervals
Total number of NULL
values in the column
Total number of NON NULL UNIQUE
values in the column
Total number of rows in the table
Interval information, where each interval contains:
The minimum value
The maximum value
The most frequently occurring value
The number of times the most frequent value occurred
The number of rows that have different values than the most frequent value
The number of unique values besides the most frequent value
Collection of complete interval statistics requires the data to be sorted.
If complete interval statistics are not selected, then statistics are collected by treating the entire distribution as a single interval.
For performance reasons, TimesTen does not hold a lock on tables or rows when computing statistics. However, computing statistics can still slow performance. Estimating statistics generally provides better performance than computing exact statistics. See "ttOptEstimateStats" for information on estimating statistics.
If you estimate or update statistics with an empty table list, statistics on system tables are updated also, if you have privileges to update the system tables.
This procedure enables applications to alter the generation of execution plans by the TimesTen query optimizer. Applications can call this procedure to disable the use of a set of indexes or enable the consideration of only a set of indexes for each correlation used in a query. Enabling the consideration of an index does not guarantee that the plan generated uses the index. Depending on the estimated cost, the optimizer might choose to use a serialization scan or a materialization scan to access the associated correlation if these scans resulted in a better plan than the ones that use the specified index.
The changes made by this call take effect immediately and affect all subsequent calls to the ODBC functions SQLPrepare
and SQLExecDirect
or the JDBC methods Connection.prepareCall
and Statement.execute
in the current transaction until the applications explicitly issue a call to clear it. The setting is cleared whenever a new transaction is started.
AutoCommit
must be set to OFF
when running this built-in procedure.
ttOptUseIndex
has a single comma-delimited string parameter, indOption
, of type TT_VARCHAR(1024)
with these components:
Component | Description |
---|---|
IndexName |
The name of the user-defined index or '_TMPRANGE' for temporary range index or '_TMPHASH' for temporary hash index. If index name is omitted, the setting applies to all indexes of the specified correlation. |
CorrelationName |
The correlation name of the table. If a table is defined with a correlation name in the FROM clause, use this correlation name instead of the table name when specifying the index hint for this table. If correlation name is omitted for an entry, the setting affects all tables with the specified index name. |
0 | 1 |
Disables(0 ) or enables (1 ) the use of the index specified by IndexName . |
CALL ttOptUseIndex('"3456"."1234", t1, 0'); CALL ttOptUseIndex('data1.i1, data1.t1, 0'); CALL ttOptUseIndex('i1, t1, 0');
If ttOptUseIndex
is called without a parameter or with a NULL
value, TimesTen clears the previous index hint.
This procedure returns result statistics about PL/SQL library cache performance and activity.
ttPLSQLMemoryStats
returns the results in the following columns:
Columns | Type | Description |
---|---|---|
paramName |
TT_VARCHAR(30) NOT NULL |
The name of the result statistic returned in this row. |
paramValue |
BINARY_FLOAT NOT NULL |
The value of the result statistic returned in this row. |
The following statistics are returned:
Gets
: Number of times a lock was requested for a PL/SQL object.
GetHits
: Number of times a PL/SQL object's handle was found in memory.
GetHitRatio
: Ratio of GetHits
to Gets
.
Pins
: Number of times a PIN
was requested for PL/SQL objects.
PinHits
: Number of times all the metadata pieces of the library object were found in memory.
PinHitRatio
: Ratio of PinHits
to Pins
.
Reloads
: Any PIN
of an object that is not the first PIN
performed since the object handle was created, and which requires loading the object from the database.
Invalidations
: Total number of times objects in this namespace were marked invalid because a dependent object was modified.
CurrentConnectionMemory
: The total amount of heap memory, in MB, allocated to PL/SQL on this database connection.
DeferredCleanups
: Total number of times a deferred cleanup occurred.
connect "DSN=sample"; Connection successful: DSN=sample;UID=timesten;DataStore=/scratch/timesten/sample; DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8; PermSize=128;TypeMode=0;PLSQL_MEMORY_SIZE=32; PLSQL_MEMORY_ADDRESS=20000000;PLSQL=1;(Default setting AutoCommit=1) Command> create procedure hello is begin dbms_output.put_line('Hello, World!'); end; > / Procedure created. Command> call ttPlsqlMemoryStats; < Gets, 485.00000 > < GetHits, 444.000000 > < GetHitRatio, .9154639 > < Pins, 260.00000 > < PinHits, 178.000000 > < PinHitRatio, .6846154 > < Reloads, 4.000000 > < Invalidations, 0.000000e+00 > < CurrentConnectionMemory, 56.00000 > 9 rows found.
This procedure returns the RAM autoreload policy used to determine if a database is reloaded into RAM after an invalidation. The policy can be either autoreload
or noautoreload
.
ttRamPolicyAutoReloadGet
returns the results:
Column | Type | Description |
---|---|---|
flag |
TT_INTEGER |
The policy used to determine if the database is reloaded into RAM after an invalidation. Valid values are:
|
This procedure determines the RAM autoreload policy if a database is invalidated. The policy can be either autoreload
or noautoreload
.
ttRamPolicyAutoReloadSet
has the parameters:
Parameter | Type | Description |
---|---|---|
flag |
TT_INTEGER NOT NULL |
The policy used to determine if the database is reloaded into RAM after an invalidation. Valid values are:
|
To automatically reload a database into RAM after an invalidation, use:
CALL ttRamPolicyAutoReloadSet(1);
This procedure returns the RAM policy used to determine when a database is loaded into memory. The policy can be either always
, manual
, or inUse
.
ttRamPolicyGet
returns the results:
Column | Type | Description |
---|---|---|
ramPolicy |
TT_VARCHAR (10) |
The policy used to determine when the database is loaded into system RAM. Valid values are:
|
ramGrace |
TT_INTEGER |
If the ramPolicy is inUse , this field reports the number of seconds the database is kept in RAM after the last application has disconnected. Otherwise, this field is NULL . |
ttRamPolicySet
This procedure defines the policy used to determine when a database is loaded into memory. The policy can be either always
, manual
, or inUse
.
ttRamPolicySet
has the parameters:
Parameter | Type | Description |
---|---|---|
ramPolicy |
TT_VARCHAR (10) NOT NULL |
The policy used to determine when the database is loaded into system RAM. Valid values are:
|
ramGrace |
TT_INTEGER |
Sets the number of seconds the database is kept in RAM after the last application has disconnected. This number is only effective if ramPoliy is inUse . This parameter is optional, and when omitted or set to NULL , the existing ramGrace period is left unchanged. |
To set the policy for loading a database into RAM to be inUse
and for the database to kept in RAM for 10 seconds after the last application has disconnected, use:
CALL ttRamPolicySet('inUse', 10);
ttRamPolicyGet
This procedure scans the indicated table (or all the current user's tables) to find redundant indexes. It returns the names of the redundant indexes and a suggestion for which to drop.
ttRedundantIndexCheck
has the parameter:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) |
Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the redundant indexes for all the current user's tables.
Using a synonym to specify a table name is not supported. |
ttRedundantIndexCheck
returns the result:
Column | Type | Description |
---|---|---|
redundancy |
TT_VARCHAR (1024) NOT NULL |
The names of redundant indexes and a suggestion for which index to drop. |
Create table y
with a primary key. Then create index i
. TimesTen returns a warning that a redundant index is being created. Create another index, i1
. The command fails and TimesTen returns an error. Call this procedure to show the warnings.
CREATE TABLE y (ID tt_integer primary key); CREATE INDEX i ON y (id); Warning 2240: New non-unique index I has the same key columns as existing unique index Y; consider dropping index I CREATE INDEX i1 ON y (id); 2231: New index I1 would be identical to existing index I The command failed. CALL ttredundantindexcheck ('y'); < Non-unique index SCOTT.Y.I has the same key columns as unique index SCOTT.Y.Y; consider dropping index SCOTT.Y.I > 1 row found.
This procedure changes the state of the active database in an active standby pair from ACTIVE
to IDLE
. Use this procedure when reversing the roles of the master databases in an active standby pair.
ttRepTransmitGet
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStateSave
ttRepStateSet
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttReplicationStatus
has the optional parameters:
Parameter | Type | Description |
---|---|---|
subscriber |
TT_VARCHAR (200) |
Subscriber of interest or NULL for all subscribers. If the parameter is provided, then it names a replication subscriber about which information is sought. If the parameter is not provided, then information on replication subscribers defined for the current database is returned. |
hostname |
TT_VARCHAR (200) |
The host name of one or more stores that are configured to receive updates from the executing store; if NULL , then receiving stores are identified by subscriber alone. If both receiver and host name are NULL , then all receiving stores are selected. |
ttReplicationStatus
returns the result set:
Column | Type | Description |
---|---|---|
subscriber |
TT_VARCHAR(200) NOT NULL |
Subscriber name. |
hostName |
TT_VARCHAR(200) NOT NULL |
Name of the system that hosts the subscriber. |
port |
TT_INTEGER NOT NULL |
TCP/IP port used by the subscriber agent to receive updates from the master. A value of 0 indicates replication has automatically assigned the port. |
pState |
TT_CHAR(10) NOT NULL |
Current replication state of the subscriber with respect to its master database. The values of the result column are:
|
logs |
TT_INTEGER NOT NULL |
Number of transaction log files the master database is retaining for a subscriber. |
lastMsg |
TT_INTEGER |
Seconds since last interaction or NULL . |
replicationName |
TT_CHAR(30) NOT NULL |
Name of replication scheme. |
replicationOwner |
TT_CHAR(30) NOT NULL |
Owner of replication scheme. |
Command> call ttReplicationStatus(); < MASTER2, HOST1, 0, start , 1, 257142, \ _ACTIVESTANDBY , TTREP > 1 row found. Command> call ttReplicationStatus('master2', 'host1'); < MASTER2, HOST1, 0, start , 1, 266439, \ _ACTIVESTANDBY , TTREP > 1 row found.
If the receiver
parameter is not NULL
, only the status of the given receiver is returned. If the receiver
parameter is NULL
, the status of all subscribers is returned.
This procedure is supported only for TimesTen Data Manager ODBC applications. It is not supported for TimesTen Client or JDBC applications.
ttRepDeactivate
ttRepPolicySet
ttRepStop
ttRepSubscriberStateSet
ttRepSyncGet
ttRepSyncSet
ttRepTransmitSet
This procedure returns the replication restart policy used to determine when the TimesTen for the connected database should run. The policy can be always
, manual
, or norestart
.
ttRepPolicyGet
returns the results:
Column | Type | Description |
---|---|---|
repPolicy |
TT_VARCHAR (10) |
The policy used to determine when the TimesTen replication agent for the database should run. Valid values are:
|
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
This procedure defines the replication restart policy used to determine when the TimesTen for the connected database should run. The policy can be either always
, manual
, or norestart
.
ttRepPolicySet
has this parameter:
Parameter | Type | Description |
---|---|---|
repPolicy |
TT_VARCHAR (10) NOT NULL |
Specifies the policy used to determine when the TimesTen replication agent for the database should run. Valid values are:
|
To set the policy for TimesTen replication agent to always, use the following.
CALL ttRepPolicySet('always');
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicyGet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
This procedure returns the number of seconds that was most recently specified as the query threshold for the replication agent. The number of seconds returned may not be the same as the query threshold in effect. Setting a new value for the query threshold takes effect the next time the replication agent is started.
ttRepQueryThresholdGet
returns the result:
Column | Type | Description |
---|---|---|
repQueryThreshold |
TT_INTEGER |
The number of seconds that a replication query executes before returning an error. |
To get the replication query threshold value, use:
CALL ttRepQueryThresholdGet; < 4 > 1 row found.
ttRepDeactivate
ttReplicationStatus
ttRepPolicyGet
ttRepQueryThresholdSet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
ttRepTransmitSet
This procedure specifies the number of seconds that a query can be executed by the replication agent before TimesTen writes a warning to the support log and throws an SNMP trap. The specified value takes effect the next time the replication agent is started. The query threshold for the replication agent applies to SQL execution on detail tables of materialized views, ON DELETE CASCADE
operations and some internal operations that execute SQL statements.
ttRepQueryThresholdSet
has the parameter:
Parameter | Type | Description |
---|---|---|
seconds |
TT_INTEGER NOT NULL |
Number of seconds a SQL statement can be executed by the replication agent before TimesTen writes a warning to the support log and throws an SNMP trap. The value must be greater than or equal to 0. Default is 0 and indicates that Timesten does not write any warnings. |
To set the replication query threshold value to four seconds, use:
CALL ttRepQueryThresholdSet(4);
ttRepDeactivate
ttReplicationStatus
ttRepPolicyGet
ttRepQueryThresholdGet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
ttRepTransmitSet
The replication agent does not start if the database does not participate in any replication scheme.
When using this procedure, no application, including the application making the call, can be holding a connection that specifies database-level locking (LockLevel=1
).
ttRepDeactivate
ttRepTransmitGet
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncSet
ttRepSyncGet
This procedure returns the current replication state of a database in an active standby pair.
ttRepStateGet
returns the result:
Column | Type | Description |
---|---|---|
state |
TT_VARCHAR (20) NOT NULL |
The current replication state of the database. One of:
|
gridState |
TT_VARCHAR (20) NOT NULL |
The current grid state of the database. One of:
|
To determine the replication and the grid state of the active standby pair, use:
Call ttRepStateGet(); <STANDBY, NO GRID> Call ttRepStateGet(); <ACTIVE, NO GRID> Call ttRepStateGet(); <ACTIVE, AVAILABLE> Call ttRepStateGet(); <ACTIVE, UNAVAILABLE>
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStateSave
ttRepStateSet
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
This procedure saves the state of a remote peer database in an active standby pair to the currently connected database. Currently, may only be used to indicate to the active database that the standby database, storeName
on hostName,
has failed, and that all updates on the active database should be replicated directly to the read-only subscribers.
ttRepStateSave
has these parameters:
Parameter | Type | Description |
---|---|---|
state |
TT_VARCHAR (20) NOT NULL |
The replication state of the indicated database. May only be specified as FAILED in this release. Recording that a standby database has failed indicates that all replicated updates are to be sent directly from the active database to the read-only subscribers. |
storeName |
TT_VARCHAR (200) NOT NULL |
Name of the database for which the state is indicated. |
hostName |
TT_VARCHAR (200) |
Name of the host where the database resides. |
To indicate to the active database that the standby database standby
on host backup1
has failed, use:
ttRepStateSave('FAILED', 'standby', 'backup1');
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStateGet
ttRepStateSet
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
This procedure sets the replication state of a database in an active standby pair replication scheme. Currently, ttRepStateSet
may only be used to set the state of a database to ACTIVE
, indicating that it is to take the active role in an active standby pair. ttRepStateSet
may only be executed in the following situations:
A database has had a CREATE ACTIVE STANDBY PAIR
command executed and no failures have occurred since.
A database is currently in the STANDBY
state, and the other database in the active standby pair has had its state changed from ACTIVE
to IDLE
using the ttRepDeactivate
procedure.
A database has just recovered from the local transaction log and was in the ACTIVE
state before it went down.
ttRepStateSet
has the parameter:
Parameter | Type | Description |
---|---|---|
state |
TT_VARCHAR (20) NOT NULL |
The replication state of the database. Must be ACTIVE , in this release. Setting a store to ACTIVE designates it as the active database in an active standby pair. |
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStateGet
ttRepStateSave
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
When using this procedure, no application, including the application making the call, can be holding a connection that specifies database-level locking (LockLevel=1
).
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
This procedure changes a replicating subscriber's state with respect to the executing master store.
ttRepSubscriberStateSet('replicationName', 'replicationOwner', 'subscriberStoreName', 'subscriberHostName', newStateCode)
ttRepSubscriberStateSet
has these parameters:
Parameter | Type | Description |
---|---|---|
replicationName |
TT_CHAR (30) |
The name of the replication scheme on which to operate. May be NULL to indicate all replication schemes. |
replicationOwner |
TT_CHAR (30) |
The owner of the replication scheme. May be NULL to indicate all replication scheme owners. |
subscriberStoreName |
TT_VARCHAR (200) |
The name of the subscribing database whose state is to be set. May be NULL to indicate all stores on host subscriberHostName . |
subscriberHostName |
TT_VARCHAR (200) |
The subscriber's host. May be NULL to indicate all hosts of subscribing peers. |
newStateCode |
TT_INTEGER |
An integer code representing the specified subscriber's new state:
All other state codes are disallowed. (This procedure cannot set a subscriber state to "failed.") "Setting the replication state of subscribers" in the Oracle TimesTen In-Memory Database Replication Guide for more information. |
For the replication scheme named REPL.REPSCHEME
, the following directs the master database to set the state of the subscriber database (SUBSCRIBERDS ON SYSTEM1
) to Stop
(2
):
CALL ttRepSubscriberStateSet('REPSCHEME', 'REPL', 'SUBSCRIBERDS','SYSTEM1', 2);
To direct the master database to set the state of all its subscribers to Pause
(1
), use:
CALL ttRepSubscriberStateSet( , , , , 1 );
Leaving a parameter empty is equivalent to using NULL
.
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepStop
ttRepSubscriberWait
ttRepTransmitGet
ttRepTransmitSet
This procedure causes the caller to wait until all transactions that committed before the call have been transmitted to the subscriber subscriberStoreName
. It also waits until the subscriber has acknowledged that the updates have been durably committed at the subscriber database.
Call this procedure in a separate transaction, when no other transaction is pending on the active database. This call returns an error if any transactions on the active database are open.
If you set the waitTime
parameter to -1
and the subscriberStoreName
parameter to NULL
, the ttRepSubscriberWait
procedure does not return until all updates committed up until the time of the procedure call have been transmitted to all subscribers, and all subscribers have acknowledged that the updates have been durably committed.
The ttRepSubscriberWait
procedure should not be used when an urgent response is required. Instead, you should use the return receipt service.
Note:
If this procedure is called after all write transaction activity is quiesced at a store (there are no active transactions and no transactions have started), it may take 60 seconds or longer before the subscriber sends the acknowledgment that all updates have been durably committed at the subscriber.ttRepSubscriberWait('replicationName', 'replicationOwner', 'subscriberStoreName', 'subscriberHostName', waitTime)
ttRepSubscriberWait
has these parameters:
Parameter | Type | Description |
---|---|---|
replicationName |
TT_CHAR (30) |
The name of the replication scheme on which to operate. May be NULL to indicate all replication schemes. |
replicationOwner |
TT_CHAR (30) |
The owner of the replication scheme. May be NULL to indicate all replication scheme owners. |
subscriberStoreName |
TT_VARCHAR (200) |
The name of the subscribing database whose state is to be set. May be NULL to indicate all stores on host subscriberHostName . |
subscriberHostName |
TT_VARCHAR(200) |
The subscriber's host. May be NULL to indicate all hosts of subscribing peers. |
waitTime |
TT_INTEGER NOT NULL |
Number of seconds to wait for the specified subscriber(s). A value of -1 indicates to wait forever. This parameter is required and may not be NULL . |
ttRepSubscriberWait
returns the result set:
Column | Type | Description |
---|---|---|
timeOut |
BINARY(1) |
0x00 - The wait succeeded within the allotted waitTime ; the specified subscribers are up to date at the time this procedure was called. TimesTen returns 0x01 if not enough time has been granted. |
If there is one defined replication scheme REPOWNER.REPSCHEME
, to direct the transmitting database to wait ten minutes for subscriber REP
2 on SERVER2
to catch up, use:
CALL ttRepSubscriberWait('REPSCHEME','REPOWNER', 'REP2', 'SERVER2', 600);
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSyncGet
ttRepSyncSet
This procedure returns static attributes associated with the caller's use of the replication- based return service. This procedure operates with either the RETURN RECEIPT
or RETURN TWOSAFE
service.
ttRepSyncGet
returns the result set:
Column | Type | Description |
---|---|---|
requestReturn |
BINARY(1) descr |
0 (default) - Don't wait for return notification configured with the RETURN RECEIPT BY REQUEST or RETURN TWOSAFE BY REQUEST option.
|
returnWait |
TT_INTEGER |
Specifies the number of seconds to wait for return service acknowledgment. The default value is 10 seconds. A value of `0' means that there is no wait time.This attribute persists across transaction boundaries and applies to all RETURN services independent of the BY REQUEST option. |
localAction |
TT_INTEGER |
The current LOCAL ACTION configuration for RETURN services.
|
To retrieve the caller's requestReturn value, use:
SQLCHAR requestReturn[1]; SQLINTEGER len; rc = SQLExecDirect ( hstmt , (SQLCHAR *) "{CALL ttRepSyncGet( NULL )}" , SQL_NTS ) rc = SQLBindCol ( hstmt , /* ColumnNumber */ 1 , /* TargetType */ SQL_C_BINARY ) , /* TargetValuePtr */ requestReturn ,./* BufferLength */ sizeof requestReturn , /* StrLen_ */ &len ); rc = SQLFetch( hstmt ); if ( requestReturn[0] ) { ... }
When called within a standalone transaction, ttRepSyncGet
always returns the default value for requestReturn
.
Applications can call ttRepSyncGet
at any point within a transaction in which it is used to request the BY REQUEST
return service for that transaction.
If you call ttRepSyncGet
in a transaction that does not update any RETURN RECEIPT BY REQUEST
or RETURN TWOSAFE BY REQUEST
replication elements, the call has no external effect.
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncSet
This procedure sets static attributes associated with the caller's use of the replication-based return service. This procedure operates with either the RETURN RECEIPT
or RETURN TWOSAFE
service.
ttRepSyncSet
has these optional parameters:
Parameter | Type | Description |
---|---|---|
requestReturn |
BINARY(1) |
0x00 - Turn off the return service for the current transaction.
You can use this parameter to turn on or turn off return services only when the replication subscribers have been configured with |
returnWait |
TT_INTEGER |
Specifies the number of seconds to wait for return service acknowledgment. The default value is 10 . A value of 0 means there is no wait time.
This timeout value overrides the value set by the The timeout set by this parameter persists across transaction boundaries and applies to all return services independent of the |
localAction |
TT_INTEGER |
Action to be performed in the event the subscriber cannot acknowledge commit of the transaction within the timeout period specified by returnWait . This parameter can only be used for return twosafe transactions. Set to NULL when using the RETURN service.
|
To enable the return receipt service in the current transaction for all the replication elements configured with RETURN RECEIPT BY REQUEST
or RETURN TWOSAFE BY REQUEST
, use:
rc = SQLExecDirect ( hstmt, (SQLCHAR *)"{CALL ttRepSyncSet( 0x01 )}", SQL_NTS )
The call to enable the return receipt service must be part of the transaction (AutoCommit
must be off).
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
This procedure queries a subscriber database in a replication scheme configured with a return service and a RETURN DISABLE
failure policy to determine whether return service blocking for the subscriber has been disabled by the failure policy.
The ttRepSyncSubscriberStatus
procedure returns the failure status of the subscriber database with the specified name on the specified host. You can specify only the storeName
. However, an error is generated if the replication scheme contains multiple subscribers with the same name on different hosts.
ttRepSyncSubscriberStatus
has these parameters:
Parameter | Type | Description |
---|---|---|
subscriber |
TT_VARCHAR (200) NOT NULL |
The name of the subscribing database to be queried. |
hostName |
TT_VARCHAR (200) |
The host name of one or more stores that are configured to receive updates from the executing store; if NULL , then receiving stores are identified by receiver alone. If both receiver and host name are NULL , then all receiving stores are selected. |
ttRepSyncSubscriberStatus
returns:
Column | Type | Description |
---|---|---|
disabled |
TT_INTEGER |
Value is either:
|
This procedure returns the status of transmission of updates to subscribers for the current transaction. The corresponding ttRepSyncSet
built-in procedure enables you to stop transmission of updates to subscribers for the length of a transaction.
ttRepTransmitGet
returns the result:
Column | Type | Description |
---|---|---|
transmit |
TT_INTEGER |
0 - Updates are not being transmitted to any subscribers for the remainder of the transaction on the connection.
|
To return the transmit status on the active database in an active standby pair, use:
CALL ttRepTransmitGet();
ttRepDeactivate
ttReplicationStatus
ttRepPolicySet
ttRepStateSave
ttRepStateSet
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepTransmitSet
This procedure stops subsequent updates on the connection it is executed in from being replicated to any subscriber. Use this procedure with care since it could easily lead to transactional inconsistency of remote stores if partial transactions are replicated. If updates are disallowed from getting replicated, the subscriber stores diverge from the master store.
ttRepTransmitSet
has the parameter:
Parameter | Type | Description |
---|---|---|
transmit |
TT_INTEGER NOT NULL |
When set to 1, updates are transmitted to subscribers on the connection after the built-in is executed. (This is the default.)
When set to 0, updates are not transmitted to any subscribers for the remainder of the transaction in which this call was issued on the connection that issued it. |
To activate the active database in an active standby pair, use:
CALL ttRepTransmitSet(1);
To deactivate the active database in an active standby pair, use:
CALL ttRepTransmitSet(0);
ttRepDeactivate
ttReplicationStatus
ttRepPolicySet
ttRepStateSave
ttRepStateSet
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepTransmitGet
This procedure checks on the status of a RETURN RECEIPT
or RETURN TWOSAFE
replication transaction. Using the built-in procedure ttRepXactTokenGet
, you can get the token of a RETURN RECEIPT
or RETURN TWOSAFE
transaction. This is then passed as an input parameter to this built-in procedure. Only a token received from ttRepXactTokenGet
may be used. The procedure returns a list of rows each of which have three parameters, a subscriber name, the replication status with respect to the subscriber and an error string that is only returned if a RETURN TWOSAFE
replication transaction began but did not complete commit processing.
Note:
The error parameter is only returned forRETURN TWOSAFE
transactions.ttRepXactStatus
has the parameter:
Parameter | Type | Description |
---|---|---|
xactID |
VARBINARY (10000) |
If no parameter is specified, status is returned for one of the following:
|
ttRepXactStatus
returns the result set:
Column | Type | Description |
---|---|---|
subscriberName |
TT_CHAR (61) |
The name of the database that subscribes to tables updated in the transaction. The name returns as: store_name @ host_name . |
state |
TT_CHAR (2) |
The state of the transaction with respect to the subscribing database. The return values are one of the following:
|
errorString |
TT_VARCHAR (2000) |
Error string retuned by the subscriber agent describing the error it encountered when applying the twosafe transaction. If no error is encountered, this parameter is NULL . Non-null values are only returned when this procedure is called inside a twosafe replication transaction that has begun, but has not yet completed, processing a commit. |
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
ttRepXactTokenGet
This procedure returns a token for RETURN RECEIPT
or RETURN TWOSAFE
replication transactions. Depending on the input parameter, type, it returns either:
A token to the most recently committed RETURN RECEIPT
transaction on the connection handle in which it is invoked.
A token to the most recent transaction on the connection handle in which it is invoked that has begun commit processing on a transaction in RETURN TWOSAFE
mode.
This procedure can be executed in any subsequent transaction or in the same transaction after commit processing has begun for a transaction in RETURN TWOSAFE
replication.
ttRepXactTokenGet
has these parameters:
Parameter | Type | Description |
---|---|---|
type |
TT_CHAR (2) NOT NULL |
The type of transaction desired:
|
ttRepXactTokenGet
returns the result set:
Column | Type | Description |
---|---|---|
token |
VARBINARY (10000) |
A VARBINARY token used to represent the transaction desired. |
ttRepDeactivate
ttRepTransmitSet
ttReplicationStatus
ttRepPolicySet
ttRepStart
ttRepStop
ttRepSubscriberStateSet
ttRepSubscriberWait
ttRepSyncGet
ttRepSyncSet
ttRepXactStatus
This procedure explicitly sets the value for the user-specified column ID. Updates presented to the application by the Transaction Log API may contain information about the columns of a table. This column information contains a system-specified column number and a user-specified column identifier. The user-specified column ID has the value 0 until set explicitly by this call.
The system assigns an ID to each column during a CREATE TABLE
or ALTER TABLE
operation. Setting a user-assigned value for the column ID enables you to have a unique set of column numbers across the entire database or a specific column numbering system for a given table.
ttSetUserColumnID
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL |
Table name.
Using a synonym to specify a table name is not supported. |
colName |
TT_CHAR(30) NOT NULL |
Column name. |
repID |
TT_INTEGER NOT NULL |
Integer identifier. |
This procedure explicitly sets the value of the user table ID. The table that each row is associated with is expressed with two codes: an application-supplied code called the user table ID and a system-provided code called the system table ID. Updates are presented to the application by the Transaction Log API in the form of complete rows. The user table ID has the value zero until explicitly set with the ttSetUserTableID
procedure.
ttSetUserTableID
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) NOT NULL |
Table name.
Using a synonym to specify a table name is not supported. |
repID |
BINARY(8) NOT NULL |
Integer identifier. |
This procedure estimates the size of a table or view and the size of indexes. It returns a single row with a single DOUBLE
column with the estimated number of bytes for the table. The table can be specified as either a table name or a fully qualified table name. A non-NULL
nrows
parameter causes the table size to be estimated assuming the statistics of the current table scaled up to the specified number of rows. If the nrows
parameter is NULL
, the size of the table is estimated with the current number of rows.
The current contents of the table are scanned to determine the average size of each VARBINARY
and VARCHAR
column. If the table is empty, the average size of each VARBINARY
and VARCHAR
column is estimated to be one-half its declared maximum size. The estimates computed by ttSize
include storage for the table itself, VARBINARY
and VARCHAR
columns and all declared indexes on the table.
The table is scanned when this built-in procedure is called. The scan of the table can be avoided by specifying a non-NULL
frac
value, which should be between 0
and 1
. This value estimates the average size of varying-length columns. The maximum size of each varying-length column is multiplied by the frac
value to compute the estimated average size of VARBINARY
or VARCHAR
columns. If the frac
parameter is not given, the existing rows in the table are scanned and the average length of the varying-length columns in the existing rows is used. If frac
is omitted and the table has no rows in it, then frac
is assumed to have the value 0.5
.
ttSize
has these parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR(61) NOT NULL |
Name of an application table. Can include table owner. This parameter is required.
Using a synonym to specify a table name is not supported. |
nRows |
TT_INTEGER |
Number of rows to estimate in a table. This parameter is optional. |
frac |
BINARY_DOUBLE |
Estimated average fraction of VARBINARY or VARCHAR column sizes. This parameter is optional. |
ttSize
returns the following result set.
Column | Type | Description |
---|---|---|
size |
BINARY_DOUBLE NOT NULL |
Estimated size of the table, in bytes. |
CALL ttSize('ACCTS', 1000000, NULL); CALL ttSize('ACCTS', 30000, 0.8); CALL ttSize('SALES.FORECAST', NULL, NULL);
When using ttSize
, you must first execute the command and then fetch the results. For example:
double size; SQLLEN len; rc = SQLExecDirect(hstmt, "call ttSize('SalesData', 250000, 0.75)", SQL_NTS); rc = SQLBindColumn(hstmt, 1, SQL_C_DOUBLE, &size, sizeof double, &len); rc = SQLFetch(hstmt); rc = SQLFreeStmt(hstmt, SQL_CLOSE);
. . . . . . String URL="jdbc:timesten:MyDataStore"; Connection con; double tblSize=0; . . . . . . con = DriverManager.getConnection(URL); CallableStatement cStmt = con.prepareCall(" {CALL ttSize('SalesData', 250000, 0.75) }"); if( cStmt.execute() ) { rs=cStmt.getResultSet(); if (rs.next()) { tblSize=rs.getDouble(1); } rs.close(); } cStmt.close(); con.close(); . . . . . .
The ttSize
procedure enables you to estimate how large a table will be with its full population of rows based on a small sample. For the best results, populate the table with at least 1,000 typical rows.
This procedure returns information about all prepared SQL statements in the TimesTen SQL command cache.
ttSQLCmdCacheInfo
has the optional parameter:
Parameter | Type | Description |
---|---|---|
sqlCmdID |
TT_INTEGER for 32-bit systems
|
The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied, information is displayed for all commands. |
ttSQLCmdCacheInfo
returns the result set:
Column | Type | Description |
---|---|---|
sqlCmdID |
TT_INTEGER NOT NULL for 32-bit systems
|
The unique identifier of a command. |
privateCommandConnectionID |
TT_INTEGER |
If the command is private, this is the connection ID of the connection where it was prepared. If not a private command, this value is 2048 . |
executions |
TT_BIGINT
|
Counts the number of executions of the command. |
prepares |
TT_BIGINT NOT NULL |
Counts the number of prepares for the command. |
reprepares |
TT_BIGINT NOT NULL |
Counts the number of reprepares for the command. |
freeable |
TT_TINYINT NOT NULL |
Indicates whether this command can be garbage collected by the subdaemon.
|
size |
TT_INTEGER NOT NULL |
The total space (bytes) allocated for this command in the command cache. |
owner |
TT_CHAR(31) NOT NULL |
The user who created the command. |
queryText |
TT_VARCHAR (409600) NOT NULL |
The full SQL text for the current command. |
To display command information in ttIsql
for all the current valid commands, use:
Command> call ttsqlcmdcacheinfo; < 51635464, 2048, 12, 12, 0, 1, 3056, SYS , delete fr om sys.idl_sb4$ where obj#=:1 and part=:2 > < 43437072, 2048, 5, 5, 0, 1, 1960, SYS , select obj# from sys.objerror$ > < 51620736, 2048, 4, 4, 0, 1, 2736, SYS , delete from sys.obj$ where obj# = :1 > < 51680216, 2048, 1, 1, 0, 1, 3592, BWAF4EVR , call ttsqlc mdcacheinfo(51623232) > < 51676856, 2048, 2, 2, 0, 0, 3552, BWAF4EVR , call ttsqlc mdcacheinfo > < 43438936, 2048, 5, 5, 0, 1, 3200, SYS , select obj# from sys.syn$ where owner=:1 and name=:2 > < 44066504, 2048, 0, 14, 0, 1, 5640, SYS , select nul l from sys.obj$ where obj#=:1 and type#=:2 and obj# not in (select p_obj# from d ependency$ where p_obj# = sys.obj$.obj#) > < 51649488, 2048, 1, 1, 0, 1, 2344, BWAF4EVR , create tabl e tab1 (c1 number primary key not null, c2 number) > < 51671608, 2048, 1, 1, 0, 1, 4656, BWAF4EVR , call ttSQLC mdCacheInfo2(51635464) > < 51666232, 2048, 1, 1, 0, 1, 2048, BWAF4EVR , call ttSQLC mdCacheInfoGet > < 51612064, 2048, 4, 4, 0, 1, 8424, SYS , select o.ow ner#, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, d.d_attrs from sys.dependency$ d, sys.obj$ o where d.p_obj#=:1 and (d.p_ti mestamp=nvl(:2,d.p_timestamp) or d.property=2) and o.owner#=nvl(:3,o.owner#) and d.d_obj#=o.obj# order by o.obj# > < 43415648, 2048, 4, 4, 0, 1, 4544, BWAF4EVR , create acti ve standby pair sampledb_1122, bwaf4evr_dummy1 subscriber bwaf4evr_dummy2 > < 43431912, 2048, 5, 5, 0, 1, 4720, SYS , select owne r#,name,namespace,obj#,type#,ctime,mtime,stime,status,flags from sys.obj$ where obj#=:1 > < 51657712, 2048, 4, 4, 0, 1, 3552, BWAF4EVR , call ttSQLC mdCacheInfo > < 51653200, 2048, 1, 1, 0, 1, 1816, BWAF4EVR , call ttxlab ookmarkcreate('mybookmark', 0x01) > < 43420768, 2048, 1, 1, 0, 1, 2064, BWAF4EVR , create tabl e tab1 (c1 number, c2 number) > < 44058168, 2048, 14, 14, 0, 1, 7760, SYS , select o. owner#,o.obj#,u.name,o.name,o.namespace from sys.user$ u, sys.obj$ o where u.use r#=o.owner# and o.type#=:1 and not exists (select p_obj# from sys.dependen cy$ where p_obj# = o.obj#) order by o.obj# for update > < 49370616, 2048, 1, 1, 0, 0, 4024, SYS , select u.us er#, u.password, u.identification, u.astatus from sys.user$ u where u.name = :na me and u.type# = 1 > < 51655376, 2048, 2, 2, 0, 1, 2528, BWAF4EVR , select * fr om tab1 > < 51638280, 2048, 4, 4, 0, 1, 2544, SYS , delete from sys.objauth$ where obj#=:1 > < 43423200, 2048, 14, 14, 0, 1, 5520, SYS , select ow ner#,name,namespace,obj#,type#,ctime,mtime,stime,status,flags from sys.obj$ wher e owner#=:1 and name=:2 and namespace=:3 > < 51668216, 2048, 1, 1, 0, 1, 3592, BWAF4EVR , call ttSQLC mdCacheInfo(51635464) > < 51661208, 2048, 3, 3, 0, 1, 4640, BWAF4EVR , call ttSQLC mdCacheInfo2 > < 43428992, 2048, 5, 5, 0, 1, 2800, SYS , select sys. objectSequence.nextval from dual > < 51629120, 2048, 12, 12, 0, 1, 3040, SYS , delete fr om sys.idl_char$ where obj#=:1 and part=:2 > < 51641192, 2048, 2, 2, 0, 1, 2112, BWAF4EVR , create tabl e tab1 (c1 number not null, c2 number) > < 43442488, 2048, 5, 5, 0, 1, 4616, SYS , insert into sys.obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,flags) value s(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10) > < 51632072, 2048, 12, 12, 0, 1, 3040, SYS , delete fr om sys.idl_ub2$ where obj#=:1 and part=:2 > < 49375216, 2048, 0, 1, 0, 0, 4232, SYS , select 1 fr om sys.sysauth$ s where (s.grantee# = :userid or s.grantee# = 1) and (s.privileg e# = :priv or s.privilege# = 67) > < 51626304, 2048, 12, 12, 0, 1, 3040, SYS , delete fr om sys.idl_ub1$ where obj#=:1 and part=:2 > < 51645776, 2048, 1, 1, 0, 1, 2344, BWAF4EVR , create tabl e tab1 (c1 number primary key not null, col2 number) > < 51623232, 2048, 4, 4, 0, 1, 2704, SYS , delete from sys.source$ where obj#=:1 > 32 rows found.
To display command information in ttIsql
for sqlCmdID
527973892
, use:
Command> call ttSQLCmdCacheInfo(527973892); < 527973892, 2048, 0, 1, 0, 1, 2872, TTUSER, select * from t1 where x1 in (select x2 from t2) or x1 in (select x3 from t3) order by 1, 2, 3 > 1 row found.
To display the information formatted vertically in ttIsql
, use:
Command> vertical call ttSQLCmdCacheInfo; ...
To display the information vertically in ttIsql
for sqlCmdID
51623232
, use:
Command> vertical call ttsqlcmdcacheinfo(51623232); SQLCMDID: 51623232 PRIVATE_COMMAND_CONNECTION_ID: 2048 EXECUTIONS: 4 PREPARES: 4 REPREPARES: 0 FREEABLE: 1 SIZE: 2704 OWNER: SYS QUERYTEXT: delete from sys.source$ where obj#=:1 1 row found.
This procedure returns information about all prepared SQL statements in the TimesTen SQL command cache.
It is similar to ttSQLCmdCacheInfo
, but returns additional columns, as indicated by the result set documentation.
ttSQLCmdCacheInfo2
has the optional parameter:
Parameter | Type | Description |
---|---|---|
sqlCmdID |
TT_INTEGER for 32-bit systems
|
The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied, information is displayed for all commands. |
ttSQLCmdCacheInfo2
returns the result set:
Column | Type | Description |
---|---|---|
sqlCmdID |
TT_INTEGER NOT NULL for 32-bit systems
|
The unique identifier of a command. |
privateCommandConnectionID |
TT_INTEGER |
If the command is private, this is the connection ID of the connection where it was prepared. If not a private command, this value is 2048 . |
executions |
TT_BIGINT
|
Counts the number of executions of the command. |
prepares |
TT_BIGINT NOT NULL |
Counts the number of prepares for the command. |
reprepares |
TT_BIGINT NOT NULL |
Counts the number of reprepares for the command. |
freeable |
TT_TINYINT NOT NULL |
Indicates whether this command can be garbage collected by the subdaemon.
|
size |
TT_INTEGER NOT NULL |
The total space (bytes) allocated for this command in the command cache. |
owner |
TT_CHAR(31) NOT NULL |
The user who created the command. |
queryText |
TT_VARCHAR (409600) NOT NULL |
The full SQL text for the current command. |
fetchCount |
TT_BIGINT |
The total number of fetch executions done for this statement. The number of fetches depends on TT_PREFETCH_COUNT . The pre-fetch count has a default value of 5. |
startTime |
TT_TIMESTAMP |
The time when the statement started. The value is in the form: YYYY-MM-DD HH:MI:SS.FFF |
maxExecuteTime |
NUMBER |
The maximum wall clock execute time in seconds for this statement. |
lastExecuteTime |
NUMBER |
Last measured execution time in seconds of the command. |
minExecuteTime |
NUMBER |
If SqlCmdSampleFactor > 0 , minimum execute time in seconds, otherwise 0.0 . |
To display command information in ttIsql
for all the current valid commands, use:
Command> call ttSQLCmdCacheInfo2; ...
The following example shows the difference in output between ttSQLCmdCacheInfo
and ttSQLCmdCacheInfo2
:
Command> call ttSQLCmdCacheInfo; ... < 51635464, 2048, 12, 12, 0, 1, 3056, SYS , delete fr om sys.idl_sb4$ where obj#=:1 and part=:2 > ... Command> call ttSQLCmdCacheInfo2; ... < 51635464, 2048, 12, 12, 0, 1, 3056, SYS , delete fr om sys.idl_sb4$ where obj#=:1 and part=:2, 0, 2013-10-28 16:47:09.173000, 0, 0, 0 > ...
This procedure displays information about the commands in the TimesTen SQL command cache.
ttSQLCmdCacheInfoGet
returns the result set:
Column | Type | Description |
---|---|---|
cmdCount |
TT_INTEGER NOT NULL |
Number of commands in the cache. |
freeableCount |
TT_INTEGER NOT NULL |
Count of number of freeable commands that can be garbage collected by the subdaemon at that moment. This number is obtained by examining the command information. |
size |
TT_BIGINT NOT NULL |
The current total space allocated to store all the cached commands, in bytes. |
To display the command count, freeable command count, and total space allocated to the command cache, use:
Command> call ttSQLCmdCacheInfoGet; < 5,4,12316 > 1 row found
This procedure returns all detailed runtime query plans for SQL statements in the TimesTen SQL command cache. If no argument is supplied, this procedure displays the query plan for all valid commands in the TimesTen cache. For invalid commands, an error is returned that displays the text of the query and the syntax problems.
ttSQLCmdQueryPlan
has the optional parameter:
Parameter | Type | Description |
---|---|---|
sqlCmdID |
TT_INTEGER for 32-bit systems
|
The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied displays the query plan for all valid commands in the TimesTen cache. |
ttSQLCmdQueryPlan
returns the result set:
Column | Type | Description |
---|---|---|
sqlCmdID |
TT_INTEGER NOT NULL for 32-bit systems
|
The unique identifier of a command in the TimesTen command cache. |
queryText |
TT_VARCHAR(409600) |
The first 1024 characters of the SQL text for the current command. |
step |
TT_INTEGER |
The step number of current operation in this run-time query plan. |
level |
TT_INTEGER |
The level number of current operation in this run-time query plan. |
operation |
TT_CHAR(31) |
The operation name of the current step in this run-time query plan. |
tblName |
TT_CHAR(31) |
Name of the table used in this step, if any.
Using a synonym to specify a table name is not supported. |
tblOwnerName |
TT_CHAR(31) |
Name of the owner of the table used in this step, if any. |
indexName |
TT_CHAR(31) |
Name of the index used in this step, if any. |
indexedPred |
TTVARCHAR(1024) |
In this step, if an index is used, the indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression. |
nonIndexedPred |
TT_VARCHAR(1024) |
In this step, if a non-indexed predicate is used, the non-indexed predicate is printed if available. Not all expressions can be printed out and the output may be fragmented and truncated. "..." represents the unfinished portion of the expression. |
To display the query plan for SQLCmdID
528078576
:
Command> call ttSqlCmdQueryPlan(528078576); < 528078576, select * from t1 where 1=2 or (x1 in (select x2 from t2, t5 where y2 in (select y3 from t3)) and y1 in (select x4 from t4)), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528078576, <NULL>, 0, 4, RowLkSerialScan , T1 , TTUSER , , , > < 528078576, <NULL>, 1, 7, RowLkRangeScan , T2 , TTUSER , I2 , , > < 528078576, <NULL>, 2, 7, RowLkRangeScan , T5 , TTUSER , I2 , , > < 528078576, <NULL>, 3, 6, NestedLoop , , , , , > < 528078576, <NULL>, 4, 6, RowLkRangeScan , T3 , TTUSER , I1 , ( (Y3=Y2; ) ) , > < 528078576, <NULL>, 5, 5, NestedLoop , , , , , > < 528078576, <NULL>, 6, 4, Filter , , , , , X1 = X2; > < 528078576, <NULL>, 7, 3, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 8, 2, Filter , , , , , > < 528078576, <NULL>, 9, 2, RowLkRangeScan , T4 , TTUSER , I2 , , Y1 = X4; > < 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin) , , , , , > < 528078576, <NULL>, 11, 0, Filter , , , , , > 13 rows found.
To display query plans for all valid queries, omit the argument for ttSqlCmdQueryPlan
:
< 528079360, select * from t7 where x7 is not null or exists (select 1 from t2,t3 where not 'tuf' like 'abc'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528079360, <NULL>, 1, 3, RowLkRangeScan , T2 , TTUSER , I2 , , NOT(LIKE( tuf ,abc ,NULL )) > < 528079360, <NULL>, 2, 3, RowLkRangeScan , T3 , TTUSER , I2 , , > < 528079360, <NULL>, 3, 2, NestedLoop , , , , , > < 528079360, <NULL>, 4, 1, NestedLoop(Left OuterJoin) , , , , , > < 528079360, <NULL>, 5, 0, Filter , , , , , X7 > < 527576540, call ttSqlCmdQueryPlan(527973892), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 527576540, <NULL>, 0, 0, Procedure Call , , , , , > < 528054656, create table t2(x2 int,y2 int, z2 int), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528066648, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528066648, <NULL>, 0, 0, Insert , T2 , TTUSER , , , > < 528013192, select * from t1 where exists ( select * from t2 where x1=x2) or y1=1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528061248, create index i1 on t3(y3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528070368, call ttOptSetOrder('t3 t4 t2 t1'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 528070368, <NULL>, 0, 0, Procedure Call , , , , , > < 528018856, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 527573452, call ttsqlCmdCacheInfo(527973892), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> > < 527573452, <NULL>, 0, 0, Procedure Call , , , , , > ….. /* more rows here */
The ttSQLExecutionTimeHistogram
built-in procedure returns a histogram of SQL execution times for either a single SQL command or all SQL commands if command cache sampling is enabled.
ttSQLExecutionTimeHistogram
has the optional parameter:
Parameter | Type | Description |
---|---|---|
sqlCommandID |
TT_INTEGER for 32-bit systems
|
The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied displays information about all current commands in the TimesTen command cache. |
ttSQLExecutionTimeHistogram
returns the result set:
Column | Type | Description |
---|---|---|
histogramSamples |
TT_BIGINT |
The number of SQL command execution time operations have been measured since either the database was started or the ttStatsConfig built-in procedure was used to reset the statistics. |
totalExecuteTime |
NUMBER |
The accumulated wall clock execution time when sampling in seconds. |
bucketUpperBound |
NUMBER |
The upper limit in seconds of execution time. |
count |
TT_BIGINT |
The number of SQL commands with time less than or equal to ExecutionTimeLimit and greater than ExecutionTimeLimit from the previous row or 0. |
The following example shows the output for the ttSQLExecutionTimeHistogram
built-in procedure:
The following example of the ttSQLExecutionTimeHistogram
built-in procedure shows that a total of 1919 statements executed. The total time for all 1919 statements to execute was 1.090751 seconds. This example shows that SQL statements ran in the following time frames:
278 statements executed in a time frame that was less than or equal to .00001562 seconds.
1484 statements executed in a time frame that was greater than .00001562 seconds and less than or equal to .000125 seconds.
35 statements executed in a time frame that was greater than .000125 seconds and less than or equal to .001 seconds.
62 statements executed in a time frame that was greater than .001 seconds and less than or equal to .008 seconds.
60 statements executed in a time frame that was greater than .008 seconds and less than or equal to .064 seconds.
Command> call ttSQLExecutionTimeHistogram; < 1919, 1.090751, .00001562, 278 > < 1919, 1.090751, .000125, 1484 > < 1919, 1.090751, .001, 35 > < 1919, 1.090751, .008, 62 > < 1919, 1.090751, .064, 60 > < 1919, 1.090751, .512, 0 > < 1919, 1.090751, 4.096, 0 > < 1919, 1.090751, 32.768, 0 > < 1919, 1.090751, 262.144, 0 > < 1919, 1.090751, 9.999999999E+125, 0 > 10 rows found.
The ttStatsConfig
built-in procedure controls statistics collection and parameters. This procedure takes a name/value pair as in put and outputs a single row result set corresponding to the name/value pair parameters.
ttStatsConfig
has the parameters:
Parameter | Type | Description |
---|---|---|
param |
VARCHAR2(50) NOT NULL |
The unique identifier of a SQL command in the TimesTen command cache. |
value |
VARCHAR2(200) |
The value of the specified command. If no value is supplied displays the query plan for all valid commands in the TimesTen cache. |
ttStatsConfig
returns the result set:
Column | Type | Description |
---|---|---|
param |
VARCHAR2(50) NOT NULL |
The unique identifier of a SQL command in the TimesTen command cache. |
value |
VARCHAR2(200) |
The value of the specified command. If no value was supplied, this is the current value of the command. |
These name/value pairs can be returned in the result set:
Name | Value | Description |
---|---|---|
SQLCmdSampleFactor |
0 <= value <= 60000 |
The frequency at which a SQL command sample is taken. The default is 0 . A value of 0 indicates that sampling is turned off. A value greater than 0 indicates that a sample is taken at that interval of SQL statements. For example, a value of 10 indicates that for every 10th SQL statement executed, the wall clock time of that execution is captured. |
ConnSampleFactor |
C,S
|
The unique identifier of a SQL command in the TimesTen command cache. If you do not supply a value, TimesTen displays the current value of the command. |
SQLCmdHistogramReset |
0 or not |
The existing SQL execution time statistics are reset if the specified value is nonzero. |
StatsLevel |
NONE
|
Specifies the level of collection for database and operating system statistics. TimesTen collects these statistics for a variety of purposes, including making self-management decisions.
Setting the The default setting of When the Setting the StatsLevel parameter to |
Sample every command:
Command> call ttStatsConfig('SqlCmdSampleFactor',1); < SQLCMDSAMPLEFACTOR, 1 > 1 row found.
Check whether sampling:
Command> call ttStatsConfig('SqlCmdSampleFactor'); < SQLCMDSAMPLEFACTOR, 1 > 1 row found.
Sample every fifth statement on connection 1.
Command> call ttStatsConfig('ConnSampleFactor', '1,5'); < CONNSAMPLEFACTOR, 1,5 > 1 row found.
Turn off sampling on connection 1.
Command> call ttStatsConfig('ConnSampleFactor', '1,0'); < CONNSAMPLEFACTOR, 1,0 > 1 row found.
Check data store statistics collection level.
Command> call ttstatsconfig('StatsLevel'); < STATSLEVEL, TYPICAL > 1 row found.
Turn off data store statistics collection.
Command> call ttstatsconfig('StatsLevel','None'); < STATSLEVEL, NONE > 1 row found.
This built-in procedure evaluates a SELECT
query on a table in an Oracle database and generates a CREATE TABLE
SQL statement that you can choose to execute. The TimesTen CREATE TABLE
statement matches the result set column names and types.
This procedure does not create the TimesTen table, it only returns a statement that identifies the table schema.
For more details and usage information, see "Loading data from an Oracle database into a TimesTen table" in the Oracle TimesTen In-Memory Database Operations Guide.
This procedure requires no privileges. The session user must have all required privileges to execute the query on the Oracle database.
ttTableSchemaFromOraQueryGet
has the parameters:
Parameter | Type | Description |
---|---|---|
tblOwner |
TT_CHAR (30) |
TimesTen table owner (optional). If not provided, the connection ID is used. |
tblName |
TT_CHAR (30) NOT NULL |
Table name for the CREATE TABLE statement.
The specified TimesTen table cannot be a system table, a synonym, a view, a materialized view or a detail table of a materialized view, a global temporary table or a cache group table. |
Query |
TT_VARCHAR (409600) NOT NULL |
A SELECT query on an Oracle database to derive the table column definition.
Any expressions in the |
ttTableSchemaFromOraQueryGet
returns the result set:
Column | Type | Description |
---|---|---|
createSQL |
TT_VARCHAR (409600) NOT NULL |
A CREATE TABLE statement that matches the result set of the SELECT query on an Oracle database. |
This example, returns the CREATE TABLE
statement to create the TimesTen HR.EMPLOYEES
table with all columns found in the Oracle database HR.EMPLOYEES
table.
Command> call ttTableSchemaFromOraQueryGet('hr','employees', 'SELECT * FROM hr.employees'); < CREATE TABLE "HR"."EMPLOYEES" ( "EMPLOYEE_ID" number(6,0) NOT NULL, "FIRST_NAME" varchar2(20 byte), "LAST_NAME" varchar2(25 byte) NOT NULL, "EMAIL" varchar2(25 byte) NOT NULL, "PHONE_NUMBER" varchar2(20 byte), "HIRE_DATE" date NOT NULL, "JOB_ID" varchar2(10 byte) NOT NULL, "SALARY" number(8,2), "COMMISSION_PCT" number(2,2), "MANAGER_ID" number(6,0), "DEPARTMENT_ID" number(4,0) ) > 1 row found.
The query on the Oracle database cannot have any parameter bindings.
TimesTen returns an error if the query cannot be described on the Oracle database, for example, if there is a syntax error.
If an output column type does not have a matching type in TimesTen, TimesTen outputs a warning and the following line for the column definition: >>>>
column_name column_type
/*
reason
*/
If the query on the Oracle database outputs types not supported by TimesTen, you can add a CAST
clause in the SELECT
list to explicitly change the output to a TimesTen supported type. Column aliases can be specified for expressions in the SELECT
list.
If the query on the Oracle database has LOB output, it is mapped to a VAR
type.
The ttVersion
utility lists the TimesTen release information, including: number, platform, instance name, instance administrator, instance home directory, daemon home directory, port number and build timestamp. You can specify various levels of output:
You can specify ttVersion
with no options to list abbreviated output.
You can specify the -m
option to list enhanced output.
You can specify an attribute to list output only for a specific attribute.
ttVersion
returns the result set:
Column | Type | Description |
---|---|---|
major1 |
TT_INTEGER NOT NULL |
The major release number. Indicates releases with major infrastructure and functionality changes. |
major2 |
TT_INTEGER NOT NULL |
The second major release number. Indicates a version with new functionality changes, but no infrastructure changes. |
minor |
TT_INTEGER NOT NULL |
The minor release number. Indicates a release that contains all bug fixes since the previous maintenance release. |
patch |
TT_INTEGER NOT NULL |
Indicates a release with minor bug fixes. |
portpatch |
TT_INTEGER NOT NULL |
Indicates a release with patch fixes for particular platforms. |
This procedure enables applications to specify that operations executed on the current connection should return a warning if they allocate memory and find that memory is low. If the value is set, a warning is returned for any operation that does an allocation and finds total memory in use to be above the connection's threshold value as specified by the PermWarnThreshold
and TempWarnThreshold
connection attributes.
ttWarnOnLowMemory
has these parameters:
Parameter | Type | Description |
---|---|---|
permanent |
TT_INTEGER NOT NULL |
1 - Enable warnings for the permanent data partition
|
temporary |
TT_INTEGER NOT NULL |
1 - Enable warnings for the permanent data partition
|
CALL ttWarnOnLowMemory(1, 0);
Enables low memory warnings for the permanent data partition only.
By default, TimesTen does not issue low memory warnings for either partition. Applications that want to receive these warnings must call this procedure. This procedure is connection specific, and so you must issue it for each connection upon which warnings are desired. Also, the current setting does not persist to subsequent connections.
This procedure returns transaction ID information for interpreting lock messages. The two result columns of ttXactIdGet
are used in combination to uniquely identify a transaction in a database. Taken individually, the columns are not interesting. The result should only be used to correlate with other sources of transaction information. The numbers may not follow a strict pattern.
ttXactIdGet
returns the result set:
Column | Type | Description |
---|---|---|
xactID |
TT_INTEGER |
Connection ID. |
counter |
TT_BIGINT |
An increasing number that distinguish successive transactions of the same transaction ID. |
Command > automcommit 0; Command > call ttXactIdGet; <2,11> 1 row found Command > commit; Command > call ttXactIdGet <3, 12> 1 row found
The output correlates to the values printed in lock error messages and ttXactAdmin
lock information output.
ttXlaBookmarkCreate
has the parameter:
Parameter | Type | Description |
---|---|---|
bookmark |
TT_CHAR (31) NOT NULL |
The name of the bookmark to be created. |
replicated |
BINARY(1) |
0x00 or NULL (equivalent) for non-replicated bookmarks (default setting).
If |
For non-replicated bookmark, execute the following:
Command > call ttXlaBookmarkCreate('mybookmark');
or:
Command> call ttxlabookmarkcreate('mybkmk2',0x00);
For a replicated bookmark, execute the following:
Command > call ttXlaBookmarkCreate('mybookmark', 0x01);
For more details on XLA bookmarks, including replicated XLA bookmarks, see "About XLA bookmarks" in the Oracle TimesTen In-Memory Database C Developer's Guide.
You can also create a bookmark when you call ttXlaPersistOpen
function to initialize an XLA handle. See "Creating or reusing a bookmark" in Oracle TimesTen In-Memory Database C Developer's Guide.
This procedure deletes the specified bookmark. The bookmark cannot be deleted while it is in use.
ttXlaBookmarkDelete
has the parameter:
Parameter | Type | Description |
---|---|---|
bookmark |
TT_CHAR (31) NOT NULL |
The name of the bookmark to be deleted. |
Before dropping a table that is subscribed to by an XLA bookmark, you must first drop all XLA bookmarks or unsubscribe from XLA tracking.
This procedure configures persistent XLA tracking of a table. This procedure cannot be executed when the specified bookmark is in use.
ttXlaSubscribe
has the parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) NOT NULL |
The name of the table to be tracked.
Using a synonym to specify a table name is not supported. |
bookmark |
TT_CHAR (31) NOT NULL |
The name of the bookmark that the application uses to track this table. |
Alternatively, the ttXlaTableStatus
function subscribes the current bookmark to updates to the specified table, or determines whether the current bookmark is already monitoring DML records associated with the table. See "Specifying which tables to monitor for updates" in Oracle TimesTen In-Memory Database C Developer's Guide
This procedure stops persistent XLA tracking of a table. This procedure cannot be executed when the specified bookmark is in use.
ttXlaUnsubscribe
has the parameters:
Parameter | Type | Description |
---|---|---|
tblName |
TT_CHAR (61) NOT NULL |
The name of the table on which XLA tracking should be stopped.
Using a synonym to specify a table name is not supported. |
bookmark |
TT_CHAR (31) NOT NULL |
The name of the bookmark that the application uses to track this table. |
Before dropping a table that is subscribed to by an XLA bookmark, you must first drop all XLA bookmarks or unsubscribe from XLA tracking.