Tag: optimizer

Harnessing the Power of Optimizer Hints

Last week I was lucky enough to have participated in the Trivadis Performance Days 2017 conference and several people have asked if I would share the slides from one of my sessions.

The session in question was 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.

In this session I explained how Optimizer hints are interpreted, when and where they should be used, and why they sometimes appear to be ignored.

Be warned this session won’t make your a hinting master over night and I’m not advocating you should try and hint every problematic SQL statement you encounter!

Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans

In last week’s post I described how to use SQL Monitor to determine what is happening during the execution of long-running SQL statements. Shortly after the post went up, I got some requests on both social media and via the blog comments asking, “If it is possible to get the same information from a traditional text-based execution plan, as not everyone has access to SQL Monitor?”.

The answer is yes, it is possible to see a lot of the information shown in SQL Monitor by viewing the execution plan via the DBMS_XPLAN.DISPLAY_CURSOR function. In order to call this function, you will need SELECT or READ privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise, you’ll get an error message.

The DBMS_XPLAN.DISPLAY_CURSOR function takes three parameters:

  1. SQL ID – default null, means the last SQL statement executed in this session
  2. CURSOR_CHILD_NO – default 0
  3. FORMAT – Controls the level of details that will be displayed in the execution plan, default TYPICAL.

The video below demonstrates how you can use the FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function to show you information about what’s happened during an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.

Under the video you will find all of the commands used, so you can cut and paste them easily.

How do I see the actual number of rows and elapse time for each step in the plan?

Continue reading “Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans”

Overriding DBMS_STATS Parameter Settings

Since it’s introduction in Oracle Database 8i, the DBMS_STATS package is Oracle’s preferred method for gathering statistics. With each new database release the DBMS_STATS package is extended to accommodate new approaches to gather statistics and new types of statistics.

Over the years, application developers and DBAs have written hundreds of scripts using the DBMS_STATS package to help gather and manage optimizer statistics effectively. However, once written these scripts are rarely modified to take advantage of the improvements in the DBMS_STATS package, which can result in suboptimal statistics.

Oracle Database 12 Release 2 makes it a lot easier to be able to manage this vast collection of scripts by includes a new DBMS_STATS preference called PREFERENCE_OVERRIDES_PARAMETER. When this preference is set to TRUE, it allows preference settings to override the parameter values specifically set in a DBMS_STATS command.

Continue reading “Overriding DBMS_STATS Parameter Settings”

Optimizer Histograms

At the recent OUG Ireland conference I had the privilege of participating in a panel discussion on the Oracle Database. During the course of the session the topic of Optimizer histograms came up. As always, a heated discussion ensued among the members of the panel, as we each had very different views on the subject.

Why so many different opinions when it comes to histograms?

The problem arises from the fact that some folks have been burnt by histograms in the past. In Oracle Database 9i and 10g, histograms in combination with bind-peeking lead to some unpredictable performance problems, which is explained in detail in this post on the  Optimizer blog.  This has resulted in a number of folks becoming histogram shy. In fact, I reckon if you were to put 3 Oracle experts on a panel, you would get at least 5 different opinions on when and how you should gather histograms!

So I thought it would be a good idea to explain some of the common misconceptions that surround histograms and the impact of adopting them.

This is a long post, so you might want to grab a coffee before you get into it!

Continue reading “Optimizer Histograms”

Histogram sample size and Online Statistics Gathering

I got a great follow up question to my earlier blog on Online Statistics Gathering; that I thought might be of interest to other and worthy of a short post of its own.

The question related to sample size used to gather histograms on a table that originally had its statistics gathered via a direct path load operation.

Let’s look at any example:

I got a great follow up question to my earlier blog on Online Statistics Gathering; that I thought might be of interest to other and worthy of a short post of its own.

The question related to sample size used to gather histograms on a table that originally had its statistics gathered via a direct path load operation.

Let’s look at any example:

Continue reading “Histogram sample size and Online Statistics Gathering”

Locking Table Statistics Provides Plan Stability True or False?

I was recently asked if I would ever recommend locking table statistics as a way of preventing execution plans from changing. As, with a lot questions’ regarding the Optimizer and statistics the answer was “it depends”.

There are definitely some edge cases where locking the statistics is the best approach but I wouldn’t generally recommend it to achieve plan stability. Before I share the edge cases where I believe locking statistics would help, lets first look at exactly what locking table statistics does and why it’s not a good approach to plan stability.

From Oracle Database 10g onwards you have had the ability to lock the optimizer statistics for a table or schema, via the DBMS_STATS package.

BEGIN
DBMS_STATS.LOCK_TABLE_STATS(‘SH’,’SALES’);
END;
/
The LOCK_STATS procedure locks all of the statistics related to a table, including the table statistics (number of rows, number of blocks etc.), column statistics (min and max values, number of distinct values etc.), histograms and the statistics on all dependent indexes. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked or if the FORCE option is used in the GATHER_*_STATS procedures.

I was recently asked if I would ever recommend locking table statistics as a way of preventing execution plans from changing. As, with a lot questions’ regarding the Optimizer and statistics the answer was “it depends”.

There are definitely some edge cases where locking the statistics is the best approach but I wouldn’t generally recommend it to achieve plan stability. Before I share the edge cases where I believe locking statistics would help, lets first look at exactly what locking table statistics does and why it’s not a good approach to plan stability.

Continue reading “Locking Table Statistics Provides Plan Stability True or False?”

Online Statistics Gathering

Although there have been a number of significant changes to the Oracle Database in 12c, some of my favorite new features are actually the small enhancements, designed to make your life a little easier. So, I decided to spend some time this year sharing the details on these small but extremely useful features.

One such enhancement is Online Statistics Gathering.

Whenever an index is created, Oracle automatically gathers optimizer statistics for that index. The database piggybacks the statistics gather on the full data scan and sort operation necessary for the index creation. This approach has worked so well since it was introduced in 9i, few people even realize it’s happening.

Although there have been a number of significant changes to the Oracle Database in 12c, some of my favorite new features are actually the small enhancements, designed to make your life a little easier. So, I decided to spend some time this year sharing the details on these small but extremely useful features.

One such enhancement is Online Statistics Gathering.

Continue reading “Online Statistics Gathering”

%d bloggers like this: