|
Symptoms
|
Diagnosis
|
Solution
|
|---|---|---|
|
Slow response time for a single query. Other concurrent access to the database is affected. Disk might be busy.
|
Inefficient usage of indexes in the query.
Non-optimal decision from the optimizer.
External sorting is not defined and a large internal sorting is causing excessive swapping to disk.
|
If index definitions are missing, create new indexes or modify existing ones to match the indexing requirements of the slow query. For more details, see Using indexes to improve query performance.
Run the EXPLAIN PLAN FOR statement for the slow query and verify whether the query optimizer is using the indexes. For more details, see EXPLAIN PLAN FOR statement.
If the optimizer is not choosing the optimal query execution plan, override the optimizer decision by using optimizer hints. For more information, see Using optimizer hints.
|
|
Slow response time is experienced for all queries. An increase in the number of concurrent users deteriorates the performance more than linearly. When all users are thrown out and then reconnected, performance still does not improve.
|
Insufficient cache size.
|
Increase the cache size. Allocate for cache at least 0.5 MB per concurrent user or 2-5% of the database size. For more details, see Managing the database cache.
|
|
Slow response time is experienced for all queries and write operations. When all users are thrown out and are connected, performance only improves temporarily. The disk is very busy.
|
The Bonsai tree is too large to fit into the cache.
|
Make sure that there are no unintentionally long-running transactions. Verify that all transactions (also read-only transactions) are committed in a timely manner. For more details, see Reducing Bonsai tree size by committing transactions.
|
|
Slow performance during batch write operation as the database size increases. There is an excessive amount of disk I/O.
|
The data is committed to the database in batches that are too small.
Data is written to disk in an order that is not supported by the primary key of the table.
|
Make sure that the autocommit is switched off and the write operations are committed in batches of at least 100 rows per transaction.
Modify the primary keys or batch write processes so that write operations occur in the primary key order. For more details, read Optimizing batch inserts and update.
|
|
The server process footprint grows excessively and causes the operating system to swap. The disk is very busy. The ADMIN COMMAND 'report' output shows a long list of currently active statements.
|
SQL statements have not been closed and dropped after use.
|
Make sure that the statements that are no longer in use by the client application are closed and dropped in a timely manner.
|