Question & Answer
Question
Q1. Resource usage
How much CPU and I/O load, and memory usage does the AI Query Optimizer add?
Answer
During model discovery and training
[CPU usage]
The AI Query Optimizer uses 100% of a single CPU core for approximately 30 seconds per table on average during model discovery and training. Only one CPU core is used for training at a time.
(Asynchronous automatic RUNSTATS processes operate on one table at a time.)
[Memory usage]
During training, at most 100 MB of memory is used to create the training data set and model for a single table. This memory is released and recycled once training for the table is completed. Models are trained serially; therefore, no more than 100 MB of memory is used at any given time.
[I/O usage]
Models are written to disk after training. This operation adds negligible I/O overhead. Each model occupies approximately 50 KB on disk. No additional I/O requirements beyond this model write operation are expected.
During query compilation
[Memory usage]
Each model consumes, on average, 50 KB of catalog cache memory during query compilation. For example, a query that leverages four models for selectivity estimation would consume approximately 200 KB of catalog cache memory.
For reference, the overall process is generally as follows:
1. During RUNSTATS execution, a sample of table data is collected.
2. The sample is expanded to understand joint relationships across columns. This step requires approximately 100 MB of memory.
3. A model is initialized (approximately 50 KB in size).
4. The model is trained using the expanded data set to learn and compress table relationships into the 50 KB model.
5. The expanded data set (100 MB) is discarded.
6. The trained 50 KB model is written to the system catalogs for future use during query compilation.
Q2. Impact on workloads
Do the model discovery and training processes slow down user workloads? Can they affect SQL concurrency, for example by accessing catalog tables?
Answer
Model discovery and training are performed as part of the automatic RUNSTATS processing framework. As a result, these processes can be throttled together with RUNSTATS if required. Applying throttling may increase overall model training time. The approximately 30-second average training time per table referenced earlier was observed under default throttling settings.
The discovery and training processes are not expected to impact user queries. Any catalog locks taken during model discovery or training are lower priority than locks required by user SQL workloads and therefore should not interfere with normal query execution or concurrency.
Q3. Expected benefits and usage guidance
What level of performance improvement can be expected from the AI Query Optimizer? In what cases is it better to turn this feature off?
Answer
On average, workloads may see up to a 3x query performance improvement for queries with local predicates, primarily due to improved cardinality estimation accuracy.
Recent support for pairwise join cardinality estimation (introduced in version 12.1.3) may further enhance performance of join-intensive workloads, with observed improvements of approximately 20% on average.
The use of the AI Query Optimizer does not guarantee performance improvements in all cases. Performance regressions are possible, and results may vary depending on workload characteristics and data distributions. However, the AI Query Optimizer includes safeguards designed to mitigate the likelihood and impact of regressions.
OLTP databases typically see limited benefit, as queries in such environments are generally simpler and already well optimized.
The AI Query Optimizer is most effective for databases where query performance is impacted by consistent cardinality underestimation.
The feature is intended to operate autonomously and reduce the amount of manual tuning typically required by DBAs, such as the manual creation and ongoing maintenance of statistical views to achieve similar performance benefits.
Additional query compilation overhead may be observed in cases where SQL statements contain OR predicates. Expansion of such predicates can result in increased memory usage during compilation.
Q4. How model discovery and training are triggered
Is there any way to control the timing of model discovery and training?
Answer
Model discovery and training are performed as part of automatic asynchronous table RUNSTATS operations.
To control when these processes occur, you can use the maintenance window policy to define the online maintenance window during which RUNSTATS operations are allowed to run.
Related Information
The AI Query Optimizer Features in Db2 Version 12.1
The Neural Networks Powering the Db2 Version 12.1 AI Query Optimizer
Simplify Query Performance Tuning with the Db2 AI Query Optimizer
Significant Performance Improvements with AI Join Cardinality Estimates in Db2 …
VIDEO 1: Generic, high level explanation
VIDEO 2: More detailed explanation, with AI Join cardinality focus.
Was this topic helpful?
Document Information
Modified date:
15 April 2026
UID
ibm17266812