上周三去东软做了TimesTen11g的性能优化与最佳实践培训，内容虽然很多但是总结一下，主要涉及四个部分：
1. Database and System Tuning
2. SQL and Application Tuning
3. Cache and Replication Tuning
4. TimesTen 11g Performance
1. Database and System Tuning
●Disk
LogDir and DataStore
LogBufMB, LogFileSize
CkptFrequency, CkptLogVolume, CkptRate
DurableCommits, LogFlushMethod
●CPU/Memory/Scale-up
MemoryLock
DatabaseCharacterSet, ConnectionCharacterSet
LogBufParallelism
PermSize, TempSize
RecoveryThreads
●Client/Server
MaxConnsPerServer, ServersPerDSN
●Cache & Replication
CacheAWTMethod
ReceiverThreads
ReplicationParallelism, ReplicationApplyOrdering, ReplicationTrack
●Miscellaneous
SQLQueryTimeout, LockWait, PLSQL_TIMEOUT, TTC_Timeout
PrivateCommands
2. SQL and Application Tuning
●Use Direct-Linked
●[Structure code to] avoid round-trips
●Update statistics
●Check query plans, use optimal SQL, use native data types
●Use indexes
●Use Best Practices for OCI and PL/SQL
●Separate I/O devices for transactions logs and checkpoint files; use fast disks!
●Optimize threads/processes to hardware
●Limit network round trips when using client/server or IMDB cache
●Monitor Performance to know what’s expected vs. what’s a problem
3. Cache and Replication Tuning
Best Practice for AutoRefresh
●Split your massive write transaction in Oracle to small ones (with sleep in between)
●Consider different intervals for different cache groups
●Coalesce the index on log table
ALTER INDEX … COALESCE
Depending on customer usage, the following may benefit. Consult Oracle Support before implementation
●Modify trigger to filter unnecessary columns (reduce number of rows in change log table)
●Partition the change log table
●Use multiple change log tables, if there are multiple partitioned cache
groups. Use a different cache admin user (hence different change log
table) for each cache group
Best Practice for AWT
●LogBufMB=1024
●Experiment setting SQL and PL/SQL mode for CacheAWTMethod
●Avoid large write transactions
●Avoid transient and permanent AWT errors (review awt.errors log)
●Tune the Oracle database schema
●Partition the workload across multiple TimesTen data stores
Best Practice for Replication
●LogBufMB=1024
●Split large write transaction into small ones
●In A/S Pair, set up time sync (e.g. NTP) between Active and Standby host
●In Return TwoSafe, evaluate threshold settings based on specific network condition
●Experiment with ReceiverThreads=2
●For Asynchronous Replication, configure large OS TCP Buffer Windows
●Use Parallel Replication (user-managed tracks)
●Consider <Asynchronous Replication + Return Twosafe by Request> versus <Return Twosafe>
●Use separate threads for <return twosafe by request> transactions
4. TimesTen 11g Performance
Upgrade to 11g – Functionality Benefit
●Cache Grid
●High Availability
Automatic Failover with Oracle Clusterware
Integration with Data Guard
Automatic Client Failover
●Application Development
Oracle Call Interface (OCI) and Pro*C Support
PL/SQL – ability to invoke PL/SQL code from all supported APIs
●Bitmap index
●Asynchronous Materialized Views
●Replicated XLA bookmark
●System stats for monitoring and diagnosing
●SQL Developer support
●Oracle Enterprise Manager Plug-In
●ttSrcScan
Upgrade to 11g – Performance Benefit
●Speed up complex queries
Bitmap indexes
●Reduce client/server path lengths for better response time
Stored procedures, deferred prepares, less chattiness
●Improve database migration time
Parallel ttMigrate
●Improve throughput of load on demand caching
Reduce transparent load code path
●Improve batch inserts for tables with > 128 columns
●Improve JDBC Client/Server performance by eliminating roundtrips
●Reduce memory usage for NULL column
Upgrade to 11g – Performance & Scalability
●Improve database caching scalability to grow with processing capacity
Cache Grid
●Improve write scalability for large SMP and ccNUMA servers
parallel transaction log manager
●Improve replication scalability and throughput
Enhanced protocol pipelining on the receiver side
Parallel replication
Upgrade to 11g – Behavior Changes
●ODBC Attribute changes
Authenticate
DDLCommitBehavior
Logging
TransparentLoad
LogBuffSize -> LogBufMB
CacheGridEnable
●IMDB Cache changes
Oracle instant client packaged with TimesTen
TransparentLoad -> Dynamic Cache Groups
Cache Connect setup (scripts)
●Access Control changes
In 7.0, no access control or instance-wide access control
In 11g, must have access control: database-wide access control
More privilege introduced
●TTClasses
Recompile TTClasses without -DTTEXCEPT flag, to preserve the old behavior
●Refer to Metalink Note 1188165.1 for details
TimesTen11g的性能优化与最佳实践基本涉及上述方面。
Popularity: 18% [?]
转载之：http://www.stonemoya.com/archives/timesten-11g%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E4%B8%8E%E6%9C%80%E4%BD%B3%E5%AE%9E%E8%B7%B5.html
添加新评论0 条评论