Table of Contents
Purpose of This Document
Audience
How This Document Is Organized
Properties Overview
Scope of Properties
Persistence of Properties
Precedence of Properties
Protection of Database-Wide Properties
Dynamic vs. Static Properties
Ways of Setting Cloudscape Properties
System-Wide Properties
Programmatically
As a Parameter to the JVM Command Line
Using a Properties Object Within an Application or Statement
In the cloudscape.properties File
Verifying System Properties
Database-Wide Properties
Shortcut for Setting Database Properties
Conglomerate-Specific Properties
Verifying Conglomerate-Specific Properties
In a Client/Server Environment
Dynamic or Static Changes to Properties
Properties Case Study
The Top Ten Tips
Tip One. Use a Fast JVM with Lots of Memory, and Tune the JIT
Tip Two. Use Stored Prepared Statements and Prepared Statements
Tip Three. Create Indexes, and Make Sure They're Being Used
Tip Four. Tune How You Load Data
Tip Five. Increase the Size of the Data Page Cache
Tip Six. Tune the Size of Database Pages
Performance Trade-Offs of Large Pages
When Large Page Size Does Not Improve Performance
When Large Page Size Is Not Desirable
Tip Seven. Avoid Expensive Queries
Tip Eight. Use the Appropriate getXXX and setXXX Methods for the Type
Tip Nine. Tune Database Booting/Class Loading
Tip Ten. Recompile Stale Stored Prepared Statements
No Longer in the Top Ten, but Worth Listing
Shut Down the System Properly
Put Cloudscape First in Your Class Path
Increase the Statement Cache Size
When Working with Development Tools, Pre-Load DatabaseMetaData Stored Prepared Statements
Application and Database Design Issues
Avoiding Table Scans of Large Tables
Index, Index, Index
Create Useful Indexes
Make Sure They Are Being Used, and Rebuild Them
Think About Join Order
Prevent the User from Issuing Expensive Queries
Understand When Statements Go Stale
Overview
Automatic Stale Plan Invalidation
Stale Plans and Stored Prepared Statements
When a Change in Table Makes a Plan Stale
Avoiding Compiling SQL Statements
Across Connections and Sessions
Within a Single Connection
Shielding Users from Cloudscape Class-Loading Events
Tuning Tips for Multi-User Systems
Tuning Tips for Single-User Systems
Analyzing Statement Execution
Storing Prepared Statements to Improve Performance
Creating a Stored Prepared Statement
Executing a Stored Prepared Statement
Additional Benefits for Multiple Connections: The Stored Prepared Statement Cache
Invalid Stored Prepared Statements and Recompilation
Stale Stored Prepared Statements
Working with RunTimeStatistics
Overview
How You Use It
Analyzing the Information
Statistics Timing
Statement Execution Plan
Subset of Statement Execution Plan
Optimizer Estimates
Viewing Runtime Statistics in Cloudview
Performance and Optimization
Index Use and Access Paths
What Is an Index?
What's Optimizable?
Directly Optimizable Predicates
Indirectly Optimizable Predicates
Joins
Covering Indexes
Single-Column Index Examples
Multiple-Column Index Example
Useful Indexes Can Use Qualifiers
When a Table Scan Is Better
Indexes Have a Cost for Inserts, Updates, and Deletes
How Indexes Affect Cursors
Joins and Performance
Join Order Overview
Join Strategies
Cloudscape's Cost-Based Optimization
About the Optimizer's Choice of Access Path
Estimating Row Counts for Unknown Search Values
About the Optimizer's Choice of Join Order
Join Order Case Study
About the Optimizer's Choice of Join Strategy
About the Optimizer's Choice of Sort Avoidance
Cost-Based ORDER BY Sort Avoidance
About the Optimizer's Selection of Lock Granularity
How the Optimizer Makes Its Decision at Compile Time
Lock Escalation Threshold
Runtime Overrides
About the Optimizer's Selection of Bulk Fetch
Overriding the Optimizer
Overriding the Optimizer's Choice of Access Path
Overriding the Optimizer's Choice of Join Order
Overriding the Optimizer's Choice of Join Strategy
Overriding the Optimizer's Choice of Fetch Size
Tuning Lock Granularity
Optimizer Accuracy
Providing Costing Information for VTIs
Locking and Performance
Transaction-Based Lock Escalation
LOCK TABLE Statement
Non-Cost-Based Optimizations
Non-Cost-Based Sort Avoidance (Tuple Filtering)
DISTINCT
Quick DISTINCT Scans
GROUP BY
The MIN() and MAX() Optimizations
Scope of Cloudscape Properties
Cloudscape Properties
cloudscape.authentication.ldap.searchAuthDN
cloudscape.authentication.ldap.searchAuthPW
cloudscape.authentication.ldap.searchBase
cloudscape.authentication.ldap.searchFilter
cloudscape.authentication.provider
cloudscape.authentication.server
cloudscape.connection.requireAuthentication
cloudscape.database.classpath
cloudscape.database.defaultConnectionMode
cloudscape.database.forceDatabaseLock
cloudscape.database.fullAccessUsers
cloudscape.database.noAutoBoot
cloudscape.database.propertiesOnly
cloudscape.database.readOnlyAccessUsers
cloudscape.infolog.append
cloudscape.jdbc.metadataStoredPreparedStatements
cloudscape.language.bulkFetchDefault
cloudscape.language.defaultIsolationLevel
cloudscape.language.logStatementText
cloudscape.language.preloadClasses
cloudscape.language.spsCacheSize
cloudscape.language.stalePlanCheckInterval
cloudscape.language.statementCacheSize
cloudscape.language.triggerMaximumRecursionLevel
cloudscape.locks.deadlockTimeout
cloudscape.locks.deadlockTrace
cloudscape.locks.escalationThreshold
cloudscape.locks.monitor
cloudscape.locks.waitTimeout
cloudscape.service
cloudscape.storage.fileCacheSize
cloudscape.storage.initialPages
cloudscape.storage.minimumRecordSize
cloudscape.storage.pageCacheSize
cloudscape.storage.pageReservedSpace
cloudscape.storage.pageSize
cloudscape.storage.rowLocking
cloudscape.storage.sortBufferMax
cloudscape.storage.tempDirectory
cloudscape.stream.error.field
cloudscape.stream.error.file
cloudscape.stream.error.logSeverityLevel
cloudscape.stream.error.method
cloudscape.system.bootAll
cloudscape.system.home
cloudscape.user.UserName
bulkFetch
constraint
index
joinOrder
joinStrategy
Predicate Transformations
BETWEEN Transformations
LIKE Transformations
Character String Beginning with Constant
Character String Without Wildcards
Unknown Parameter
Static IN Predicate Transformations
NOT IN Predicate Transformations
OR Transformations
Transitive Closure
Transitive Closure on Join Clauses
Transitive Closure on Search Clauses
View Transformations
View Flattening
Predicates Pushed into Views or Derived Tables
Subquery Processing and Transformations
Materialization
Flattening a Subquery into a Normal Join
Flattening a Subquery into an EXISTS Join
Flatting VALUES Subqueries
DISTINCT Elimination in IN, ANY, and EXISTS Subqueries
IN/ANY Subquery Transformation
Outer Join Transformations
Sort Avoidance
DISTINCT Elimination Based on a Uniqueness Condition
Combining ORDER BY and DISTINCT
Combining ORDER BY and UNION
Aggregate Processing
COUNT(nonNullableColumn)
|