Contents

Title and Copyright Information

Preface

• Audience
• Related documents
• Conventions
• Documentation Accessibility

What's New

• New features in release 11.2.2.8.0
• New features in release 11.2.2.7.0
• New features in release 11.2.2.6.0
• New features in release 11.2.2.5.0
• New features in release 11.2.2.4.0
• New features in release 11.2.2.2.0
• New features in release 11.2.2.1.0
• New features in release 11.2.2.0.0

1 Managing TimesTen Databases

• Connecting to TimesTen with ODBC and JDBC drivers
  • Connecting using TimesTen ODBC drivers
  • Connecting using the TimesTen JDBC driver and driver manager
• Specifying Data Source Names to identify TimesTen databases
  • Overview of user and system DSNs
  • Defining DSNs for direct or client/server connections
  • Connection attributes for Data Manager DSNs or server DSNs
• Defining a Data Manager DSN
  • Creating a Data Manager DSN on Windows
    • Specify the ODBC driver
    • Specify the Data Manager DSN
    • Specify the connection attributes
  • Creating a Data Manager DSN on UNIX
    • Create a user or system odbc.ini file
    • Using environment variables in database path names
• Defining client and server DSNs
• Resolution path for a DSN
• DSN examples
  • Setting up a default DSN
  • Setting up a temporary database
  • Specifying PL/SQL connection attributes in a DSN
  • Creating multiple DSNs to a single database
• odbc.ini file entry descriptions
  • ODBC Data Sources
  • DSN specification
  • odbc.ini file example
• Connecting to a database using a connection string
• Specifying a RAM policy
  • Changes to RAM policy after automatic recovery fails
  • Preventing an automatic reload of the database after failure
• Loading and unloading the database from memory
  • Loading the database into memory
  • Unloading the database from memory
• Specifying the size of a database
  • Estimating and modifying the memory region sizes for the database
  • Ensuring sufficient disk space
  • Monitoring PermSize and TempSize attributes
  • Receiving out-of-memory warnings
• Manage existing tables in the database
• Thread programming with TimesTen
• Defragmenting TimesTen databases
  • Offline defragmentation of TimesTen databases
  • Online defragmentation of TimesTen databases
    • Online defragmentation of databases in an active standby pair replication scheme
    • Online defragmentation of databases in a non active standby pair replication scheme

2 Working with the TimesTen Client and Server

• Overview of the TimesTen Client/Server
  • Restrictions on client/server communication
  • Communication protocols for Client/Server communication
    • TCP/IP Communication
    • Shared memory communication
    • UNIX domain socket communication
• Configuring TimesTen Client and Server
  • Overview of TimesTen Client/Server configuration
  • Installing and configuring for client/server connections
    • Configuring Client/Server of the same TimesTen release
    • Configuring cross-release TimesTen Client/Server
  • Defining server DSNs on a TimesTen Server system
    • Server DSN connection attributes defined in odbc.ini file
    • Server DSN connection attributes defined in ODBC Data Source Administrator
  • Defining a logical server name
    • Creating and configuring a logical server name on Windows
    • Creating and configuring a logical server name on UNIX
  • Creating client DSNs on a TimesTen Client system
    • Creating and configuring client DSNs on Windows
    • Creating and configuring client DSNs on UNIX
  • Using automatic client failover
    • Features and functionality of automatic client failover
    • Configuring automatic client failover
• Running the TimesTen Server
  • Server informational messages
• Accessing a remote database on UNIX
  • Testing connections

3 Working with the TimesTen Data Manager Daemon

• Starting and stopping the TimesTen daemon
  • Starting and stopping the Oracle TimesTen Data Manager service on Windows
    • Changing the startup mode
  • Starting and stopping the daemon on UNIX
    • Running the setuproot script
• Shutting down a TimesTen application
• Managing TimesTen daemon options
  • Determining the daemon listening address
    • Listening on IPv6
  • Modifying informational messages
  • Changing the allowable number of subdaemons
  • Allowing database access over NFS-mounted systems
  • Enabling Linux large page support
  • Modifying the default automatic database recovery after a fatal error
  • Configuring a range for shared memory keys
• Managing TimesTen Client/Server options
  • Modifying the TimesTen Server options
  • Controlling the TimesTen Server
  • Prespawning TimesTen Server processes
  • Specifying multiple connections to the TimesTen Server
    • Configuring the maximum number of client connections per child server process
    • Configuring how connections are distributed among the child server processes spawned for a server DSN
    • Configuring the thread stack size of the child server processes
  • Using shared memory for Client/Server IPC
    • Managing the size of the shared memory segment
    • Changing the size of the shared memory segment
  • Controlling the TimesTen Server log messages

4 Managing Access Control

• Managing users to control authentication
  • Overview of users
  • Creating or identifying users to the database
  • Changing the password of the internal user
  • Dropping users from the database
• Providing authorization to objects through privileges
  • Privileges overview
    • System privileges
    • Object privileges
    • PUBLIC role
    • Privilege hierarchy rules
  • Granting or revoking system privileges
    • Granting administrator privileges
    • Granting ALL PRIVILEGES
    • Granting privileges to connect to the database
    • Granting additional system privileges
    • Enabling users to perform operations on any database object type
  • Granting or revoking object privileges
    • Grant all object privileges
    • Object privileges for tables
    • Object privileges for views
    • Object privileges for sequences
    • Object privileges for materialized views
    • Object Privileges needed when creating foreign key with REFERENCES clause
    • Object privileges for PL/SQL functions, procedures and packages
    • Object privileges for synonyms
  • Granting or revoking multiple privileges with a single SQL statement
  • Granting or revoking privileges for cache groups
    • Cache manager privilege
    • Cache group system privileges
    • Cache group object privileges
  • Viewing user privileges
  • Privileges needed for utilities, built-in procedures and first connection attributes
  • Privilege checking rules for parent-child tables

5 Globalization Support

• Overview of globalization support features
• Choosing a database character set
  • Character sets and languages
  • Client operating system and application compatibility
  • Performance and storage implications
  • Character sets and replication
• Length semantics and data storage
• Connection character set
• Linguistic sorts
  • Monolingual linguistic sorts
  • Multilingual linguistic sorts
  • Case-insensitive and accent-insensitive linguistic sorts
  • Performing a linguistic sort
  • Using linguistic indexes
• SQL string and character functions
• Setting globalization support attributes
  • Backward compatibility using TIMESTEN8
• Globalization support during migration

6 Using the ttIsql Utility

• Batch mode vs. interactive mode
• Defining default settings with the TTISQL environment variable
• Customizing the ttIsql command prompt
• Using the ttIsql online help
• Using the ttIsql 'editline' feature for UNIX only
  • Emacs binding
  • vi binding
• Using the ttIsql command history
  • Saving and clearing the ttIsql command history
• Using the ttIsql edit command
  • Changing the default text editor for the ttIsql edit command
• Working with character sets
• Displaying database structure information
  • Using the ttIsql describe command
  • Using the ttIsql cachegroups command
  • Using the ttIsql dssize command
  • Using the ttIsql tablesize command
  • Using the ttIsql monitor command
• Listing database objects by object type
• Viewing and setting connection attributes
• Working with transactions
• Working with prepared and parameterized SQL statements
• Using, declaring, and setting variables
  • Declaring and setting bind variables
  • Automatically creating bind variables for retrieved columns
• Creating and executing PL/SQL blocks
• Passing data from PL/SQL using OUT parameters
• Conditional control with the IF-THEN-ELSE command construct
• Loading data from an Oracle database into a TimesTen table
  • Use ttIsql to create a table and load SQL query results
  • Use TimesTen built-in procedures to recommend a table and load SQL query results
  • Cancel a parallel load operation
• Viewing and changing query optimizer plans
  • Using the showplan command
  • Viewing commands and explain plans from the SQL command cache
    • View commands in the SQL command cache
    • Display query plan for statement in SQL command cache
• Managing ODBC functions
  • Canceling ODBC functions
  • Timing ODBC function calls
• Error recovery with WHENEVER SQLERROR

7 Transaction Management

• Transaction overview
• Transaction implicit commit behavior
  • Transaction autocommit behavior
  • TimesTen DDL commit behavior
  • Relationship between autocommit and DDLCommitBehavior
• Ensuring ACID semantics
  • Transaction atomicity, consistency, and isolation
  • Transaction consistency and durability
• Concurrency control through isolation and locking
  • Transaction isolation levels
  • Locking granularities
    • Setting wait time for acquiring a lock
• Checkpoint operations
  • Purpose of checkpoints
  • Usage of checkpoint files
  • Types of checkpoints
    • Fuzzy or non-blocking checkpoints
    • Transaction-consistent checkpoints
  • Setting and managing checkpoints
    • Programmatically performing a checkpoint
    • Configuring or turning off background checkpointing
    • Displaying checkpoint history and status
    • Setting the checkpoint rate
    • Setting the number of checkpoint file read threads
• Transaction logging
  • Managing transaction log buffers and files
  • Monitoring accumulation of transaction log files
    • Purging transaction log files
    • Log holds by TimesTen components or operations
    • Monitoring log holds and log file accumulation
  • Durable options for logging transactions
    • Guaranteed durability
    • Delayed durability
    • Durable commit performance enhancements
• Transaction reclaim operations
  • About reclaim operations
  • Configuring the commit buffer for reclaim operations
• Recovery with checkpoint and transaction log files

8 Working with Data in a TimesTen Database

• Database overview
  • Database components
  • Database users and owners
  • Database persistence
