sql_id

Unique SQL ID.

forcing_matching_signature

Signature with literals, case, and whitespace removed.

sql_text

Full text for the SQL statement.

object_list

Currently not implemented.

bind_data

Bind data as captured for this SQL. Note that you cannot stipulate an argument for this parameter and also for bind_list - they are mutually exclusive.

parsing_schema_name

Schema where the SQL is parsed.

module

Last application module for the SQL.

action

Last application action for the SQL.

elapsed_time

Sum total elapsed time for this SQL statement.

cpu_time

Sum total CPU time for this SQL statement.

buffer_gets

Sum total number of buffer gets.

disk_reads

Sum total number of disk reads.

direct_writes

Sum total number of direct path writes.

rows_processed

Sum total number of rows processed by this SQL.

fetches

Sum total number of fetches.

executions

Total executions of this SQL statement.

end_of_fetch_count

Number of times the SQL statement was fully executed with all of its rows fetched.

optimizer_cost

Optimizer cost for this SQL.

optimizer_env

Optimizer environment for this SQL statement.

priority

User-defined priority (1,2,3).

command_type

Statement type, such as INSERT or SELECT.

first_load_time

Load time of the parent cursor.

stat_period

Period of time (seconds) when the statistics of this SQL statement were collected.

active_stat_period

Effective period of time (in seconds) during which the SQL statement was active.

other

Other column for user-defined attributes.

plan_hash_value

Plan hash value of the plan.

sql_plan

Execution plan for the SQL statement.

bind_list

List of user-specified binds for the SQL statement. This is used for user-specified workloads. Note that you cannot stipulate an argument for this parameter and also for bind_data: they are mutually exclusive.

con_dbid

DBID of the PDB or CDB root.

last_exec_start_time

Most recent execution start time of this SQL statement.

task_name

The (mandatory) name of the SQL tuning task

category

This is the category name which must match the value of the SQLTUNE_CATEGORY parameter in a session for the session to use this SQL profile. It defaults to the value "DEFAULT". This is also the default of the SQLTUNE_CATEGORY parameter. The category must be a valid Oracle identifier. The category name specified is always converted to upper case. The combination of the normalized SQL text and category name creates a unique key for a SQL profile. An ACCEPT_SQL_PROFILE fails if this combination is duplicated.

replace

If the profile already exists, it is replaced if this argument is TRUE. It is an error to pass a name that is already being used for another signature/category pair, even with replace set to TRUE.

force_match

If TRUE this causes SQL profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.) This is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter.

If FALSE, literals are not transformed. This is analogous to the matching algorithm used by the EXACT option of the cursor_sharing parameter.

profile_type

Options:

• REGULAR_PROFILE - profile without a change to parallel execution (Default, equivalent to NULL). Note that if the SQL statement currently has a parallel execution plan, the regular profile will cause the optimizer to choose a different, but still parallel, execution plan.

• PX_PROFILE - regular profile with a change to parallel execution

autotune_period

The time period for the automatic SQL tuning. This setting applies only to the automatic SQL Tuning Advisor task. Possible values are as follows:

• null or negative value (default) - all or full. The result includes all task executions.

• 0 - result of the current or most recent task execution.

• 1 - result for the most recent 24-hour period.

• 7 - result for the most recent 7-day period.

The procedure interprets any other value as the time of the most recent task execution minus the value of this argument.

execution_name

Name of the task execution to use. If null, then the procedure generates the report for the most recent task execution.

task_owner

Owner of the tuning task. This is an optional parameter that must be specified to accept a SQL profile associated to a tuning task owned by another user. The current user is the default value.

description

A user specified string describing the purpose of the SQL profile. The description is truncated if longer than 256 characters. The maximum size is 500 characters.

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

task_name

The (mandatory) name of the SQL tuning task

object_id

The identifier of the advisor framework object representing the SQL statement associated with the tuning task

name

The name of the SQL profile. It cannot contain double quotation marks. The name is case sensitive. If not specified, the system generates a unique name for the SQL profile.

description

A user specified string describing the purpose of the SQL profile. The description is truncated if longer than 256 characters. The maximum size is 500 characters.

category

The category name. This name must match the value of the SQLTUNE_CATEGORY parameter in a session for the session to use this SQL profile. It defaults to the value "DEFAULT". This is also the default of the SQLTUNE_CATEGORY parameter. The category must be a valid Oracle identifier. The category name specified is always converted to upper case. The combination of the normalized SQL text and category name creates a unique key for a SQL profile. An ACCEPT_SQL_PROFILE fails if this combination is duplicated.

task_owner

Owner of the tuning task. This is an optional parameter that has to be specified to accept a SQL profile associated to a tuning task owned by another user. The current user is the default value.

replace

If the profile already exists, it is replaced if this argument is TRUE. It is an error to pass a name that is already being used for another signature/category pair, even with replace set to TRUE.

force_match

If TRUE this causes SQL profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.) This is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter.

If FALSE, literals are not transformed. This is analogous to the matching algorithm used by the EXACT option of the cursor_sharing parameter.

profile_type

Options:

• REGULAR_PROFILE - profile without a change to parallel execution (Default, equivalent to NULL). Note that if the SQL statement currently has a parallel execution plan, the regular profile will cause the optimizer to choose a different, but still parallel, execution plan.

• PX_PROFILE - regular profile with a change to parallel execution

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

sqlset_name

Specifies the name of the SQL tuning set.

description

Provides an optional description of the usage of SQL tuning set.

The description is truncated if longer than 256 characters.

sqlset_owner

Specifies the owner of the SQL tuning set, or NULL for the current schema owner.

name

The (mandatory) name of the existing SQL profile to alter

attribute_name

The (mandatory) attribute name to alter (case insensitive) using valid attribute names

value

The (mandatory) new value of the attribute using valid attribute values

task_name

Specifies the name of the task to cancel

sqlset_name

Specifies the SQL tuning set name

time_limit

Defines the total amount of time, in seconds, to execute.

repeat_interval

Defines the amount of time, in seconds, to pause between sampling.

capture_option

Specifies whether to insert new statements, update existing statements, or both.

Values are INSERT, UPDATE, or MERGE. The values are the same as for load_option in load_sqlset.

capture_mode

Specifies the capture mode (UPDATE and MERGE capture options). Possible values:

• MODE_REPLACE_OLD_STATS — Replaces statistics when the number of executions is greater than the number stored in the SQL tuning set

• MODE_ACCUMULATE_STATS — Adds new values to current values for SQL that is already stored. Note that this mode detects if a statement has been aged out, so the final value for a statistics is the sum of the statistics of all cursors that statement existed under.

basic_filter

Defines a filter to apply to the shared SQL area for each sample.

If basic_filter is not set by the caller, then the subprogram captures only statements of type CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and MERGE.

sqlset_owner

Specifies the owner of the SQL tuning set or NULL for current schema owner

recursive_sql

Defines a filter that includes recursive SQL in the SQL tuning set (HAS_RECURSIVE_SQL) or excludes it (NO_RECURSIVE_SQL).

task_name

Name of the task for which to get a script

object_id

Object ID to which the SQL corresponds

plan_hash_value

Plan to create plan baseline

owner_name

Owner of the relevant tuning task. Defaults to the current schema owner.

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

sqlset_name

Specifies the name of the created SQL tuning set. The name is the name passed to the function. If no name is passed to the function, then the function generates an automatic name.

description

Provides an optional description of the SQL tuning set.

sqlset_owner

Specifies the owner of the SQL tuning set, or NULL for the current schema owner.

table_name

The name of the table to create (case-insensitive unless double quoted).

schema_name

The schema to create the table in, or NULL for the current schema (case-insensitive unless double quoted).

tablespace_name

The tablespace to store the staging table within, or NULL for the default tablespace of the current user (case-insensitive unless double quoted).

table_name

Specifies the of the table to create. The name is case sensitive.

schema_name

Defines the schema in which to create the table, or NULL for the current schema. The name is case sensitive.

tablespace_name

Specifies the tablespace in which to store the staging table, or NULL for the default tablespace of the current user. The name is case sensitive.

db_version

Specifies the database version that determines the format of the staging table.

You can also create an older database version staging table to export an STS to an older database version. Use one of the following values:

• NULL (default) — Specifies the current database version.

• STS_STGTAB_10_2_VERSION — Specifies the 10.2 database version.

• STS_STGTAB_11_1_VERSION — Specifies the 11.1 database version.

• STS_STGTAB_11_2_VERSION — Specifies the 11.2 database version.

• STS_STGTAB_12_1_VERSION — Specifies the 12.1 database version.

• STS_STGTAB_12_2_VERSION — Specifies the 12.2 database version.

sql_text

Specifies the text of a SQL statement.

begin_snap

Specifies the begin snapshot identifier.

end_snap

Specifies the end snapshot identifier.

sql_id

Specifies the identifier of a SQL statement.

bind_list

Defines an ordered list of bind values in ANYDATA type.

NOTE: This parameter is not supported on a standby database.

plan_hash_value

Specifies the hash value of the SQL execution plan.

sqlset_name

Specifies the SQL tuning set name.

basic_filter

Specifies the predicate used to filter the SQL from the SQL tuning set.

object_filter

Specifies the object filter.

rank(i)

Specifies an ORDER BY clause on the selected SQL statement.

result_percentage

Specifies the percentage on the sum of a ranking measure.

result_limit

Specifies the top L(imit) SQL from the filtered or ranked SQL.

user_name

Specifies the user name for whom the statement is to be tuned.

scope

Specifies the tuning scope:

• LIMITED: SQL Tuning Advisor produces recommendations based on statistical checks, access path analysis, and SQL structure analysis. SQL profile recommendations are not generated.

• COMPREHENSIVE: SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL profiling.

time_limit

Specifies the maximum duration in seconds for the tuning session.

task_name

Specifies an optional tuning task name.

description

Provides a description of the SQL tuning session, up to a maximum of 256 characters.

plan_filter

Specifies the plan filter. It is applicable when multiple plans (plan_hash_value) are associated with the same statement. This filter allows for selecting one plan (plan_hash_value) only. Possible values are:

• LAST_GENERATED: most recent timestamp

• FIRST_GENERATED: earliest timestamp, the opposite to LAST_GENERATED

• LAST_LOADED: most recent first_load_time statistics information

• FIRST_LOADED: earliest first_load_time statistics information, the opposite to LAST_LOADED

• MAX_ELAPSED_TIME: maximum elapsed time

• MAX_BUFFER_GETS: maximum buffer gets

• MAX_DISK_READS: maximum disk reads

• MAX_DIRECT_WRITES: maximum direct writes

• MAX_OPTIMIZER_COST: maximum optimizer cost

sqlset_owner

Specifies the owner of the SQL tuning set, or NULL for the current schema owner.

spa_task_name

Specifies the name of the SQL Performance Analyzer task whose regressions are to be tuned.

spa_task_owner

Specifies the owner of specified SQL Performance Analyzer task or NULL for current user.

spa_compare_exec

Specifies the execution name of the Compare Performance trial of SQL Performance Analyzer task. If NULL, then the advisor uses the most recent execution of the given SQL Performance Analyzer task, of type COMPARE PERFORMANCE.

Specifies the DBID for imported or PDB-level AWR data. If NULL, then the current database DBID is used.

con_name

Specifies the container for the tuning task. The semantics depend on the function format:

• For the SQL text format, this parameter specifies the container in which SQL Tuning Advisor tunes the SQL statement. If null (default), then SQL Tuning Advisor uses the current container.

• For the SQL ID format, this parameter specifies the container from which the database fetches the SQL statement for tuning. SQL Tuning Advisor tunes the statement in this container. If null, then the database uses the current PDB for tuning, fetches the statement from the cursor cache of all valid containers executing the SQL statement, and tunes the most expensive statement in its container.

• For the AWR format, this parameter specifies the container from whose AWR data the database fetches the SQL statement for tuning. SQL Tuning Advisor tunes the statement in this container. If null, then the database uses the current PDB for tuning, fetches the statement from the AWR of all valid containers that have this SQL statement, and tunes the most expensive statement in its container.

The following statements are true of all function formats:

• In a non-CDB, this parameter is ignored.

• In a PDB, this parameter must be null or match the container name of the PDB. Otherwise, an error occurs.

• In a CDB root, this parameter must be null or match the container name of a container in this CDB. Otherwise, an error occurs.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

sqlset_name

Specifies the name of the SQL tuning set.

basic_filter

Specifies the SQL predicate to filter the SQL from the SQL tuning set. This basic filter is used as a where clause on the SQL tuning set content to select a desired subset of SQL from the SQL tuning set.

sqlset_owner

Specifies the owner of the SQL tuning set, or NULL for current schema owner.

name

The (mandatory) name of SQL profile to be dropped. The name is case sensitive.

ignore

Ignores errors due to object not existing

sqlset_name

Specifies the name of the SQL tuning set.

sqlset_owner

Specifies the owner of the SQL tuning set, or NULL for current schema owner.

task_name

Specifies name of the tuning task to drop.

task_name

Name of the tuning task to execute.

execution_name

A name to qualify and identify an execution. If not specified, it is generated by the advisor and returned by function.

execution_params

List of parameters (name, value) for the specified execution. The execution parameters have effect only on the execution for which they are specified. They override the values for the parameters stored in the task (set through the SET_TUNING_TASK_PARAMETER Procedures).

execution_desc

A 256-length string describing the execution.

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

task_name

Name of the tuning task for which to implement recommendations.

rec_type

Filter the types of recommendations to implement. Only 'PROFILES' is supported.

owner_name

Owner of the relevant tuning task or NULL for the current user.

execution_name

Name of the task execution to use. If NULL, then the procedure implements recommendations from the last task execution.

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

task_name

Name of the tuning task to interrupt

sqlset_name

Specifies the name of SQL tuning set to be loaded.

populate_cursor

Specifies the cursor reference to the SQL tuning set to be loaded.

load_option

Specifies which statements are loaded into the SQL tuning set. The possible values are:

• INSERT (default) — Adds only new statements.

• UPDATE — Updates existing the SQL statements and ignores any new statements.

• MERGE — Inserts new statements and updates the information of the existing ones.

update_option

Specifies how existing SQL statements are updated.

This parameter is considered only if load_option is specified with UPDATE or MERGE as an option. The possible values are:

• REPLACE (default) — Updates the statement using the new statistics, bind list, object list, and so on.

• ACCUMULATE — Combines attributes when possible (for example, statistics such as elapsed_time), and otherwise replaces the existing values (for example, module and action) with the provided values. The SQL statement attributes that can be accumulated are: elapsed_time, buffer_gets, direct_writes, disk_reads, row_processed, fetches, executions, end_of_fetch_count, stat_period and active_stat_period.

update_condition

Specifies when to perform the update.

The procedure only performs the update when the specified condition is satisfied. The condition can refer to either the data source or destination. The condition must use the following prefixes to refer to attributes from the source or the destination:

• OLD — Refers to statement attributes from the SQL tuning set (destination).

• NEW — Refers to statement attributes from the input statements (source).

update_attributes

Specifies the list of SQL statement attributes to update during a merge or update.

The possible values are:

• NULL (default) — Specifies the content of the input cursor except the execution context. On other terms, it is equivalent to ALL without execution contexts such as module and action.

• BASIC — Specifies statistics and binds only.

• TYPICAL — Specifies BASIC with SQL plans (without row source statistics) and without an object reference list.

• ALL — Specifies all attributes, including the execution context attributes such as module and action.

• List of comma separated attribute names to update:

  • EXECUTION_CONTEXT

  • EXECUTION_STATISTICS

  • SQL_BINDS

  • SQL_PLAN

  • SQL_PLAN_STATISTICS (similar to SQL_PLAN with added row source statistics)

ignore_null

Specifies whether to update attributes when the new value is NULL.

If TRUE, then the procedure does not update an attribute when the new value is NULL. That is, do not override with NULL values unless intentional.

commit_rows

Specifies whether to commit statements after DML.

If a value is provided, then the load commits after each specified number of statements is inserted. If NULL is provided, then the load commits only once, at the end of the operation.

Providing a value for this argument enables you to monitor the progress of a SQL tuning set load operation in the DBA_SQLSET views. The STATEMENT_COUNT value increases as new SQL statements are loaded.

sqlset_owner

Defines the owner of the SQL tuning set, or the current schema owner (or NULL for the current owner).

profile_name

The name of the profile to pack (% wildcards acceptable, case-sensitive)

profile_category

The category to pack profiles from (% wildcards acceptable, case-sensitive)

staging_table_name

The name of the table to use (case-insensitive unless double quoted). Required.

staging_schema_owner

The schema where the table resides, or NULL for current schema (case-insensitive unless double quoted)

old_profile_name

The name of the profile to target for a remap operation (case-sensitive)

new_profile_name

The new name of the profile, or NULL to remain the same (case-sensitive)

new_profile_category

The new category for the profile, or NULL to remain the same (case-sensitive)

staging_table_name

The name of the table on which to perform the remap operation (case-sensitive). Required.

staging_schema_owner

The schema where the table resides, or NULL for current schema (case-sensitive)

old_sqlset_name

Specifies the name of the tuning set to target for a remap operation. Wildcard characters (%) are not supported.

old_sqlset_owner

Specifies the new name of the tuning set owner to target for a remap operation. NULL for current schema owner

new_sqlset_name

Specifies the new name for the tuning set, or NULL to keep the same tuning set name.

new_sqlset_owner

Specifies the new owner for the tuning set, or NULL to keep the same owner name.

staging_table_name

Specifies the name of the table on which to perform the remap operation. The value is case sensitive.

staging_schema_owner

Specifies the name of staging table owner, or NULL for the current schema owner. The value is case sensitive.

old_con_dbid

Specifies the old container DBID to be remapped to a new container DBID.

Specify NULL to use the same container DBID. You must provide both old_con_dbid and new_con_dbid for the remap to succeed.

new_con_dbid

Specifies the new container DBID to replace with the old container DBID.

Specify NULL to use the same container DBID. You must provide both old_con_dbid and new_con_dbid for the remap to succeed.

sqlset_name

Specifies the name of the SQL tuning set.

reference_id

Specifies the identifier of the reference to remove.

sqlset_owner

Specifies the owner of the SQL tuning set (or NULL for the current schema owner).

force_remove

Specifies whether references can be removed for other users (1) or whether they cannot be removed (0).

Setting this parameter to 1 only takes effect when the user has the ADMINISTER ANY SQL TUNING SET privilege. Otherwise, the database only removes references owned by the user.

begin_exec

Specifies the name of the execution from which to begin the report. NULL retrieves a report on the most recent run.

end_exec

Specifies the name of the execution at which to end the report. NULL retrieves a report on the most recent run.

type

Specifies the type of the report to produce. Possible values are TYPE_TEXT which produces a text report

level

Specifies the level of detail in the report:

• LEVEL_BASIC: simple version of the report. Just show info about the actions taken by the advisor.

• LEVEL_TYPICAL: show information about every statement analyzed, including requests not implemented.

• LEVEL_ALL: highly detailed report level, also provides annotations about statements skipped over.

section

Limits the report to a single section (ALL for all sections):

• SECTION_SUMMARY - summary information

• SECTION_FINDINGS - tuning findings

• SECTION_PLAN - explain plans

• SECTION_INFORMATION - general information

• SECTION_ERROR - statements with errors

• SECTION_ALL - all statements

object_id

Specifies the advisor framework object ID that represents a single statement to restrict reporting to. Specify NULL for all statements. Only valid for reports that target a single execution.

result_limit

Specifies the maximum number of SQL statements to show in the report.

sql_id

SQLID for which monitoring information should be displayed. If NULL (the default), display statistics for the SQLID of the last SQL statement executed in the current session.

sql_plan_hash_value

Displays SQL statistics and details for a specific plan_hash_value. If NULL (default), displays statistics and details for all plans of the SQL_ID.

start_time

If specified, shows SQL activity (from GV$ACTIVE_SESSION_HISTORY) starting at this time. On Oracle RAC, the minimum start_time is the earliest sample_time of the in-memory ASH buffers across all instances. If NULL (default), one hour before the current time.

duration

Duration of activity in seconds for the report. If NULL (default) uses a value of 1 hour.

inst_id

Target instance to get SQL details from. If NULL, uses data from all instances. If 0 or -1, uses current instance.

dbid

DBID from which to get SQL details. If NULL, uses current DBID.

event_detail

When set to 'NO', the activity is aggregated by wait_class only. Use 'YES' (the default) to aggregate by (wait_class, event_name).

bucket_max_count

If specified, this should be the maximum number of histogram buckets created in the report. If not specified, a value of 128 is used.

bucket_interval

If specified, this represents the exact time interval in seconds, of all histogram buckets. If specified, bucket_max_count is ignored.

top_n

Controls the number of entries to display per dimension in the top dimensions section. If not specified, a default value of 10 is used.

report_level

Level of detail for the report, either 'BASIC', 'TYPICAL' or 'ALL'. Default assumes 'TYPICAL'. Their meanings are explained below.

In addition, individual report sections can also be enabled or disabled by using a +/- section_name. Several sections are defined:

• 'TOP'- Show top values for the ASH dimensions for a SQL statement; ON by default

• 'SPM'- Show existing plan baselines for a SQL statement; OFF by default

• 'MISMATCH'- Show reasons for creating new child cursors (sharing criteria violations); OFF by default.

• 'STATS'- Show SQL execution statistics per plan from GV$SQLAREA_PLAN_HASH; ON by default

• 'ACTIVITY' - Show top activity from ASH for each plan of a SQL statement; ON by default

• 'ACTIVITY_ALL' - Show top activity from ASH for each line of the plan for a SQL statement; OFF by default

• 'HISTOGRAM' - Show activity histogram for each plan of a SQL statement (plan time line histogram); ON by default

• 'SESSIONS' - Show activity for top sessions for each plan of a SQL statement; OFF by default

• 'MONITOR' - Show show one monitored SQL execution per execution plan; ON by default

• 'XPLAN' - Show execution plans; ON by default

• 'BINDS' - show captured bind data; ON by default

In addition, SQL text can be specified at different levels:

• -SQL_TEXT - No SQL text in report

• +SQL_TEXT - OK with partial SQL text up to the first 2000 chars as stored in GV$SQL_MONITOR

• -SQL_FULLTEXT - No full SQL text (+SQL_TEXT)

• +SQL_FULLTEXT - Show full SQL text (default value)

The meanings of the three top-level report levels are:

• NONE - minimum possible

• BASIC - SQL_TEXT+STATS+ACTIVITY+HISTOGRAM

• TYPICAL - SQL_FULLTEXT+TOP+STATS+ACTIVITY+HISTOGRAM+XPLAN+MONITOR

• ALL - everything

Only one of these 4 levels can be specified and, if it is, it has to be at the start of the REPORT_LEVEL string

type

Report format: 'ACTIVE' by default. Can also be 'XML' (see Usage Notes).

data_source

Determines the data source of SQL data based on one of the following values:

• MEMORY: The data source is GV$ view

• DISK: The data source is DBA_HIST_* view

• AUTO: Automatically determines the data source based on the time frame (default)

end_time

If specified, shows SQL activity from start_time to end_time. If NULL (default), shows SQL activity for systimestamp.

duration_stats

Duration of additional SQL execution statistics from AWR (in hours), for the report. If NULL (default), then the duration of 24 hours is considered.

con_name

Name of the multitenant container database (CDB).

sql_id

SQL_ID for which monitoring information should be displayed. Use NULL (the default) to report on the last statement monitored by Oracle.

dbop_name

DBOP_NAME for which monitoring information of the composite database operation is displayed.

dbop_exec_id

