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:

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:

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.