Function reference for query plan management
The apg_plan_mgmt
extension provides the following functions.
Functions
apg_plan_mgmt.delete_plan
Delete a managed plan.
Syntax
apg_plan_mgmt.delete_plan( sql_hash, plan_hash )
Return value
Returns 0 if the delete was successful or -1 if the delete failed.
Parameters
Parameter | Description |
---|---|
sql_hash
|
The sql_hash ID of the plan's managed SQL
statement.
|
plan_hash |
The managed plan's plan_hash ID.
|
apg_plan_mgmt.evolve_plan_baselines
Verifies whether an already approved plan is faster or whether a plan identified by the query optimizer as a minimum cost plan is faster.
Syntax
apg_plan_mgmt.evolve_plan_baselines( sql_hash, plan_hash, min_speedup_factor, action )
Return value
The number of plans that were not faster than the best approved plan.
Parameters
Parameter | Description |
---|---|
sql_hash |
The sql_hash ID of the plan's managed SQL
statement.
|
plan_hash |
The managed plan's plan_hash ID. Use NULL to
mean all plans that have the same sql_hash ID
value.
|
min_speedup_factor |
The minimum speedup factor can be the number of times faster that a plan must be than the best of the already approved plans to approve it. Alternatively, this factor can be the number of times slower that a plan must be to reject or disable it. This is a positive float value. |
action |
The action the function is to perform. Valid values include the following. Case does not matter.
|
Usage notes
Set specified plans to approved, rejected, or disabled based on whether the
planning plus execution time is faster than the best approved plan by a factor that
you can set. The action parameter might be set to 'approve'
or
'reject'
to automatically approve or reject a plan that meets the
performance criteria. Alternatively, it might be set to '' (empty string) to do the
performance experiment and produce a report, but take no action.
You can avoid pointlessly rerunning of the
apg_plan_mgmt.evolve_plan_baselines
function for a plan on which it
was recently run. To do so, restrict the plans to just the recently created
unapproved plans. Alternatively, you can avoid running the
apg_plan_mgmt.evolve_plan_baselines
function on any approved plan
that has a recent last_verified
timestamp.
Conduct a performance experiment to compare the planning plus execution time of each plan relative to the other plans in the baseline. In some cases, there is only one plan for a statement and the plan is approved. In such a case, compare the planning plus execution time of the plan to the planning plus execution time of using no plan.
The incremental benefit (or disadvantage) of each plan is recorded in the
apg_plan_mgmt.dba_plans
view in the
total_time_benefit_ms
column. When this value is positive, there is
a measurable performance advantage to including this plan in the baseline.
In addition to collecting the planning and execution time of each candidate plan,
the last_verified
column of the apg_plan_mgmt.dba_plans
view is updated with the current_timestamp
. The
last_verified
timestamp might be used to avoid running this
function again on a plan that recently had its performance verified.
apg_plan_mgmt.get_explain_stmt
Generates the text of an EXPLAIN
statement for the specified SQL statement.
Syntax
apg_plan_mgmt.get_explain_stmt( sql_hash, plan_hash, explain_option_list )
Return value
Returns runtime statistics for the specified SQL statements. Use without
explain_options_list
to return a simple EXPLAIN
plan.
Parameters
Parameter | Description |
---|---|
sql_hash
|
The sql_hash ID of the plan's managed SQL statement.
|
plan_hash |
The managed plan's plan_hash ID.
|
explain_option_list |
A comma-separated list of |
Usage notes
For the explain_option_list
, you can use any of the same options that you would use with an EXPLAIN
statement. The Aurora PostgreSQL optimizer
concatenates the list of options you provide to the EXPLAIN
statement, so you can request any option that EXPLAIN
supports.
apg_plan_mgmt.plan_last_used
Returns the last_used
date of the specified plan from shared memory.
The value in shared memory is always current on the read/write node. The value is
only periodically flushed
to the last_used column of the apg_plan_mgmt.dba_plans
view.
Syntax
apg_plan_mgmt.plan_last_used( sql_hash, plan_hash )
Return value
Returns the last_used
date.
Parameters
Parameter | Description |
---|---|
sql_hash
|
The sql_hash ID of the plan's managed SQL statement.
|
plan_hash |
The managed plan's plan_hash ID.
|
apg_plan_mgmt.reload
Reload plans into shared memory from the apg_plan_mgmt.dba_plans
view.
Syntax
apg_plan_mgmt.reload()
Return value
None.
Parameters
None.
Usage notes
Call reload
for the following situations:
-
Use it to refresh the shared memory of a read-only replica immediately, rather than wait for new plans to propagate to the replica.
-
Use it after importing managed plans.
apg_plan_mgmt.set_plan_enabled
Enable or disable a managed plan.
Syntax
apg_plan_mgmt.set_plan_enabled( sql_hash, plan_hash, [true | false] )
Return value
Returns 0 if the setting was successful or -1 if the setting failed.
Parameters
Parameter | Description |
---|---|
sql_hash |
The sql_hash ID of the plan's managed SQL
statement.
|
plan_hash |
The managed plan's plan_hash ID.
|
enabled |
Boolean value of true or false:
|
apg_plan_mgmt.set_plan_status
Set a managed plan's status to Approved
,
Unapproved
, Rejected
, or Preferred
.
Syntax
apg_plan_mgmt.set_plan_status( sql_hash, plan_hash, status )
Return value
Returns 0 if the setting was successful or -1 if the setting failed.
Parameters
Parameter | Description |
---|---|
sql_hash |
The sql_hash ID of the plan's managed SQL
statement.
|
plan_hash |
The managed plan's plan_hash ID.
|
status |
A string with one of the following values:
The case you use does not matter, however the status value is
set to initial uppercase in the
|
apg_plan_mgmt.validate_plans
Validate that the optimizer can still recreate plans. The optimizer validates
Approved
, Unapproved
, and Preferred
plans, whether the plan is enabled or disabled. Rejected
plans are not
validated. Optionally, you can use the apg_plan_mgmt.validate_plans
function to delete or disable invalid plans.
Syntax
apg_plan_mgmt.validate_plans( sql_hash, plan_hash, action) apg_plan_mgmt.validate_plans( action)
Return value
The number of invalid plans.
Parameters
Parameter | Description |
---|---|
sql_hash |
The sql_hash ID of the plan's managed SQL
statement.
|
plan_hash |
The managed plan's plan_hash ID. Use NULL to
mean all plans for the same sql_hash ID value.
|
action |
The action the function is to perform for invalid plans. Valid string values include the following. Case does not matter.
Any other value is treated like the empty string. |
Usage notes
Use the form validate_plans(action)
to validate all the managed plans
for all the managed statements in the entire apg_plan_mgmt.dba_plans
view.
Use the form validate_plans(sql_hash, plan_hash, action)
to validate
a managed plan specified with plan_hash
, for a managed statement
specified with sql_hash
.
Use the form validate_plans(sql_hash, NULL, action)
to validate all
the managed plans for the managed statement specified with
sql_hash
.