SQL Tuning Advisor is one of a suite of advisors, a set of expert systems that identifies and helps resolve database performance problems. Specifically, SQL Tuning Advisor automates tuning of problematic SQL statements. It takes one or more SQL statements as input and gives precise advice on how to tune the statements. The advisor provides the advice in the form of SQL actions for tuning the SQL along with their expected performance benefit.

The group of DBMS_SQLTUNE SQL Tuning Advisor Subprograms provide a task-oriented interface that enables you to access the advisor. You can call the following subprograms in the order given to use some of SQL Tuning Advisor's features:

You use the SCRIPT_TUNING_TASK Function to create a SQL*Plus script which can then be executed to implement a set of Advisor recommendations

SQL Profile Subprograms

SQL Tuning Advisor may recommend the creation of a SQL profile to improve the performance of a statement. SQL profiles consist of auxiliary statistics specific to the statement. The query optimizer makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount, resulting in poor execution plans. The SQL profile addresses this problem by collecting additional information using sampling and partial execution techniques to adjust these estimates.

The group of DBMS_SQLTUNE SQL Profile Subprograms provides a mechanism for delivering statistics to the optimizer that targets one particular SQL statement, and helps the optimizer make good decisions for that statement by giving it the most accurate statistical information possible. For example:

Use DBMS_SQLTUNE subprograms to move SQL profiles and SQL tuning sets from one system to another using a common programmatic model. In both cases, you create a staging table on the source database and populate this staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as Oracle Data Pump, or a database link), where it is used to reconstitute the objects in their original form. The following steps are implemented by means of subprograms included in this package:

The automated system task SYS_AUTO_SQL_TUNING_TASK is created by the database as part of the catalog scripts. This task automatically chooses a set of high-load SQL from AWR and runs SQL Tuning Advisor on this SQL. The automated task performs the same comprehensive analysis as any other SQL Tuning task.

You can obtain a report on the activity of the Automatic SQL Tuning task through the DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK API. See the DBMS_AUTO_SQLTUNE package for the list of subprograms that you can use to manage the automated SQL tuning task.

Real-time SQL Monitoring enables DBAs or performance analysts to monitor the execution of long-running SQL statements while they are executing. Both cursor statistics (such as CPU times and IO times) and execution plan statistics (such as number of output rows, memory and temp space used) are updated in almost real time during statement execution. The V$SQL_MONITOR and V$SQL_PLAN_MONITOR views expose these statistics. In addition, DBMS_SQLTUNE provides the REPORT_SQL_MONITOR and REPORT_SQL_MONITOR_LIST functions to report monitoring information.

Note:

DBMS_SQL_MONITOR also contains the REPORT_SQL_MONITOR and REPORT_SQL_MONITOR_LIST functions.

Tuning a Standby Database Workload

In some cases, a standby database can assume a reporting role in addition to its data protection role. The standby database can have its own workload of queries, some of which may require tuning. You can issue SQL Tuning Advisor statements on a standby database, which is read-only. A standby-to-primary database link enables DBMS_SQLTUNE to write data to and read data from the primary database. The procedures that are eligible for tuning standby workloads include the database_link_to parameter.

157.2 DBMS_SQLTUNE Security Model

This package is available to PUBLIC and performs its own security checking.

SQL tuning set subprograms (XXX_SQLSET) require either the ADMINISTER SQL TUNING SET or the ADMINISTER ANY SQL TUNING SET privilege. Users having the ADMINISTER SQL TUNING SET privilege can only create and modify a SQL tuning set they own, while the ADMINISTER ANY SQL TUNING SET privilege allows them to operate upon all SQL tuning sets, even those owned by other users. For example, using the CREATE_SQLSET Procedure and Function you can create a SQL tuning set to be owned by another user. In this case, the user need not necessarily have the ADMINISTER SQL TUNING SET privilege to operate upon her tuning set.

Previously, three different privileges were needed to invoke subprograms concerned with SQL profiles:

CREATE ANY SQL PROFILE

ALTER ANY SQL PROFILE

DROP ANY SQL PROFILE

The preceding privileges have been deprecated in favor of ADMINISTER SQL MANAGEMENT OBJECT .

157.3 DBMS_SQLTUNE Data Structures

The SELECT_* subprograms in the DBMS_SQLTUNE package return objects of the SQLSET_ROW type.

157.3.1 SQLSET_ROW Object Type

The SQLSET_ROW object models the content of a SQL tuning set for the user.

