Database Management SQL Tuning Functions

CLONE_SQL_TUNING_TASK Function

Clones and runs a SQL tuning task in the database.

Syntax

FUNCTION clone_sql_tuning_task (
  managed_database_id varchar2,
  clone_sql_tuning_task_details dbms_cloud_oci_database_management_clone_sql_tuning_task_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_clone_sql_tuning_task_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`clone_sql_tuning_task_details`	(required) The detailed inputs required to clone a SQL tuning task.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

CREATE_SQL_TUNING_SET Function

Creates an empty Sql tuning set within the Managed Database specified by managedDatabaseId.

Syntax

FUNCTION create_sql_tuning_set (
  managed_database_id varchar2,
  create_sql_tuning_set_details dbms_cloud_oci_database_management_create_sql_tuning_set_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_create_sql_tuning_set_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`create_sql_tuning_set_details`	(required) The details required to create a Sql tuning set.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

DROP_SQL_TUNING_SET Function

Drops the Sql tuning set specified by sqlTuningSet within the Managed Database specified by managedDatabaseId.

Syntax

FUNCTION drop_sql_tuning_set (
  managed_database_id varchar2,
  sql_tuning_set_id number,
  drop_sql_tuning_set_details dbms_cloud_oci_database_management_drop_sql_tuning_set_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_drop_sql_tuning_set_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_set_id`	(required) The unique identifier of the Sql tuning set. This is not OCID.
`drop_sql_tuning_set_details`	(required) The details required to drop a Sql tuning set.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

DROP_SQL_TUNING_TASK Function

Drops a SQL tuning task and its related results from the database.

Syntax

FUNCTION drop_sql_tuning_task (
  managed_database_id varchar2,
  drop_sql_tuning_task_details dbms_cloud_oci_database_management_drop_sql_tuning_task_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_drop_sql_tuning_task_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`drop_sql_tuning_task_details`	(required) The detailed inputs required to drop a SQL tuning task.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

DROP_SQLS_IN_SQL_TUNING_SET Function

Deletes the Sqls in the specified Sql tuning set that matches the filter criteria provided in the basicFilter. If basicFilter criteria is not provided, then entire Sqls in the Sql tuning set is deleted.

Syntax

FUNCTION drop_sqls_in_sql_tuning_set (
  managed_database_id varchar2,
  sql_tuning_set_id number,
  drop_sqls_in_sql_tuning_set_details dbms_cloud_oci_database_management_drop_sqls_in_sql_tuning_set_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_drop_sqls_in_sql_tuning_set_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_set_id`	(required) The unique identifier of the Sql tuning set. This is not OCID.
`drop_sqls_in_sql_tuning_set_details`	(required) Drops the selected list of Sql statements from the current Sql tuning set.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

FETCH_SQL_TUNING_SET Function

Fetch the details of Sql statements in the Sql tuning set specified by name, owner and optional filter parameters.

Syntax

FUNCTION fetch_sql_tuning_set (
  managed_database_id varchar2,
  sql_tuning_set_id number,
  fetch_sql_tuning_set_details dbms_cloud_oci_database_management_fetch_sql_tuning_set_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_fetch_sql_tuning_set_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_set_id`	(required) The unique identifier of the Sql tuning set. This is not OCID.
`fetch_sql_tuning_set_details`	(required) The details required to fetch the Sql tuning set details.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

GET_EXECUTION_PLAN_STATS_COMPARISION Function

Retrieves a comparison of the existing SQL execution plan and a new plan. A SQL tuning task may suggest a new execution plan for a SQL, and this API retrieves the comparison report of the statistics of the two plans.

Syntax

FUNCTION get_execution_plan_stats_comparision (
  managed_database_id varchar2,
  sql_tuning_advisor_task_id number,
  sql_object_id number,
  execution_id number,
  opc_request_id varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_get_execution_plan_stats_comparision_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_advisor_task_id`	(required) The SQL tuning task identifier. This is not the OCID.
`sql_object_id`	(required) The SQL object ID for the SQL tuning task. This is not the OCID.
`execution_id`	(required) The execution ID for an execution of a SQL tuning task. This is not the OCID.
`opc_request_id`	(optional) The client request ID for tracing.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

GET_SQL_EXECUTION_PLAN Function

Retrieves a SQL execution plan for the SQL being tuned.

Syntax

FUNCTION get_sql_execution_plan (
  managed_database_id varchar2,
  sql_tuning_advisor_task_id number,
  sql_object_id number,
  attribute varchar2,
  opc_request_id varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_get_sql_execution_plan_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_advisor_task_id`	(required) The SQL tuning task identifier. This is not the OCID.
`sql_object_id`	(required) The SQL object ID for the SQL tuning task. This is not the OCID.
`attribute`	(required) The attribute of the SQL execution plan. Allowed values are: 'ORIGINAL', 'ORIGINAL_WITH_ADJUSTED_COST', 'USING_SQL_PROFILE', 'USING_NEW_INDICES', 'USING_PARALLEL_EXECUTION'
`opc_request_id`	(optional) The client request ID for tracing.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

GET_SQL_TUNING_ADVISOR_TASK_SUMMARY_REPORT Function

Gets the summary report for the specified SQL Tuning Advisor task.

Syntax

FUNCTION get_sql_tuning_advisor_task_summary_report (
  managed_database_id varchar2,
  sql_tuning_advisor_task_id number,
  search_period varchar2 DEFAULT NULL,
  time_greater_than_or_equal_to timestamp with time zone DEFAULT NULL,
  time_less_than_or_equal_to timestamp with time zone DEFAULT NULL,
  begin_exec_id_greater_than_or_equal_to number DEFAULT NULL,
  end_exec_id_less_than_or_equal_to number DEFAULT NULL,
  opc_request_id varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_get_sql_tuning_advisor_task_summary_report_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_advisor_task_id`	(required) The SQL tuning task identifier. This is not the OCID.
`search_period`	(optional) How far back the API will search for begin and end exec id. Unused if neither exec ids nor time filter query params are supplied. This is applicable only for Auto SQL Tuning tasks. Allowed values are: 'LAST_24HR', 'LAST_7DAY', 'LAST_31DAY', 'SINCE_LAST', 'ALL'
`time_greater_than_or_equal_to`	(optional) The optional greater than or equal to query parameter to filter the timestamp. This is applicable only for Auto SQL Tuning tasks.
`time_less_than_or_equal_to`	(optional) The optional less than or equal to query parameter to filter the timestamp. This is applicable only for Auto SQL Tuning tasks.
`begin_exec_id_greater_than_or_equal_to`	(optional) The optional greater than or equal to filter on the execution ID related to a specific SQL Tuning Advisor task. This is applicable only for Auto SQL Tuning tasks.
`end_exec_id_less_than_or_equal_to`	(optional) The optional less than or equal to query parameter to filter on the execution ID related to a specific SQL Tuning Advisor task. This is applicable only for Auto SQL Tuning tasks.
`opc_request_id`	(optional) The client request ID for tracing.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

LIST_SQL_TUNING_ADVISOR_TASK_FINDINGS Function

Gets an array of the details of the findings that match specific filters.

Syntax

FUNCTION list_sql_tuning_advisor_task_findings (
  managed_database_id varchar2,
  sql_tuning_advisor_task_id number,
  begin_exec_id number DEFAULT NULL,
  end_exec_id number DEFAULT NULL,
  search_period varchar2 DEFAULT NULL,
  finding_filter varchar2 DEFAULT NULL,
  stats_hash_filter varchar2 DEFAULT NULL,
  index_hash_filter varchar2 DEFAULT NULL,
  sort_by varchar2 DEFAULT NULL,
  sort_order varchar2 DEFAULT NULL,
  page varchar2 DEFAULT NULL,
  limit number DEFAULT NULL,
  opc_request_id varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_list_sql_tuning_advisor_task_findings_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_advisor_task_id`	(required) The SQL tuning task identifier. This is not the OCID.
`begin_exec_id`	(optional) The optional greater than or equal to filter on the execution ID related to a specific SQL Tuning Advisor task.
`end_exec_id`	(optional) The optional less than or equal to query parameter to filter on the execution ID related to a specific SQL Tuning Advisor task.
`search_period`	(optional) The search period during which the API will search for begin and end exec id, if not supplied. Unused if beginExecId and endExecId optional query params are both supplied. Allowed values are: 'LAST_24HR', 'LAST_7DAY', 'LAST_31DAY', 'SINCE_LAST', 'ALL'
`finding_filter`	(optional) The filter used to display specific findings in the report. Allowed values are: 'none', 'FINDINGS', 'NOFINDINGS', 'ERRORS', 'PROFILES', 'INDICES', 'STATS', 'RESTRUCTURE', 'ALTERNATIVE', 'AUTO_PROFILES', 'OTHER_PROFILES'
`stats_hash_filter`	(optional) The hash value of the object for the statistic finding search.
`index_hash_filter`	(optional) The hash value of the index table name.
`sort_by`	(optional) The possible sortBy values of an object's recommendations. Allowed values are: 'DBTIME_BENEFIT', 'PARSING_SCHEMA', 'SQL_ID', 'STATS', 'PROFILES', 'SQL_BENEFIT', 'DATE', 'INDICES', 'RESTRUCTURE', 'ALTERNATIVE', 'MISC', 'ERROR', 'TIMEOUTS'
`sort_order`	(optional) The option to sort information in ascending (‘ASC’) or descending (‘DESC’) order. Descending order is the default order. Allowed values are: 'ASC', 'DESC'
`page`	(optional) The page token representing the page from where the next set of paginated results are retrieved. This is usually retrieved from a previous list call.
`limit`	(optional) The maximum number of records returned in the paginated response.
`opc_request_id`	(optional) The client request ID for tracing.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

LIST_SQL_TUNING_ADVISOR_TASK_RECOMMENDATIONS Function

Gets the findings and possible actions for a given object in a SQL tuning task. The task ID and object ID are used to retrieve the findings and recommendations.

Syntax

FUNCTION list_sql_tuning_advisor_task_recommendations (
  managed_database_id varchar2,
  sql_tuning_advisor_task_id number,
  sql_object_id number,
  execution_id number,
  sort_by varchar2 DEFAULT NULL,
  sort_order varchar2 DEFAULT NULL,
  page varchar2 DEFAULT NULL,
  limit number DEFAULT NULL,
  opc_request_id varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_list_sql_tuning_advisor_task_recommendations_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_advisor_task_id`	(required) The SQL tuning task identifier. This is not the OCID.
`sql_object_id`	(required) The SQL object ID for the SQL tuning task. This is not the OCID.
`execution_id`	(required) The execution ID for an execution of a SQL tuning task. This is not the OCID.
`sort_by`	(optional) The possible sortBy values of an object's recommendations. Allowed values are: 'RECOMMENDATION_TYPE', 'BENEFIT'
`sort_order`	(optional) The option to sort information in ascending (‘ASC’) or descending (‘DESC’) order. Descending order is the default order. Allowed values are: 'ASC', 'DESC'
`page`	(optional) The page token representing the page from where the next set of paginated results are retrieved. This is usually retrieved from a previous list call.
`limit`	(optional) The maximum number of records returned in the paginated response.
`opc_request_id`	(optional) The client request ID for tracing.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

LIST_SQL_TUNING_ADVISOR_TASKS Function

Lists the SQL Tuning Advisor tasks for the specified Managed Database.

Syntax

FUNCTION list_sql_tuning_advisor_tasks (
  managed_database_id varchar2,
  name varchar2 DEFAULT NULL,
  status varchar2 DEFAULT NULL,
  time_greater_than_or_equal_to timestamp with time zone DEFAULT NULL,
  time_less_than_or_equal_to timestamp with time zone DEFAULT NULL,
  page varchar2 DEFAULT NULL,
  limit number DEFAULT NULL,
  sort_by varchar2 DEFAULT NULL,
  sort_order varchar2 DEFAULT NULL,
  opc_request_id varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_list_sql_tuning_advisor_tasks_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`name`	(optional) The optional query parameter to filter the SQL Tuning Advisor task list by name.
`status`	(optional) The optional query parameter to filter the SQL Tuning Advisor task list by status. Allowed values are: 'INITIAL', 'EXECUTING', 'INTERRUPTED', 'COMPLETED', 'ERROR'
`time_greater_than_or_equal_to`	(optional) The optional greater than or equal to query parameter to filter the timestamp.
`time_less_than_or_equal_to`	(optional) The optional less than or equal to query parameter to filter the timestamp.
`page`	(optional) The page token representing the page from where the next set of paginated results are retrieved. This is usually retrieved from a previous list call.
`limit`	(optional) The maximum number of records returned in the paginated response.
`sort_by`	(optional) The option to sort the SQL Tuning Advisor task summary data. Allowed values are: 'NAME', 'START_TIME'
`sort_order`	(optional) The option to sort information in ascending (‘ASC’) or descending (‘DESC’) order. Descending order is the default order. Allowed values are: 'ASC', 'DESC'
`opc_request_id`	(optional) The client request ID for tracing.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

LIST_SQL_TUNING_SETS Function

Lists the SQL tuning sets for the specified Managed Database.

Syntax

FUNCTION list_sql_tuning_sets (
  managed_database_id varchar2,
  owner varchar2 DEFAULT NULL,
  name_contains varchar2 DEFAULT NULL,
  sort_by varchar2 DEFAULT NULL,
  sort_order varchar2 DEFAULT NULL,
  page varchar2 DEFAULT NULL,
  limit number DEFAULT NULL,
  opc_request_id varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_list_sql_tuning_sets_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`owner`	(optional) The owner of the SQL tuning set.
`name_contains`	(optional) Allow searching the name of the SQL tuning set by partial matching. The search is case insensitive.
`sort_by`	(optional) The option to sort the SQL tuning set summary data. Allowed values are: 'NAME'
`sort_order`	(optional) The option to sort information in ascending (‘ASC’) or descending (‘DESC’) order. Ascending order is the default order. Allowed values are: 'ASC', 'DESC'
`page`	(optional) The page token representing the page from where the next set of paginated results are retrieved. This is usually retrieved from a previous list call.
`limit`	(optional) The maximum number of records returned in the paginated response.
`opc_request_id`	(optional) The client request ID for tracing.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

LOAD_SQL_TUNING_SET Function

Load Sql statements into the Sql tuning set specified by name and optional filter parameters within the Managed Database specified by managedDatabaseId.

Syntax

FUNCTION load_sql_tuning_set (
  managed_database_id varchar2,
  sql_tuning_set_id number,
  load_sql_tuning_set_details dbms_cloud_oci_database_management_load_sql_tuning_set_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_load_sql_tuning_set_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_set_id`	(required) The unique identifier of the Sql tuning set. This is not OCID.
`load_sql_tuning_set_details`	(required) The details required to load Sql statements into the Sql tuning set.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

SAVE_SQL_TUNING_SET_AS Function

Saves the specified list of Sqls statements into another new Sql tuning set or loads into an existing Sql tuning set'.

Syntax

FUNCTION save_sql_tuning_set_as (
  managed_database_id varchar2,
  sql_tuning_set_id number,
  save_sql_tuning_set_as_details dbms_cloud_oci_database_management_save_sql_tuning_set_as_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_save_sql_tuning_set_as_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_set_id`	(required) The unique identifier of the Sql tuning set. This is not OCID.
`save_sql_tuning_set_as_details`	(required) The details required to save a Sql tuning set into another Sql tuning set.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

START_SQL_TUNING_TASK Function

Starts a SQL tuning task for a given set of SQL statements from the active session history top SQL statements.

Syntax

FUNCTION start_sql_tuning_task (
  managed_database_id varchar2,
  start_sql_tuning_task_details dbms_cloud_oci_database_management_start_sql_tuning_task_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_start_sql_tuning_task_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`start_sql_tuning_task_details`	(required) The detailed inputs required to start a SQL tuning task.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

VALIDATE_BASIC_FILTER Function

Executes a SQL query to check whether user entered basic filter criteria is valid or not.

Syntax

FUNCTION validate_basic_filter (
  managed_database_id varchar2,
  sql_tuning_set_id number,
  validate_basic_filter_details dbms_cloud_oci_database_management_validate_basic_filter_details_t,
  opc_request_id varchar2 DEFAULT NULL,
  opc_retry_token varchar2 DEFAULT NULL,
  region varchar2 DEFAULT NULL, 
  endpoint varchar2 DEFAULT NULL,
  credential_name varchar2 DEFAULT NULL
) RETURN dbms_cloud_oci_dm_sql_tuning_validate_basic_filter_response_t;

Parameters

Parameter	Description
`managed_database_id`	(required) The OCID of the Managed Database.
`sql_tuning_set_id`	(required) The unique identifier of the Sql tuning set. This is not OCID.
`validate_basic_filter_details`	(required) Validate the basic filter criteria provided by the user.
`opc_request_id`	(optional) The client request ID for tracing.
`opc_retry_token`	(optional) A token that uniquely identifies a request so it can be retried in case of a timeout or server error without risk of executing that same action again. Retry tokens expire after 24 hours, but can be invalidated before then due to conflicting operations. For example, if a resource has been deleted and purged from the system, then a retry of the original creation request might be rejected.
`region`	(optional) OCI region id. e.g us-phoenix-1 for US West (Phoenix).
`endpoint`	(optional) The endpoint of the service to call using this function. e.g https://dbmgmt.{region}.oci.{secondLevelDomain}.If both endpoint and region are given, then endpoint takes precedence.
`credential_name`	(optional) The name of the credential for authenticating with the corresponding cloud native API.

Oracle Cloud Infrastructure Documentation

CLONE_SQL_TUNING_TASK Function

CREATE_SQL_TUNING_SET Function

DROP_SQL_TUNING_SET Function

DROP_SQL_TUNING_TASK Function

DROP_SQLS_IN_SQL_TUNING_SET Function

FETCH_SQL_TUNING_SET Function

GET_EXECUTION_PLAN_STATS_COMPARISION Function

GET_SQL_EXECUTION_PLAN Function

GET_SQL_TUNING_ADVISOR_TASK_SUMMARY_REPORT Function

LIST_SQL_TUNING_ADVISOR_TASK_FINDINGS Function

LIST_SQL_TUNING_ADVISOR_TASK_RECOMMENDATIONS Function

LIST_SQL_TUNING_ADVISOR_TASKS Function

LIST_SQL_TUNING_SETS Function

LOAD_SQL_TUNING_SET Function

SAVE_SQL_TUNING_SET_AS Function

START_SQL_TUNING_TASK Function

VALIDATE_BASIC_FILTER Function