Category Archives: Optimizer

Automatic Collection of Fixed Objects Statistics in 12c

In my previous life as the Optimizer Lady, I wrote a blog on the importance of gathering fixed object statistics, since they were not originally gathered as part of the automatic statistics gather task. Starting with Oracle Database 12c Release … Continue reading

Posted in Optimizer, Statistics | Tagged , , | Leave a comment

Should you gather System Statistics?

While at the HotSOS Symposium, last month, I caused quite a stir when I recommended that folks should never gather system statistics. Why such a stir? It turns out this goes against what we recommend in the Oracle SQL Tuning … Continue reading

Posted in Optimizer, Statistics | Tagged , , | Leave a comment

SQL Plan Management – Selective Automatic Plan Capture Now Available!

Over the years, Oracle has provided a number of techniques to help you control the execution plan for a SQL statement, such as Store Outlines and SQL Profiles but for me the only feature to truly give you plan stability … Continue reading

Posted in Optimizer, Oracle Database 12c New Features, SQL Plan Management, Uncategorized | Tagged , , , , | Leave a comment

Does the Explain Plan command really show the execution plan that will be used?

When it comes to SQL tuning we often need to look at the execution plan for a SQL statement to determine where the majority of the time is spent. But how we generate that execution plan can have a big … Continue reading

Posted in Beginners, Optimizer | Tagged , , , | 1 Comment

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 … Continue reading

Posted in Optimizer | Tagged , , | 1 Comment

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 … Continue reading

Posted in Optimizer, Syntax | Tagged , , , | 7 Comments

Getting the most out of Oracle SQL Monitor

I’m often asked what is the best tool for viewing execution plans and for me the answer is always SQL Monitor (included in the Oracle Tuning Pack). It really is the most invaluable tool if you need to determine what … Continue reading

Posted in Optimizer | Tagged , , , , | 6 Comments

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 … Continue reading

Posted in New Features, Optimizer, Oracle Database 12c New Features | Tagged , , | Leave a comment

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 … Continue reading

Posted in Optimizer, Statistics, Syntax | Tagged , , | 11 Comments

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: Continue reading

Posted in AskTom, Optimizer, Oracle Database 12c New Features | Tagged , , | 1 Comment