Amazon Aurora
User Guide for Aurora (API Version 2014-10-31)

Examining Plans in the apg_plan_mgmt.dba_plans view

Query plan management provides a new SQL view for database administrators (DBAs) to use called apg_plan_mgmt.dba_plans. Each database in a DB instance has its own apg_plan_mgmt.dba_plans view.

This view contains the plan history for all of your managed statements. Each managed plan is identified by the combination of a SQL hash value and a plan hash value. With these identifiers, you can use tools such as Amazon RDS Performance Insights to track individual plan performance. For more information on Performance Insights, see Using Amazon RDS Performance Insights.

Note

Access to the apg_plan_mgmt.dba_plans view is restricted to users that hold the apg_plan_mgmt role.

Listing Managed Plans

To list the managed plans, use a SELECT statement on the apg_plan_mgmt.dba_plans view. The following example displays some columns in the dba_plans view such as the status, which identifies the approved and unapproved plans.

SELECT sql_hash, plan_hash, status, enabled, stmt_name FROM apg_plan_mgmt.dba_plans; sql_hash | plan_hash | status | enabled | stmt_name ------------+-----------+------------+---------+------------ 1984047223 | 512153379 | approved | t | rangequery 1984047223 | 512284451 | unapproved | t | rangequery (2 rows)

Reference for the apg_plan_mgmt.dba_plans View

The columns of plan information in the apg_plan_mgmt.dba_plans view include the following.

dba_plans Column Description
cardinality_error

A measure of the error between the estimated cardinality versus the actual cardinality. Cardinality is the number of table rows that the plan is to process. If the cardinality error is large, then it increases the likelihood that the plan isn't optimal. This column is populated by the apg_plan_mgmt.evolve_plan_baselines function.

compatibility_level

The feature level of the Aurora PostgreSQL optimizer.

created_by The authenticated user (session_user) who created the plan.
enabled

An indicator of whether the plan is enabled or disabled. All plans are enabled by default. You can disable plans to prevent them from being used by the optimizer. To modify this value, use the apg_plan_mgmt.set_plan_enabled function.

environment_variables

The PostgreSQL Grand Unified Configuration (GUC) parameters and values that the optimizer has overridden at the time the plan was captured.

estimated_startup_cost The estimated optimizer setup cost before the optimizer delivers rows of a table.
estimated_total_cost The estimated optimizer cost to deliver the final table row.
execution_time_benefit_ms The execution time benefit in milliseconds of enabling the plan. This column is populated by the apg_plan_mgmt.evolve_plan_baselines function.
execution_time_ms The estimated time in milliseconds that the plan would run. This column is populated by the apg_plan_mgmt.evolve_plan_baselines function.
has_side_effects A value that indicates that the SQL statement is a data manipulation language (DML) statement or a SELECT statement that contains a VOLATILE function.
last_used This value is updated to the current date whenever the plan is either executed or when the plan is the query optimizer's minimum-cost plan. This value is stored in shared memory and periodically flushed to disk. To get the most up-to-date value, read the date from shared memory by calling the function apg_plan_mgmt.plan_last_used(sql_hash, plan_hash) instead of reading the last_used value. For additional information, see the apg_plan_mgmt.plan_retention_period parameter.
last_validated The most recent date and time when it was verified that the plan could be recreated by either the apg_plan_mgmt.validate_plans function or the apg_plan_mgmt.evolve_plan_baselines function.
last_verified The most recent date and time when a plan was verified to be the best-performing plan for the specified parameters by the apg_plan_mgmt.evolve_plan_baselines function.
origin

How the plan was captured with the apg_plan_mgmt.capture_plan_baselines parameter. Valid values include the following:

M – The plan was captured with manual plan capture.

A – The plan was captured with automatic plan capture.

param_list

The parameter values that were passed to the statement if this is a prepared statement.

plan_created The date and time the plan that was created.
plan_hash The plan identifier. The combination of plan_hash and sql_hash uniquely identifies a specific plan.
plan_outline A representation of the plan that is used to recreate the actual execution plan, and that is database-independent. Operators in the tree correspond to operators that appear in the EXPLAIN output.
planning_time_ms

The actual time to run the planner, in milliseconds. This column is populated by the apg_plan_mgmt.evolve_plan_baselines function.

queryId A statement hash, as calculated by the pg_stat_statements extension. This isn't a stable or database-independent identifier because it depends on object identifiers (OIDs).
sql_hash A hash value of the SQL statement text, normalized with literals removed.
sql_text The full text of the SQL statement.
status

A plan's status, which determines how the optimizer uses a plan. Valid values include the following. Case does not matter.

  • approved – A usable plan that the optimizer can choose to run. The optimizer runs the least-cost plan from a managed statement's set of approved plans (baseline). To reset a plan to approved, use the apg_plan_mgmt.evolve_plan_baselines function.

  • unapproved – A captured plan that you have not verified for use. For more information, see Evaluating Plan Performance.

  • rejected – A plan that the optimizer won't use. For more information, see Rejecting or Disabling Slower Plans.

  • preferred – A plan that you have determined is a preferred plan to use for a managed statement.

    If the optimizer's minimum-cost plan isn't an approved or preferred plan, you can reduce plan enforcement overhead. To do so, make a subset of the approved plans preferred. When the optimizer's minimum cost isn't an approved plan, a preferred plan is chosen before an approved plan.

    To reset a plan to preferred, use the apg_plan_mgmt.set_plan_status function.

stmt_name The name of the SQL statement within a PREPARE statement. This value is an empty string for an unnamed prepared statement. This value is NULL for a nonprepared statement.
total_time_benefit_ms

The total time benefit in milliseconds of enabling this plan. This value considers both planning time and execution time.

If this value is negative, there is a disadvantage to enabling this plan. This column is populated by the apg_plan_mgmt.evolve_plan_baselines function.