• Dbms_sqltune.execute_tuning_task 12c
• Exec Dbms_sqltune.execute_tuning_task('sys_auto_sql_tuning_task')
• Dbms_sqltune.execute_tuning_task
• Dbms_sqltune.execute_tuning_task( 'sys_auto_sql_tuning_task')
• Oracle Dbms_sqltune.report_tuning_task

Mar 30, 2005  With the tuning task defined, the next step is to execute it using the EXECUTETUNINGTASK procedure: EXEC DBMSSQLTUNE.executetuningtask(taskname = 'empdepttuningtask'); During the execution phase, you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:- Interrupt and resume a tuning task. Oct 31, 2012  The DBMSAUTOSQLTUNE package is the interface to SQL Tuning Advisor (DBMSSQLTUNE) when run within the Autotask framework. The database creates the automated system task SYSAUTOSQLTUNINGTASK 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. Automatic SQL Tuning in Oracle Database 11g Release 2 (DBMSAUTOSQLTUNE) Oracle Database 11g Release 2 (11.2.0.2 onward) has some minor changes to the Automatic SQL Tuning feature introduced on Oracle 11g Release 1. In the previous release, reports and amendments to the automatic tuning task parameters was performed using the DBMSSQLTUNE package.

140 DBMSSQLTUNE. The DBMSSQLTUNE package is the interface for tuning SQL on demand. The related package DBMSAUTOSQLTUNE package provides the interface for SQL Tuning Advisor run as an automated task. F0BD30 7294 package body SYS.DBMSSQLTUNEINTERNAL 000000044F365B58 8 SYS.WRI$ADVSQLTUNE 000000044BD3CEB8 545 package body SYS.PRVTADVISOR 000000044BD3CEB8 2597 package body SYS.PRVTADVISOR A528D0 241 package body SYS.DBMSADVISOR EED340 702 package body SYS.DBMSSQLTUNE. SQL exec dbmssqltune.executetuning task. ORA-13605: The specified task or object SYSAUTOSQLTUNINGTASK does not exist for the current user.

Begin automatic SQL Tuning Advisor run for special tuning task 'SYSAUTOSQLTUNINGTASK' Thu May 29 22: Thread 1 advanced to log sequence 16574 (LGWR. The DBMSSQLTUNE package provides several procedures to control various aspects of the Automatic SQL Tuning task (SYAUTOSQLTUNINGTASK). EXECUTETUNINGTASK: Use this to.

Oracle automatic SQL tuning is a misnomer, a product of the Oraclemarketing machine.

As I note in my book 'OracleTuning: The Definitive Reference', SQL tuning is extremely complex,and Oracle has never made a SQL tuning tool that can make anything more thansimple recommendations for missing indexes and materialized views.

The first attempt 'Oracle Expert' was a total joke, a resourcehungry tool that cranked for hours and then made ridiculousrecommendations. In Oracle 11g,Oracle has made some progress toward automatic SQL tuning by applyingreal-world workloads and measuring changes to execution plans, but it stilltunes with about the same skill as a trained dolphin.

Just like it's predecessors, the Oracle automatic SQL tuning tool stillmakes ludicrous recommendations, but it is a step in the right direction. The process of tuning SQL is a semi-structured task, where some decision rulesexist, but it still requires human intuition and there is no technology thatwill approach the ability of a human SQL tuning expert.

Today, 'fully automatic SQL tuning' is a DSS tool, as it shouldbe. In a decision support system, the tools managed the easy,well-structured components of the SQL tuning process (the benchmark testing),and the human expert sets-up the tuning tests.
The human expert provides the intelligence and parameters, and Oracle 'SQLTuning Advisor' grabs the real-world workload and manages thewell-structured component of the tuning process.

For more, see my related notes on Oracle automatic SQL tuning:

1.Integrationof SQL Plan Management with Automatic SQL tuning

2.AutomaticSQL Tuning with the SQL Tuning Advisor

3.Oracle11g fully automated SQL tuning tips

4.How AutomaticSQL Plan Management Works

5.11g CasesScenarios for Automatic SQL tuning

###########################

Oracle 11g New Features Tips

The next step is also already implemented in Oracle database 11g. It is called Automatic SQL Tuning.With this feature enabled, Oracle runs an automatic task job every night in amaintenance window. It is also called Automatic SQL Tuning Advisor andsearches for high load SQL, then starts automatically tuning the worststatements. Output could be recommendations about missing indexes for instance,and if allowed, automatic implementation of automatically created and testedSQL Profiles. This is not enabled by default. The job would then feed the SQLplan baselines with the plans from the automatically created and implementedSQL profiles and mark the new plans as ENABLED and ACCEPTED.

The SYS_AUTO_SQL_TUNING_TASKruns in the daily maintenance window and can be monitored with the datadictionary view DBA_ADVISOR_EXECUTIONS.The Automatic SQL Tuning job can be managed via the built in package DBMS_AUTO_TASK_ADMIN. Thechoice can also be made to not fix the plan in the new baseline and allow theoptimizer from now on to evolve the plan baseline and use new best cost plansfound for the statement, but only if it has been verified that they do notcause performance regression.

If the Automatic Tuning jobfinds a better plan and implements a SQL Profile automatically and automaticimplementation of SQL Profiles has been allowed, it adds the new plan to theplan baseline but does not verify existing unaccepted plans. The criteria forautomatic implementation of SQL profiles and feeding new plans into the planbaselines would be that the improvement of the new plan sums up to at leastthree times less cost (sum of CPU and I/O time). Only High Load Repeatable SQLis automatically tuned. Who cares about low load SQL?

The syntax to enable AutomaticSQL Tuning is as follows:

LUTZ AS SYSDBA @ prod11g1 SQL> BEGIN

dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES', 'TRUE');

END;

% The default value for ACCEPT_SQL_PROFILESis FALSE!

It is also possible to adjustthe Automatic SQL Tuning Task in the graphical interface:

Figure 29: Automatic SQLTuning Settings Screen

For more detailed informationon Automatic SQL Tuning, please refer to Chapter 6 in this book.

There have been multipletechniques to control execution plans available for quite a while. Theseinclude hints, stored outlines and SQL profiles. What all of these methods havein common is that they need manual intervention since they are fixed remedieswhich do not adjust to changed demands automatically. Also, they can only beused after the problem has actually occurred, so they are reactive methods.

SQL Plan management is aproactive approach to possible upcoming problems and can help to ensure thatperformance regression is prevented and only execution plans which aresignificantly better than the 聴ell tuned and well known� plans can be used by the optimizer.

Automatic SQL Tuning Using SQL Tuning Advisor
June 17, 2005
Mark Rittman

Automatic SQL Tuning using SQL Tuning Advisor: 'SQL tuning is one of the challenging tasks faced by DatabaseAdministrators. It is an interesting and creative, but at the same time,daunting task. Manual tuning of SQL statements requires through knowledge ofhow the statements are executed in the background and experience to understandsuitable access paths to yield better response times. Moreover, in a typical application,there are just too many SQL statements to tune and advise the developers.Oracle Database 10gintroduces many useful and easy-to-use tuning tools and methodologies. In thisarticle, we will examine one of these new and improved features - SQL TuningAdvisor.' A good concise guide to this new Oracle 10g feature.

Automatic SQL Tuning using SQL Tuning Advisor

by Sreeram Surapaneni

Overview:

SQL tuningis one of the challenging tasks faced by Database Administrators. It is aninteresting and creative, but at the same time, daunting task. Manual tuning ofSQL statements requires through knowledge of how the statements are executed inthe background and experience to understand suitable access paths to yieldbetter response times. Moreover, in a typical application, there are just toomany SQL statements to tune and advise the developers.

OracleDatabase 10gintroduces many useful and easy-to-use tuning tools and methodologies. In thisarticle, we will examine one of these new and improved features - SQL TuningAdvisor.

SQL TuningAdvisor is primarily designed to replace the manual tuning of SQL statements orany third-party tools currently used by some DBAs. SQL Tuning Advisor examinespoorly executing SQL statements and evaluates resource consumption in terms ofCPU, I/O, and temporary space. The advisor receives one or more SQL statementsas input and provides advice on how to optimize their execution plans, givesthe rationale for the advice, the estimated performance benefit, and the actualcommand to implement the advice.

In itsnormal mode, the query optimizer needs to make decisions about execution plansin a very short time. As a result, it may not always be able to obtain enoughinformation to make the best decision. Oracle 10g allows the optimizer to run in tuning mode whereit can gather additional information and make recommendations about howspecific statements can be tuned further. This process may take several minutesfor a single statement so it is intended to be used on high-loadresource-intensive statements.

SQL TuningAdvisor also identifies any objects with missing statistics referenced in thequery. Thus, the advisor performs four distinct types of tasks:

• Checks if objects have valid, usable statistics for proper optimization
• Attempts to rewrite queries for better performance and suggests rewriting
• Checks the access path to see if performance could be improved by adding additional structures such as indexes and materialized views
• Creates SQL profiles and attaches them to specific queries.

In tuningmode, the optimizer performs the following analysis:

• Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
• SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible, the information is stored in an SQL profile. If accepted, this information can then be used by the optimizer when running in normal mode. Unlike a stored outline, which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it is sensible to update profiles periodically. The SQL profiling is not performed when the tuning optimizer is run in limited mode.
• Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path. Its index recommendations relate to a specific statement, so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
• SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact performance. The implementation of these suggestions requires human intervention to check their validity.

Using Enterprise Manager:

The belowexample was formulated to illustrate how to use SQL Tuning Advisor incommand-line mode, which is very useful for scripting these tasks proactively.In most cases, however, you will need to perform tuning in response to problemsreported by an end user. Enterprise Manager 10gcomes in handy in those cases.

From theDatabase home page, click on the link 'Advisor Central' at the bottomof the screen, which launches the page containing all of the advisors. Next,click on 'SQL Tuning Advisor' at the top of the screen as shown inFigure A.

Figure A: Advisor Central inEnterprise Manager

Now youhave launched the SQL Tuning Advisor. Choose 'Top SQL' from the nextpage as shown in Figure B.

Figure B: SQL Tuning Advisors

When youchoose Top SQL, it launches a page with a graph containing the various waitclasses, traced along a time dimension Dragging the mouse to a location on thegraph where the CPU wait is high, you would be able to see relevant SQLstatements and be able to identify areas with the highest activity and maximumCPU consumption.

Now wefocus on the PL/SQL API, as the Enterprise Manager interface is a reasonablyintuitive method. The statements to be analyzed can be retrieved from theAutomatic Workload Repository (AWR), the cursor cache, an SQL tuning set orspecified manually.

In order toaccess the SQL tuning advisor API, a user must be granted the ADVISORprivilege:

CONN sys/password AS SYSDBA

GRANT ADVISOR TO scott;

CONN scott/tiger

The firststep when using the SQL tuning advisor is to create a new tuning task using theCREATE_TUNING_TASK function.

SET SERVEROUTPUT ON

-- Tuning task created forspecific a statement from the AWR.

DECLARE

l_sql_tune_task_idVARCHAR2(100);

BEGIN

l_sql_tune_task_id :=DBMS_SQLTUNE.create_tuning_task (

begin_snap=> 764,

end_snap=> 938,

sql_id=> '19v5guvsgcd1v',

scope=> DBMS_SQLTUNE.scope_comprehensive,

time_limit=> 60,

task_name=> '19v5guvsgcd1v_AWR_tuning_task',

description =>'Tuning task for statement 19v5guvsgcd1v in AWR.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' l_sql_tune_task_id);

END;

/

-- Tuning task created forspecific a statement from the cursor cache.

DECLARE

l_sql_tune_task_idVARCHAR2(100);

BEGIN

l_sql_tune_task_id :=DBMS_SQLTUNE.create_tuning_task (

sql_id=> '19v5guvsgcd1v',

scope=> DBMS_SQLTUNE.scope_comprehensive,

time_limit=> 60,

task_name=> '19v5guvsgcd1v_tuning_task',

description =>'Tuning task for statement 19v5guvsgcd1v.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' l_sql_tune_task_id);

END;

Dbms_sqltune.execute_tuning_task 12c

/

-- Tuning task created froman SQL tuning set.

DECLARE

l_sql_tune_task_idVARCHAR2(100);

BEGIN

l_sql_tune_task_id :=DBMS_SQLTUNE.create_tuning_task (

sqlset_name =>'test_sql_tuning_set',

scope=> DBMS_SQLTUNE.scope_comprehensive,

time_limit=> 60,

task_name=> 'sqlset_tuning_task',

description =>'Tuning task for an SQL tuning set.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' l_sql_tune_task_id);

END;

/

-- Tuning task created for amanually specified statement.

DECLARE

l_sqlVARCHAR2(500);

l_sql_tune_task_idVARCHAR2(100);

BEGIN

l_sql := 'SELECT e.*, d.* '

Exec Dbms_sqltune.execute_tuning_task('sys_auto_sql_tuning_task')

'FROMemp e JOIN dept d ON e.deptno = d.deptno '

'WHERENVL(empno, '0') = :empno';

l_sql_tune_task_id :=DBMS_SQLTUNE.create_tuning_task (

sql_text=> l_sql,

bind_list=> sql_binds(anydata.ConvertNumber(100)),

user_name=> 'scott',

scope=> DBMS_SQLTUNE.scope_comprehensive,

time_limit=> 60,

task_name=> 'emp_dept_tuning_task',

description =>'Tuning task for an EMP to DEPT join query.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' l_sql_tune_task_id);

END;

/

If theTASK_NAME parameter is specified, its value is returned as the SQL tune taskidentifier. If omitted, a system generated name such as 'TASK_1478'is returned. If the SCOPE parameter is set to scope_limited the SQL profilinganalysis is omitted. The TIME_LIMIT parameter simply restricts the time theoptimizer can spend compiling the recommendations.

Thefollowing examples will reference the last tuning set, as it has no externaldependencies other than the SCOTT schema. The NVL in the SQL statement was putin to provoke a reaction from the optimizer. In addition, we can delete thestatistics from one of the tables to provoke it even more:

EXECDBMS_STATS.delete_table_stats('SCOTT','EMP');

With thetuning task defined, the next step is to execute it using theEXECUTE_TUNING_TASK procedure:

EXECDBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

During theexecution phase, you may wish to pause and restart the task, cancel it or resetthe task to allow it to be re-executed:

-- Interrupt and resume atuning task.

EXECDBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');

EXECDBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.

EXECDBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning taskallowing it to be re-executed.

EXECDBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

The status of the tuningtask can be monitored using the DBA_ADVISOR_LOG view:

SELECT task_name, statusFROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAMESTATUS

-----------------------------------------

emp_dept_tuning_taskCOMPLETED

1 row selected.

Once thetuning task has executed successfully the recommendations can be displayedusing the REPORT_TUNING_TASK function:

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECTDBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROMdual;

SET PAGESIZE 24

In thiscase, the output looks like this:

RECOMMENDATIONS

--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name: emp_dept_tuning_task

Scope: COMPREHENSIVE

Time Limit(seconds): 60

Completion Status: COMPLETED

Started at: 05/06/2004 09:29:13

Completed at: 05/06/2004 09:29:15

-------------------------------------------------------------------------------

SQL ID: 0wrmfv2yvswx1

SQL Text: SELECT e.*, d.*FROMemp e JOIN dept d ON e.deptno =d.deptno

WHERENVL(empno, '0') = :empno

-------------------------------------------------------------------------------

FINDINGS SECTION (2findings)

-------------------------------------------------------------------------------

1- Statistics Finding

---------------------

Table 'SCOTT'.'EMP' andits indices were not analyzed.

Recommendation

--------------

Consider collecting optimizer statisticsfor this table and its indices.

executedbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>

'EMP', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNSSIZE AUTO', cascade => TRUE)

Rationale

---------

The optimizer requires up-to-datestatistics for the table and its indices

in order to select a good execution plan.

2- Restructure SQL finding(see plan 1 in explainplans section)

----------------------------------------------------------------

The predicateNVL('E'.'EMPNO',0)=:B1 used at line ID 2 of the executionplan

contains an expression on indexed column'EMPNO'. This expression prevents

the optimizer from selecting indices on table'SCOTT'.'EMP'.

Recommendation

--------------

Rewrite the predicate into an equivalentform to take advantage of

indices. Alternatively, create afunction-based index on the expression.

Rationale

---------

The optimizer is unable to use an index ifthe predicate is an inequality

condition or if there is an expression oran implicit data type conversion

Dbms_sqltune.execute_tuning_task

on the indexed column.

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

1- Original

-----------

Dbms_sqltune.execute_tuning_task( 'sys_auto_sql_tuning_task')

Plan hash value: 1863486531

----------------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

----------------------------------------------------------------------------------------

0 SELECT STATEMENT 1 107 4(0) 00:00:01

1 NESTED LOOPS 1 107 4(0) 00:00:01

2 TABLE ACCESS FULL EMP 1 87 3(0) 00:00:01

3 TABLE ACCESS BY INDEX ROWID DEPT 1 20 1(0) 00:00:01

4 INDEX UNIQUE SCAN PK_DEPT 1 0(0) 00:00:01

----------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

-------------------------------------------------------------------------------

1 row selected.

3utools failed to load page. Manages docking & desktop by mouse.a. Photos and Ablums management: Imports and exports.8. Docking & desktop Folders and positions backup and restore.9. Smart classify supports automatic classify icons into folders named by system category.b.

Once thetuning session is over the tuning task can be dropped using theDROP_TUNING_TASK procedure:

BEGIN

DBMS_SQLTUNE.drop_tuning_task (task_name=> '19v5guvsgcd1v_AWR_tuning_task');

DBMS_SQLTUNE.drop_tuning_task (task_name=> '19v5guvsgcd1v_tuning_task');

DBMS_SQLTUNE.drop_tuning_task (task_name=> 'sqlset_tuning_task');

DBMS_SQLTUNE.drop_tuning_task (task_name=> 'emp_dept_tuning_task');

END;

/

Conclusion

SQL TuningAdvisor is a powerful tool that has the 'brains' to analyzeperformance metrics and offer recommendations based on best practices and acceptedmethodologies professed by seasoned Oracle professionals, all automatically.This functionality can tell the DBA not only what happened and why, but mostimportant, what to do next.

Sreeram Surapaneni is an Oracle Certified DBA(7.3,8,8i,9i) working currently as DBA at Research In Motion Ltd, Canadaand can be reached at Sreeram Surapaneni.

This is a work in progressexcerpt from the book 'Oracle11g New Features' by Rampant TechPress.

Oraclehas touted their new 11g滞olistic� approach to SQL tuning as 対ully automated SQL tuning�, but the marketing hype must beseparated from the reality. The main benefit is that the DBA can now testchanges to global parameters against a real-world workload, using a SQL TuningSet (STS). Holistic tuning in Oracle 11g is offered through several functional areas, mostimportantly the SQL Performance Advisor (SPA) and the automated SQL PlanManagement (SPM) facility. SPA is the natural evolution of the SQLAccessadvisor:

• 10g SQLAccess Advisor � The 10g SQL access advisor tests real-world SQL workloads, recommending missing indexes and materialized views.
  
• 11g SQL Performance Analyzer � The SPA takes the SQLAccess advisor one step further and implements tuning recommendations for any SQL statements that run 3x faster (when tested with a new workload). For more details, see Inside the 11g SQL Performance Advisor .

How fully automated SQL tuning works

In anutshell, the 11g fullyautomated SQL tuning is a series of processes and tools, loosely coupled formaximum flexibility.

A �TheSetup for fully automatic SQL tuning � Here we capture representative SQLworkloads (SQL tuning sets) and set-up a testing environment:

1 - Define the SQL workload - The DBAdefines a 'set' of problematic SQL statements (or chooses arepresentative workload). This is called the SQL Tuning set, orSTS. This uses the dbms_sqltune.create_sqlsetpackage.

2 - Set-up a changed environment - Here youcan chose to change your initialization parms, test your performance against aprevious release of the CBO (a very useful features when testing upgrades) orconduct 'custom experiments' on the effect of environmental changeson your SQL tuning set.

B �InitialSQL tuning�Using the SQL Performance Analyzer, we optimize our environment using the SQLtuning set:

1 - Schedule & run workload tests - Theworkload is scheduled for execution during 'low usage' periods, sothat an empirical sample of real-world execution times can be collected andcompared, using different execution plans from the two SQL tuning sets. To do this we run the dbms_sqlpapackage. You can also use the OEM SPA 専uidedWorkflow�wizard.

2 - Implement the changes - For anystatements that execute more then 3x faster, after the changes, Oracle 11g will automatically implement the changesvia 'SQL Profiles', a tool that bypasses the generation of anexecution plans for incoming SQL, replacing it with the pre-tuned access plan.

C �GatherBaseline - Create the SQL Plan Baseline - To enable automatic SQL plancapture, set the optimizer_capture_sql_plan_baselines initializationparameter to TRUE.

D �Regressiontesting and implementation� Wetest global changes with the SQL Plan Manager (SPM). As the systemcharacteristics change, you can use the SQL Plan Manager to test against realworkloads and ensure that all changed execution plans result in at least 3xfaster performance.

Thisis a huge improvement over the hit-and-miss SQL tuning techniques of the past,but it旧 not a truly 対ully-automated� approach either. We mustremember that there will always be 団utlier� SQL statements that must be tunedmanually.

Fully Automated SQL Tuning is not a Panacea

Thereare many internal and external factors that influence the elapsed time for agiven SQL statement, and the 11gSQL Performance Analyzer (SPA) and SQL Plan Management (SPM), the DBA canestablish an 'optimal baseline' before diving into the tuning ofindividual SQL statements:

• Optimize the server kernel - Always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency. Kernel settings have an indirect effect on SQL performance. For example, a kernel setting may speed up I/O, a change which is noted by the CBO workload statistics (using dbms_stats.gather_workload_stats). This, in turn, directly influences the optimizer's access decisions.
  
• Adjust your optimizer statistics - Always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent SQL access plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates. Also new in 11g, multi-column statistics can be gathered for use by the optimizer to determine optimal ways to run queries based upon multiple column criteria.
  
• Adjust optimizer parameters� You can empirically determine the best settings for optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
  
• Optimize the instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, etc.) can influence SQL performance.
  
