• Dbms_sqltune.report_auto_tuning_task Example
• Select Dbms_sqltune.report_tuning_task From Dual

SQL SELECT DBMSAUTOSQLTUNE.REPORTAUTOTUNINGTASK FROM DUAL; SQL SELECT DBMSSQLTUNE.REPORTAUTOTUNINGTASK FROM DUAL; Depending on the activity and load on your database, the report may contain no suggestions or may provide a great deal of advice. The Automatic SQL Tuning job uses the high workload SQL statements identified in the AWR. Sep 26, 2007 DBMSSQLTUNE.REPORTAUTOTUNINGTASK function will generate a text-based report that displays formatted results for a specific run of an AST task. Implicit AST Deactivation. Finally, please note that AST will be completely deactivated by default if either: The STATISTICSLEVEL initialization parameter has been set to BASIC;.

The DBMS_AUTO_SQLTUNE package is the interface for managing the Automatic SQL Tuning task. Unlike DBMS_SQLTUNE, the DBMS_AUTO_SQLTUNE package requires the DBA role.

You can compile C/C programs using makefile. For more details, you can check our 'Makefile Tutorial'. Semicolons and Blocks in C. In C, the semicolon is a statement terminator. That is, each individual statement must be ended with a semicolon. It indicates the end of one logical entity. For example, following are three different statements −. All basic C programs start at what is called the 'main' function. A function is just a piece of code with a name, and that name, in this case, is 'main'. Functions also have a type, generally speaking the 'main' function should have the integer (whole number) type, and this is shown by writing the int keyword before the function name. Dev-C is a free IDE for Windows that uses either MinGW or TDM-GCC as underlying compiler. Originally released by Bloodshed Software, but abandoned in 2006, it has recently been forked by Orwell, including a choice of more recent compilers. Summary of basic C-commands Compiling To compile a C-program, you can use either gor c. G -oexecutable filename.out sourcefilename.cc c -oexecutable filename.out sourcefilename.cc e.g. G -o Csampleinout.out Csampleinout.cc For the following commands you can find at the end of this summary sample programs. Each command in C is followed by “;”.

Note:

The chapter contains the following topics:

• • Overview

  • Security Model

Using DBMS_AUTO_SQLTUNE

Overview

The DBMS_AUTO_SQLTUNE package is the interface to SQL Tuning Advisor (DBMS_SQLTUNE) when run within the Autotask framework. The database creates the automated system task SYS_AUTO_SQL_TUNING_TASK as part of the catalog scripts. This task automatically chooses a set of high-load SQL from AWR and runs the SQL Tuning Advisor on this SQL. The automated task performs the same comprehensive analysis as any other SQL Tuning task.

The automated task tests any SQL profiles it finds by executing both the old and new query plans. Automatic SQL Tuning differs from manual SQL tuning in one important way. If automatic implementation of SQL profiles is enabled (the default is disabled), then the database implements any SQL profiles that promise a great performance benefit. The implementation occurs at tuning time so that the database can immediately benefit from the new plan. You can enable or disable automatic implementation by using the SET_AUTO_TUNING_TASK_PARAMETER API to set the ACCEPT_SQL_PROFILES parameter.

In each maintenance window, the automated tuning task stores its results as a new execution. Each execution result has the same task name but a different execution name. Query the DBA_ADVISOR_EXECUTIONS view for information about task executions. Use the REPORT_AUTO_TUNING_TASK Function to view reports that span multiple executions.

Security Model

This package is available to users with the DBA role. For other users, you must grant the EXECUTE privilege on the package explicitly. Note that the EXECUTE_AUTO_TUNING_TASK procedure is an exception: only SYS can invoke it.

Users can call APIs in this package to control how the automatic tuning task behaves when it runs, such as enabling automatic SQL profile creation and configuring the total and per-SQL time limits under which the task runs. Because these settings affect the overall performance of the database, it may not be appropriate for all users with the ADVISOR privilege to have access to this package.

Summary of DBMS_AUTO_SQLTUNE Subprograms

Table 28-1 DBMS_AUTO_SQLTUNE Package Subprograms

EXECUTE_AUTO_TUNING_TASK Function & Procedure

This function and procedure executes the Automatic SQL Tuning task (SYS_AUTO_SQL_TUNING_TASK). Both the function and the procedure run in the context of a new task execution. The difference is that the function returns that new execution name. Note that only SYS can invoke this subprogram.

Parameters

Table 28-2 EXECUTE_TUNING_TASK Function & Procedure Parameters

Usage Notes

A tuning task can be executed multiple times without having to reset it.

REPORT_AUTO_TUNING_TASK Function

Dbms_sqltune.report_auto_tuning_task Example