Execution ID for the composite database operation for which monitoring information is displayed.

session_id

If not NULL, this parameters targets only the sub-set of statements executed by the specified session. Default is NULL. Use USERENV('SID') for current session.

session_serial

In addition to the session_id parameter, one can also specify its session serial to ensure that the desired session incarnation is targeted. This parameter is ignored when session_id is NULL.

sql_exec_start

This parameter, along with sql_exec_id, is only applicable when sql_id is also specified. Jointly, they can be used to display monitoring information associated to any execution of the statement identified by sql_id, assuming that this statement was monitored. When NULL (the default), the last monitored execution of SQL sql_id is shown.

sql_exec_id

This parameter, along with sql_exec_start, is only applicable when sql_id is also specified. Jointly, they can be used to display monitoring information associated to any execution of the statement identified by sql_id, assuming that this statement was monitored. When NULL (the default), the last monitored execution of SQL sql_id is shown.

inst_id

Only considers statements started on the specified instance. Use -1 to target the login instance. NULL (default) targets all instances.

start_time_filter

If not NULL, the report considers only the activity (from GV$ACTIVE_SESSION_HISTORY) recorded after the specified date. If NULL, the reported activity starts when the execution of the targeted SQL statement has started.

end_time_filter

If not NULL, the report shows only the activity (from GV$ACTIVE_SESSION_HISTORY) collected before the date end_time_filter. If NULL, the reported activity ends when the targeted SQL statement execution has ended or is the current time if the statement is still executing.

instance_id_filter

Only applies when the execution runs parallel across multiple Oracle Real Application Cluster (Oracle RAC) instances. This parameter allows to only report the activity of the specified instance. Use a NULL value (the default) to include the activity on all instances where the parallel query was executed.

parallel_filter

Applies only to parallel execution and allows reporting the activity of only a subset of the processes involved in the parallel execution (Query Coordinator and/or Parallel eXecution servers). The value of this parameter can be:

• NULL to target all processes

• [qc][servers(<svr_grp>[,] <svr_set>[,] <srv_num>)]: 'qc' stands for query coordinator and servers() stipulate which PX servers to consider.

The following examples show how to target a subset of the parallel processes:

• qc: targets only the query coordinator

• servers(1): targets all parallel execution servers in group number 1. Note that statement running parallel have one main server group (group number 1) plus one additional group for each nested sub-query running parallel.

• servers(,2): targets all parallel execution servers from any group but only running in set 1 of each group (each group has at most two set of parallel execution servers)

• servers(1,1): consider only group 1, set 1

• servers(1,2,4): consider only group 1, set 2, server number 4. This reports for a single parallel server process

• qc servers(1,2,4): same as above by also including the query coordinator

event_detail

When value is 'YES' (the default), reported activity from GV$ACTIVE_SESSION_HISTORY is aggregated by (wait_class, event_name). Use 'NO' to only aggregate by wait_class.

bucket_max_count

If specified, this should be the maximum number of histogram buckets created in the report

bucket_interval

If specified, this represents the exact time interval in seconds, of all histogram buckets. If specified, bucket_max_count is ignored.

base_path

URL path for flex HTML resources since flex HTML format is required to access external files (java scripts and the flash SWF file itself)

last_refresh_time

If not NULL (default is NULL), the time when the report was last retrieved (see SYSDATE attribute of the report tag). Use this option to display the report of a running query, and when the report is refreshed on a regular basis. This optimizes the size of the report since only the new or changed information is returned. In particular, the following are optimized:

• SQL text is not returned when this option is specified

• activity histogram starts at the bucket that intersect at that time. The entire content of the bucket is returned, even if last_refresh_time is after the start of that bucket

report_level

Level of detail for the report:'NONE', 'BASIC', 'TYPICAL' or 'ALL'. Default assumes 'TYPICAL'.

In addition, individual report sections can also be enabled or disabled by using a +/- section_name. Several sections are defined:

• 'XPLAN'- Show explain plan; ON by default

• 'PLAN'- Show plan monitoring statistics; ON by default

• 'SESSIONS'- Show session details. Applies only to parallel queries; ON by default

• 'INSTANCE'- Show instance details. Applies only to parallel and cross instance; ON by default

• 'PARALLEL'- An umbrella parameter for specifying sessions+instance details

• 'ACTIVITY' - Show activity summary at global level, plan line level and session or instance level (if applicable); ON by default

• 'BINDS' - Show bind information when available; ON by default

• 'METRICS' - Show metric data (CPU, I/Os, ...) over time; ON by default

• 'ACTIVITY_HISTOGRAM' - Show an histogram of the overall query activity; ON by default

• 'PLAN_HISTOGRAM' - Show activity histogram at plan line level; OFF by default

• 'OTHER' - Other info; ON by default

In addition, SQL text can be specified at different levels:

• SQL_TEXT - No SQL text in report

• +SQL_TEXT - OK with partial SQL text up to the first 2000 chars as stored in GV$SQL_MONITOR

• -SQL_FULLTEXT - No full SQL text (+SQL_TEXT)

• +SQL_FULLTEXT - Show full SQL text (default value)

report_level (contd.)

The meanings of the three top-level report levels are:

• NONE - minimum possible

• +BASIC - SQL_TEXT-PLAN-XPLAN-SESSIONS-INSTANCE-ACTIVITY_HISTOGRAM-PLAN_HISTOGRAM-METRICS

• TYPICAL - everything but PLAN_HISTOGRAM

• ALL - everything

Only one of these 4 levels can be specified and, if it is, it has to be at the start of the REPORT_LEVEL string

type

Report format, 'TEXT' by default. Can be 'TEXT', 'HTML', 'XML' or 'ACTIVE' (see Usage Notes).

sql_plan_hash_value

Target only those SQL executions with the specified plan_hash_value. Default is NULL.

con_name

Name of the multitenant container database (CDB).

report_id

ID of the report in auto-report repository. Report IDs can be found in DBA_HIST_REPORTS.

sql_id

SQL_ID for which monitoring information should be displayed. Use NULL (the default) to report on the last statement monitored by Oracle.