• Tune with indexes and materialized views - Just as the 10g SQLAccess Advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a godsend for SQL tuning.

Oracle 11g New Features Tips

In version 10g, Oraclehas introduced the new mandatory SYSAUX tablespace. It is the default location for a number of tools whichhad their own tablespaces before 10g,such as Oracle Text, Ultra Search, and Statspack and some more. These toolstablespaces have all been consolidated into the SYSAUX tablespace per default.

Alsowith version 10g, Oraclehas introduced the Automatic Workload Repository (AWR), which is a kind of datawarehouse for historical performance statistics. Oracle 10 creates performancesnapshots in memory statistics from the SGA per default every one hour andretains them in the AWR for seven days if these settings are not adjusted.

Thereare historical statistics about waits and resource consumption in the database,even across shutdown and startup operations. Beside the AWR in an 11g database, another structure is located inthe SYSAUX tablespace. It is a special infrastructure which is called SQLManagement Base (SMB). It holds the SQL Plan Baselines as well as SQL Profileswhich are created by the SQL Performance Analyzer that is implemented with thepackage DMBS_SQLTUNE. Sql Profiles were introduced with Oracle 10gR1. Inan Oracle 10g database,they are stored in the SYSTEM tablespace. There was no such thing as SMB in 10g. SQL profiles are a reactive methodfor fixing performance problems of SQL statements. In 10g this is always done manually by the DBA.