• Understanding tables
  • Overview of tables
    • Column overview
    • Inline and out-of-line columns
    • Default column values
    • Table names
    • Table access
    • Primary keys, foreign keys and unique indexes
    • System tables
  • Working with tables
    • Creating a table
    • Dropping a table
    • Estimating table size
  • Implementing aging in your tables
    • Usage-based aging
    • Time-based aging
    • Aging and foreign keys
    • Scheduling when aging starts
    • Aging and replication
• Understanding views
  • Creating a view
    • The SELECT query in the CREATE VIEW statement
  • Dropping a view
  • Restrictions on views and detail tables
• Understanding materialized views
  • Overview of materialized views
    • Synchronous materialized view
    • Asynchronous materialized view
    • When to use synchronous or asynchronous materialized views
  • Working with materialized views
    • Creating a materialized view
    • Dropping a materialized view or a materialized view log
    • Restrictions on materialized views and detail tables
    • Performance implications of materialized views
• Understanding indexes
  • Overview of index types
  • Creating an index
  • Altering an index
  • Dropping an index
  • Estimating index size
  • Using the Index Advisor to recommend indexes
    • Prepare for executing the Index Advisor
    • Capture the data used for generating index recommendations
    • Retrieve index recommendations and data collection information
    • Drop data collected for the index advisor and finalize results
    • Example using Index Advisor built-in procedures
• Understanding rows
  • Inserting rows
  • Deleting rows
• Understanding synonyms
  • Creating synonyms
  • Dropping synonyms
  • Synonyms may cause invalidation or recompilation of SQL queries

9 The TimesTen Query Optimizer

• When optimization occurs
• Viewing SQL statements stored in the SQL command cache
  • Managing performance and troubleshooting commands
  • Displaying commands stored in the SQL command cache
• Viewing SQL query plans
  • Viewing a query plan from the system PLAN table
    • Instruct TimesTen to store the plan in the system PLAN table
    • Reading query plan from the PLAN table
    • Describing the PLAN table columns
  • Viewing query plans associated with commands stored in the SQL command cache
• Modifying plan generation
  • Why modify an execution plan?
  • How hints can influence an execution plan
  • Use optimizer hints to modify the execution plan
    • Apply statement level optimizer hints for a SQL statement
    • Apply transaction level optimizer hints for a transaction

10 TimesTen Database Performance Tuning

• System and database tuning
  • Provide enough memory
  • Size your database correctly
  • Calculate shared memory size for PL/SQL runtime
  • Increase LogBufMB if needed
  • Use temporary databases if appropriate
  • Avoid connection overhead
  • Load the database into RAM when duplicating
  • Prevent reloading of the database after automatic recovery fails
  • Reduce contention
  • Avoid operating system paging at load time
  • Consider special options for maintenance
  • Check your driver
  • Enable tracing only as needed
  • Use metrics to evaluate performance
  • Investigate alternative JVMs
  • Migrate data with character set conversions
• Client/Server tuning
  • Diagnose Client/Server performance
  • Work locally when possible
  • Choose a timeout interval
  • Choose the best method of locking
    • Choose an appropriate lock level
    • Choose an appropriate isolation level
  • Use shared memory segment as IPC when client and server are on the same system
  • Enable TT_PREFETCH_CLOSE for Serializable transactions
  • Use a connection handle when calling SQLTransact
• SQL tuning
  • Tune statements and use indexes
  • Collect and evaluate sampling of execution times for SQL statements
  • Select hash, range, or bitmap indexes appropriately
  • Size hash indexes appropriately
  • Use foreign key constraint appropriately
  • Compute exact or estimated statistics
  • Update table statistics for large tables in parallel
  • Create script to regenerate current table statistics
  • Control the invalidation of commands in the SQL command cache
  • Avoid ALTER TABLE
  • Avoid nested queries
  • Prepare statements in advance
  • Avoid unnecessary prepare operations
  • Store data efficiently with table compression
  • Control read optimization during concurrent write operations
• Materialized view tuning
  • Limit number of join rows
  • Use indexes on join columns
  • Avoid unnecessary updates
  • Avoid changes to the inner table of an outer join
  • Limit number of columns in a view table
• Transaction tuning
  • Size transactions appropriately
  • Use durable commits appropriately
  • Avoid frequent checkpoints
  • Turn off autocommit mode
  • Avoid transaction rollback
  • Avoid large DELETE statements
    • Avoid DELETE FROM statements
    • Prefer the TRUNCATE TABLE statement
    • Consider using the DELETE FIRST clause
  • Increase the commit buffer cache size
• Recovery tuning
  • Set RecoveryThreads
  • Set CkptReadThreads
• Scaling for multiple CPUs
  • Run the demo applications as a prototype
  • Limit database-intensive connections per CPU
  • Use read operations when available
  • Limit prepares, re-prepares and connects
  • Allow indexes to be rebuilt in parallel during recovery
  • Use private commands
• XLA tuning
  • Increase transaction log buffer size when using XLA
  • Prefetch multiple update records
  • Acknowledge XLA updates
• Cache and replication tuning

Glossary

Index