Getting Tuning Recommendations

Call the function ANALYZE_WORKLOAD to get tuning recommendations for queries and database objects. The function arguments specify what events to analyze and when.

Setting Scope and Time Span

ANALYZE_WORKLOAD's scope argument determines what to analyze:

This argument... Returns WLA recommendations for...
'' (empty string) All database objects
Table name A specific table
Schema name All objects in the specified schema

The optional since‑time argument specifies to return values from all in -scope events starting from since‑time and continuing to the current system status. If you omit since_time, ANALYZE_WORKLOAD returns recommendations for events since the last recorded time that you called the function. You must explicitly cast the since‑time string value to either TIMESTAMP or TIMESTAMPTZ.

The following examples show four ways to express the since‑time argument with different formats. All queries return the same result for workloads on table t1 since October 4, 2012:

=> SELECT ANALYZE_WORKLOAD('t1', TIMESTAMP '2012-10-04 11:18:15');
=> SELECT ANALYZE_WORKLOAD('t1', '2012-10-04 11:18:15'::TIMESTAMPTZ);
=> SELECT ANALYZE_WORKLOAD('t1', 'October 4, 2012'::TIMESTAMP);
=> SELECT ANALYZE_WORKLOAD('t1', '10-04-12'::TIMESTAMPTZ);

Saving Function Results

Instead of analyzing events since a specific time, you can save results from ANALYZE_WORKLOAD, by setting the function's second argument to true. The default is false, and no results are saved. After saving function results, subsequent calls to ANALYZE_WORKLOAD analyze only events since you last saved returned data, and ignore all previous events.

For example, the following statement returns recommendations for all database objects in all schemas and records this analysis invocation.

=> SELECT ANALYZE_WORKLOAD('', true);

The next invocation of ANALYZE_WORKLOAD analyzes events from this point forward.

Observation Count and Time

The observation_count column returns an integer that represents the total number of events Workload Analyzer (WLA) observed for this tuning recommendation. In each case above, WLA is making its first recommendation. Null results in observation_time only mean that the recommendations are from the current system status instead of from a prior event.

Tuning Targets

The tuning_parameter column returns the object on which WLA recommends that you apply the tuning action. The parameter of release in the example above notifies the DBA to set a password for user release.

Tuning Recommendations and Costs

WLA's output returns a brief description of tasks you should consider in the tuning_description column, along with a SQL command you can run, where appropriate, in the tuning_command column. In records 1 and 2 above, WLA recommends that you run the Database Designer on two tables, and in record 3 recommends setting a user's password. Record 3 also provides the ALTER USER command to run because the tuning action is a SQL command.

Output in the tuning_cost column indicates the cost of running the recommended tuning command:

Examples

The following statement tells WLA to analyze all events for the locations table:

=> SELECT ANALYZE_WORKLOAD('locations');

WLA returns with a recommendation that you run the Database Designer on the table, an operation that, depending on the size of locations, might incur a high cost:

-[ RECORD 1 ]----------+------------------------------------------------
observation_count      | 1
first_observation_time |
last_observation_time  |
tuning_parameter       | public.locations
tuning_description     | run database designer on table public.locations
tuning_command         |
tuning_cost            | HIGH

The following statement analyzes workloads on all tables in the VMart example database since one week before today:

=> SELECT ANALYZE_WORKLOAD('', NOW() - INTERVAL '1 week');

WLA returns with the following results:

-[ RECORD 1 ]----------+------------------------------------------------------
observation_count      | 4
first_observation_time | 2012-02-17 13:57:17.799003-04
last_observation_time  | 2011-04-22 12:05:26.856456-04
tuning_parameter       | store.store_orders_fact.date_ordered
tuning_description     | analyze statistics on table column store.store_orders_fact.date_ordered
tuning_command         | select analyze_statistics('store.store_orders_fact.date_ordered');
tuning_cost            | MEDIUM
-[ RECORD 2 ]---------+------------------------------------------------------
...
-[ RECORD 14 ]---------+-----------------------------------------------------
observation_count      | 2
first_observation_time | 2012-02-19 17:52:03.022644-04
last_observation_time  | 2012-02-19 17:52:03.02301-04
tuning_parameter       | SELECT x FROM t WHERE x > (SELECT SUM(DISTINCT x) FROM
                       | t GROUP BY y) OR x < 9;
tuning_description     | consider incremental design on query
tuning_command         |
tuning_cost            | HIGH

WLA finds two issues:

System Table Recommendations

You can also get tuning recommendations by querying system table TUNING_RECOMMENDATIONS, which returns tuning recommendation results from the last ANALYZE_WORKLOAD call.

=> SELECT * FROM tuning_recommendations;

System information that WLA uses for its recommendations is held in SQL system tables, so querying the TUNING_RECOMMENDATIONS system table does not run WLA.

See Also

Collecting Database Statistics