|Oracle® TimesTen In-Memory Database Troubleshooting Procedures Guide
Part Number E13075-07
The following sections provide information to help you diagnose and remedy some of the problems encountered while using a TimesTen database:
Note:If you are still having problems with your database after following the troubleshooting recommendations in this chapter, please contact Technical support.
This section describes what to check if you are unable to start or stop the TimesTen main daemon.
|Possible cause||What to do|
|Incorrect privilege||You need the
|Another process is using the TimesTen daemon port.||Use the
|TimesTen daemon is already running.||Ensure that you are using the
|Other problems||Inspect the user error log produced by the daemon. See "Using the logs generated by the TimesTen daemon".|
The following sections describe what to do if one or more of the TimesTen processes appears to be unavailable:
If you receive an error that indicates the TimesTen subdaemon has stopped, inspect the user error log, as described in "Using the logs generated by the TimesTen daemon".
If the TimesTen daemon crashes, it cannot send anything to the user error log, but the subdaemons send a 'main daemon vanished' message to the log before exiting:
09:24:13 Err : 4375 ------------------: Main daemon has vanished
Restart the daemon. The next connection to each database causes TimesTen to recover from the checkpoint and transaction log files. See "Working with the Oracle TimesTen Data Manager Daemon" in the Oracle TimesTen In-Memory Database Operations Guide.
If you experience a crash by one of the TimesTen processes on a UNIX system and have exhausted all of the diagnostic options, check to see if TimesTen has generated a core file. Use the
ttVersion utility to find the core file. Look for a line in the output that shows a path for the daemon home directory:
TimesTen Release (ttuser:40732) 2007-04-04T17:53:04Z Instance admin: ttuser Instance home directory: /node1/ttuser/ttcur/TTBuild/linux86_dbg/install Daemon home directory: /node1/ttuser/ttcur/TTBuild/linux86_dbg/install/info
After locating the core file, attach to the debugger on the system and extract the stack trace from the core file and send the trace results to Technical support.
On Windows systems you can obtain diagnostic information for a service failure by enabling the 'allow service to interact with desktop' option in the properties dialog for the TimesTen data manager in the Service menu. If a fatal fault occurs in the TimesTen data manager service, a pop-up asks if you would like to start the debugger. Contact Technical support and provide the stack trace.
4671: TT14000: TimesTen daemon internal error: Error 28 creating shared segment, KEY 0x0201f7eb 4671: -- OS reports too many shared segments in use 4671: -- Confirm using 'ipcs' and take appropriate action 4671: 18538 ------------------: subdaemon process exited
Using the Linux
ipcs command may display information like this:
------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 1098350592 user1 777 10624 2 dest 0x00000000 1084817409 user1 777 2439680 2 dest 0x911fc211 1098383362 user2 666 67108864 1 0x2814afba 170721285 root 666 1048576 1
A status of
dest means the memory segment is marked to be destroyed.
nattch shows the number of processes still attached to the memory segment. The
ipcrm command cannot free the shared memory until the processes detach from the segment or exit. If an application connects to TimesTen and then becomes inactive, nothing can free the shared memory until the user exits or stops the application.
This section describes what to check if your application is unable to connect to a database in direct mode.
|Mismatch between the release of TimesTen and database||"Upgrading your database"|
|User does not have the
||"Privileges to connect to database"|
|Incorrect file permissions||"Check file system permissions to access database"|
|TimesTen daemon or Data Manager service not running||"Check that the TimesTen daemon is running"|
|Incompatible connection attributes or incorrect path name for database set in the DSN||"Check DSN definition"|
|No available shared memory segment or maximum size of shared memory segment too small||"Manage semaphores and shared memory segments"|
|Not enough swap space||"Check available swap space (virtual memory)"|
|Inadequate number of file descriptors||"Increase the number of available file descriptors"|
|Other possible causes||"Using the logs generated by the TimesTen daemon"|
A database is only guaranteed to be accessible by the same minor release of TimesTen that was used to create the database. When you upgrade the TimesTen software and you would like to use the new release to access a database that was previously created, create a database with the new release. Then use the
ttMigrate utility to copy the tables, indexes, and table data from the old database to the new one.
See "Database Upgrades" in the Oracle TimesTen In-Memory Database Installation Guide for details.
The user must have the
CREATE SESSION privilege to connect to the database. If you do not have access, the administrator must use the
GRANT statement to grant you the
CREATE SESSION privilege. See "Granting privileges to connect to the database" in the Oracle TimesTen In-Memory Database Operations Guide.
permission denied" error is generated if you attempt to connect to a database and you do not have the proper permissions to access the checkpoint or transaction log files or the directory where those files reside. Check the file system permissions on the files located in the directory specified in the
DataStore attribute in your DSN.
If the TimesTen daemon or Data Manager service is not running, an attempt to connect to a database generates TimesTen error 799 "
Unable to connect to daemon; check daemon status."
ttStatus utility as described in "Check the TimesTen user error log" to check the status of the TimesTen daemon.
In your DSN description, perform the following:
Certain connection options or DSN attribute settings combinations are not compatible. In cases where incompatible settings are used, an error is returned to the application when it attempts to connect to a database.
Confirm that you have specified the correct path names in the
LogDir attributes in your DSN. Also confirm that the path names are absolute path names, rather than relative. Otherwise, the path name will be relative to the directory where the application was started.
On Windows, be careful to distinguish between User and System DSNs in the ODBC Data Source Administrator. Do not create user DSNs because they are visible only to the user who defines them. System DSNs are visible to all users. In particular, if you run a TimesTen application as a Windows service, it runs as the user
SYSTEM by default and does not see any User DSNs. Make sure that you are not using a mapped drive in the database path name.
An error is generated if you attempt to connect to or create a shared database whose size is larger than the maximum size of shared memory segments configured on your system. Also, an error is generated if the system cannot allocate any more shared memory segments.
On UNIX systems, use commands similar to the following:
ipcs -ma to check if you have other shared memory segments using up memory, such as Oracle instances or other instances of TimesTen.
ipcrm to remove a message queue, semaphore set or shared memory segment identifier. Use
ipcrm to clean up semaphores or shared memory segments after a faulty TimesTen shutdown, instance crash, daemon crash or other application issues that use shared memory segments and semaphores. Use
-m to remove a shared memory segment. Use
-s to remove a semaphore.
ps -eafl to see how much memory is being used by running processes.
ulimit -a to see if there are any limits on the maximum amount of memory one process can address, maximum file size, and the maximum number of open files.
If a shared memory segment is available but is too small to hold your database, use the
ttSize utility to estimate the amount of memory required for your tables and then check the values of the
TempSize attributes to verify the amount of memory established for your database. "Monitoring PermSize and TempSize attributes" in the Oracle TimesTen In-Memory Database Operations Guide describes guidelines for setting the size of your permanent and temporary data partitions. If the amount of memory established for your database is too large, reset
TempSize to smaller values. See "Check the amount of memory allocated to the database" for more information. Another option is to increase the maximum size of the shared memory segment, as described below.
If a database becomes invalidated because of a system or application failure, a subsequent connection recovers the database. If recovery fails because you have run out of database space, then reconnect to the database with a larger
TempSize value than the ones that are currently in effect. If recovery fails because you do not have enough shared memory, then you should increase the maximum size of the shared memory segments for the system.
For more information on how to configure shared memory for TimesTen, see "Installation prerequisites" in the Oracle TimesTen In-Memory Database Installation Guide.
There must be enough swap space to back up shared memory.
On UNIX systems, use the swap command to check and add virtual memory to your system.
On Windows systems, check and reset the size of your virtual memory from the Advanced tab in your Computer Management Properties dialog window.
Each process connected to a TimesTen database keeps at least one operating system file descriptor open. Additional file descriptors may be opened for each connection if checkpoints are issued, and transactions are committed or rolled back. If you receive an error that all file descriptors are in use when attempting to connect to a database, then increase the allowable number of file descriptors. See your operating system documentation for limits on file descriptors and information about changing the number of file descriptors.
This section includes the following topics:
Also consider the topics described in "Application unable to connect to database in direct mode".
You have not correctly identified the system where the TimesTen Server is running.
On a Windows client machine, select the TimesTen Server in the TimesTen Data Source Setup dialog that is displayed as part of the ODBC Data Source Administrator. To verify the TimesTen Server:
On the Windows Desktop, choose Start > Settings > Control Panel.
Double click the ODBC icon. This opens the ODBC Data Source Administrator.
Click the System DSN tab. This displays the System Data Sources list.
Select the TimesTen Client data source. This opens the TimesTen Client DSN Setup dialog.
Click Servers. This opens the TimesTen Logical Server List.
Select the TimesTen Server from the list. This opens the TimesTen Logical Server Name Setup dialog.
Verify that the values for the Network Address and Port Number are correct. If necessary, change the values.
Note:If you typed the hostname or network address directly into the Server Name field of the TimesTen Client DSN Setup, the Client tries to connect to the TimesTen Server using the default port.
If the Network Address and Port Number values are correct, the TimesTen Server may not be running. See "Starting and stopping the Oracle TimesTen Data Manager service on Windows" in the Oracle TimesTen In-Memory Database Operations Guide for information about starting the server manually. See "Testing connections" in the Oracle TimesTen In-Memory Database Operations Guide for more information about identifying this problem.
On UNIX, specify the TimesTen Server with the
TTC_Server connection attribute in the
odbc.ini file on the client machine. If the value specified for
TTC_Server is an actual hostname or IP address, the client tries to connect to the TimesTen Server using the default port. In TimesTen, the default port is associated with the TimesTen release number. If the value specified for
TTC_Server is a logical ServerName, this logical ServerName must be defined in the
ttconnect.ini file. The
ttconnect.ini entry for this ServerName needs to correctly define the hostname/IP address and port number on which the TimesTen Server is listening.
If the Network Address and Port Number values are correct, the TimesTen Server may not be running or did not start. See "Starting and stopping the daemon on UNIX" in the Oracle TimesTen In-Memory Database Operations Guide for information about starting the server manually. See "Testing connections" in the Oracle TimesTen In-Memory Database Operations Guide for more information about identifying this problem.
Check the server's log file. Server log messages are stored in the files specified by the
-supportlog options in the
ttendaemon.options file. See "Creating and configuring Client DSNs on UNIX" and "Managing TimesTen daemon options" in the Oracle TimesTen In-Memory Database Operations Guide.
The maximum number of concurrent IPC connections to the Server of a particular TimesTen instance is 24,999. However, TimesTen has a limit of 2043 connections (direct or client/server) to a single DSN.
Client/server users can change the file descriptor limit to support a large number of connections. For an example, see "Installation prerequisites" in the Oracle TimesTen In-Memory Database Installation Guide.
On UNIX, verify that the Server DSN is defined in the
sys.odbc.ini file on the machine running the TimesTen Server.
On Windows, verify that the Server DSN is defined as a System DSN in the ODBC Data Source Administrator on the machine running the TimesTen Server. See "Creating and configuring a logical server name on Windows" in the Oracle TimesTen In-Memory Database Operations Guide.
This error only occurs on UNIX platforms. Open the
sys.odbc.ini file on the machine running the TimesTen Server and locate the Server DSN you are trying to connect. Verify that the dynamic library specified in the
DRIVER attribute for the Server DSN exists and is executable.
The default TimeOut interval is 60 seconds.
To increase this interval on UNIX, change the value of the
TTC_Timeout attribute in the
To set the timeout interval on Windows, see the instructions in "Setting the timeout interval and authentication" in the Oracle TimesTen In-Memory Database Operations Guide.
Check to see if the error was due to the Client timing out. Check the TimesTen Server's log to see why the Server may have severed connection with the Client. Use ping to determine if your network is up or try using
telnet to connect to the TimesTen Server port number.
While using shared memory segment (SHM) as IPC, the application may see the following error message from the TimesTen Client ODBC Driver if the application reaches the system-defined per-process file-descriptor-limit.
SQLState = S1000, Native Error = 0, Message = [TimesTen][TimesTen 11.2.1 CLIENT]Failed to attach to shared memory segment for IPC. System error: 24
This may happen during a connect operation to the Client DSN when the
shmat system call fails because the application has more open file descriptors than the system-defined per-process file descriptor limit. To correct this problem, you must increase your system-defined per-process file descriptor limit. For more information about file descriptor limits, see "System Limits" in the Oracle TimesTen In-Memory Database System Tables and Limits Reference.
On Windows XP, by default, there can be approximately 47 child server processes. You can increase the number of connections by setting the
MaxConnsPerServer connection attribute in the
ttendaemon.options file or in the DSN. This increases the number of connections to 47 times the
On Solaris, you may receive messages in the user error log about thread stack overflow. On other platforms, you may receive messages about a segmentation fault that mention a possible thread stack overflow.
If these messages occur, increase the server stack size by one of the following methods:
-ServerStackSize option in the
ttendaemon.options file. The
ttendaemon.options file applies to all DSNs in the TimesTen instance.
ServerStackSize connection attribute for a specific DSN. This takes precedence over the value in the
Increasing the server stack size decreases the number of concurrent connections that can be made before running out of swap space.
See "Working with the TimesTen Client and Server" in the Oracle TimesTen In-Memory Database Operations Guide.
You may receive "out of space" messages if you change a DSN to specify a new database while there are existing connections to the original database in a system with multiple client connections. This can happen on 32-bit platforms if either database is close to 2 GB.
Close all connections to the original database. This causes a new server process to be created for connections to the database that is now specified in the DSN. Use the
ttStatus utility to list the connections for the old database. Alternatively, you can restart the server by using the
ttDaemonAdmin utility with the
-restartServer option, which resets all client connections on all DSNs in the instance.
This section describes what to check if you encounter slow connects and disconnects to a database.
|Database is being recovered.||"Check if database is being recovered"|
|ODBC tracing is enabled.||"Check ODBC tracing"|
|Other possible causes||"API tracing"|
A slow connect may indicate that a TimesTen database is being recovered. This happens only for a first connect.
On Windows platforms, if ODBC tracing is enabled, it can slow connect and disconnect speeds. Double-click ODBC in the Control Panel to open the ODBC Data Source Administrator. Select the Tracing tab and confirm tracing is disabled. See "Using ODBC tracing".
If an application becomes disconnected from a TimesTen database, one of the following events occurs:
If there was no outstanding transaction, the connection is cleanly removed by the TimesTen daemon. Other existing connections continue processing as if no problem had occurred.
If there was an outstanding transaction but the application was not in the middle of executing code in the TimesTen library, the transaction is rolled back and the connection is cleanly removed by the TimesTen daemon. Other existing connections continue processing as if no problem had occurred.
This section describes what to check if your application unexpectedly disconnects from the database.
|Internal application error.||"Check for ODBC or JDBC errors"|
|Failure of a concurrent application thread.||"Check for ODBC or JDBC errors"|
|If using a client/server connection, the client may have disconnected from the application.||"Troubleshooting Client/Server problems"|
|An error in the TimesTen library||Contact Technical support.|
Check for the following types of errors:
ODBC errors returned by the
JDBC errors returned by the
The application may have encountered a problem that caused it to exit prematurely, which in turn may have caused other connections to be forced to disconnect. Call
SQLError after each ODBC call to identify error or warning conditions when they first happen. Examples of
SQLError usage can be found in the demo programs and in "Retrieving errors and warnings" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.
In more extreme cases, it may be helpful to use
ttTraceMon to generate a level 4
ERR trace for the application and review all of the errors messages that are pushed in the TimesTen direct driver. See "ERR tracing" for details.
If a TimesTen application disconnects without returning an ODBC error or any other warning, look through the user error log. See "Using the logs generated by the TimesTen daemon".
For details on how to maximize the performance of your application and TimesTen database, see:
"TimesTen Database Performance Tuning" in the Oracle TimesTen In-Memory Database Operations Guide
"Application Tuning" in the Oracle TimesTen In-Memory Database C Developer's Guide
"Application Tuning" in the Oracle TimesTen In-Memory Database Java Developer's Guide
This section describes some of the issues that impair performance.
|Using client/server mode||"Consider connection mode"|
|Outdated database statistics||"Update statistics for your tables"|
|Committing transactions too frequently||"Turn off autocommit mode" in the Oracle TimesTen In-Memory Database Operations Guide|
||"Use durable commits appropriately" in the Oracle TimesTen In-Memory Database Operations Guide|
|Not preparing SQL statements used more than once||"Prepare statements in advance" in the Oracle TimesTen In-Memory Database Operations Guide|
|Wrong kind of index, too many indexes, wrong size for hash index||"Select hash, range, or bitmap indexes appropriately" in the Oracle TimesTen In-Memory Database Operations Guide
"Size hash indexes appropriately" in the Oracle TimesTen In-Memory Database Operations Guide
|Inefficient use of locks||"Verify lock and isolation levels"|
|Improperly configured materialized view||"Performance implications of materialized views" and "Materialized view tuning" in the Oracle TimesTen In-Memory Database Operations Guide|
|If replication is used, configuration of replication scheme or network environment may be impacting application.||"Poor replication or XLA performance"|
|If IMDB Cache is used, IMDB Cache configuration or environment may be impacting application.||"Poor autorefresh performance"|
|Too many table partitions||"Check partition counts for the tables"|
|Tracing is unnecessarily enabled for one or more TimesTen components.||"Check trace settings"|
Client/server connections are slower than direct connections to TimesTen databases. Driver manager connections can also moderately impact performance. The performance overhead imposed by client/server connections can be significant because of the network latencies involved in all communication with the database.
If your application must run on a different machine from the one hosting the database, see "Client/Server tuning" in the Oracle TimesTen In-Memory Database Operations Guide.
The TimesTen query optimizer in general is very good at choosing the most efficient query plan. However, it needs additional information about the tables involved in complex queries in order to choose the best plan. By knowing the number of rows and data distributions of column values for a table, the optimizer has a much better chance of choosing an efficient query plan to access that table.
Before preparing queries that will access a TimesTen table, use the
ttOptUpdateStats procedure to update the statistics for that table. When updating the statistics for a table, you get the best results if you update statistics on your tables after loading them with data, but before preparing your queries. For example, if you update statistics on a table before populating it with data, then your queries are optimized with the assumption that the tables contain no rows (or very few). If you later populate your tables with millions of rows and then execute the queries, the plans that worked well for the situation where your tables contained few rows may now be very slow.
For more information about updating statistics, see "The TimesTen Query Optimizer" in the Oracle TimesTen In-Memory Database Operations Guide.
The manner in which multiple applications concurrently access the database can have a major impact on performance.
An application can acquire locks on the entire database, individual tables, and individual rows. Additionally, applications can set an isolation level that determines whether they hold read and update locks until their transactions commit or roll back.
SYS.MONITOR table or use the
ttXactAdmin utility to detect whether an application is spending time waiting for locks. See "Check for deadlocks and timeouts" and "Using the ttXactAdmin utility".
If lock contention is high, you may be able to improve the overall performance of your system by implementing the following:
Use read-committed isolation level (
Isolation=1, the default) for those applications do not require serializable access to the transaction data.
If you see a lot of lock contention, but the above settings are all set to minimize contention, then the contention may be related to the application itself. For example, concurrent threads may be repeatedly accessing the same row. The
ttXactAdmin utility can sometimes help you detect this sort of contention. Tracing can also be useful in this situation.
For more information about locks and isolation levels, see "Concurrency control through isolation and locking" in the Oracle TimesTen In-Memory Database Operations Guide.
-e show as described in "Using the ttTraceMon utility" to confirm tracing is off on all TimesTen components.
ERR should be set to 1; all other components should be set to 0. Trace levels are preserved when a database is reloaded.
On Windows platforms, confirm that ODBC tracing is disabled. Double-click ODBC in the Control Panel to open the ODBC Data Source Administrator. Select the Tracing tab and confirm tracing is disabled. See "Using ODBC tracing".
When a table is created, it has one partition. When you use
ALTER TABLE ... ADD COLUMN to add new columns, a new partition is added to the table. Adding multiple columns with a single
ALTER TABLE ... ADD COLUMN statement only adds one partition.
There is a limit of 255 partitions per table. Exceeding this number generates error 8204. An extra read for each new partition slightly degrades performance for each of the new partitions. A high partition count should be avoided. On replicated tables that have multiple partitions, additional space is used for each update on the subscriber side, proportional to the number of partitions. This can result in the subscribers using slightly more perm space than the master.
The partition value for each table is tracked in the
SYS16 column of the system table,
SYS.TABLES. Obtain the partition counts for tables by using the following query:
SELECT tblname, sys16 FROM SYS.TABLES;
If you discover that a table has too many partitions, do one of the following:
Re-create the table
Save and restore the table. Use
ttMigrate -c to create a migration file. Then restore the table without additional partitions by using
ttMigrate -r -noRepUpgrade.
ALTER TABLE ... DROP COLUMN does not remove partitions from a table. On replicated systems, all master and subscriber databases must be migrated using the
-noRepUpgrade option. Replication does not occur for tables that have different partition structures.
This section describes what to check if your application is unresponsive and appears to be hung.
|All causes||"Check logs and gather trace information"|
|Internal application error||"Check for ODBC errors"|
|Inconsistent connection attributes set in DSN||"Consider connection mode"|
|Excessive lock contention||"Check for deadlocks and timeouts"|
If your application hangs, check the transaction log by using the
ttXactAdmin utility. See "Using the ttXactAdmin utility".
Also check the user error log for errors, as described in "Using the logs generated by the TimesTen daemon" .
You can also generate a trace log to detect the activities on various TimesTen components as described in "Using the ttTraceMon utility" .
Check the ODBC errors returned by the
SQLError function in all applications to determine whether one of them has encountered a problem that caused it to hang. Call
SQLError after each ODBC call to identify error or warning conditions when they first happen. Examples of
SQLError usage can be found in the demo programs and in "Retrieving errors and warnings" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.
If the problem is repeatable, use
ttTraceMon to generate a SQL trace to determine where the application is hanging. See "SQL tracing" for details. In more extreme cases, it may be helpful to generate a level 4
ERR trace for the application and review all of the errors messages that are pushed in the TimesTen direct driver. See "ERR tracing" for details.
If there is no connect problem, a deadlock or timeout may be the problem. The
SYS.MONITOR table records information about deadlocks and timeouts. See "Monitoring the TimesTen system tables" for information on how view the contents of this table. You can also use the
ttXactAdmin utility to detect the types of locks currently held by uncommitted transactions and the resources on which they are being held.
If a deadlock occurs, the TimesTen subdaemon negotiates the problem by having an application involved in the deadlock generate TimesTen error 6002, "
Lock request denied because of deadlock." The error message contains the SQL that the lock holder is running, which can help you diagnose the cause of the deadlock. If your application encounters this error, it should roll back the transaction and then reissue the statements for that transaction. Deadlocks can be caused if your application issues statements in a particular order that results in a circular wait, and can sometimes be prevented by changing the order in which the statements are issued.
An application encounters TimesTen error 6003, "
Lock request denied because of timeout," if it is unable to acquire a lock within the time period defined by the lock timeout interval set by the
LockWait attribute in the DSN or by the
ttLockWait procedure in your application. Upon encountering a timeout error, your application can reissue the statement. Keeping transactions short reduces the possibility of lock timeout errors.
System tables are a common source of lock contention. Reduce contention on the system tables by executing prepared statements, rather than executing the same statements directly each time.
In multithreaded applications, a thread that issues requests on different connection handles to the same database may encounter lock conflict with itself. TimesTen resolves these conflicts with lock timeouts.
This section describes what to check if your application is unable to locate previously created tables, indexes, sequences or views in the database.
|No owner or incorrect owner specified||"Specify object owner"|
|User does not have
||"Check privilege to access tables"|
|Database is temporary.||"Check temporary DSN attribute"|
|Overwrite attribute is enabled.||"Check Overwrite DSN attribute"|
|Path name specified in DSN is relative.||"Check path name to database"|
Tables, indexes and sequences can be created either with a single name, such as
PARTS, or with a qualified name incorporating an owner and table name, such as
STAN.PARTS. When accessing a table or index, if no owner is specified, TimesTen first assumes that the owner is the login ID of the user (the value of the
UID attribute). If TimesTen cannot find the table or index under the user's login ID, it then assumes that the owner is user
If applications need to connect to a database as different users and share objects, explicitly specify the owners of the objects when they are created and referenced.
All privileges for the user can be viewed in the
SYS.USER_SYS_PRIVS table, which contains all of the system-level privileges for a given user, and the
SYS.USER_TAB_PRIVS table, which contains all of the object-level privileges for a given user. Check these tables to verify if you have
SELECT privilege for the tables. If you do not have
SELECT privilege for the tables, the privilege may be granted with the
GRANT statement. The method for granting privileges is described in the "Managing Access Control" chapter in the Oracle TimesTen In-Memory Database Operations Guide.
Temporary databases (DSN attribute:
Temporary=1) persist until all connections to the database have been removed. When attempting to access a table in a temporary database and the table does not exist, it is possible that the database in which the table resided in has been dropped.
AutoCreate DSN attributes are enabled and the database already exists, TimesTen drops that database and creates a new one. Any tables that were created in the old database are dropped.
To ensure that you are always accessing the same database when connecting to a particular DSN, use an absolute database path name instead of a relative one. For example, if the demo database is in the
datastore directory, specify:
In the latter case, the database path name is relative to the directory where the application was started. If you are unable to find a table and you are using a relative database path name, it is possible that the database in which the table resides in does exist but the database (checkpoint and log) files are in a different directory than the one that you are accessing.
See "Specifying Data Source Names to identify TimesTen databases" in the Oracle TimesTen In-Memory Database Operations Guide.
On Windows, the
NLS_LANG setting is taken from the registry if it is not in the environment. If
NLS_LANG is set to an unsupported value, such as NA, an OCI connection failed error or an
ORA-12705 error is thrown. If your OCI or Pro*C/C++ program has trouble connecting to TimesTen, verify that the setting of
HKEY_LOCAL_MACHINE\Software\ORACLE\NLS_LANG is valid and indicates a character set supported by TimesTen. This is likely only an issue on machines that previously had Oracle9i or earlier Oracle versions installed.
Refer to the "Globalization support" section in the OCI chapter of the Oracle TimesTen In-Memory Database C Developer's Guide for more information on
This section describes what to check if TimesTen runs out of resources such as memory space, disk space, file descriptors, and semaphores.
|Memory consumption seems high.||"Operating system tools and shared memory"|
|Running out of memory space|
|Running out of disk space||"Check transaction log file use of disk space"|
|Running out of transaction log space||"Check transaction log file use of disk space"|
|Running out of file descriptors||"Increase the number of available file descriptors"|
|Running out of semaphores||"Check the semaphore limit"|
|Running out of CPU||Obtain a stack trace and contact Technical support.|
Operating system tools such as
sar provide statistics about processes and memory usage. The output from these tools can be misleading as an indicator of TimesTen memory consumption because they report shared memory usage for each process but do not report total shared memory usage. Adding together various memory statistics for TimesTen processes overestimates the amount of memory used by TimesTen because shared memory is by definition shared.
TimesTen uses both permanent and temporary data partitions. The amount of memory allocated for these partitions is set by the
TempSize attributes in the DSN definition for the database.
When the TimesTen database fills up, it is important to determine whether it is the permanent or the temporary segment that is filling up. Use the
dssize command to list allocated, in-use, and high water mark sizes for the permanent and temporary data partitions. The
dssize command selects the following values from
The permanent segment consists of table and index data, while the temporary segment consists of internal structures, such as locks, sorting areas, and compiled commands.
Keeping transactions short and making sure there is enough temporary space in the database prevents locks from occupying all of the remaining temporary space. You can also use table locks if transactions are acquiring tens of thousands of row locks.
For tips on how to estimate the size of your database, see "Size your database correctly" in the Oracle TimesTen In-Memory Database Operations Guide.
Consider whether you can drop any indexes. You may want to look at query plans to see which indexes are actually used. See "Viewing and changing query optimizer plans" in the Oracle TimesTen In-Memory Database Operations Guide. You can also use the
ttRedundantIndexCheck procedure to discover redundant indexes. The procedure returns suggestions about which indexes to drop.
ttSize utility to estimate the amount of memory used by each table in the database. If the amount of data you need to store is too big, you may need to reset the
PermSize attribute for the database to increase the size of the permanent segment. Alternatively, you may need to partition your data into several different databases if, for example, you cannot shrink the temporary segment or create a bigger database because of limits on the memory segment size.
Sometimes when the permanent segment fills up, copying the data out of the database, deleting all the data, and copying it back in frees up space. This can be done more efficiently by using the
ttMigrate utility with the
-noRepUpgrade option to migrate the data out, destroy and re-create the database, and migrate the data back in. This operation is described in "Reducing database size" in the Oracle TimesTen In-Memory Database Installation Guide.
Finally, you may have to configure the operating system to allow a larger amount of shared memory to be allocated to a process. You may also have to allocate more swap space for virtual memory. See "Check available swap space (virtual memory)".
Some commands may be allocating too much space because of out-of-date statistics. See "Update query optimizer statistics".
If updating the statistics does not reduce temporary segment memory usage, disconnect all connections and then reconnect them. Verify that all connections have been disconnected by using the
ttStatus utility. That frees up all temporary space, but you must reprepare commands.
Diagnose memory usage by queries. See "Check memory used by queries".
If the problem is chronic, monitor the database to try to identify the source of the problem. Use the
ttWarnOnLowMemory procedure to enable warnings in the user log that indicate that the database is filling up.
If the database seems to have enough free space but runs out of database space when executing a query, make sure you have updated the optimizer statistics with the
ttOptEstimateStats procedure. To execute some queries, TimesTen needs to allocate temporary space. The amount of temporary space required is estimated from statistics about the tables used by the query. Without correct statistics, the temporary space required may be underestimated.
You can check the memory that a query uses by observing the high water mark for temporary memory usage. The high water mark represents the largest amount of in-use temporary space used since the high water mark was initialized or reset.
Complete the following tasks:
dssize command to check
TEMP_IN_USE_HIGH_WATER. Alternatively, you can query the
SYS.MONITOR table for these values.
ttMonitorHighWaterReset procedure to reset the
TEMP_IN_USE_HIGH_WATER to the current value for
Execute a query.
dssize to check
TEMP_IN_USE_HIGH_WATER for peak memory usage for the query.
If you receive an error indicating that you have run out of swap space, you may need to increase the amount of available swap space (also referred to as "virtual memory").
On UNIX systems, use the
swap command to check and reset the amount of virtual memory currently established for your system.
On Windows systems, check and reset the size of your virtual memory by choosing Control Panel > System > Advanced.
Fatal errors, such as errors 846 and 994, invalidate a TimesTen database. However, the database remains in memory, which is only freed after all users have disconnected from the database. If the database is restarted while users are connected to the invalidated database, both old and new instances exist in memory at the same time. In this case, users could receive out-of-memory conditions. To prevent an
"Out of memory" error, disconnect all active connections at the time of the fatal error before reconnecting.
TimesTen saves a copy of the database in one of two checkpoint files, which are stored in the directory specified by the
DataStore attribute. Each checkpoint file can grow on disk to be equivalent to the size of the database in shared memory. For each permanent database, you must have enough disk space for the two checkpoint files and for transaction log files.
Transaction log files accumulate in the directory specified by the
LogDir attribute and are only deleted when checkpoints are performed. If the
LogDir attribute is not specified in the DSN, transaction log files accumulate in the directory specified by the
DataStore attribute. The maximum size of your transaction log files is set by the
When a disk fills up with TimesTen data, it is most often due to a build-up of transaction log files. Transaction log files are used for numerous purposes in TimesTen, including checkpointing, backups, and replication. It is important to determine which operation is putting a "hold" on the transaction log files, so that appropriate action can be taken to allow the transaction log files to be purged. This can be done by using the
ttLogHolds built-in procedure. There are six types of log holds. They are discussed in detail below.
Checkpoint - If a TimesTen application crashes and the database needs to be recovered, the checkpoint files and transaction log files are used to recover the data. The "most recent" transaction log files are used -- those written since the checkpoint was done. Transaction log files accumulate during the interval between checkpoints. Your application should periodically call the
ttCkptBlocking procedure to checkpoint the data and free up the space on the disk. If checkpoints are done very infrequently, a large number of transaction log files may accumulate, particularly if many changes are made to the database during that interval. See "Checkpoint Operations" in the Oracle TimesTen In-Memory Database Operations Guide.
Replication -TimesTen replication transmits changes to one database to one or more other databases. It does this by reading the log and sending any relevant changes. If replication is paused, the transaction log files build up. To prevent log build-up, avoid pausing replication for too long. Delete subscriptions entirely, and reset replication where appropriate. See "Setting the replication state of subscribers" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information on pausing and restarting or resetting replication.
Backup - TimesTen supports an incremental backup facility that uses transaction log files to augment a backup with changes made since the last backup. Transaction log files accumulate during the interval between incremental backups. To avoid a large log build-up, do incremental backups at relatively frequent intervals. If desired, disable incremental backups and do full backups instead. See "Copying, migrating, backing up and restoring a database" in the Oracle TimesTen In-Memory Database Operations Guide.
XLA - TimesTen's persistent XLA facility reports changes to the database by using transaction log files. Transaction log files are kept until the corresponding transactions have been acknowledged using the
ttXlaAcknowledge C function. Call
ttXlaAcknowledge frequently enough to prevent transaction log files building up. See "Retrieving update records from the transaction log" in the Oracle TimesTen In-Memory Database C Developer's Guide.
XA - TimesTen's XA support uses transaction log files to resolve distributed transactions. If these transactions are not resolved in a timely manner, transaction log files build up. See "Distributed Transaction Processing XA" in the Oracle TimesTen In-Memory Database C Developer's Guide.
Long-running transactions - TimesTen uses the transaction log to roll back transactions. A log hold is placed for the duration of a transaction. Transactions that are active for a long time result in log file building up if the transaction has written at least one log record. (That is, it is not a read-only transaction.) Commit write transactions with reasonable frequency to avoid significant log file build-up. See "Size transactions appropriately" in the Oracle TimesTen In-Memory Database Operations Guide for more information on transaction length.
The following attributes are related to disk use:
LogPurge attribute indicates whether transaction log files that no longer have a hold on them are purged (removed from the disk) or simply archived (renamed). If the
LogPurge attribute is set to the default value of 0, TimesTen renames transaction log files that it no longer needs by appending the string
.arch to the name. Once renamed, you must delete the transaction log files manually when they are no longer needed. If transaction log files are not purged, they continue to accumulate space, even when no longer needed by TimesTen.
Preallocate attribute indicates whether disk space should be reserved for checkpoint files at connect time. This is useful for big databases, to ensure that the disk always has room for the checkpoint files as data is added to the database.
On certain platforms, the file size is limited to 2G. If you reach this limit, the process is terminated unless you catch the
SIGXFSZ signal. The error shown is the "
FILESIZE LIMIT EXCEEDED" error. Ensure that you want tracing enabled when using environments with strict file size limits.
When creating multiple client/server connections to a TimesTen database configured to allow shared memory segment as IPC, you may encounter errors that indicate TimesTen was unable to create a semaphore.
Semaphore limits are platform-dependent. See your operating system documentation and "Increase number of semaphores" in the Oracle TimesTen In-Memory Database Installation Guide.
Using read-committed isolation level can lead to duplicates in a result set. A
SELECT statement selects more or fewer rows than the total number of rows in the table if some rows are added or removed and committed in the range in which the
SELECT scan is occurring. This may happen when an
DELETE statement adds or deletes a value from an index and the
SELECT scan is using this index. This can also happen when an
DELETE adds or deletes rows from the table and the
SELECT operation is using an all-table scan.
Index values are ordered. An
UPDATE of an index value may delete the old value and insert the new value into a different place. In other words it moves a row from one position in the index to another position. If an index scan sees the same row in both positions, it returns the row twice. This does not happen with a serial scan because table pages are unordered and rows do not need to be moved around for an
UPDATE. Hence once a scan passes a row, it will not see that same row again.
The only general way to avoid this problem is for the
SELECT statement to use Serializable isolation. This prevents a concurrent
UPDATE operation. There is no reliable way to avoid this problem with
DELETE by forcing the use of an index because these operations affect all indexes. With
UPDATE, this problem can be avoided by forcing the
SELECT statement to use an index that is not being updated.
For more information about Serializable isolation, see "Concurrency control through isolation and locking" in the Oracle TimesTen In-Memory Database Operations Guide.
PLSQL_MEMORY_ADDRESS first connection attribute determines the virtual address at which the PL/SQL shared memory segment is loaded into each process that uses the TimesTen direct drivers. Since each operating system platform has different mappings for its address space, the default values for the PL/SQL address space defined in the
PLSQL_MEMORY_ADDRESS connection attribute are different for each platform, which avoids conflict with operating system mapped address space.
However, if your application overlaps with the PL/SQL mapped address space, you may receive error 8517 "
Cannot attach PL/SQL shared memory; PLSQL_MEMORY_ADDRESS not valid or already in use." In this case, modify the setting for the
PLSQL_MEMORY_ADDRESS connection attribute to eliminate the overlap. The reasons for receiving error 8517 can be one of the following:
User allocated memory already uses that address.
Some shared memory already uses that address.
A shared library already uses that address.
To recover, specify a virtual address that is free for all processes that may connect to the database. If you have a 32-bit program that allocates large amounts of memory before connecting to TimesTen, it may clash with the PL/SQL shared memory segment. In this case, either allocate memory after connecting to TimesTen or use a 64-bit application. In a 64-bit environment, the options for reassigning to another memory address are less complicated than for a 32-bit operating system, where options are limited and potential for overlap is more common.
If an application accesses two or more TimesTen databases at the same time, you must modify the default setting for the
PLSQL_MEMORY_ADDRESS attribute in all but one of the TimesTen databases, since the default settings would map the PL/SQL memory address to the same address for all TimesTen databases.