SQL performance analyzer in Oracle 11g

SQL Performance Analyzer
SQL Performance Analyzer

SQL performance analyzer performs a similar job to the SQL Tuning Advisor with one major difference, SQL performance analyser allows you to compare the performance of SQL statements before and after a change, allowing you to predict the impact of changes to the system on SQL performance as well as pre tuning your database before making changes.

So, The SQL Tuning Advisor is designed to help tune SQL in the system as it currently exists where as the SQL Performance Analyzer is designed to help determine the impact of system changes to SQL performance.

It allows you to compare a wide range of changes from operating system changes to database upgrades and down to the level of index changes.

The recommended approach would be to replecate your productoin system and run your tests on that system.

The basic procedure for using SQL Performance Analyzer is as follows:

  1. Capture SQL workload – Load the required SQL into a tuning set.
  2.  Set up test system РSo that you do not need to make changes to the live system.
  3. Move SQL tuning set to new system.
  4. Create SQL Performance Analyzer task – by associating your SQL tuning set with a SQL performance analyzer task.
  5. Build trial by executing SQL – Get a baseline to compare the post change performance to.
  6. Make System Change
  7. Run Post change trial
  8. Compare changes
  9. Tune as necessary – The report generated includes guidance and suggestions on tuning the SQL for the new changes.


A SQL tuning set is a database object that includes one or more SQL statements , along with their execution statistics and execution context.

SQL statements can be loaded into a SQL tuning set from many different sources. It’s possible to use the cursor cache, Automatic Workload Repository (AWR), or existing SQL tuning sets.

Using a SQL tuning set enables you to:

  • Store the SQL text and any necessary auxiliary information in a single, persistent database object
  • Populate, update, delete, and select captured SQL statements in the SQL tuning set
  • Load and merge content from various data sources, such as the Automatic Workload Repository (AWR) or the cursor cache
  • Export the SQL tuning set from the system where the SQL workload is captured and import it into another system
  • Reuse the SQL workload as an input source for other advisers, such as the SQL Tuning Adviser and the SQL Access Adviser


Useful SQL Performance Adviser views:




A great article with examples can be found on Oracle Base















CREATE_SQLSET – creates tuning set object in DB

LOAD_SQLSET – populates tuning set

CREATE_SGTAB_SQLSET – creates staging table

PACK_SGTAB_SQLSET – copy tuning set from SYS schema to staging table

UNPACK_SGTAB_SQLSET – copy tuning set from staging table to sql tuning set schema





DBMS_SQLPA package:














Automatic SQL Tuning in Oracle 11g


The Automatic Database Diagnostic Monitor in Oracle 11g
The ADDM in Oracle 11g

The automated tuning process in Oracle 11g is implemented by a number of functions in the Oracle database:

Automatic Database Diagnostic Monitor (ADDM)

Takes input from the AWR reports which it analyses in order to identify potential bottlenecks. If the statistics level parameter is set to TYPICAL or ALL then the ADDM is ran every time an AWR snapshot is taken.

Reports can be vierwed through Enterprise manager but can also be generated with the addmrpt.sql script located at $ORACLE_HOME/rdbms/admin/addmrpt.sql

SQL Tuning Advisor

The optimizer can run in 2 modes, Normal and Tuning. In Normal mode, the optimizer must produce a plan in a very short timeframe and so has to accept the information that it has is up to date and accurate. The alternative is Tuning mode where the optimizer is given more time so that it can perform analysis and gather more information in rder to produce a more accurate and efficient plan.

The ADDM identifies SQL that could be improved and passes it to the SQL Tuning Advisor. This invokes the Automatic Tuning Optimizer and evaluates possibilities for improvement. The automatic Tuning Advisor performs a range of different types of analysis including statistics analysis (gathering stats), SQL profiling (creating new SQL profiles), Access path analysis (adding aindexes to allow faster access to data) and SQL structure analysis (actually rewriting or rather suggesting options for rewriting SQl statements).

From Version 11g, the Automatic SQL Tuning Advisor is ran (by default) during the nightly maintanance window. While it does not automatically implement suggestoins, it can be set up to do so.

You can use the ‘ENABLE‘ or ‘DISABLE‘ procedures of the DBMS_AUTO_TASK_ADMIN package to let the automatic tuning job run or not. Setting the STATISTICS_LEVEL to BASIC will also disable it because that stops stats gathering be the AWR.

— Enable



client_name => ‘sql tuning advisor’,

operation => NULL,

window_name => NULL);



When it does run, the procedure is as folows:

  1. Candidate SQL queries are identifed from the AWR. Only statements with execution plan that has a high potential for improvement and a high impact on the system are considered.
  2. Each statementnd is invidually tuned by calling the SQL Tuning Advisor. The only recommendation that can be automatically implemented are SQL profiles.
  3. SQL profiles are implemented when:
    • There is at least 3 fold improvement.
    • ACCEPT_SQL_PROFILES task parameter is TRUE

These profiles will have a type of AUTO in DBA_SQL_PROFILES

How to Setup and Configure Automatic SQL Tuning

Automatic SQL tuning task can be configured with the DBMS_SQLTUNE package. Only SYS can do this.

You can turn on or off the acceptance of automatically generated SQL profiles with the SET_TUNING_TASK_PARAMETER procedure.



parameter = > ‘ACCEPT_SQL_PROFILES’,

