Oracle Database Product Manager with a passion for SQL, the Optimizer and performance.

SQL Tuning: 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

Accurately characterise the problem

Determine and apply a solution

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.

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?

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).

Thx alot for sharing knowledge

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?

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

Very impressive. I really like this post very informative and helpful thanks a lot.

Thanks. Great insights. I am sure many would miss such a simple analysis…

Thank you.