sql tuning overview

By Timothy Peterson,2014-10-18 16:11
9 views 0
sql tuning overview

SQL Tuning Overview

    This chapter discusses goals for tuning, how to identify high-resource SQL statements, explains what should be collected, and provides tuning suggestions.

    This chapter contains the following sections:

    ; Introduction to SQL Tuning

    ; Goals for Tuning

    ; Identifying High-Load SQL

    ; Automatic SQL Tuning Features

    ; Developing Efficient SQL Statements

    See Also:

    ; Oracle Database Concepts for an overview of SQL

    ; Oracle Database 2 Day DBA for information on monitoring and

    tuning the database

    Introduction to SQL Tuning

    An important facet of database system performance tuning is the tuning of SQL statements. SQL tuning involves three basic steps:

    ; Identifying high load or top SQL statements that are responsible for a large share of the

    application workload and system resources, by reviewing past SQL execution history

    available in the system.

    ; Verifying that the execution plans produced by the query optimizer for these statements

    perform reasonably.

    ; Implementing corrective actions to generate better execution plans for poorly

    performing SQL statements.

    These three steps are repeated until the system performance reaches a satisfactory level or no more statements can be tuned.

    Goals for Tuning

    The objective of tuning a system is either to reduce the response time for end users of the system, or to reduce the resources used to process the same work. You can accomplish both of these objectives in several ways:

    ; Reduce the Workload

    ; Balance the Workload

    ; Parallelize the Workload

    Reduce the Workload

    SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption. Two examples of how resource usage can be reduced are:

    1. If a commonly executed query needs to access a small percentage of data in the table,

    then it can be executed more efficiently by using an index. By creating such an index, you

    reduce the amount of resources used.

    2. If a user is looking at the first twenty rows of the 10,000 rows returned in a specific sort

    order, and if the query (and sort order) can be satisfied by an index, then the user does

    not need to access and sort the 10,000 rows to see the first 20 rows. Balance the Workload

    Systems often tend to have peak usage in the daytime when real users are connected to the system, and low usage in the nighttime. If noncritical reports and batch jobs can be scheduled to run in the nighttime and their concurrency during day time reduced, then it frees up resources for the more critical programs in the day.

    Parallelize the Workload

    Queries that access large amounts of data (typical data warehouse queries) often can be parallelized. This is extremely useful for reducing the response time in low concurrency data warehouse. However, for OLTP environments, which tend to be high concurrency, this can adversely impact other users by increasing the overall resource usage of the program. Identifying High-Load SQL

    This section describes the steps involved in identifying and gathering data on high-load SQL statements. High-load SQL are poorly-performing, resource-intensive SQL statements that impact the performance of the Oracle database. High-load SQL statements can be identified by:

    ; Automatic Database Diagnostic Monitor

    ; Automatic Workload Repository

    ; V$SQL view

    ; Custom Workload

    ; SQL Trace

    Identifying Resource-Intensive SQL

    The first step in identifying resource-intensive SQL is to categorize the problem you are attempting to fix:

    ; Is the problem specific to a single program (or small number of programs)

    ; Is the problem generic over the application?

    Tuning a Specific Program

    If you are tuning a specific program (GUI or 3GL), then identifying the SQL to examine is a simple matter of looking at the SQL executed within the program. Oracle Enterprise Manager provides tools for identifying resource intensive SQL statements, generating explain plans, and evaluating SQL performance.

    See Also:

    ; Oracle Enterprise Manager Concepts for information about the tools

    available for monitoring and tuning SQL applications

    ; Chapter 13, "Automatic SQL Tuning" for information on automatic

    SQL tuning features

    If it is not possible to identify the SQL (for example, the SQL is generated dynamically), then use SQL_TRACE to generate a trace file that contains the SQL executed, then use TKPROF to generate an output file. The SQL statements in the TKPROF output file can be ordered by various parameters, such as the execution elapsed time (exeela), which usually assists in the identification by ordering the SQL statements by elapsed time (with highest elapsed time SQL statements at the top of the file). This makes the job of identifying the poorly performing SQL easier if there are many SQL statements in the file.

    See Also:

    Chapter 20, "Using Application Tracing Tools"

    Tuning an Application / Reducing Load

    If your whole application is performing suboptimally, or if you are attempting to reduce the overall CPU or I/O load on the database server, then identifying resource-intensive SQL involves the following steps:

    1. Determine which period in the day you would like to examine; typically this is the

    application's peak processing time.

    2. Gather operating system and Oracle statistics at the beginning and end of that period.

    The minimum of Oracle statistics gathered should be file I/O (V$FILESTAT), system

    statistics (V$SYSSTAT), and SQL statistics (V$SQLAREA or V$SQL, V$SQLTEXT,


    See Also:

    Chapter 6, "Automatic Performance Diagnostics" for

    information on how to use Oracle tools to gather Oracle

    instance performance data

    3. Using the data collected in step two, identify the SQL statements using the most

    resources. A good way to identify candidate SQL statements is to query V$SQLAREA.

    V$SQLAREA contains resource usage information for all SQL statements in the shared

    pool. The data in V$SQLAREA should be ordered by resource usage. The most common

    resources are:

    ; Buffer gets (V$SQLAREA.BUFFER_GETS, for high CPU using statements)

    ; Disk reads (V$SQLAREA.DISK_READS, for high I/O statements)

    ; Sorts (V$SQLAREA.SORTS, for many sorts)

    One method to identify which SQL statements are creating the highest load is to compare the resources used by a SQL statement to the total amount of that resource used in the period. For BUFFER_GETS, divide each SQL statement's BUFFER_GETS by the total number of buffer gets during the period. The total number of buffer gets in the system is available in the V$SYSSTAT table, for the statistic session logical reads.

    Similarly, it is possible to apportion the percentage of disk reads a statement performs out of the total disk reads performed by the system by dividing V$SQL_AREA.DISK_READS by the value for the V$SYSSTAT statistic physical reads. The SQL sections of the Automatic Workload Repository report include this data, so you do not need to perform the percentage calculations manually.

    See Also:

    Oracle Database Reference for information about dynamic

    performance views

    After you have identified the candidate SQL statements, the next stage is to gather information that is necessary to examine the statements and tune them.

    Gathering Data on the SQL Identified

    If you are most concerned with CPU, then examine the top SQL statements that performed the most BUFFER_GETS during that interval. Otherwise, start with the SQL statement that performed the most DISK_READS. Information to Gather During Tuning

    The tuning process begins by determining the structure of the underlying tables and indexes. The information gathered includes the following:

    1. Complete SQL text from V$SQLTEXT

    2. Structure of the tables referenced in the SQL statement, usually by describing the table

    in SQL*Plus

    3. Definitions of any indexes (columns, column orderings), and whether the indexes are

    unique or nonunique

    4. Optimizer statistics for the segments (including the number of rows each table,

    selectivity of the index columns), including the date when the segments were last


    5. Definitions of any views referred to in the SQL statement

    6. Repeat steps two, three, and four for any tables referenced in the view definitions found

    in step five

    7. Optimizer plan for the SQL statement (either from EXPLAINPLAN, V$SQL_PLAN, or the

    TKPROF output)

    8. Any previous optimizer plans for that SQL statement


    It is important to generate and review execution plans for

    all of the key SQL statements in your application. Doing

    so lets you compare the optimizer execution plans of a SQL

    statement when the statement performed well to the plan

    when that the statement is not performing well. Having the

    comparison, along with information such as changes in data

    volumes, can assist in identifying the cause of performance


Automatic SQL Tuning Features

    Because the manual SQL tuning process poses many challenges to the application developer, the SQL tuning process has been automated by the automatic SQL Tuning manageability features. Theses features have been designed to work equally well for OLTP and Data Warehouse type applications. See Chapter 13, "Automatic SQL Tuning".


    Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database, including high-load SQL statements. See "Automatic Database

    Diagnostic Monitor".

    SQL Tuning Advisor

    SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements. See "SQL Tuning


    SQL Tuning Sets

    When multiple SQL statements are used as input to ADDM or SQL Tuning Advisor, a SQL Tuning Set (STS) is constructed and stored. The STS includes the set of SQL statements along with their associated execution context and basic execution statistics. See "SQL Tuning Sets".

    SQLAccess Advisor

    In addition to the SQL Tuning Advisor, Oracle provides the SQLAccess Advisor, which is a tuning tool that provides advice on materialized views, indexes, and materialized view logs. The SQLAccess Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. In general, as the number of materialized views and indexes and the space allocated to them is increased, query performance improves. The SQLAccess Advisor considers the trade-offs between space usage and query

    performance and recommends the most cost-effective configuration of new and existing materialized views and indexes.

    To access the SQLAccess Advisor through Oracle Enterprise Manager Database Control:

    ; Click the AdvisorCentral link under RelatedLinks at the bottom of the Database pages.

    ; On the AdvisorCentral page, you can click the SQLAccessAdvisor link to analyze a

    workload source.

    See Also:

    Oracle Data Warehousing Guide for more information on

    SQLAccess Advisor

    Developing Efficient SQL Statements

    This section describes ways you can improve SQL statement efficiency:

    ; Verifying Optimizer Statistics

    ; Reviewing the Execution Plan

    ; Restructuring the SQL Statements

    ; Restructuring the Indexes

    ; Modifying or Disabling Triggers and Constraints

    ; Restructuring the Data

    ; Maintaining Execution Plans Over Time

    ; Visiting Data as Few Times as Possible


    The guidelines described in this section are oriented to

    production SQL that will be executed frequently. Most of

    the techniques that are discouraged here can legitimately

    be employed in ad hoc statements or in applications run

    infrequently where performance is not critical.

Verifying Optimizer Statistics

    The query optimizer uses statistics gathered on tables and indexes when determining the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data

    stored within the database, then the optimizer does not have sufficient information to generate the best plan.

    Things to check:

    ; If you gather statistics for some tables in your database, then it is probably best to gather

    statistics for all tables. This is especially true if your application includes SQL statements

    that perform joins.

    ; If the optimizer statistics in the data dictionary are no longer representative of the data

    in the tables and indexes, then gather new statistics. One way to check whether the

    dictionary statistics are stale is to compare the real cardinality (row count) of a table to

    the value of DBA_TABLES.NUM_ROWS. Additionally, if there is significant data skew on

    predicate columns, then consider using histograms.

    Reviewing the Execution Plan

    When tuning (or writing) a SQL statement in an OLTP environment, the goal is to drive from the table that has the most selective filter. This means that there are fewer rows passed to the next step. If the next step is a join, then this means that fewer rows are joined. Check to see whether the access paths are optimal.

    When examining the optimizer execution plan, look for the following:

    ; The plan is such that the driving table has the best filter.

    ; The join order in each step means that the fewest number of rows are being returned to

    the next step (that is, the join order should reflect, where possible, going to the best

    not-yet-used filters).

    ; The join method is appropriate for the number of rows being returned. For example,

    nested loop joins through indexes may not be optimal when many rows are being


    ; Views are used efficiently. Look at the SELECT list to see whether access to the view is


    ; There are any unintentional Cartesian products (even with small tables).

    ; Each table is being accessed efficiently:

    Consider the predicates in the SQL statement and the number of rows

    in the table. Look for suspicious activity, such as a full table

    scans on tables with large number of rows, which have predicates

    in the where clause. Determine why an index is not used for such

    a selective predicate.

    A full table scan does not mean inefficiency. It might be more

    efficient to perform a full table scan on a small table, or to

    perform a full table scan to leverage a better join method (for

    example, hash_join) for the number of rows returned.

    If any of these conditions are not optimal, then consider restructuring the SQL statement or the indexes available on the tables. Restructuring the SQL Statements

    Often, rewriting an inefficient SQL statement is easier than modifying it. If you understand the purpose of a given statement, then you might be able to quickly and easily write a new statement that meets the requirement.

    Compose Predicates Using AND and =

    To improve SQL efficiency, use equijoins whenever possible. Statements that perform equijoins on untransformed column values are the easiest to tune.

    Avoid Transformed Columns in the WHERE Clause

    Use untransformed column values. For example, use:

    WHERE a.order_no = b.order_no

rather than:

    WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1)) = TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))

    Do not use SQL functions in predicate clauses or WHERE clauses. Any expression using a column, such as a function having the column as its argument, causes the optimizer to ignore the possibility of using an index on that column, even a unique index, unless there is a function-based index defined that can be used.

    Avoid mixed-mode expressions, and beware of implicit type conversions. When you want to use an index on the VARCHAR2 column charcol, but the WHERE clause looks like this:

    AND charcol = numexpr

    where numexpr is an expression of number type (for example, 1, USERENV('SESSIONID'), numcol, numcol+0,...), Oracle translates that expression into:

    AND TO_NUMBER(charcol) = numexpr

Avoid the following kinds of complex expressions:

    ; col1 = NVL(:b1,col1)

    ; NVL (col1,-999) = ....

    ; TO_DATE(), TO_NUMBER(), and so on

    These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method.

    Add the predicate versus using NVL() technique.

    For example:

    SELECT employee_num, full_name Name, employee_id

     FROM mtl_employees_current_view

     WHERE (employee_num = NVL (:b1,employee_num)) AND (organization_id=:1)

     ORDER BY employee_num;


    SELECT employee_num, full_name Name, employee_id

     FROM mtl_employees_current_view

     WHERE (employee_num = :b1) AND (organization_id=:1)

     ORDER BY employee_num;

    When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement: TO_CHAR(numcol) = varcol

rather than

    varcol = TO_CHAR(numcol)

Report this document

For any questions or suggestions please email