This procedure displays the results of an Automatic SQL Tuning task.

Parameters

Table 28-3 REPORT_TUNING_TASK Function Parameters

Return Values

A CLOB containing the desired report.

SET_AUTO_TUNING_TASK_PARAMETER Procedures

This procedure updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER as used for the reserved auto tuning task, SYS_AUTO_SQL_TUNING_TASK.

Parameters

Table 28-4 SET_AUTO_TUNING_TASK_PARAMETER Procedure Parameters

Select Dbms_sqltune.report_tuning_task From Dual

Introduce of SQL Tuning Advisor

In Oracle Database 10g, Oracle introduced the Automatic Tuning Optimizer, which is the name given to the optimizer when it is running in the tuning mode instead of in the normal mode. In the normal mode, the optimizer creates an execution plan under strict time constraints. In the tuning mode, the optimizer is given more latitude to produce a better execution plan. The output of the optimizer isn’t an execution plan but recommendations for improvements along with a summary of the benefits you might expect by implementing those recommendations. Oracle offered the Automatic Tuning Optimizer for complex and high-load SQL statements.

The advisor’s goal is to target poorly written SQL statements as well as SQL statements that perform poorly because the optimizer generates a poor execution plan due to the lack of up-to-date statistics. A good way to find these types of statements is to use the Automatic Database Diagnostic Monitor (ADDM). The Automatic Tuning Optimizer performs the following types of analysis for high-load SQL statements:

• Statistics analysis
• SQL profiling
• Access path analysis

SQL structure analysis The ADDM identifies high-load SQL statements and you can use the SQL Tuning Advisor to fix these SQL statements. The SQL Tuning Advisor invokes the Automatic Tuning Optimizer to tune the SQL statements it offers the Optimizer. However, this still leaves the responsibility for reviewing the ADDM reports and running the SQL Tuning Advisor in the hands of the Oracle DBAs, as the SQL Tuning Advisor only makes recommendations and doesn’t automatically tune the statements in any way.

In Oracle Database 11g, by default, the Automatic Tuning Optimizer runs regularly during the Oracle Scheduler maintenance window, as the new automated maintenance task called the SQL Tuning Advisor task. You can also customize the maintenance window by changing attributes such as the days of the week, the frequency, and the start and end time. The database can automatically tune problem SQL statements by implementing the recommendations made by the SQL Tuning Advisor during the nightly maintenance window. The Automatic SQL Tuning Advisor is essentially the same as the Automatic Tuning Optimizer introduced in Oracle Database 10g. The automatic part of the SQL Tuning Advisor is what’s new in Oracle Database 11g.During its run each night, the Automatic SQL Tuning Advisor picks the high-load SQL statements from the AWR and offers tuning recommendations. The Automatic SQL Tuning Advisor can also automatically implement any SQL profile recommendations without your intervention.

The Automatic SQL Tuning Advisor chooses the tuning candidates from the AWR. When you manually run the SQL Tuning Advisor, you have to provide the advisor the SQL queries you want it to analyze.You normally use SQL Tuning Sets to provide the SQL statements to the optimizer. In automatic SQL tuning, the SQL workload is automatically chosen by the Advisor, without the need for the DBA to create and load any SQL tuning sets. The advisor also automatically tests SQL profiles and can automatically implement them if you want. The advisor also automatically retunes any SQL statements whose performance is found to deteriorate over time. The SQL Tuning Advisor still takes into account the same four things that it did in Oracle Database 10g:

• Statistical analysis Gathering missing or stale statistics
• SQL profiling Creating new SQL profiles
• Access path analysis Adding indexes
• SQL structure analysis Modifying SQL statements to make them efficient.

Automatic SQL Tuning targets SQL statements for automatic tuning based on the Automatic Workload Repository (AWR) Top SQL identification. The SQL statements are identified if they are at the top of the list in four distinct time periods: any hour in the past week, any day in the past week, the past week, or single response time. When the Automatic SQL Tuning task executes during the maintenance window, the candidate SQL statements identified by the AWR are automatically tuned by it. The advisor will create any SQL profiles that are necessary to improve the performance of the candidate SQL statements. Before implementing the SQL profiles, the advisor tests the profiles first. You can request a report of the tuning analysis and check the tuned SQL statements to determine if you want to retain the new SQL profiles implemented by the tuning advisor or remove them.

SQL Profiles

