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 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.