Logically, a SQL tuning set is a collection of SQLSET_ROW objects. Each SQLSET_ROW contains a single SQL statement along with its execution context, statistics, binds, and plan. The SELECT_* subprograms each model a data source as a collection of SQLSET_ROW objects, with each object uniquely identified by (sql_id, plan_hash_value). Similarly, the LOAD_SQLSET procedure takes as input a cursor whose row type is SQLSET_ROW, treating each SQLSET_ROW in isolation according to the policies requested by the user.

Several subprograms package accept basic filters on the content of a SQL tuning set or data source. These filters are expressed in terms of the attributes within the SQLSET_ROW as defined.

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.

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.

database_link_to

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:

157.5.2 ACCEPT_SQL_PROFILE Procedure and Function

The SQL text is normalized for matching purposes although it is stored in the data dictionary in denormalized form for readability. SQL text is provided through a reference to the SQL Tuning task. If the referenced SQL statement does not exist, then the database reports an error.

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

database_link_to

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:

In the following example, you tune a SQL tuning set, and you create a SQL profile for only one of the SQL statements in the SQL tuning set. The SQL statement is represented by an advisor framework object with ID equal to 5. You must pass an object ID to the ACCEPT_SQL_PROFILE procedure because there are potentially many SQL profiles for the tuning task. This object ID is given along with the report.

The description of the usage of SQL tuning set. The description is truncated if longer than 256 characters.

Return Values

The identifier of the added reference.

Examples

To prevent the tuning set from being modified while it is being used, you can add a reference to a SQL tuning set. References are automatically added when you invoke SQL Tuning Advisor on the SQL tuning set, so use this function for custom purposes only. The function returns a reference ID that is used to remove it later. Use the REMOVE_SQLSET_REFERENCE procedure to delete references to a SQL tuning set.

MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. 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).

Examples

In this example capture takes place over a 30-second period, polling the cache once every five seconds. This captures all statements run during that period but not before or after. If the same statement appears a second time, the process replaces the stored statement with the new occurrence.

Note that in production systems the time limit and repeat interval would be set much higher. You should tune the time_limit and repeat_interval parameters based on the workload time and shared SQL area turnover properties of your system.

In the following call you accumulate execution statistics as you go. This option produces an accurate picture of the cumulative activity of each cursor, even across age-outs, but it is more expensive than the previous example.

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

database_link_to

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:

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:

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.

dbid

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.

database_link_to

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:

A SQL tuning task name that is unique by user (two different users can give the same name to their advisor tasks).

Usage Notes

With regard to the form of this subprogram that takes a SQL tuning set, filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

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.

Examples

-- Delete all statements in a sql tuning set.
EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'my_workload');
-- Delete all statements in a sql tuning set which ran for less than a second
EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'my_workload', -
basic_filter => 'elapsed_time < 1000000');

157.5.16 EXECUTE_TUNING_TASK Function and Procedure

This function and procedure executes a previously created tuning task. Both the function and the procedure run in the context of a new task execution. The difference is that the function version returns that new 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.

database_link_to

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:

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.

database_link_to

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:

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.

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).

Exceptions

This procedure returns an error when sqlset_name is invalid, or a corresponding SQL tuning set does not exist, or the populate_cursor is incorrect and cannot be executed.

Exceptions are also raised when invalid filters are provided. Filters can be invalid either because they don't parse (for example, they refer to attributes not in sqlset_row), or because they violate the user's privileges.

Usage Notes

Rows in the input populate_cursor must be of type SQLSET_ROW.

Examples

In this example, you create and populate a SQL tuning set with all shared SQL area statements with an elapsed time of 5 seconds or more excluding statements that belong to SYS schema (to simulate an application user workload). You select all attributes of the SQL statements and load them in the tuning set using the default mode, which loads only new statements, since the SQL tuning set is empty.

Suppose now you wish to augment this information with what is stored in the workload repository (AWR). You populate the tuning set with 'ACCUMULATE' as your update_option because it is assumed the cursors currently in the cache had aged out since the snapshot was taken.

You omit the elapsed_time filter because it is assumed that any statement captured in AWR is important, but still you throw away the SYS-parsed cursors to avoid recursive SQL.

The following example is a simple load that only inserts new statements from the workload repository, skipping existing ones (in the SQL tuning set). Note that 'INSERT' is the default value for the load_option argument of the LOAD_SQLSET procedure.

The next example demonstrates a load with UPDATE option. This updates statements that already exist in the SQL tuning set but does not add new ones. By default, old statistics are replaced by their new values.