Asof 11g, Automatic SQL Tuninghappens every night via an automatic task job if the choice is to allow theserver to not only create SQL Profiles but also implement them automatically.This will be covered more at the end of this chapter.
M If the SYSAUX tablespace is offline in 11g, this can cause significant performanceimpact because needed SQL Profiles are not accessible. This couldnot happen in 10g!
Another reactive method to implement plan stabilityis the use of Stored Outlines.

For backward compatibility,Oracle uses existing stored outlines. A plan generated using a stored outlineis not stored in the SMB even if automatic plan capture is enabled for thesession. Stored Outlines have been deprecated in 11g and have been replaced by SQL plan managementfeatures.

Also beginning in 11g, a SQL Log forall statements as well as a SQL Plan History for repeatable SQL is maintainedin the SMB by the optimizer along with the SQL plan baselines.

Oracle Dbms_sqltune.report_tuning_task

By default, Oracle uses SQLplan baselines but does not automatically capture new plans.

Figure 26: Schematic of SYSAUXTablespace

The above graphicshows the AWR and the SMB in the SYSAUX tablespace. This tablespace must becreated with Automatic Segment Space Management (ASSM) enabled to allow automatic purging tasks to maintain theSMB. If the SYSAUX tablespace is offline, the optimizer cannotmaintain the SQL Log and the SQL Plan History and cannot use SQL PlanBaselines.

