How to tune a SQL Statement with the wrong Join Type

When it comes to SQL Tuning, I don’t typically recommend folks add one-off hints or look for magical underscore parameters to help improve their query performance.

Instead, I like to share some simple steps to

  1. Accurately characterise the problem
  2. Determine and apply a solution
  3. Measure the effectiveness of that solution

But instead of talking about my approach, I thought it would be more beneficial to show you how I do it. So in the video below, you will see the exact steps I used to determine what was causing a Sales Report to run slowly and the two alternative approaches you can take to resolve similar problems.

3 thoughts on “How to tune a SQL Statement with the wrong Join Type”

  1. Recently, I am using optimizer_real_time_statistics=true with good success. It seems to keep statistics up to date, although the mechanism isn’t well documented. Can you say something about the mechanism used with that setting?

    1. Hi,
      Oracle originally introduced Real-time statistics in 12c to gather statistics as part of a CREATE TABLE AS SELECT statements and direct-path inserts (INSERT /*+ APPEND */ ) statements into empty objects. In Oracle Database 19c, we extended real-time statistics online support to conventional DML statements. To reduce the chances that stale statistics will adversely affect the optimizer decisions when generating execution plans. For example, “out-of_range” errors where the value used in the WHERE CLAUSE predicate falls outside the mix/max range for that column.
      During DML operations, real-time statistics will adjust the following statistics:
      Table statistics
      Number of rows
      Column statistics
      Min value
      Max value
      Note, some of the most critical statistics, such as NDV, are not adjusted with real-time statistics due to the performance overheads that would incur. So, it is essential you still allow the nightly statistics job to run to gather these additional statistics.

      You should also note that the real-time statistics feature only available on a subset of platforms (as specified in the licensing manual).

      Thanks,
      Maria

Leave a Reply

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