SQL Tuning Workshop

Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group –  Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based Optimizer, the statistics that feed it, the hints that influence it, and the key tools you need to examine execution plans.

The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the five topics.

Part 1 Understanding the Oracle Optimizer

The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.

Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. Most of Oracle’s query transactions are now cost-based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations, including:

You can also download the slides here.

Part 2 Best Practices for Managing Optimizer Statistics

Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.

It also covers what additional information you may need to give the Optimizer, such as histograms, extended statistics, and all the techniques you can use to speed up statistics gathering, including taking advantage of Incremental statistics, parallelism, and concurrency. Finally, we look at some guidance on when not to gather statistics. A lot of the topics covered in this presentation are discussed in detail in the following two white papers:

You can also download the slides here.

Part 3 Explain the Explain Plan

Part 3 of the workshop examines the different aspects of an execution plan, from cardinality estimates to parallel execution, and explains what information you should glean from the plan and how it affects the execution. It offers insight into what caused the Optimizer to make the decision, as well as a set of corrective measures that can be used to improve each aspect of the plan.

More information on displaying and reading execution plans can be found in my previous blog posts on DBMS_XPLAN.DISPLAY_CURSOR and using SQL Monitor. Or, in the white paper, Explain the Explain Plan.

You can also download the slides here.

Part 4 Influencing Execution Plans with Optimizer Hints

Part 4 is called “Harnessing the power of optimizer hints.” Although I am not a strong supporter of adding hints to SQL statements for a whole host of reasons, from time to time, it may become necessary to influence the plan the Optimizer chooses. The most powerful way to alter the plan chosen is via Optimizer hints. But knowing when and how to use Optimizer hints correctly is somewhat of a dark art. This session explains how Optimizer hints are interpreted, when and where they should be used, and why they sometimes appear to be ignored.

You can download the slides here or watch the recorded sessions below.

Part 5 SQL Tuning Tips and Tricks

The final part of the SQL Tuning workshop focuses on applying the techniques discussed in the previous sections to help diagnose and correct several problematic SQL statements. It shows how to use SQL Plan Management or a SQL Patch to influence an execution plan.

You can download the slides here.

I hope you find the materials useful!

14 thoughts on “SQL Tuning Workshop”

  1. Maria : Thanks for the great post on sql tuning . Is there a known limitation with pulling a runtime execution plan using dbms_xplan.display_cursor when one tables in the SQL goes to a read only standby database via a db_link ?
    I get an error that sql not found in cursor , Try v$sqlplan,

    Thanks

    1. Hi Kumar,

      DBMS_XPLAN.DISPLAY_CURSOR can only show an execution plan for a SQL statement that has a cursor in the CURSOR CACHE inside the SHARED_POOL on the local system. If the SQL statement in question is executed remotely via DBLink, then the cursor for that statement will actually be in the CURSOR CACHE inside the SHARED_POOL on the remote system and hence the error.
      Thanks,
      Maria

    1. Hi Jack,

      I’ve added links to the blog so you can download each of the files.

      I’m also working on a video series of this workshop, so you will be able to watch the session as well as get access to the files.

      Thanks,
      Maria

    1. Hi Jamsher,

      Unfortunately, the workshop was not recorded. However, I am creating short videos that cover all of the content discussed in this course in more easily consumed chunks.
      For example, the explain the explain plan section is now a series of videos on the SQLMaria YouTube Channel
      It’s going to take some time before all of the content has been turned into videos, but hopefully, the content already available will be helpful to you.
      Thanks,
      Maria

  2. Thanks Maria for sharing these great slides,

    The statements in slides 36 and 37 are identical without any changes but they got different plans,
    so not quite sure how the plan got fixed in slides 37 ?!

    1. Hi Mohamed,
      On slide 36, the plan is shown where the SALES and SALES_RETURNS tables are using different data types for the join column, TIME_ID. The sales table is using a DATE datatype but the SALES_RETURNS tables is using a TIMESTAMP datatype. Because of the data type mismatch the optimizer has to apply an INTERNAL_FUNCTION to the TIME_ID column on one side of the join before it can complete the join. The application of this function prevents partition pruning from occurring.

      The plan shown on slide 37 is for the same statement, but both the SALES and SALES_RETURNS tables are using the same data types (DATE) for the join column, TIME_ID. No INTERNAL_FUNCTION is required to complete the join and therefore partition pruning is possible.

      Thanks,
      Maria

  3. One again great information!
    With respect to incremental statistics gathering while using partition exchange:
    If one gathers the statistics on the non-partitioned table (with incremental true and level table) prior to doing the exchange and then do the exchange, when will the global statistics on the partitioned target table be updated by the partition that was just swapped in by using the partition synopses?

    Do the relevant synopses get updated during the partition exchange with the statistics pulled from the non-partitioned table used to do the partition exchange?

    I seemed to find that I needed to do the statistics after the exchange on the partition in order for the synopses to be update and to get the global statistics on the partitioned table updated. Is that incorrect?
    Thanks in advance for your help.

  4. One other question, which understandably may be too specific for this setting, but are there any guidelines as to when to use incremental statistics and when not to use them?
    When using the autonomous database, with table containing 16 numeric columns, 214 partitions, no indices and 13,652,883 rows, I got the following results when comparing the MEDIUM and LOW service levels.

    collect statistics partition by partition using low
    PL/SQL procedure successfully completed.
    Elapsed: 01:00:32.861

    collect statistics at the table level using low
    PL/SQL procedure successfully completed.
    Elapsed: 00:14:45.782

    collect statistics partition by partition using medium
    PL/SQL procedure successfully completed.
    Elapsed: 01:23:04.754

    collect statistics at the table level using medium
    PL/SQL procedure successfully completed.
    Elapsed: 00:25:45.116

    I am just looking for guidance as to whether my table is an appropriate use case for incremental statistics as I have my doubts given that the low service is performing better than the medium service. Perhaps my table is too large or has too many partitions to make effective use of the feature.

Leave a Reply

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