This is another example where you put all profiles into the staging table. Note this moves profiles that are not currently being used by default but are in other categories, such as for testing purposes.

Use the DBA_SQLSET_REFERENCES view to find all references to a given SQL tuning set.

157.5.25 REPORT_AUTO_TUNING_TASK Function

This function displays a report from the automatic tuning task.

This function reports on a range of task executions, whereas the REPORT_TUNING_TASK Function reports on a single execution. Note that this function is deprecated with Oracle Database 11g Release 2 (11.2) in favor of DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK.

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)

ACTIVE reports have a rich, interactive user interface similar to Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.

The invoker needs the SELECT or READ privilege on the following views:

V$SESSION

DBA_ADVISOR_FINDINGS

V$DATABASE

GV$ASH_INFO

GV$ACTIVE_SESSION_HISTORY

GV$SQLAREA_PLAN_HASH

GV$SQL

DBA_HIST_SNAPSHOT

DBA_HIST_WR_CONTROL

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_SQLSTAT

DBA_HIST_SQL_BIND_METADATA

DBA_HIST_SQLTEXT

DBA_SQL_PLAN_BASELINES

DBA_SQL_PROFILES

DBA_ADVISOR_TASKS

DBA_SERVICES

DBA_USERS

DBA_OBJECTS

DBA_PROCEDURES

157.5.27 REPORT_SQL_MONITOR Function

This function builds a report (text, simple HTML, active HTML, XML) for the monitoring information collected on behalf of the targeted statement execution.

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:

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.

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

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.

Return Values

A CLOB containing the desired report.

Usage Notes

The target SQL statement for this report can be:

The last SQL monitored by Oracle Database. This is the default behavior, so there is no need to specify any parameter.

The last SQL executed by a specific session and monitored by Oracle. The session is identified by its session id and optionally it serial number. For example, use session_id => USERENV ('SID') for the current session or session_id=>20, session_serial=>103 for session ID 20, serial number 103.

The last execution of a specific statement identified by its sql_id.

A specific execution of a SQL statement identified by its execution key (sql_id, sql_exec_start and sql_exec_id).

This report produces performance data exposed by several fixed views, listed below. For this reason, the invoker of the report function must have privilege to select data from these fixed views (such as the SELECT_CATALOG role).

GV$SQL_MONITOR

GV$SQL_PLAN_MONITOR

GV$SQL_PLAN

GV$ACTIVE_SESSION_HISTORY

GV$SESSION_LONGOPS

GV$SQL

The bucket_max_count and bucket_interval parameters control the activity histogram.

By default, the maximum number of buckets is set to 128. The database derives the bucket_interval value based on this count. The bucket_interval (value is in seconds) is computed such that it is the smallest possible power of 2 value (starting at 1s) without exceeding the maximum number of buckets. For example, if the query has executed for 600 seconds, then the database selects a bucket_interval of 8 seconds (a power of two). The database chooses the value of 8 because 600/8 = 74, which is less than 128 buckets maximum. Smaller than 8 seconds would be 4 seconds, which would lead to more buckets than the 128 maximum. If bucket_interval is specified, then the database uses the specified value instead of deriving it from bucket_max_count.

ACTIVE reports have a rich, interactive user interface similar to Enterprise Manager, while not requiring any EM installation.

The report file is in HTML format. The code powering the active report is downloaded transparently by the web browser when the report is first viewed. Therefore, viewing the report requires outside connectivity.

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, this parameters targets only the sub-set 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.

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.

database_link_to

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:

A SQL predicate to filter the SQL from the SQL tuning set. Note that this filter is applied in conjunction with the basic filter (i.e., parameter basic_filter) when calling CREATE_TUNING_TASK Functions.

Usage Notes

Resuming a single SQL tuning task (a task that was created to tune a single SQL statement as compared to a SQL tuning set) is not supported.

Examples

-- Interrupt the task
EXEC DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(:conc_task);
-- Once a task is interrupted, we can elect to reset it, resume it, or check
-- out its results and then decide. For this example we will just resume.
EXEC DBMS_SQLTUNE.RESUME_TUNING_TASK(:conc_task);

157.5.33 SCHEDULE_TUNING_TASK Function

This function creates a tuning task for a single SQL statement and schedules a DBMS_SCHEDULER job to execute the tuning task. One form of the function finds the information about the statement to be tuned in the shared SQL area, whereas the other finds the information in AWR.

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.

