Table of Contents

Search

  1. Preface
  2. Performance Tuning Overview
  3. Bottlenecks
  4. Optimizing the Target
  5. Optimizing the Source
  6. Optimizing Mappings
  7. Optimizing Transformations
  8. Optimizing Sessions
  9. Optimizing Grid Deployments
  10. Optimizing the PowerCenter Components
  11. Optimizing the System
  12. Using Pipeline Partitions
  13. Performance Counters

Performance Tuning Guide

Performance Tuning Guide

Optimizing Single-Sorted Queries

Optimizing Single-Sorted Queries

To optimize a single-sorted query on the database, consider the following tuning options that enable parallelization:
  • Check for configuration parameters that perform automatic tuning.
    For example, Oracle has a parameter called parallel_automatic_tuning.
  • Make sure intra-parallelism is enabled.
    Intra-parallelism is the ability to run multiple threads on a single query. For example, on Oracle, look at parallel_adaptive_multi_user. On DB2, look at intra_parallel.
  • Verify the maximum number of parallel processes that are available for parallel executions.
    For example, on Oracle, look at parallel_max_servers. On DB2, look at max_agents.
  • Verify the sizes for various resources used in parallelization.
    For example, Oracle has parameters such as large_pool_size, shared_pool_size, hash_area_size, parallel_execution_message_size, and optimizer_percent_parallel. DB2 has configuration parameters such as dft_fetch_size, fcm_num_buffers, and sort_heap.
  • Verify the degrees of parallelism.
    You may be able to set this option using a database configuration parameter or an option on the table or query. For example, Oracle has parameters parallel_threads_per_cpu and optimizer_percent_parallel. DB2 has configuration parameters such as dft_prefetch_size, dft_degree, and max_query_degree.
  • Turn off options that may affect database scalability.
    For example, disable archive logging and timed statistics on Oracle.
For a comprehensive list of database tuning options, see the database documentation.