Workload Analyzer Recommendations
Workload Analyzer monitors database activity and logs recommendations as needed in system table TUNING_RECOMMENDATIONS
. When you run Workload Analyzer, the utility returns the following information:
- Description of the object that requires tuning
- Recommended action
- SQL command to implement the recommendation
Common Issues and Recommendations
Issue | Recommendation |
---|---|
No custom resource pools, user queries are typically handled by the GENERAL resource pool. | Create custom resource pools to handle queries from specific users. |
A projection is identified as rarely or never used to execute queries: |
Remove the projection with |
User with admin privileges has empty password. |
Set the password for user with |
Table has too many partitions. |
Alter the table's partition expression with |
Partitioned table data is not fully reorganized after repartitioning. |
Reorganize data in the partitioned table with |
Table has multiple partition keys within the same ROS container. |
|
Tuple Mover's |
Decrease the parameter setting, or reset the parameter to its default setting. |
Average CPU usage exceeds 95% for 20 minutes. |
Check system processes, or change resource pool settings of parameters |
Excessive swap activity; average memory usage exceeds 99% for 10 minutes. |
Check system processes |
A table does not have any Database Designer-designed projections. |
Run database designer on the table. For details, see Incremental Design. |
Table statistics are stale. |
Run |
Data distribution in segmented projection is skewed. |
Resegment projection on high-cardinality columns. For details, see Designing for Segmentation. |
Attempts to execute a query generated a GROUP BY spill event. |
Consider running an incremental design on the query. |
Internal configuration parameter is not the same across nodes. |
Reset configuration parameter with |
LGE threshold setting is lower than the default setting. |
Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support. |
Tuple Mover is disabled. |
|
Too many ROS containers since the last mergeout operation; configuration parameters are set lower than the default. |
|
Too many ROS containers since the last mergeout operation; the TM Mergeout service is disabled. |