Top 60 Oracle Blogs

Recent comments


Oracle Rolling Invalidate Window Exceeded(3)

By Franck Pachot

This extends a previous post (Rolling Invalidate Window Exceeded) where, in summary, the ideas were:

Adaptive error

There’s a thread on the Oracle Database Forum at present where someone has supplied a script to create some data that’s guaranteed to reproduce wrong results (provided your system stats and optimizer parameters are at their default values). They’ve even supplied a link to the script on LiveSQL (opens in new window) – which is running 19.8 – to demonstrate the problem.

I’ve tested on and and the problem occurs in both versions – though with my setup the initial plan that returned the wrong results didn’t re-optimize to a plan with the correct results in

CBO Example

A little case study based on an example just in on the Oracle-L list server. This was supplied with a complete, working, test case that was small enough to understand and explain very quickly.

The user created a table, and used calls to dbms_stats to fake some statistics into place. Here, with a little cosmetic editing, is the code they supplied.

Optimizer Statistics Gathering – pending and history

By Franck Pachot

This was initially posted to CERN Database blog on Wednesday, 12 September 2018 where it seems to be lost. Here is a copy thanks to

Demo table

I create a table for the demo. The CTAS gathers statistics (12c online statistics gathering) with one row and then I insert more rows:

10:33:56 SQL> create table DEMO as select rownum n from dual;
Table DEMO created.
10:33:56 SQL> insert into DEMO select rownum n from xmltable('1 to 41');
41 rows inserted.
10:33:56 SQL> commit;
Commit complete.

The estimations are stale: estimates 1 row (E-Rows) but 42 actual rows (A-Rows)

Oracle SPD status on two learning paths

By Franck Pachot

I have written a lot about SQL Plan Directives that appeared in 12c. They were used by default and, because of some side effects at the time of 12cR1 with legacy applications that were parsing too much, they have been disabled by default in 12cR2. Today, there are probably not used enough because of their bad reputation from those times. But for datawarehouses, they should be the default in my opinion.

Oracle 12.2: New Statistic Preference PREFERENCE_OVERRIDES_PARAMETER


There is a new statistic preference, PREFERENCE_OVERRIDES_PARAMETER available from Oracle 12.2.  It allows the DBA to override any parameters specified when gathering statistics in favour of any statistics preferences that are defined.  This new preference can be specified at database-level or at table-level, or both.

From the introduction of the cost-based optimizer in Oracle 7, we all had to write scripts to collect statistics.  The introduction of the maintenance window in Oracle 10g was supposed to supersede that with a regularly scheduled maintenance window.  It still is not uncommon to find systems that rely on custom scripts that collect object statistics.  Sometimes, commands to collect statistics are embedded in applications.  

Indexing partitions

This is one of those notes on the “thinking about the data / indexes” theme that I like to write; it’s a draft I wrote over two and a half years ago that I’ve just rediscovered and polished slightly and refers to a “recent” question that came up on the ODC Forum. It’s not intended as “The Answer” to the question, it’s a just a list of ideas and observations you’d want to think about if you had to address the problem:

Oracle 19c: Real-Time Statistics & High-Frequency Statistics Collection

The video of this recent presentation, given as a part of the Oracle Groundbreakers EMEA Tour 2020, is now available.

Keeping object statistics up to date is critical to Oracle database performance and stability. Both of these features aim to address the challenge of using data that has been significantly updated before the statistics maintenance window has run again. The features are only available on engineered systems, and so certainly are targetted at the autonomous database.

Column Groups

Here’s an odd little detail about the statistics of column groups. At first glance it’s counter-intuitive but it’s actually an “obvious” (once you’ve thought about it for a bit) consequence of the approximate_ndv() algorithm for gathering stats.

I’ll present it as a question:

I have a table with two columns: flag and v1. Although the column are not declared as non-null neither holds any nulls. If there are 26 distinct values for flag, and 1,000,000 distinct values for v1, what’s the smallest number of distinct values I should see if I create the column group (flag, v1) ?

Index FFS Cost 2

Here’s a little puzzle, highlighting a “bug that’s not a bug” that was “fixed but not fixed” some time in the 10.2 timeline. (If you want specifics about exactly when the fix became available and what patches might be available they’re in MOS – Bug 5099019 : DBMS_STATS DOESN’T COUNT LEAF_BLOCKS CORRECTLY.

Running, with the system statistics as shown: