Try SpotterFetches details about a SqlTuningAdvisorTaskSqlExecutionPlan resource in Oracle Cloud Infrastructure
| "added in version" 2.9.0 of oracle.oci"
Authors: Oracle (@oracle)
preview | supported by community
Install with ansible-galaxy collection install oracle.oci:==5.0.0
collections:
- name: oracle.oci
version: 5.0.0Fetches details about a SqlTuningAdvisorTaskSqlExecutionPlan resource in Oracle Cloud Infrastructure
Retrieves a SQL execution plan for the SQL being tuned.
- name: Get a specific sql_tuning_advisor_task_sql_execution_plan
oci_database_management_sql_tuning_advisor_task_sql_execution_plan_facts:
# required
managed_database_id: "ocid1.manageddatabase.oc1..xxxxxxEXAMPLExxxxxx"
sql_tuning_advisor_task_id: 56
sql_object_id: 56
attribute: ORIGINAL
region:
description:
- The Oracle Cloud Infrastructure region to use for all OCI API requests. If not set,
then the value of the OCI_REGION variable, if any, is used. This option is required
if the region is not specified through a configuration file (See C(config_file_location)).
Please refer to U(https://docs.us-phoenix-1.oraclecloud.com/Content/General/Concepts/regions.htm)
for more information on OCI regions.
type: str
tenancy:
description:
- OCID of your tenancy. If not set, then the value of the OCI_TENANCY variable, if
any, is used. This option is required if the tenancy OCID is not specified through
a configuration file (See C(config_file_location)). To get the tenancy OCID, please
refer U(https://docs.us-phoenix-1.oraclecloud.com/Content/API/Concepts/apisigningkey.htm)
type: str
api_user:
description:
- The OCID of the user, on whose behalf, OCI APIs are invoked. If not set, then the
value of the OCI_USER_ID environment variable, if any, is used. This option is required
if the user is not specified through a configuration file (See C(config_file_location)).
To get the user's OCID, please refer U(https://docs.us-phoenix-1.oraclecloud.com/Content/API/Concepts/apisigningkey.htm).
type: str
attribute:
choices:
- ORIGINAL
- ORIGINAL_WITH_ADJUSTED_COST
- USING_SQL_PROFILE
- USING_NEW_INDICES
- USING_PARALLEL_EXECUTION
description:
- The attribute of the SQL execution plan.
required: true
type: str
auth_type:
choices:
- api_key
- instance_principal
- instance_obo_user
- resource_principal
- security_token
default: api_key
description:
- The type of authentication to use for making API requests. By default C(auth_type="api_key")
based authentication is performed and the API key (see I(api_user_key_file)) in
your config file will be used. If this 'auth_type' module option is not specified,
the value of the OCI_ANSIBLE_AUTH_TYPE, if any, is used. Use C(auth_type="instance_principal")
to use instance principal based authentication when running ansible playbooks within
an OCI compute instance.
type: str
cert_bundle:
description:
- The full path to a CA certificate bundle to be used for SSL verification. This will
override the default CA certificate bundle. If not set, then the value of the OCI_ANSIBLE_CERT_BUNDLE
variable, if any, is used.
type: str
auth_purpose:
choices:
- service_principal
description:
- The auth purpose which can be used in conjunction with 'auth_type=instance_principal'.
The default auth_purpose for instance_principal is None.
type: str
sql_object_id:
description:
- The SQL object ID for the SQL tuning task. This is not the L(OCID,https://docs.cloud.oracle.com/Content/General/Concepts/identifiers.htm).
required: true
type: int
api_user_key_file:
description:
- Full path and filename of the private key (in PEM format). If not set, then the
value of the OCI_USER_KEY_FILE variable, if any, is used. This option is required
if the private key is not specified through a configuration file (See C(config_file_location)).
If the key is encrypted with a pass-phrase, the C(api_user_key_pass_phrase) option
must also be provided.
type: str
config_profile_name:
description:
- The profile to load from the config file referenced by C(config_file_location).
If not set, then the value of the OCI_CONFIG_PROFILE environment variable, if any,
is used. Otherwise, defaults to the "DEFAULT" profile in C(config_file_location).
type: str
managed_database_id:
description:
- The L(OCID,https://docs.cloud.oracle.com/Content/General/Concepts/identifiers.htm)
of the Managed Database.
required: true
type: str
api_user_fingerprint:
description:
- Fingerprint for the key pair being used. If not set, then the value of the OCI_USER_FINGERPRINT
environment variable, if any, is used. This option is required if the key fingerprint
is not specified through a configuration file (See C(config_file_location)). To
get the key pair's fingerprint value please refer U(https://docs.us-phoenix-1.oraclecloud.com/Content/API/Concepts/apisigningkey.htm).
type: str
config_file_location:
description:
- Path to configuration file. If not set then the value of the OCI_CONFIG_FILE environment
variable, if any, is used. Otherwise, defaults to ~/.oci/config.
type: str
api_user_key_pass_phrase:
description:
- Passphrase used by the key referenced in C(api_user_key_file), if it is encrypted.
If not set, then the value of the OCI_USER_KEY_PASS_PHRASE variable, if any, is
used. This option is required if the key passphrase is not specified through a configuration
file (See C(config_file_location)).
type: str
sql_tuning_advisor_task_id:
aliases:
- id
description:
- The SQL tuning task identifier. This is not the L(OCID,https://docs.cloud.oracle.com/Content/General/Concepts/identifiers.htm).
required: true
type: int
realm_specific_endpoint_template_enabled:
description:
- Enable/Disable realm specific endpoint template for service client. By Default,
realm specific endpoint template is disabled. If not set, then the value of the
OCI_REALM_SPECIFIC_SERVICE_ENDPOINT_TEMPLATE_ENABLED variable, if any, is used.
type: bool
sql_tuning_advisor_task_sql_execution_plan:
contains:
plan:
contains:
access_predicates:
description:
- The predicates used to locate rows in an access structure. For example,
start or stop predicates for an index range scan.
returned: on success
sample: access_predicates_example
type: str
attribute:
description:
- The text string identifying the type of execution plan.
returned: on success
sample: attribute_example
type: str
bytes:
description:
- The number of bytes returned by the current operation.
returned: on success
sample: 56
type: int
cardinality:
description:
- The number of rows returned by the current operation (estimated by the
CBO).
returned: on success
sample: 56
type: int
cost:
description:
- The cost of the current operation estimated by the cost-based optimizer
(CBO).
returned: on success
sample: 1.2
type: float
cpu_cost:
description:
- The CPU cost of the current operation.
returned: on success
sample: 1.2
type: float
filter_predicates:
description:
- The predicates used to filter rows before producing them.
returned: on success
sample: filter_predicates_example
type: str
io_cost:
description:
- The I/O cost of the current operation.
returned: on success
sample: 1.2
type: float
number_of_search_column:
description:
- Number of index columns with start and stop keys (that is, the number
of columns with matching predicates).
returned: on success
sample: 56
type: int
object_name:
description:
- The name of the object.
returned: on success
sample: object_name_example
type: str
object_node:
description:
- The name of the database link used to reference the object.
returned: on success
sample: object_node_example
type: str
object_owner:
description:
- The owner of the object.
returned: on success
sample: object_owner_example
type: str
object_position:
description:
- The numbered position of the object name in the original SQL statement.
returned: on success
sample: 56
type: int
object_type:
description:
- The descriptive modifier that further describes the type of object.
returned: on success
sample: object_type_example
type: str
operation:
description:
- The name of the operation performed at this step.
returned: on success
sample: operation_example
type: str
optimizer_mode:
description:
- The current mode of the optimizer, such as all_rows, first_rows_n (where
n = 1, 10, 100, 1000, and so on).
returned: on success
sample: optimizer_mode_example
type: str
options:
description:
- The options used for the operation performed at this step.
returned: on success
sample: options_example
type: str
other:
description:
- Information about parallel execution servers and parallel queries
returned: on success
sample: other_example
type: str
other_tag:
description:
- Describes the function of the SQL text in the OTHER column.
returned: on success
sample: other_tag_example
type: str
parent_step_id:
description:
- The ID of the next step that operates on the results of this step. This
is not the L(OCID,https://docs.cloud.oracle.com/Content/General/Concepts/identifiers.htm).
returned: on success
sample: 56
type: int
partition_id:
description:
- The ID of the step in the execution plan that has computed the pair of
values of partitionStart and partitionStop.
returned: on success
sample: 56
type: int
partition_start:
description:
- A step may get data from a range of partitions of a partitioned object,
such as table or index, based on predicates and sorting order. The partionStart
is the starting partition of the range. The partitionStop is the ending
partition of the range.
returned: on success
sample: partition_start_example
type: str
partition_stop:
description:
- A step may get data from a range of partitions of a partitioned object,
such as table or index, based on predicates and sorting order. The partionStart
is the starting partition of the range. The partitionStop is the ending
partition of the range.
returned: on success
sample: partition_stop_example
type: str
plan_hash_value:
description:
- The numerical representation of the SQL execution plan.
returned: on success
sample: 56
type: int
position:
description:
- The order of processing for steps with the same parent ID.
returned: on success
sample: 56
type: int
remarks:
description:
- The place for comments that can be added to the steps of the execution
plan.
returned: on success
sample: remarks_example
type: str
step_id:
description:
- The identification number of a step in the SQL execution plan. This is
unique within the SQL execution plan. This is not the L(OCID,https://docs.cloud.oracle.com/Content/General/Concepts/identifiers.htm).
returned: on success
sample: 56
type: int
temp_space:
description:
- The temporary space usage (in bytes) of the operation (sort or hash-join)
as estimated by the CBO.
returned: on success
sample: 56
type: int
time:
description:
- The elapsed time (in seconds) of the operation as estimated by the CBO.
returned: on success
sample: 56
type: int
description:
- A SQL execution plan as a list of steps.
returned: on success
type: complex
description:
- SqlTuningAdvisorTaskSqlExecutionPlan resource
returned: on success
sample:
plan:
- access_predicates: access_predicates_example
attribute: attribute_example
bytes: 56
cardinality: 56
cost: 1.2
cpu_cost: 1.2
filter_predicates: filter_predicates_example
io_cost: 1.2
number_of_search_column: 56
object_name: object_name_example
object_node: object_node_example
object_owner: object_owner_example
object_position: 56
object_type: object_type_example
operation: operation_example
optimizer_mode: optimizer_mode_example
options: options_example
other: other_example
other_tag: other_tag_example
parent_step_id: 56
partition_id: 56
partition_start: partition_start_example
partition_stop: partition_stop_example
plan_hash_value: 56
position: 56
remarks: remarks_example
step_id: 56
temp_space: 56
time: 56
type: complex