Dbms_sqltune Execute_tuning_task Sys_auto_sql_tuning_task
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. DBMSSQLTUNE.EXECUTETUNINGTASK( taskname IN VARCHAR2, executionname IN VARCHAR2:= NULL, executionparams IN dbmsadvisor.argList:= NULL, executiondesc IN VARCHAR2:= NULL) RETURN VARCHAR2. 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.
Oracle automatic SQL tuning tips Oracle Tips by Burleson Consulting |
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
###########################
11g Integration of SQL Plan Management with Automatic SQL Tuning Oracle 11g New Features Tips by Burleson Consulting |
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
Murus vs little snitch. 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;
/
-- 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.* '
'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
on the indexed column.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
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.
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.
Oracle 11g fully automated SQL tuning tips
|
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.
11g How Automatic SQL Plan Management Works Oracle 11g New Features Tips by Burleson Consulting |
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.
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.
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.
Dbms_sqltune.execute_tuning_task
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.
11g Cases for Automatic SQL Plan Management Oracle 11g New Features Tips by Burleson Consulting |
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.
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.
Note:
This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).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
| Subprogram | Description |
|---|---|
Executes the Automatic SQL Tuning task immediately ( | |
Displays a text report of the automatic tuning task's history | |
Changes a task parameter value for the daily automatic runs |
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.
Syntax
Parameters
Table 28-2 EXECUTE_TUNING_TASK Function & Procedure Parameters
| Parameter | Description |
|---|---|
| A name to qualify and identify an execution. If not specified, it is generated by the advisor and returned by function. |
| 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_AUTO_TUNING_TASK_PARAMETER Procedures). |
| A 256-length string describing the execution |
Usage Notes
A tuning task can be executed multiple times without having to reset it.
Examples
REPORT_AUTO_TUNING_TASK Function
Dbms_sqltune.execute_tuning_task 12c
This procedure displays the results of an Automatic SQL Tuning task.
Syntax
Parameters
Table 28-3 REPORT_TUNING_TASK Function Parameters
| Parameter | Description |
|---|---|
| Name of the beginning task execution to use. If |
| Name of the ending task execution to use. If |
| Type of the report to produce. Possible values are |
| Level of detail in the report:
|
| Section of the report to include:
|
| Advisor framework object id that represents a single statement to restrict reporting to. |
| Maximum number of SQL statements to show in the report |
Return Values
A CLOB containing the desired report.
Exec Dbms_sqltune.execute_tuning_task('sys_auto_sql_tuning_task')
Examples
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.
Syntax
Parameters
Table 28-4 SET_AUTO_TUNING_TASK_PARAMETER Procedure Parameters
| Parameter | Description |
|---|---|
| Name of the parameter to set. The possible tuning parameters that can be set by this procedure using the parameter in the form
The following parameters are supported for the automatic tuning task only:
|
| New value of the specified parameter |