You can create a SQL Performance Analyzer task in Database Management SQL Watch to run SQL Performance
Analyzer.
A SQL Performance Analyzer analysis comprises of at least two SQL trials
and a comparison. The SQL trials capture the execution performance
of a SQL tuning set under specific environmental conditions such as
a database upgrade, and the analysis provides a comparison based on
the impact of the environmental differences between the two
trials.
Before you create a SQL Performance Analyzer task, you must:
- Capture a set of SQL statements on the production
system that represents the SQL workload that you intend to
analyze, and store them in a SQL tuning set. The SQL tuning
set is the input source for a SQL Performance Analyzer task.
For information, see Capturing
the SQL Workload in Oracle
Database Testing Guide.
You can
create a SQL tuning set in Database Management Diagnostics &
Management or in Oracle Enterprise Manager. For
information on how to create a SQL tuning set
in:
Note
You must ensure that the database user used to
create the SQL tuning set is used to create the SQL
Performance Analyzer task.
- Ensure that you've created database links if
establishing a connection to a remote database. For
information on how to create a database link, see Create
Database Link in Oracle
Database SQL Language Reference.
- Perform the following steps, if you plan to use a
test system separate from your production system.
- Set up the test system to match the
production environment as closely as possible.
- Transport the SQL tuning set to the
test system.
To create a SQL Performance Analyzer task:
- Go to the SQL Watch home page for the database.
- In the Tasks section, click
Create.
- In the Create SQL Performance Analyzer
task panel:
- Specify the following general
options:
- Name:
Enter a name for the task.
- SQL tuning
set: Click the
Search icon () to select a SQL tuning set.
- Description: Optionally,
enter a description for the task.
- Change
type: Select one of the following
change type options to determine its impact on SQL
performance:
- Parameter
change: Enables you to test the
performance effect on a SQL workload when you
change the value of initialization parameters. If
you select this option, click Add
parameter to add the initialization
parameters whose values you want to modify.
- Optimizer
statistics: Enables you to analyze the
impact of optimizer statistic changes on the
performance of a SQL workload. This option
establishes that:
- Ignore optimizer
hints: Enables you to analyze the
impact of optimizer hints on the performance of a
SQL workload. This option establishes that:
- Pre-change SQL trial uses
optimizer hints
- Post-change SQL trial ignores
optimizer hints
- Upgrade:
Enables you to analyze the impact of a database
upgrade from one version to another.
- Migration:
Enables you to analyze the impact of data
migration from source databases to target
databases.
- Custom:
Enables you to customize your task according to
your preferences.
- Select one of the following SQL
Performance Analyzer task creation methods:
- Basic:
Enables you to quickly create a SQL Performance
Analyzer task, and the pre-change and post-change
SQL trials share the same configuration. If you
select the Basic option,
you must specify the following:
- Trial
executions: Specify the execution
method and time limit:
- Execution
method: Select one of the following
options to determine how the SQL trials are
created and what content is generated:
- Execute
SQL: Generates both execution plans
and statistics for each SQL statement in the SQL
tuning set by actually running the SQL
statements.
- Generate
plans: Invokes the optimizer to create
execution plans only, without actually running the
SQL statements.
- Hybrid:
Finds SQL statements with plan changes first, and
then test-executes the SQL statements with plan
changes. This is the default option.
- Per SQL time
limit: Select one of the following
options to specify the time limit for SQL
execution during the trial.
- 5 mins:
Runs each SQL statement in the SQL tuning set for
up to 5 minutes and gathers performance data.
- Unlimited:
Runs each SQL statement in the SQL tuning set to
completion and gathers performance data.
Collecting execution statistics provides greater
accuracy in the performance analysis but takes a
longer time. Using this setting is not recommended
because the task may be stalled by one SQL
statement for a prolonged time period.
- Custom:
Enables you to specify the number of seconds,
minutes, or hours.
- Comparison:
Specify the details for the comparison analysis:
- Comparison
metric: Select the metrics that you
want to use for the comparison analysis.
- Validate SQL result
sets: Select to direct the SQL
Performance Analyzer to detect if the result-sets
between the two trials being compared are
different. If differences are seen in the
result-sets of any SQL statement in the two trials
being compared, the SQL Performance Analyzer
comparison report will indicate this for every
such SQL statement. This is
On by default.
- Schedule:
Select Immediately to start
the task now or Later to
schedule the task at a later time.
- Advanced:
Enables you to customize the pre-change and
post-change SQL trials and comparison
configuration. If you select the
Advanced option, you must
specify the following:
- Pre-change
trial: Specify details to collect the
pre-change SQL performance data:
- Trial
name: Enter a name for the pre-change
SQL trial.
- Description: Optionally,
enter a description for the pre-change SQL
trial.
- Execution
method: Select one of the following
options to determine how the SQL trials are
created and what content is generated:
- Execute
SQL: Generates both execution plans
and statistics for each SQL statement in the SQL
tuning set by actually running the SQL
statements.
- Generate
plans: Invokes the optimizer to create
execution plans only without actually running the
SQL statements.
- Hybrid:
Finds SQL statements with plan changes first, and
then test-executes the SQL statements with plan
changes. This is the default option.
- Execution
options: Review and make changes to
the execution options, if required:
- Per SQL time
limit: Select one of the following
options to specify the time limit for SQL
execution during the trial.
- 5 mins:
Runs each SQL statement in the SQL tuning set for
up to 5 minutes and gathers performance data.
- Unlimited:
Runs each SQL statement in the SQL tuning set to
completion and gathers performance data.
Collecting execution statistics provides greater
accuracy in the performance analysis but takes a
longer time. Using this setting is not recommended
because the task may be stalled by one SQL
statement for a prolonged time period.
- Custom:
Enables you to specify the number of seconds,
minutes, or hours.
- Use SQL capture
compile environment: Select to
indicate that the compilation environment should
be captured with the SQL statements. This is
Off by default.
- Maximum number of rows
to fetch: Select one of the following
options to specify the number of rows to be
fetched:
- All rows:
Fetches all the rows for the SQL statement.
- Average:
Calculates the number of result rows as the ratio
of total rows processed and total executions for
each SQL statement in the SQL tuning set.
- Automatic:
Determines the number of result rows using the
value of
optimizer_mode
parameter
of the optimizer environment captured in the SQL
tuning set. If the value of
optimizer_mode
is
ALL_ROWS
, then all result rows
will be fetched. If its value is
FIRST_ROWS_n
, then
n
result rows will be
fetched.
- Disable multiple
executions of SQL: Select to execute
each SQL statement in the SQL tuning set only
once. This is On by
default.
- Execute full
DML: Select to execute DML statement
fully, including acquiring row locks and modifying
rows. This is On by
default.
- + Add custom
option: Click to add a custom
execution option.
- Post-change
trial: Specify details to collect the
post-change SQL performance data:
- Trial
name: Enter a name for the post-change
SQL trial.
- Description: Optionally,
enter a description for the post-change SQL
trial.
- Execution
method: Select one of the following
options to determine how the SQL trials are
created and what content is generated:
- Execute
SQL: Generates both execution plans
and statistics for each SQL statement in the SQL
tuning set by actually running the SQL
statements.
- Generate
plans: Invokes the optimizer to create
execution plans only without actually running the
SQL statements.
- Hybrid:
Finds SQL statements with plan changes first, and
then test-executes the SQL statements with plan
changes. This is the default option.
- Use same options as
pre-change trial: Select to use the
same options specified for the pre-change SQL
trial. This is the default option.
- Use trial specific
options: Select to specify different
execution options for the post-change SQL trial.
- Comparison: Specify the
details for the comparison analysis:
- Comparison
metric: Select the metrics that you
want to use for the comparison analysis. By
default, SQL Performance Analyzer uses Elapsed
Time as a metric for comparison.
Alternatively, you can select another metric for
comparison in this field.
- Workload Impact
Threshold (%): Enter a value between 0
and 100 to indicate the threshold of a SQL
statement change impact on a workload. Statements
having workload change impact below the absolute
value of this threshold will be considered as
unchanged, that is, the performance of those
statements will be considered neither improved nor
regressed.
- SQL Impact Threshold
(%): Enter a value between 0 and 100
to indicate the threshold of a change impact on a
SQL statement. Statements having SQL change impact
below the absolute value of this threshold will be
considered as unchanged, that is, the performance
of those statements will be considered neither
improved nor regressed.
- Compare Plan
Lines: Select one of the following
plan line comparison options:
- Always:
Performs a line by line comparison of plans in all
scenarios.
- Automatic:
Performs a line-by-line comparison of execution
plans only if the computation of the plan hash
value for the first SQL trial has changed or the
second SQL trial is unavailable.
- None:
Performs a line-by-line comparison of execution
plans only if the plan hash value is unknown. This
is the default value.
- Metric Delta Threshold
(%): Enter a value between 0 and 100
to indicate the threshold of the difference
between the SQL performance metric before and
after the change.
- Validate SQL Result
Sets: Select to direct the SQL
Performance Analyzer to detect if the result-sets
between the two trials being compared are
different. If differences are seen in the
result-sets of any SQL statement between the two
trials being compared, the SQL Performance
Analyzer comparison report will indicate this for
every such SQL statement. This is
On by default.
- + Add custom
option: Click to add a custom
execution option.
- Schedule:
Select Immediately to start
the task now or Later to
schedule the task at a later time.
- Click Submit.
After one or more SQL Performance Analyzer tasks are created on the SQL Watch
home page for the database, you can monitor the task-related information in
the following tiles, for the time period selected in the
View
data drop-down list.
- Latest comparison report:
Displays the task status and the latest comparison for a
change type between the pre-change and post-change SQL
trials specified in the task. You can click the comparison
report (change percentage) link to view the comparison
report. For information on the comparison report, see View SQL Performance Analyzer Comparison Reports.
- Tasks by status: Displays the
SQL Performance Analyzer tasks categorized by status in a
donut chart. Click the All tasks link
to reset the data displayed in the chart.
- Tasks by change type:
Displays the SQL Performance Analyzer tasks categorized by
change type in a horizontal bar chart. Click the
All tasks link to reset the
data displayed in the chart.
The SQL Performance Analyzer tasks are listed in the
Tasks section on the SQL Watch home
page for the database. If you have a number of tasks, you can use
the Change type and
Status drop-down lists or the
Search by task or description field
to filter the tasks. To view additional information such as the
description of the task, select an option in the
Columns drop-down list.
In the Tasks section, you
can:
- Click the name of the task to view SQL Performance
Analyzer task details, executions, and comparison, and
perform tasks such as creating trials and comparisons. For
information, see Monitor a SQL Performance Analyzer Task.
- Click the Actions icon
() for the task and perform the following tasks:
- View: Click
to view SQL Performance Analyzer task
details.
- Show latest
report: Click to view the latest
comparison report generated for the task.
- Delete:
Click to delete the task.
- Complete:
Click to complete the creation of a SQL
Performance Analyzer task. The creation of certain
SQL Performance Analyzer tasks requires two
dbms_scheduler
jobs as manual
steps must be performed. Once the first job is
completed, the task is flagged and once you've
performed the required manual steps, click
Complete to trigger the
execution of the second job, and complete the
task.