value = > ‘TRUE’);


 Additional parameters:

  • ACCEPT_SQL_PROFILE – automatically accept profiles?
  • MAX_SQL_PROFILES_PER_EXEC – maximum number of profiles to evaluate per execution (default 20).
  • MAX_AUTO_SQL_PROFILES – total auto profiles allowed on the system (default 1000).
  • EXECUTION_DAYS_TO_EXPIRE – default 30 days – how long to save task history

Viewing Automatic SQL Tuning Reports

You can view the Automatic SQL Tuning Reports through Enterprise Manager as well as via the DBMS_SQLTUNE package – DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK



: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);




SET LONG 1000000

PRINT :l_report

There are several useful views that can be referred to and are worth exploring to gain more information on Automatic SQL Tuning.

  • DBA_ADVISOR_ACTIONS – Actions associated with recommendations
  • DBA_ADVISOR_EXECUTIONS – Metadata for task executions


SQL Plan Baselines.

SQL plan baselines are a new feature introduced in Version 11g.

SQL execution plans determine how a query is executed and the path that is taken to retrieve the data. Plans can be evolved or improved over executions and should only change when a better plan can be found.

When a query is hard parsed, the cost based optimizer creates an execution plan. In the absence of any other control, this plan would be used. However, circumstances can lead to different plans being generated with some being much more efficient than others.

The purpose of SQL plan baselines is to make sure that a less optimal execution plan is never used and that changes in the database such as statistics being gathered, upgrades or patches will not lead to slower plans being adopted.

In fact, when SQL plan baselines are in use, a new plan will not become ‘Accepted’ and become available for use untill it has been demonstrated to be more efficient than the old plan.

Here are a couple of good articles on SQL plan baselines and SQL plan management

Difference between SQL plan Baselines and SQL profiles.

SQL plan management


SQL plan baselines are stored in the SYSAUX tablespace and by default can use up to 10% of that tablespace. That figure can be manually set between 1% and 50% using the command – DBMS_SPM.CONFIGURE(‘space_budget_percent’,30); – to set it to use 30% of SYSAUX.

If the space limit is exceeded a message is generated into the alert log.

The length of time that unused plans are kept can be set by setting the retention period. This setting can be between 5 and 523 weeks.

Set it with the DBMS_SPM package: DBMS_SPM.CONFUGURE(‘plan_retention_weeks’,105)

The current settings for ‘space budget’ and ‘retention time’ can be can be seen in the DBA_SQL_MANAGEMENT_CONFIG view:


So, the basic premise is that changes in the execution plan that are an improvement should be accepted but changes for the worse should be rejected.

It is important that untested plans are not used until proven to be an improvement. This is because it is preferable to use an accepted plan that is suboptimal rather than risk a dramatic drop in performance caused by a poor new plan.

Only plans that are set as ‘ACCEPTED’ within the plan baseline can be used.

The first time a piece of SQL is ran, if it has no prior plan in place, it will automatically have the first generated execution plan used and it will become ‘ACCEPTED’ in the baseline.

Additionally, if a change occurs that appears to invalidate all of the ‘ACCEPTED’ plans, the plan with the lowest cost will be used and set as ‘ACCEPTED’ in the same way as when no plans are included.

Subsequently, if a new plan is generated and appears to be an improvement it will be saved but cannot be used until it is accepted.

An automatic task needs to be created and ran that tests all of the candidate execution plans to verify which is the fastest and that one will be set as ‘ACCEPTED’ and will be used as the current baseline. Note that subsequently, there may be more than one ‘ACCEPTED’ plan, so being ‘ACCEPTED’ does not mean that a plan will be used.

To set the database to automatically capture plans you must change the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (it is FALSE by default).

For SQL Plan Baselines to be used, you also need to set the parameter: OPTIMIZER_USE_PLAN_BASELINES = true/false (default true)

You can also load plans manually if you want to force a particular plan into a baseline. This can be done from a number of sources, SQL Tuning Sets, AWR snapshots and the Cursor Cache. Manually added plans are set as ACCEPTED immediately.

To load a plan from a SQL set:

First load into SQL set with CREATE_SQLSET and LOAD_SQLSET then

load the plan from the SQL set:


For example:



l_plans_loaded PLS_INTEGER;


l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(

sqlset_name => ‘my_sqlset’);

END; /


Follow a similar procedure to load from the cursor cache:



l_plans_loaded PLS_INTEGER;


l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(

sql_id => ‘1fkh93md0802n’);

END; /


Evolving SQL Plan Baselines

As mentioned earlier, only ‘ACCEPTED’ plans can be uesd and apart from the first one, new plans are not accepted when they are created even if they appear to be more efficient.

There will be no change the the current accepted plans unless a new one has been tested and found to be an improvement. The process of testing plans is called ‘Evolving the Plan Baseline’.

There is a procedure to evolve baselines that must be ran. The normal method is to create a job which calls the procedure and have that job ran during off peak hours.


Fixed Plans

It is possible to set a plan as fixed (by setting the fixed attribute to yes). This essentially stops the baseline from evolving. A fixed plan takes precedence over a non fixed plan and no new plans are automatically added where a fixed plan exists, although you can manually add them, but for them to be used, you must manually alter them to FIXED


Displaying SQL Plan Baselines.

You can view SQL Plan Baselines with the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE procedure



FROM table( dbms_xplan.display_sql_plan_baseline(

sql_handle = > ‘SYS_SQL_109c10fabeeec639’,

format = > ‘basic’)



A nice discussion with clear examples can be seen here