session_id

If not NULL, then this parameter targets only the subset of statements executed by the specified session. Default is NULL. Use -1 or USERENV('SID') for current session.

session_serial

In addition to the session_id parameter, you can also specify its session serial to ensure that the desired session incarnation is targeted. This parameter is ignored when session_id is NULL.

inst_id

Only considers statements started on the specified instance. Use -1 to target the login instance. NULL (default) targets all instances.

active_since_date

If not NULL (default), returns only monitored statements active since the specified time. This includes all statements that are still executing along with all statements that have completed their execution after the specified date and time.

active_since_sec

Same as active_since_date but with the date specified relative to the current SYSDATE minus a specified number of seconds. For example, use 3600 to apply a limit of 1 hour.

active_before_date

If not NULL (default), returns only monitored statements that have been active before the specified date and time.

last_refresh_time

If not NULL (default), the date and time when the list report was last retrieved. This optimizes the case where an application shows the list and refreshes the report on a regular basis (such as once every 5 seconds). In this case, the report shows detail about the execution of monitored queries that active since the specified last_refresh_time. For other queries, the report returns the execution key (sql_id, sql_exec_start, sql_exec_id). For queries with a first refresh time after the specified date, the function returns only the SQL execution key and statistics.

dbop_name

DB operation name. Specify NULL to display all the monitored DB operations.

monitor_type

Type of the SQL Monitor operation. Specify one of the following values:

• MONITOR_TYPE_SQL - Returns only SQL statements

• MONITOR_TYPE_DBOP - Returns only database operations

• MONITOR_TYPE_ALL - Returns SQL statements as well as database operations

max_sqltext_length

Maximum length of the SQL text. Default is NULL (no limit).

top_n_count

Limits the number of top-N SQL statements that need to be included in the report.

top_n_rankby

Specifies the attribute to rank the SQL statements. Specify this value when top_n_count value is not NULL. The ranking of an SQL statement is done based on one of the following values:

• LAST_ACTIVE_TIME - Last active date and time (top N most recent)

• DURATION - Total duration of execution

• DB_TIME - DB time used

• CPU_TIME - CPU time used

• IO_REQUESTS - Number of I/O requests

• IO_BYTES - Number of I/O bytes

report_level

Level of detail for the report. The level is one of the following:

• BASIC - SQL text up to 200 characters

• TYPICAL - include full SQL text assuming that cursor has not aged out, in which case the SQL text is included up to 2000 characters

• ALL - currently the same as TYPICAL

auto_refresh

Currently non-operational, reserved for future use.

base_path

URL path for flex HTML resources because flex HTML format is required to access external files (java scripts and the flash SWF file itself).

type

Report format: TEXT (default), HTML, or XML.

con_name

Name of the multitenant container database (CDB)

top_n_detail_count

Limits the number of top-N SQL statements for which the SQL monitor details need to be included in the report.

task_name

Name of the tuning task.

type

Type of the report to produce. Possible values are TEXT which produces a text report.

level

Level of detail in the report:

• BASIC: simple version of the report. Just show info about the actions taken by the advisor.

• TYPICAL: show information about every statement analyzed, including requests not implemented.

• ALL: highly detailed report level, also provides annotations about statements skipped over.

section

Section of the report to include.

You can limit the report to any of the following single sections (ALL for all sections):

• SUMMARY - Summary information

• FINDINGS - Tuning findings

• PLAN - Explain plans

• INFORMATION - General information

• ERROR - Statements with errors

• ALL - All statements

object_id

Advisor framework object ID that represents a single statement to restrict reporting to. NULL for all statements. Only valid for reports that target a single execution.

result_limit

Maximum number of SQL statements to show in the report.

owner_name

Owner of the relevant tuning task. The default is the current schema owner.

execution_name

Name of the task execution to use. If NULL, then the function generates the report for the last task execution.

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

task_name

The name of the tuning task to reset

task_name

The name of the tuning task to resume.

basic_filter

A SQL predicate to filter the SQL from the SQL tuning set. Note that this filter is applied in conjunction with the parameter basic_filteri when calling CREATE_TUNING_TASK Functions.

begin_snap

The beginning snapshot identifier. The range is exclusive, which means that SQL statements in this snapshot ID are not included.

end_snap

The end snapshot identifier. The range is inclusive, which means that SQL statements in this snapshot ID are included.

sql_id

The SQL ID of the statement to be tuned.

plan_hash_value

The plan hash value of the statement to be tuned. For example, the tuning job fetches captured binds for this SQL plan.

start_date

The date on which the schedule becomes valid. If null, then SQL Tuning Advisor immediately executes the task.

scope

The scope of the tuning job: limited, or comprehensive.

time_limit

The maximum duration in seconds for the SQL tuning session.

task_name

Optional SQL tuning task name.

description

Description of the SQL tuning session. The description can contain a maximum of 256 characters.

con_name

The container from which SQL Tuning Advisor accesses the SQL statement information.

dbid

DBID for imported or PDB-level AWR data. If NULL, then the current database DBID is used.

task_name

Name of the tuning task for which to apply a script.

rec_type

Filter the script by types of recommendations to include. You can use any subset of the following values, separated by commas: 'ALL: ''PROFILES' ''STATISTICS' ''INDEXES'. For example, a script with profiles and statistics would use the filter 'PROFILES,STATISTICS'.

object_id

Optionally filters by a single object ID.

result_limit

Optionally shows commands for only top n SQL (ordered by object_id and ignored if an object_id is also specified).

owner_name

Owner of the relevant tuning task. Defaults to the current schema owner.

excution_name

Name of the task execution to use. If NULL, the script is generated for the last task execution.

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

basic_filter

Specifies the SQL predicate that filters the SQL from the shared SQL area defined on attributes of the SQLSET_ROW.

If basic_filter is not set by the caller, then the subprogram captures only statements of the type CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and MERGE.

object_filter

Currently not supported.

ranking_measure(n)

Defines an ORDER BY clause on the selected SQL.

result_percentage

Specifies a filter that picks the top n% according to the supplied ranking measure.

