Skip Headers
Oracle® TimesTen In-Memory Database Operations Guide
Release 11.2.1
Part Number E13065-08
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
Contents
Title and Copyright Information
Preface
Audience
Related documents
Conventions
Documentation Accessibility
Technical support
What's New
New features in release 11.2.1.8.0
New features in release 11.2.1.6.0
New features in release 11.2.1.4.0
New features in release 11.2.1.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 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
Data Source specification
ODBC.INI file example
Connecting to a database using a connection string
Specifying a RAM policy
Specifying the size of a database
Estimating and modifying the data partition sizes for the database
Unloading the database from memory
Monitoring PermSize and TempSize attributes
Receiving out-of-memory warnings
Manage existing tables in the database
Migration, backup, and restoration of the database
Copying, migrating and restoring a database
Backing up and restoring a database
Types of backup provided
Thread programming with TimesTen
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
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
Working with the TTCONNECT.INI file
Creating Client DSNs
Creating and configuring Client DSNs on Windows
Creating and configuring Client DSNs on UNIX
Running the TimesTen Server
Server informational messages
Accessing a remote database on UNIX
Testing connections
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 database access over NFS-mounted systems
Enabling Linux large page support
Shared memory daemon option for HP-UX ccNUMA systems
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 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
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
Object migration and character sets
Migration and length semantics
Migrating linguistic indexes
Migrating cache group tables
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
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 monitor command
Listing database objects by object type
Viewing and setting connection attributes
Working with transactions
Working with prepared and parameterized SQL statements
Creating and executing PL/SQL blocks
Pass data from PL/SQL using OUT parameters
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
Timing ODBC function calls
Managing XLA bookmarks
7
Working with Data in a TimesTen Database
Database overview
Database components
Database users and owners
Database 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 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
Understanding synonyms
Creating synonyms
Dropping synonyms
Synonyms may cause invalidation or recompilation of SQL queries
8
Transaction Management and Recovery
Transaction overview
Configuring transaction implicit commit behavior
Transaction autocommit behavior
TimesTen DDL commit behavior
Relationship between autocommit and DDLCommitBehavior
Transaction semantics
Transaction atomicity
Transaction durability
Guaranteed durability
Delayed durability
Durable commit performance enhancements
Transaction logging
Managing transaction log buffers and files
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
Configure or turn off background checkpointing
Display checkpoint history and status
Setting the checkpoint rate
9
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
Reduce contention
Avoid operating system 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 transaction 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
Scripting on this page enhances content navigation, but does not change the content in any way.