Because of the limited nature of its run, often the cost optimizer’s estimates of important things such as cardinality and selectivity and cost aren’t accurate, leading the optimizer to pick less than optimal execution plans. When you run the SQL Tuning Advisor with the comprehensive scope, it invokes the cost optimizer in the tuning mode, requiring the optimizer to collect additional statistics beyond what it usually collects by using partial execution and sampling techniques. A SQL profile contains these additional statistics. SQL Profiles, since they don’t require changing of the SQL statement itself, are ideal for use in packaged applications where you can’t easily change the code. Once a SQL profile is generated, the optimizer uses its normal statistics that it gathers, along with the additional information collected in the SQL profile, to turn out more accurate execution plans.The Automatic SQL Tuning Advisor may recommend SQL profiles as part of its overall recommendations and you can choose to have these SQL profiles automatically implemented as well.

The auxiliary information contained in a SQL profile includes the following:

• Customized optimizer settings based on past execution history for statements
• Adjustments to compensate for missing or stale optimizer statistics
• Adjustment for estimation errors in optimizer statistics resulting from factors such as a skewed data distribution

The Automatic Tuning Optimizer (ATO) uses the additional time at its disposal to gather the auxiliary information, which it stores in a SQL profile and makes a recommendation for the acceptance of the SQL profile. Once you implement a SQL profile, the query optimizer will make use of that profile to generate a more accurate and efficient execution plan for a SQL statement.

Limitations
Automatic SQL tuning doesn’t tune the following types of SQL statements:

• Recursive SQL statements.
• Ad hoc or rarely repeated SQL statements. If a SQL statement isn’t repeatedly run, or if a SQL statement isn’t repeated at least once a week, it is deemed ad hoc.
• Parallel queries.
• A query that continues to show a long execution time even after SQL profiling, thus making it impractical to test-execute those queries. The advisor ignores such queries. As long as a new SQL profile makes a long
• running query finish much faster, the advisor can accept them because it can test-execute them.
• SQL statements using the insert and delete statements.
• Statements using DDL statements such as the create table as select statement.

The interesting thing is that with the exception of the ad hoc statements, you can tune all the other types of statements by running the SQL Tuning Advisor yourself!
The Automatic SQL Tuning Process

Automatic SQL tuning consists of first identifying candidates for the tuning process, tuning the SQL statements, making recommendations, and automatically implementing any SQL profile recommendations.Identification of SQL Tuning Candidates Using the sum of the CPU time and the I/O time as metrics, the database orders SQL statements as candidates for tuning. Only those statements that offer a high potential for improvement are accepted as candidates. The automatic SQL tuning process bases the candidates on the top SQL identified by the AWR. The AWR prepares a candidate list by putting the top queries in the past week into the following four “buckets”:

• Top for the week
• Top for any day in the week
• Top for any hour during the week
• Highest average single execution

Each of the buckets can have a maximum of 150 SQL statements in it. The SQL tuning advisor combines the four buckets into a single group, by assigning weights to each of the buckets. The advisor then ranks the candidate SQL statements based on the impact they’ll have on performance.The advisor figures the performance impact of each SQL statement by combining the CPU time and the I/O time statistics captured by the AWR for the statement.

Tuning and Making Recommendations

The SQL Tuning Advisor tunes each of the candidate SQL statements in the order of their performance impact. If a SQL profile is found for a statement, the advisor verifies that the base optimizer statistics are current for the SQL profile. If not—that is, if the advisor finds stale or missing statistics—it passes along this information to the GATHER_STATS_JOB job, which gathers statistics.The advisor makes different types of recommendations and reports them, but it can automatically implement only those recommendations that involve the creation of SQL profiles.In order for the advisor to automatically implement SQL profiles, you must set the accept_sql_profiles task parameter to true when executing the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER procedure. The other types of recommendations (besides SQL profiles) include the creation of new indexes, refreshing stale statistics, and restructuring SQL statements.

Testing the Recommendations for New SQL Profiles For each SQL profile it recommends, the automatic SQL Tuning Advisor will run the problem statement with and without the SQL profile and compare the performance. The database will accept a new SQL profile only if it offers a potential performance improvement of at least threefold. The advisor creates all SQL profiles in the standard EXACT mode.The SQL Tuning Advisor will have to consider implementing a new SQL profile only if the profile results in a new explain plan for a SQL statement. The advisor will implement a new SQL profile after tests reveal that it will result in at least a threefold improvement in performance. The benefit is computed by summing the CPU and I/O time savings that result from the adoption of the new SQL profile. The advisor will not only look for a threefold improvement in the sum of the CPU and I/O usage, but it also stipulates that neither statistic must become worse as a result of implementing the new SQL profile. Thus, the adoption of a new SQL profile by the advisor will ensure that the SQL statement in question will run much faster.

The benefit percentage for each recommendation uses the formula
benefit%=(time_old – time_new)/(time_old).
Implementing the SQL Profiles That Meet the Criteria