Oracle uses two new parametersto manage the SMB:

• optimizer_capture_sql_plan_baselines
• optimizer_use_sql_plan_baselines

LUTZ AS SYSDBA @ orcl11g SQL> show parameter plan_base

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

It is possible to feed SQLexecution plans manually into the SQL plan baselines. By this, an executionplan can be fixed and thus force the optimizer to use it. The manual SQL planmaintenance will be reviewed later in this chapter.

MSettingoptimizer_use_sql_plan_baselinesto FALSE disables SQLPlan Management.

M Settingoptimizer_capture_sql_plan_baselines to TRUEenables automatic capturingof SQL plans.

Pedal steel guitar vst free. Both parameters are dynamicallychangeable:

LUTZ AS SYSDBA @ racdb11g1 SQL> ALTER SYSTEM SEToptimizer_capture_sql_plan_baselines= TRUE;

From now on, the optimizerstarts capturing SQL execution plans automatically. A list of SQL_IDs, or theSQL log, is used to check if a statement has been executed before.

If a statement is parsed forthe first time, its SQL_ID gets inserted into the SQL log in the SMB. For eachstatement which is executed multiple times, the optimizer starts maintaining aSQL plan history. There is a plan history for every repeatable SQL statement inthe SMB.

The plan history includes allrelevant information the optimizer has used to produce an execution plan andthus makes it reproducible. The information in the plan history includes thefollowing:

