Auto Sql Tuning Task 12c

Oracle 12c offers a useful utility called the SQL Tuning Advisor. You can use this built-in tool to provide suggestions or recommendations about certain SQL statements. Although it may not always give perfect advice, just like anything else, having it in your toolbox of tuning techniques is beneficial.

  • In addition to configuring the standard behavior of the SQL Tuning Advisor, the DBMSSQLTUNE package enables you to configure automatic SQL tuning by specifying the task parameters using the SETTUNINGTASKPARAMETER procedure. Because the automatic tuning task is owned by SYS, only the SYS user can set the task parameters.
  • Jan 30, 2017  A new white paper for SQL Plan Management is now available. Just go to the Query Optimization page in OTN and click the SQL Plan Management with Oracle Database link. Just like the Optimizer white paper, I've kept the content and format similar to the way Maria organized it so that it will be familiar to those of you that have read previous versions.
  • . Oracle automatically runs the SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. You can customize attributes of the maintenance windows, including start and end.
  • SQL tuning tasks can be created with SQL from the cursor cache, the AWR views, or previously collected SQL Tuning Sets. The overloaded dbmssqltune.createtask functions are provided to allow the DBA to change the inputs. After successfully creating a SQL tuning task, the DBA can launch the SQL Tuning Optimizer to produce tuning recommendations.
  • The ENABLE and DISABLE procedures of the DBMSAUTOTASKADMIN package control the execution of the Automatic SQL Tuning Tasks. When enabled, the SQL Tuning Advisor is run during the defined maintenance windows; the default maintenance windows are shown in Table 1.
  • As you can notice, sql tuning advisor task is already disabled, but the remaining two tasks are enabled. Also, the overall task status is enabled (From DBAAUTOTASKSTATUS view). Let us now, disable the two individual task: EXEC DBMSAUTOTASKADMIN.DISABLE('AUTO OPTIMIZER STATS COLLECTION',NULL,NULL).

Oracle 12c offers a useful utility called the SQL Tuning Advisor. You can use this built-in tool to provide suggestions or recommendations about certain SQL statements. Although it may not always give perfect advice, just like anything else, having it in your toolbox of tuning techniques is beneficial.

  1. Use PL/SQL and the internal package DBMS_SQL_TUNE to create a tuning task. Type this:

    You should see the following:

    Midnight sun trish cook pdf download full. In the preceding command, note the TIME_LIMIT of 60. That limits the processing time to 60 seconds. You may not always want to run something like this for long periods in your database, because it incurs system overhead.

  2. Execute the tuning advisor with your task by typing this:

    Because of the limit of 60 seconds provided in the task creation, this step may take up to 60 seconds to complete. During this time, your prompt won’t come back.

    When it completes, you should see this:

    If you’ve set a longer time and are getting impatient, you can open another SQL window to make sure that the task is still executing by typing

    You see something like the following:

  3. When the execution is complete, you can view the results by running the BMS_SQLTUNE.report_tuning_task procedure. Type the following:

    For the sake of space, we’ve snipped some sections from the output that follows, but you see something like this:

    The latter part of the report shows the before and after execution plans. In this case, you’ve seen the before when you were generating execution plans. Go ahead and add the index, regenerate the execution plan, and see whether you’ve made an improvement.

    Before you add the index, note that the recommendations give the SQL to add the index:

    Also note that Oracle gives a warning:

  4. Add the index with your own name by typing this:

    You should see something like the following:

  5. Take a look at the execution plan. Type the following:

    And then type

    You should see output like this:

Now that you’ve added the index, a few things are evident:

  • The cost of the plan dropped from 40336 to 5.

  • There are now six steps.

  • The full table scan is gone. Instead you see the use of your new index.

Often one of the tough parts about tuning a database is having a solid understanding of the application and the data. The issue might not always be obvious. Sometimes engaging other application and data experts helps.

Auto Sql Tuning Task

Explain to them your findings and what you propose. They may be able to help you come to a conclusion. Also, if the data is part of a packaged third-party application, sometimes opening a ticket with the vendor is the way to go.

8i 9i 10g 11g 12c 13c 18c 19c Misc PL/SQL SQL RAC WebLogic Linux

Home » Articles » 11g » Here

As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:

  • AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
  • The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
  • Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.
  • The accepted SQL profiles are optionally implemented . Several factors may prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).

The ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks.

It is also indirectly disabled by setting the STATISTICS_LEVEL parameter to BASIC, as this stops automatic statistics gathering by the AWR.

The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package controls the behavior of the SQL tuning advisor. The parameters specifically for the automatic runs include:

  • ACCEPT_SQL_PROFILES - Automatically accept SQL profiles (default FALSE).
  • MAX_SQL_PROFILES_PER_EXEC - The maximum number of SQL profiles automatically implemented per run (default 20).
  • MAX_AUTO_SQL_PROFILES - The maximum number of automatic SQL profiles allowed on the system (default 10000).

The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.

The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.

The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.

Auto Sql Tuning Task 12c Free

As you can see from the above report, this was run against a very quiet system, so there were no candidate SQL statements to process. If this were run against a more active system, you might expect the report to contain the following sections:

  • General information - High-level information about the SQL tuning task.
  • Summary - A summary of the SQL statements tuned during the task, including the estimated benefit associated with the tuning operation.
  • Tuning finding - Information about findings, acceptance of the profile, implementation of the profile, and detailed execution statistics for each analyzed statement.
  • Explain plans - The old and new execution plans for each analyzed statement.
  • Errors - Any errors encountered during the task.

For more information see:

Hope this helps. Regards Tim..