Besides the threefold improvement in performance, a new SQL profile may have to satisfy other requirements as well, such as the objects involved in the query having recent optimizer statistics. Use the DBA_SQL_PROFILES view to determine which SQL profiles have been automatically implemented by the database. The value of the TYPE column in this view will be set to AUTO for all automatically implemented SQL profiles.

Configuring Automatic SQL Tuning

The DBMS_SQLTUNE package provides several procedures to control various aspects of the Automatic SQL Tuning task (SY_AUTO_SQL_TUNING_TASK). Use the SET_TUNING_TASK_PARAMETERS procedure to configure automatic SQL tuning in your database. You must log in as the user SYS to configure automatic SQL tuning because SYS owns the automatic SQL tuning task. The following are the various parameters you can specify for an automatic SQL tuning task, using the SET_TUNING_TASK_PARAMETERS procedure:

• accept_sql_profiles determines whether the database must automatically accept a SQL profile.
• replace_user_sql_profiles determines whether the task should replace the SQL profiles created by the user.
• max_sql_profiles_per_exec specifies the maximum number of SQL profiles that can be accepted for a single automatic SQL tuning task.
• max_auto_sql_profiles determines the total number of SQL profiles that are accepted by the database.
• execution_days_to_expire specifies the maximum number of days for which the database saves the task history. Default is 30 days.

Note：The five parameters shown here are applicable only to the automatic tuning task and not to the manual SQL tuning tasks.

The database implements Automatic SQL Tuning as an automated maintenance task some time after the start of the maintenance window. The job’s name is SYS_ AUTO_SQL_TUNING_TASK. The job first sorts the candidate SQL statements that it gets from the AWR. The job then tunes each statement according to its performance rank. If it creates a SQL profile recommendation for any candidate statement, it tests that profile before moving on to the tuning of the next SQL statement. By default, the automatic SQL Tuning Advisor runs for a maximum of one hour but you can change this by specifying a higher value for the time_limit parameter in the SET_TUNING_TASK_PARAMETERS procedure, as shown in this example:

SQL> exec dbms_sqltune.set_tuning_task_parameters
('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 14400);
In the example, he Automatic SQL Tuning task will run for 4 hours (14400 seconds) during a maintenance window.

You have other two following procedure to control automatic sql tuning task.
EXECUTE_TUNING_TASK: Use this to manually run the turning task in the foreground.
REPORT_TUNING_TASK: Lets you produce a text report of the task execution.
Managing Automatic SQL Tuning

The Automatic SQL Tuning process runs as part of a single automated maintenance task on a single instance, during the maintenance window. Use the new PL/SQL package DBMS_AUTO_TASK_ADMIN to manage all automated maintenance tasks including the automatic SQL tuning task. For example, you can enable the automatic SQL tuning advisor task using the ENABLE procedure, as shown here:

BEGIN
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
END;
/

The window_name attribute indicates the sql tuning advisor task is enabled for MONDAY_WINDOW maintenance window,if its value is NULL,means it's enable for all the maintenance windows.You can also disable the tuning task by using dbms_auto_task_admin.disable procedure:

BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

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

BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
END;
/

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.
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, -- 'TEXT'
level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
section => DBMS_SQLTUNE.section_all, -- 'ALL'
object_id => NULL,
result_limit => NULL);
END;
/
SET LONG 1000000
PRINT :l_report
L_REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 31
Current Execution : EXEC_1_25
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 01/16/2008 22:00:06
Completed at : 01/16/2008 22:00:46
Number of Candidate SQLs : 0
Cumulative Elapsed Time of SQL (s) : 0
-------------------------------------------------------------------------------

The text report produced by the REPORT_AUTO_TUNING_TASK procedure contains all SQL statements that the Automatic SQL Tuning Advisor analyzed in its last execution, including both the implemented as well as the unimplemented recommendations made by it. The text report contains the following sections:

• General information section describes the task and the inputs for the report and the number of SQL profiles created.
• Summary section lists the tuned SQL statements, the estimated benefits for each SQL profile, and the test execution statistics for the SQL statements after incorporating the SQL profiles.
• Tuning Findings section shows all findings and information as to whether the profiles were accepted or not.
• Explain Plans section shows explain plans for the SQL statements both before and after tuning.
• Errors section lists all errors produced during the tuning task.

You can also query following Data Dictionary to view the automatic tuning task info.

• DBA_ADVISOR_EXECUTIONS: Shows metadata information for each task.
• DBA_ADVISOR_SQLSTATS: Shows a list of all SQL compilation and execution statistics.
• DBA_ADVISOR_SQLPLANS: Shows a list of all SQL execution plans.

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》