The value applies only if one ranking measure is supplied.

result_limit

Defines the top limit SQL from the filtered source ranked by the ranking measure.

attribute_list

Specifies the list of SQL statement attributes to return in the result.

Possible values are:

• TYPICAL — Specifies BASIC plus SQL plan (without row source statistics) and without object reference list (default).

• BASIC — Specifies all attributes (such as execution statistics and binds) except the plans. The execution context is always part of the result.

• ALL — Specifies all attributes.

• Comma-separated list of attribute names.

  This values returns only a subset of SQL attributes:

  • EXECUTION_STATISTICS

  • BIND_LIST

  • OBJECT_LIST

  • SQL_PLAN

  • SQL_PLAN_STATISTICS — Similar to SQL_PLAN plus row source statistics

recursive_sql

Specifies that the filter must include recursive SQL in the SQL tuning set (HAS_RECURSIVE_SQL, which is the default) or exclude it (NO_RECURSIVE_SQL).

directory

Defines the directory object containing the trace files. This field is mandatory.

file_name

Specifies all or part of the name of the trace files.

If NULL, then the function uses the current or most recent file in the specified location or path. '%' wildcards are supported for matching trace file names.

mapping_table_name

Specifies the mapping table name.

Note that the mapping table name is case insensitive. If the mapping table name is NULL, then the function uses the mappings in the current database.

mapping_table_owner

Specifies the mapping table owner.

If it is NULL, then the function uses the current user.

select_mode

Specifies the mode for selecting SQL from the trace.

Possible values are:

• SINGLE_EXECUTION — Returns one execution of a SQL. This is the default.

• ALL_EXECUTIONS — Returns all executions.

options

Specifies which types of SQL statements are returned.

• LIMITED_COMMAND_TYPE — Returns the SQL statements with the command types CREATE, INSERT, SELECT, UPDATE, DELETE, and MERGE. This value is the default.

• ALL_COMMAND_TYPE — Returns the SQL statements with all command types.

pattern_start

Specifies the delimiting pattern of the trace file sections to consider. CURRENTLY INOPERABLE.

pattern_end

Specifies the closing delimiting pattern of the trace file sections to process. CURRENTLY INOPERABLE.

result_limit

Specifies the top SQL from the filtered source. Default to MAXSB4 if NULL.

task_name

Specifies the name of the SQL Performance Analyzer task.

task_owner

Specifies the owner of the SQL Performance Analyzer task. If NULL, then assume the current user.

execution_name

Specifies the name of the SQL Performance Analyzer task execution (type COMPARE PERFORMANCE) from which the provided filters will be applied. If NULL, then assume the most recent COMPARE PERFORMANCE execution.

level_filter

Specifies which subset of SQL statements to include. Same format as DBMS_SQLPA.REPORT_ANALYSIS_TASK.LEVEL, with some possible strings removed.

• IMPROVED includes only improved SQL.

• REGRESSED includes only regressed SQL (default).

• CHANGED includes only SQL with changed performance.

• UNCHANGED includes only SQL with unchanged performance.

• CHANGED_PLANS includes only SQL with plan changes.

• UNCHANGED_PLANS includes only SQL with unchanged plans.

• ERRORS includes only SQL with errors only.

• MISSING_SQL includes only missing SQL statements (across STS).

• NEW_SQL includes only new SQL statements (across STS).

basic filter

Specifies the SQL predicate to filter the SQL in addition to the level filters.

object_filter

Currently not supported.

attribute_list

Defines the SQL statement attributes to return in the result.

Possible values are:

• TYPICAL — Returns BASIC plus the SQL plan (without row source statistics) and without an object reference list. This is the default.

• BASIC — Returns all attributes (such as execution statistics and binds) except the plans. The execution context is always part of the result.

• ALL — Returns all attributes.

• Comma-separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN_STATISTICS (similar to SQL_PLAN + row source statistics).

sqlset_name

Specifies the name of the SQL tuning set to query.

basic_filter

Specifies the SQL predicate to filter the SQL from the SQL tuning set defined on attributes of the SQLSET_ROW.

object_filter

Currently not supported.

ranking_measure(n)

Specifies an ORDER BY clause on the selected SQL.

result_percentage

Specifies a filter that picks the top n% according to the supplied ranking measure.

Note that this parameter applies only if one ranking measure is supplied.

result_limit

The top limit SQL from the filtered source, ranked by the ranking measure.

attribute_list

Defines the SQL statement attributes to return in the result.

The possible values are:

• BASIC — Returns all attributes (such as execution statistics and binds) except the plans. The execution context is included in the result.

• TYPICAL — Returns BASIC plus the SQL plan, but without row source statistics and without the object reference list. This is the default.

• ALL — Returns all attributes.

• Comma-separated list of attribute names. This value enables the function to return only a subset of SQL attributes:

  • EXECUTION_STATISTICS

  • SQL_BINDS

  • SQL_PLAN_STATISTICS (similar to SQL_PLAN plus row source statistics)

plan_filter

Specifies the plan filter.

This parameter enables you to select a single plan when a statement has multiple plans. Possible values are:

• LAST_GENERATED — Returns the plan with the most recent timestamp.

• FIRST_GENERATED — Returns the plan with the least recent timestamp.

• LAST_LOADED — Returns the plan with the most recent FIRST_LOAD_TIME statistical information.

• FIRST_LOADED — Returns the plan with the least recent FIRST_LOAD_TIME statistical information.

• MAX_ELAPSED TIME — Returns the plan with the maximum elapsed time.

• MAX_BUFFER_GETS — Returns the plan with the maximum buffer gets.

• MAX_DISK_READS — Returns the plan with the maximum disk reads.

• MAX_DIRECT_WRITES — Returns the plan with the maximum direct writes.

• MAX_OPTIMIZER_COST — Returns the plan with the maximum optimizer cost value.

sqlset_owner

Specifies the owner of the SQL tuning set, or NULL for the current schema owner.

recursive_sql

Specifies that the filter must include recursive SQL in the SQL tuning set (HAS_RECURSIVE_SQL, which is the default) or exclude it (NO_RECURSIVE_SQL).

