Contents

Title and Copyright Information

Preface

• Audience
• Related documents
• Conventions
• Documentation Accessibility
• Technical support

What's New

• New features for release 11.2.1
• Access control
• Asynchronous materialized views
• SQL command cache
• Bitmap indexes
• Transaction log buffer file size
• Automatic client failover
• PL/SQL support
• Replication performance

1 Creating TimesTen Data Stores

• TimesTen ODBC and JDBC drivers
  • TimesTen ODBC drivers
  • TimesTen JDBC driver and driver manager
• Data source names
  • User and system DSNs
  • Data Manager and Client DSNs
  • Connection attributes for Data Manager DSNs
  • Thread programming with TimesTen
• Creating a DSN on Windows
  • Specify the ODBC driver
  • Specify the DSN
  • Specify the connection attributes
• Creating a DSN on UNIX
  • Create a user ODBC.INI file
  • Specify the DSN
  • Specify the ODBC driver
  • Specify the data store path name
    • Choose a database character set
    • Set data store attributes
    • Using environment variables in data store path names
• DSN examples
  • Setting up a temporary data store
  • Specifying PL/SQL connection attributes in a DSN
  • Creating multiple DSNs to a single data store
  • Connecting to a data store without using a DSN
• Specifying the size of a data store
  • Temporary and permanent memory
  • Changing data store size
    • Estimating and modifying the data partition sizes for the data store
    • Unloading the data store from memory
    • Monitoring PermSize and TempSize attributes
  • Receiving out-of-memory warnings
• Specifying a RAM policy
• Copying, migrating, backing up and restoring a data store
  • Backing up and restoring a data store
• Working with the ODBC.INI file
  • The user ODBC.INI file
  • The system ODBC.INI file
  • Searching for a DSN
  • ODBC Data Sources
  • Data Source specification
  • ODBC.INI file example

2 Working with the TimesTen Client and Server

• Restrictions on Client/Server communication
• Client/Server communication
  • TCP/IP Communication
  • Shared memory communication
  • UNIX domain socket communication
• Configuring TimesTen Client and Server
  • Configuring client/server of the same TimesTen release
  • Configuring cross-release Client/Server
• Running the TimesTen Server daemon
  • Server informational messages
• TimesTen Server connection attributes
• Defining Server DSNs
• TimesTen Client connection attributes
• Creating and configuring Client DSNs on Windows
  • Creating and configuring a logical server name
  • Creating a Client DSN on Windows
  • Configuring automatic client failover
  • Setting the timeout interval and authentication
  • Deleting a server name
  • Accessing a remote data store on Windows
  • Testing connections
• Creating and configuring Client DSNs on UNIX
  • Searching for a DSN
  • Creating and configuring a logical server name
  • Creating a Client DSN
  • Configuring automatic client failover
• Accessing a remote data store on UNIX
  • Testing connections
• Working with the TTCONNECT.INI file
  • Defining a server name on UNIX

3 Working with the Oracle TimesTen Data Manager Daemon

• Starting and stopping the Oracle TimesTen Data Manager Service on Windows
• Starting and stopping the daemon on UNIX
• 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 data store access over NFS-mounted systems
  • Enabling Linux large page support
  • Shared memory daemon option for HP-UX ccNUMA systems
• Managing TimesTen Client/Server daemon options
  • Modifying the TimesTen Server daemon options
  • Controlling the TimesTen Server daemon
  • Prespawning TimesTen Server processes
  • Specifying multiple connections to the TimesTen Server
    • Configuring the maximum number of client connections per child server process
    • Configuring the desired number of 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
  • Communicating with older releases of TimesTen

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 privilege 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 functions, procedures and packages
  • 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
  • Object migration and character sets
  • Migration and length semantics
  • Migrating linguistic indexes
  • Migrating cache group tables
• Supported character sets
  • Asian character sets
  • European character sets
  • Middle Eastern character sets
  • TimesTen character set
  • Universal character sets
• Supported linguistic sorts
  • Monolingual linguistic sorts
  • Multilingual linguistic sorts

6 Using the ttIsql Utility

• Batch mode vs. interactive mode
• Customizing the ttIsql command prompt
• Using ttIsql's online help
• Using ttIsql's 'editline' feature for UNIX only
  • Emacs binding
  • vi binding
• Using ttIsql's command history
  • Saving and clearing ttIsql's command history
• Working with character sets
• Working with transactions
• Displaying data store information
• Creating and executing PL/SQL blocks
• Using OUT parameters
• Displaying information about PL/SQL objects
• Viewing and setting data store attributes
• Viewing and changing query optimizer plans
• Timing ODBC function calls
• Working with prepared and parameterized SQL statements
• Defining default settings with the TTISQL environment variable
• Managing XLA bookmarks

7 Working with Data in a TimesTen Data Store

• Data store overview
  • Data store components
  • Data store users and owners
  • Data store persistence
• Understanding tables
  • Overview of tables
    • Column overview
    • In-line 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 their 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
• Understanding rows
  • Inserting rows
  • Deleting rows

8 Transaction Management and Recovery

• TimesTen commit behavior
• Transaction semantics
• Transaction atomicity and durability
  • Guaranteed atomicity and durability
  • Guaranteed atomicity, delayed durability
• Controlling durability and logging
  • Using durable commits
  • Log files
• Concurrency control
  • Transaction isolation levels
  • Locking granularities
  • Coexistence of different locking levels
• Checkpoints
  • Types of checkpoints
    • Transaction-consistent checkpoints
    • Fuzzy or non-blocking checkpoints
  • Setting and managing checkpoints
    • Setting the checkpoint rate for background checkpoints

9 TimesTen Database Performance Tuning

• System and data store tuning
  • Provide enough memory
  • Size your data store correctly
  • Calculate shared memory size for PL/SQL runtime
  • Increase LogBufMB if needed
  • Use temporary data stores if appropriate
  • Avoid connection overhead
  • Load the data store into RAM when duplicating
  • Reduce contention
  • Avoid OS paging at load time
  • Consider special options for maintenance
  • Check your driver
  • Enable tracing only as needed
  • Investigate alternative JVMs
  • If you are using replication, adjust log buffer size and CPU
  • Increase replication throughput for active standby pairs
  • Migrating data with character set conversions
• Client/Server tuning
  • 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 machine
  • Enable TT_PREFETCH_CLOSE for serializable transactions
  • Use a connection handle when calling SQLTransact
• SQL tuning
  • Tune statements and use indexes
  • Select hash, range, or bitmap indexes appropriately
  • Size hash indexes appropriately
  • Use foreign key constraint appropriately
  • Computing exact or estimated statistics
  • Avoid ALTER TABLE
  • Avoid nested queries
  • Prepare statements in advance
  • Avoid unnecessary prepare 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
• Recovery tuning
  • Set RecoveryThreads
  • Discovered direct I/O on HP-UX
• 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
  • Limit replication transmitters and receivers and XLA readers
  • 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

10 The TimesTen Query Optimizer

• When optimization occurs
• Viewing SQL commands 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?
  • When to modify an execution plan
  • How to modify execution plan generation

Glossary

Index