Workload analyzer recommendations
Workload Analyzer monitors database activity and logs recommendations as needed in system table TUNING_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 DROP PROJECTION |
| User with admin privileges has empty password. | Set the password for user with ALTER USER...IDENTIFIED BY. |
| Table has too many partitions. | Alter the table's partition expression with ALTER TABLE. Also consider grouping partitions and hierarchical partitioning. |
| Partitioned table data is not fully reorganized after repartitioning. | Reorganize data in the partitioned table with ALTER TABLE...REORGANIZE. |
| Table has multiple partition keys within the same ROS container. | |
| Tuple Mover's MoveOutInterval parameter setting is greater than the default value. | 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 PLANNEDCONCURRENCY and/or MAXCONCURRENCY. For details, see ALTER RESOURCE POOL and Built-in resource pools configuration. |
| 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 ANALYZE_STATISTICS on table columns. See also Collecting database statistics. |
| 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 ALTER DATABASE...SET |
| 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. |