Home / Oracle DBA / Performance Tuning using SQL Tuning Advisor

Performance Tuning using SQL Tuning Advisor

SQL Tuning Advisor

SQL Tuning Advisor analyzes individual SQL statements, and recommend SQL profiles, statistics, indexes, and restructured SQL to SQL performance.

The optimizer uses the current object’s statistics and the SQL structure to generate the execution plan for a SQL statement. It is important that the optimizer determines the execution plan in a timely manner and that the execution of the plan for the statement performs well. Therefore, if the statistics don’t reflect the real characteristics of the objects, or the query is poorly written, or there are access structures missing (e.g. indexes) the optimizer may generate a Bad Sql Plan.

In addition to this “normal” mode of the optimizer, it can also be run in “tuning mode” using the SQL Tuning Advisor (STA). In the tuning mode the optimizer has plenty of time and resources to identify the BEST plan available. When statistics are not available, or when access path cannot be used because lack of indexes defined, the STA is able to collect auxiliary statistics on the objects involved or simulate new execution plan assuming the existence of indexes.

SQL Tuning Advisor looks into following things to generate the best execution plan:

  • Detect stale or missing statistics and generate auxiliary statistics to compensate for missing stats.
  • Determine a new execution plan and create SQL profile.
  • Detect missing indexes and suggest new indexes and materialized views.
  • Restructure SQL to generate a better execution plan.

SQL Profiles

  • SQL Profiles are Auxiliary Statistics specific to a SQL statement and are stored in the data dictionary.
  • SQL profiles guides the query optimizer to use a better plan for a SQL statement.
  • Once the SQL profile is created and “applied” to the system this new plan will be used AUTOMATICALLY every time that the statement is executed.

 SQL Tuning Advisor can be started using

  1. OEM
  2. DBMS_SQLTUNE package.
  3. Specify the SQL_ID of the sql you want to analyze using SQL Tuning Advisor

Sample SQL Tuning Advisor Output:

Tuning Task Name   : my_sql_tuning_task10
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/09/2004 21:30:22
Completed at       : 06/09/2004 21:30:23

SQL ID  : b296symum0xk1
SQL Text: SELECT /*+ ORDERED */ * FROM employees e, locations l, departments
d WHERE e.department_id = d.department_id AND l.location_id =
d.location_id AND e.employee_id < 10

1- Statistics Finding
Table “CLASS6″.”DEPARTMENTS” and its indices were not analyzed.

Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => ‘CLASS6′, tabname =>
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE)

The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
4- SQL Profile Finding (see explain plans section below)
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 87.1%)

Consider accepting the recommended SQL profile.
execute :profile_name := dbms_sqltune.accept_sql_profile(task_name =>

5- Restructure SQL finding (see plan 1 in explain plans section)
An expensive cartesian product operation was found at line ID 2 of the execution plan.

    Consider removing the “ORDERED” hint.

The “ORDERED” hint might force the optimizer to generate a cartesian product. A cartesian product should be avoided whenever possible because it is an expensive operation and might produce a large amount of data.

SQL Access Advisor

SQL Access Advisor evaluates an entire workload of SQL statements and recommend indexes, partitions, materialized views that will improve the performance of the SQL workload.

The SQL Access Advisor identifies possible access paths to the data using indexes or materialized views to improve the performance of access to the data.

The SQL Access Advisor takes an actual workload input (or it can derive an hypothetical one) and recommends the access structures needed for a faster execution.

To identify SQL statements that can benefit from having better access paths the SQL Access Advisor can take the workload directly from the SQL Cache (V$SQL), or from user-defined workloads (in the form of input tables or SQL Tuning Sets).

This advisor takes the following into consideration:

  • Simultaneous effect of using indexes, materialized views, or the combination of both
  • Storage creation parameters
  • Combination of single indexes into one index
  • Dropping unused indexes
  • Modifying existing indexes

Use OEM to start SQL Access Advisor

Time Model

In Oracle10g and onwards, the database introduces a different way of storing statistics and metrics regarding the time consumed by the different sessions connected. This data is key in a speedy analysis of performance problems, providing a different perspective on time usage within the database.

The following describes these characteristics in detail:

  • Time Model – With the new time model the time of every operation is stored in a bucket of time. Thus, one can identify either at database level or at session level how the time distribution looks. Examples of these buckets are: “DB CPU”, “DB Time”, “background elapsed time”, “sql execute elapsed time”, etc. See the following views for more details:


  • Wait Classes  – To enable easier high-level analysis of the wait events and allow a more accurate diagnosis, the time events are grouped now by “wait classes” based on the solution space that applies to fixing a problem with that wait event. See the following views for more details:


  • Metrics Metrics are automatically calculated based on basic statistics to track the rates of changes in the activity of the Database. Most of the metrics are calculated every 60 seconds. A good example of metrics is the frequently used “cache hit ratios”. In releases of Oracle previous of 10g, cache hit ratios had to be manually calculated using formulas that eventually may change from release to release. Cache hit ratios are now calculated and stored in memory. See the following views for more details:


  • OS Statistics CPU and memory statistics are gathered by default. This helps ADDM determine how the database activity is related to the bottleneck found. See the following views for more details:


Queries for practice

select * from V$OSSTAT

select * from V$SYS_TIME_MODEL

select * from V$SESS_TIME_MODEL

select * from V$SYSTEM_WAIT_CLASS

select * from V$SESSION_WAIT_CLASS

select * from V$SYSMETRIC

select * from V$SESSMETRIC

select * from V$FILEMETRIC

select * from V$EVENTMETRIC

select * from V$METRICNAME

select * from V$WAIT CLASS METRIC
Read More:
Compressed Backups
Types of Recovery Manager Backups
Improving Incremental RMAN Backup Performance: Change Tracking
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Your information will never be shared
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.