Understanding WLA Triggering Conditions
Workload Analyzer (WLA) monitors suspicious system activity and makes recommendations based on its observations. When you run Workload Analyzer, the utility returns the following information:
- The tuning description
- The objects on which WLA applies tuning action
- The suggested SQL command for you to run (where appropriate)
In rare circumstances, tuning recommendation WLA proposes might not resolve the underlying problem. Because you might occasionally need help understanding WLA's recommendations, the following section lists some of the most common triggering conditions, along with the recommendations to resolve the issue and a pointer to more information, when available.
Common Problems
Problem
Internal configuration parameter is not the same across nodes.
Solution
Reset configuration parameter.
=> ALTER DATABASE mydb SET parameter = value;
Problem
An unused projection meets the following conditions:
- No queries on projection for more than 30 days but, projection's anchor table has been queried more than 10 times
- Projection's anchor table is not a temp or system table
- Projection's table is not small, where the number of bytes of disk storage in use by the projection (used_bytes) is more than 10M
Solution
Drop the projection (projection-name).
=> DROP PROJECTION public.T1_fact_super_P1_B1;
Problem
User with dbadmin/pseudosuperuser role has empty password.
Solution
Set the password for user.
=> ALTER USER (user) IDENTIFIED BY ('new_password');
Problem
Table with too many partition count.
Solution
Alter the table's partition expression.
=> ALTER TABLE (schema.table) PARTITION BY (new_partition_expression) REORGANIZE;
Problem
LGE threshold setting is lower than the default setting.
Solution
Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Problem
Tuple Mover's MoveOutInterval
parameter is set greater than the default setting.
Solution
Decrease the MoveOutInterval
configuration parameter setting.
=> ALTER DATABASE mydb SET MoveOutInterval = default-value;
For more information, see Monitoring Events and ACTIVE_EVENTS
.
Problem
Tuple Mover has been disabled.
Solution
Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Problem
Too many ROS containers since the last mergeout operation; configuration parameters are set lower than the default.
Solution
Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Problem
Too many ROS containers since the last mergeout operation; the TM Mergeout service is disabled.
Solution
Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Problem
Average CPU usage exceeds 95% for 20 minutes.
Solution
Check system processes or change the settings for PLANNEDCONCURRENCY
and/or MAXCONCURRENCY
for the resource pool. For details, see ALTER RESOURCE POOL
and Built-In Pool Configuration.
Problem
Partitioned table data is not fully reorganized after repartitioning.
Solution
Reorganize data in partitioned table public.T1.
=> ALTER TABLE public.T1 REORGANIZE;
Problem
Table has multiple partition keys within the same ROS container.
Solution
Reorganize data in partitioned table public.T1.
=> ALTER TABLE public.T1 REORGANIZE;
Problem
Excessive swap activity; average memory usage exceeds 99% for 10 minutes.
Solution
Check system processes
Problem
A table does not have any Database Designer-designed projections.
Solution
Run database designer on table public.T1. For details, see Incremental Design.
Problem
Statistics are stale (no histogram or predicate falls outside histogram).
Solution
Run ANALYZE_STATISTICS
on table columns.
=> SELECT analyze_statistics ('public.t.a');
See also Collecting Database Statistics.
Problem
Data distribution in segmented projection is skewed.
Solution
Re-segment projection public.t_super on high-cardinality column(s). For details, see Designing for Segmentation.
Problem
GROUP BY spill event.
Solution
Consider running an incremental design on query.