database_link_to

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:

SET LINESIZE 140
-- Get a script for all actions recommended by the task.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL;
-- Get a script of only the sql profiles we should create.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'PROFILES') FROM DUAL;
-- Get a script of only stale / missing stats
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task, 'STATISTICS') FROM DUAL;
-- Get a script with recommendations about only one SQL statement when we have
-- tuned an entire STS.
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:sts_task, 'ALL', 5) FROM DUAL;

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).

Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Usage Notes

Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

Users need privileges on the shared SQL area views.

Examples

-- Get sql ids and sql text for statements with 500 buffer gets.
SELECT sql_id, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;
-- Get all the information we have about a particular statement.
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j'''));
-- Notice that some statements can have multiple plans. The output of the
-- SELECT_XXX table functions is unique by (sql_id, plan_hash_value). This is
-- because a data source can store multiple plans per sql statement.
SELECT sql_id, plan_hash_value
FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''ay1m3ssvtrh24'''))
ORDER BY sql_id, plan_hash_value;
-- PL/SQL examples: load_sqlset is called after opening a cursor, along the
-- lines given below
-- Select all statements in the shared SQL area.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT value(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;
-- Process each statement (or pass cursor to load_sqlset).
CLOSE cur;
END;/
-- Look for statements not parsed by SYS.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur for
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''')) P;
-- Process each statement (or pass cursor to load_sqlset).
CLOSE cur;
end;/
-- All statements from a particular module/action.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'module = ''MY_APPLICATION'' and action = ''MY_ACTION''')) P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;/
-- all statements that ran for at least five seconds
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000')) P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
end;/
-- select all statements that pass a simple buffer_gets threshold and
-- are coming from an APPS user
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'buffer_gets > 100 and parsing_schema_name = ''APPS'''))P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
end;/
-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL)
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(dbms_sqltune.select_cursor_cache(
'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN')) P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
END;/
-- Select the top 100 statements in the shared SQL area ordering by elapsed_time.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
NULL,
'ELAPSED_TIME', NULL, NULL,
1,
100)) P;
-- Process each statement (or pass cursor to load_sqlset)
CLOSE cur;
end;/
-- Select the set of statements which cumulatively account for 90% of the
-- buffer gets in the shared SQL area. This means that the buffer gets of all
-- of these statements added up is approximately 90% of the sum of all
-- statements currently in the cache.
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(NULL,
NULL,
'BUFFER_GETS', NULL, NULL,
.9)) P;
-- Process each statement (or pass cursor to load_sqlset).
CLOSE cur;
END;
/

157.5.36 SELECT_SQL_TRACE Function

This table function reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row.

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.

Return Values

This function returns a SQLSET_ROW object.

Usage Notes

The ability to create a directory object for the system directory creates a potential security issue. For example, in a CDB, all containers write trace files to the same directory. A local user with SELECT privileges on this directory can read the contents of trace files belonging to any container.

To prevent this type of unauthorized access, copy the files from the default SQL trace directory into a different directory, and then create a directory object. Use the PATH_PREFIX clause of the CREATE PLUGGABLE DATABASE statement to ensure that all directory object paths associated with the PDB are restricted to the specified directory or its subdirectories.

Examples

The following code shows how to enable SQL trace for a few SQL statements and load the results into a SQL tuning set:

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.

For example, you can use this function to create a SQL tuning set containing the subset of SQL statements that regressed during a SQL Performance Analyzer (SPA) experiment. You can also specify other arbitrary filters.

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).

Return Values

This function returns one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Usage Notes

Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

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.

Return Values

This function returns one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Usage Notes

Filters provided to this function are evaluated as part of a SQL run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

* 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

database_link_to

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:

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)

Usage Notes

Using this procedure requires the CREATEANYSQLPROFILE privilege and the SELECT privilege on staging table.

Examples

-- Unpack all profiles stored in a staging table.
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => FALSE
, staging_table_name => 'PROFILE_STGTAB');
END;
-- If there is a failure during the unpack operation, you can find the profile
-- that caused the error and perform a remap_stgtab_sqlprof operation targeting it.
-- You can resume the unpack operation by setting replace to TRUE so that
-- the profiles that were already created are replaced.
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => TRUE
, staging_table_name => 'PROFILE_STGTAB');
END;

157.5.43 UNPACK_STGTAB_SQLSET Procedure

This procedure copies one or more SQL tuning sets from their location in the staging table into the SQL tuning sets schema, making them proper SQL tuning sets.