begin_snap

Defines the beginning AWR snapshot (non-inclusive).

end_snap

Defines the ending AWR snapshot (inclusive).

baseline_name

Specifies the name of the AWR baseline period.

basic_filter

Specifies the SQL predicate to filter the SQL from the workload repository. The filter is defined on attributes of the SQLSET_ROW.

If basic_filter is not set by the caller, then the subprogram captures only statements of type CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and MERGE.

object_filter

Currently not supported.

ranking_measure(n)

Defines an ORDER BY clause on the selected SQL.

result_percentage

Specifies a filter that picks the top n% according to the supplied ranking measure. Note that this percentage applies only if one ranking measure is given.

result_limit

Specifies the top limit SQL from the source according to the supplied ranking measure.

attribute_list

Specifies the SQL statement attributes to return in the result. The possible values are:

• TYPICAL — Returns BASIC plus SQL plan (without row source statistics) and without object reference list. This is the default.

• BASIC — Returns all attributes (such as execution statistics and binds) are returned except the plans. The execution context is always part of the result.

• ALL — Returns all attributes

• Comma-separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN_STATISTICS (similar to SQL_PLAN plus row source statistics).

recursive_sql

Specifies the filter that includes recursive SQL in the SQL tuning set (HAS_RECURSIVE_SQL) or excludes it (NO_RECURSIVE_SQL).

dbid

Specifies the DBID for imported or PDB-level AWR data. If NULL, then the function uses the current database DBID.

task_name

Identifier of the task to execute

parameter

Name of the parameter to set. The possible tuning parameters that can be set by this procedure using the parameter in the form VARCHAR2:

• APPLY_CAPTURED_COMPILENV: indicates whether the advisor could use the compilation environment captured with the SQL statements. The default is 0 (that is, NO).

• BASIC_FILTER: basic filter for SQL tuning set

• DAYS_TO_EXPIRE: number of days until the task is deleted

• DEFAULT_EXECUTION_TYPE: the task defaults to this type of execution when none is specified by the EXECUTE_TUNING_TASK Function and Procedure

• EXECUTION_DAYS_TO_EXPIRE: number of days until the tasks's executions is deleted (without deleting the task)

• LOCAL_TIME_LIMIT: per-statement time out (seconds)

• MODE: tuning scope (comprehensive, limited)

• OBJECT_FILTER: object filter for SQL tuning set

• PLAN_FILTER: plan filter for SQL tuning set (see SELECT_SQLSET for possible values)

• RANK_MEASURE1: first ranking measure for SQL tuning set

• RANK_MEASURE2: second possible ranking measure for SQL tuning set

• RANK_MEASURE3: third possible ranking measure for SQL tuning set

• RESUME_FILTER: a extra filter for SQL tuning sets besides BASIC_FILTER

• SQL_LIMIT: maximum number of SQL statements to tune

• SQL_PERCENTAGE: percentage filter of SQL tuning set statements

• TEST_EXECUTE: FULL/AUTO/OFF.

  * FULL - test-execute for as much time as necessary, up to the local time limit for the SQL (or the global task time limit if no SQL time limit is set)

  * AUTO - test-execute for an automatically-chosen time proportional to the tuning time

  * OFF - do not test-execute

• TIME_LIMIT: global time out (seconds)

• USERNAME: username under which the statement is parsed

value

New value of the specified parameter

Name of a database link that exists on a standby database.

The link specifies the connection to a primary database. By default, the value is null, which means that the SQL Tuning Advisor session is local.

Use DBMS_SQLTUNE to tune high-load SQL statements running on a standby database in an Active Data Guard scenario. When you execute REPORT_TUNING_TASK locally on the standby database, the function uses the database link to obtain the data from the primary database, and then constructs it locally on the standby database.

The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. The following sample statement creates a link named lnk_to_pri:

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

sql_text

SQL text whose signature is required. Required.

force_match

If TRUE, this returns a signature that supports SQL matching with literal values transformed into bind variables. If FALSE, returns the signature based on the text with literals not transformed

profile_name

The name of the profile to unpack (% wildcards acceptable, case-sensitive)

profile_category

The category from which to unpack profiles (% wildcards acceptable, case-sensitive)

replace

The option to replace profiles if they already exist. Note that profiles cannot be replaced if one in the staging table has the same name as an active profile in a different SQL statement. If FALSE, this function raises errors if you try to create a profile that already exists

staging_table_name

The name of the table on which to perform the remap operation (case-insensitive unless double quoted). Required.

staging_schema_owner

The schema where the table resides, or NULL for current schema (case-insensitive unless double quoted)

sqlset_name

Specifies the name of the tuning set to unpack (not null).

Wildcard characters (%) are supported to unpack multiple tuning sets in a single call. For example, specify % to unpack all tuning sets from the staging table.

sqlset_owner

Specifies the name of tuning set owner, or NULL for the current schema owner. Wildcard characters (%) are supported.

replace

Specifies whether to replace an existing SQL tuning set. If FALSE, then this procedure raises errors when you try to create a tuning set that already exists.

staging_table_name

Specifies the name of the staging table, moved after a call to the DBMS_SQLTUNE.PACK_STGTAB_SQLSET or DBMS_SQLSET.PACK_STGTAB procedure (case-sensitive).

staging_schema_owner

Specifies the name of staging table owner, or NULL for the current schema owner (case-sensitive).

sqlset_name

Specifies the name of the SQL tuning set.

sql_id

Specifies the identifier of the SQL statement to be updated.

plan_hash value

Specifies the hash value of the execution plan for a SQL statement.

Use this parameter when you want to update the attribute for a specific plan for a statement, but not all plans for the statement.

attribute_name

Specifies the name of the attribute to be modified.

You can update the text field for MODULE, ACTION, PARSING_SCHEMA_NAME, and OTHER. The only numerical field that you can update is PRIORITY.

If a statement has multiple plans, then the procedure changes the attribute value for all plans.

attribute_value

Specifies the new value of the attribute.