Quick start for acceptable performance on a small database

If you have a small database with each table residing on only one disk and using only one CPU virtual processor, you can take specific measurements to help you anticipate and correct performance problems.

To achieve acceptable initial performance on a small database:

  1. Generate statistics of your tables and indexes to provide information to the query optimizer to enable it to choose query plans with the lowest estimated cost.

    These statistics are a minimum starting point to obtain good performance for individual queries. For guidelines, see Update statistics when they are not generated automatically. To see the query plan that the optimizer chooses for each query, see Display the query plan.

  2. If you want a query to run in parallel with other queries, you must turn on the Parallel Database Query (PDQ) feature.

    Without table fragmentation across multiple disks, parallel scans do not occur. With only one CPU virtual processor, parallel joins or parallel sorts do not occur. However, PDQ priority can obtain more memory to perform the sort. For more information, see Parallel database query (PDQ).

  3. If you want to mix online transaction processing (OLTP) and decision-support system (DSS) query applications, you can control the amount of resources a long-running query can obtain so that your OLTP transactions are not affected.

    For information about how to control PDQ resources, see The allocation of resources for parallel database queries.

  4. Monitor sessions and drill down into various details to improve the performance of individual queries.

    For information about the various tools and session details to monitor, see Monitoring memory usage for each session and Monitor sessions and threads.

Copyright© 2018 HCL Technologies Limited