• SQL text
• outline
• bind variables
• compilation environment

When a statement is parsed, the optimizer creates a best cost plan usingobject statistics from the data dictionary for the new statement. If it isparsed the first time, a SQLplan baseline is also created in the SMB for the new statement.The next time, the same statement is reparsed for whatever reason, so again abest cost plan is created. Before this plan can be used, the optimizer firstchecks if the same plan has been created before and if it is in the statementsplan baseline. If this is the case, the statement is executed with this plan.Subsequently created differing plans for the same statement are added to theplan historyand marked for verification. They have thestatus ENABLED but not ACCEPTED and cannot be used for execution as long it hasnot been checked if they do not cause performance regression.

The verification is a taskwhich can be performed manually by the DBA. It is called SQL plan evolution. Itwill be shown later that this can also be made manually using dbms_spm.evolve_sql_plan_baseline.The plan baselines can also be loaded manually from the cursor cache or SQLTuning Sets. Only plans with status ENABLED and ACCEPTED are part of the SQLplan baseline. All other plans are part of the plan history.

SQL planevolution can also happen automatically via a new automatic maintenance task.Oracle runs an automatic SQL tuning job every night which can create SQLProfiles automatically and implement them, therefore populating SQL planbaselines. This will be covered more at the end of this section.

Oracle 11g New Features Tips

Good candidate situations wouldcertainly be any upgrades of the Oracle 11g database system. Again, use a testing system withchanged environment, possibly a Snapshot Standby Database, and run theapplication for a representative period of time. During this period, Automatic SQL Plan Management would be enabled and Oracle would evolvethe SQL Plan Baselines for our application.

After this evolution, it ispossible to use dbms_spm to create a staging table and then expdpto EXPORT the captured baselines and import them into the production systemdirectly after upgrading. This would guarantee stable execution plans from thevery beginning. How this is done will be shown in detail shortly.

Another scenario would be thedeployment of a new application. The application vendor would ship well tunedplans in the form of exported SQL plan baselines together with the applicationwhich could be imported after the application was added to the database system.This would guarantee that the good plans would be used right away from the verybeginning. Oracle would use new plans only if they were verified to not causeperformance degradation.

A variation of this case couldbe using an 11g databaseas a testing system and IMPORT SQL Tuning Sets using a staging table from a pre-11g databasesystem. Bysetting optimizer_features_enable to a pre-11g value, Oracle finds the best execution plans forthe workload on the 11g testingsystem. After this, use the SQL Tuning set packed into a staging table againand then import them back into the pre-11g database.
SQL Plan Management in OEM

The enterprise manager comeswith an interface for SQL plan management and offers full control of SQL planevolution.

This shows the link to SQL PlanControl for the SMB in the Query Optimizer section of the SERVER pane. Alsofind the SQL Profiles and SQL Patches.