Oracle Scratchpad

June 8, 2012

Unique Fail

Filed under: Bugs,CBO,Execution plans,Oracle,Statistics — Jonathan Lewis @ 5:54 pm BST Jun 8,2012

As in – how come a unique (or primary key) index is predicted to return more than one row using a unique scan. Here’s and example (running on – but the same type of thing happens on newer versions):

June 4, 2012

Subpartition stats

Filed under: CBO,Execution plans,Oracle,Performance,Tuning — Jonathan Lewis @ 7:07 am BST Jun 4,2012

You might have expected the following query ought to run reasonably efficiently, after all it seems to be targeted very accurately at precisely the few rows of information I’m interested in:

	owner             = 'TEST_USER'
and 	table_name        = 'TEST_COMP'
and	subpartition_name = 'P_MAX_D'


April 11, 2012

Extended Stats

Filed under: CBO,Oracle,Statistics — Jonathan Lewis @ 11:37 pm BST Apr 11,2012

I’m very keen on the 11g extended stats feature, but I’ve just discovered a critical weakness in one of the implementation details that could lead to some surprising instability in execution plans. It’s a combination of “column group” statistics and “out of range” predicates. Let’s start with  some sample data. (Note: I have run this test on and

March 12, 2012

First_rows hash

Filed under: Bugs,CBO,Execution plans,Oracle — Jonathan Lewis @ 1:49 am BST Mar 12,2012

Just like my posting on an index hash, this posting is about a problem as well as being about a hash join. The article has its roots in a question posted on the OTN database forum, where a user has shown us the following execution plan:

| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT              |                         |    14 |   896 |    63   (7)| 00:00:01 |
|   1 |  SORT GROUP BY                |                         |    14 |   896 |    63   (7)| 00:00:01 |
|   2 |   NESTED LOOPS                |                         |       |       |            |          |
|   3 |    NESTED LOOPS               |                         |    14 |   896 |    62   (5)| 00:00:01 |
|*  4 |     HASH JOIN                 |                         |    14 |   280 |    48   (7)| 00:00:01 |
|   5 |      VIEW                     | V_SALES_ALL             |   200 |  1800 |     4   (0)| 00:00:01 |
|   6 |       UNION-ALL               |                         |       |       |            |          |
|   7 |        INDEX FAST FULL SCAN   | PRODUCTS_DATES_IDX      |   100 |   900 |     2   (0)| 00:00:01 |
|   8 |        INDEX FAST FULL SCAN   | PRODUCTS_DATES_IDX_HARD |   100 |   900 |     2   (0)| 00:00:01 |
|*  9 |      VIEW                     | index$_join$_003        |  2238 | 24618 |    44   (7)| 00:00:01 |
|* 10 |       HASH JOIN               |                         |       |       |            |          |
|* 11 |        INDEX RANGE SCAN       | PRODUCTS_GF_INDEX2      |  2238 | 24618 |     6   (0)| 00:00:01 |
|  12 |        INDEX FAST FULL SCAN   | PRODUCTS_GF_PK          |  2238 | 24618 |    45   (3)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN         | DATES_PK                |     1 |       |     0   (0)| 00:00:01 |
|  14 |    TABLE ACCESS BY INDEX ROWID| DATES                   |     1 |    44 |     1   (0)| 00:00:01 |


March 9, 2012

Index Upgrades

Filed under: CBO,Oracle,Upgrades — Jonathan Lewis @ 6:05 pm BST Mar 9,2012

Listening to a presentation by Paul Matuszyk on extended statistics yesterday, I learned something that I should have spotted ages ago. Here’s a little demo script to introduce the point:

create table t1
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1000)
	rownum			id,
	mod(rownum,100)		n1,
	mod(rownum,100)		n2,
	mod(rownum,100)		n3,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
	generator	v1,
	generator	v2
	rownum <= 1000000;

create index t1_i1 on t1(n1, n2, n3);

-- collect stats, no histograms.


February 21, 2012

Not In – 2

Filed under: CBO,Execution plans,Oracle,Performance,subqueries,Tuning — Jonathan Lewis @ 9:24 pm BST Feb 21,2012

My note on “NOT IN” subqueries is one of the most popular on my blog, staying in the top 5 hits for the last five years – but it’s getting a bit old, so it’s about time I said something new about “NOT IN” – especially since the Null Aware Anti Join has been around such a long time. The example I want to talk about is, as so often, something that came up as a problem on a customer site. Here’s a bit of SQL to model the situation, which is currently running under Oracle


February 20, 2012


Filed under: CBO,Oracle — Jonathan Lewis @ 5:53 pm BST Feb 20,2012

A couple of weeks ago I posted a reference list of links to the bug fix notes for several of the most recent versions of Oracle – and several of the links recorded a surprisingly large number of clicks very rapidly, especially the link. As a follow-up on the difficulties of upgrading, then, and with an insight into the number of enhancements and fixes to the optimizer that take place I decided to take a look at recent developments in the “fix control” list, and the “optimizer environment” parameters. Here’s a breakdown of the number of entries in recent versions of Oracle.


February 14, 2012

Subquery Factoring

Filed under: Bugs,CBO,Execution plans,Oracle,Subquery Factoring,Tuning — Jonathan Lewis @ 5:59 pm BST Feb 14,2012

Here’s an interesting little conundrum about subquery factoring that hasn’t changed in the recent ( patch for subquery factoring. It came to me from Jared Still (a fellow member of Oak Table Network) shortly after I’d made some comments about the patch. It’s an example based on the scott/tiger schema – which I’ve extracted from the script $ORACLE_HOME/rdbms/admin/utlsampl.sql (though the relevant scripts may be demobld.sql or scott.sql, depending on version).

February 6, 2012

Index naming

Filed under: CBO,Indexing,Oracle — Jonathan Lewis @ 6:28 pm BST Feb 6,2012

Here’s a model of a little problem I came across recently. It’s something I wrote about many years ago, and I thought I’d seen a note on Metalink (probably Note 73167.1 – see comments 2 and 4 below) explaining that the issue had been addressed; but the problem is still there, even in

We start with a little data set (and it’s my standard setup of 8KB blocks, LMTs, 1MB uniform extents, and no ASSM):

February 1, 2012

Subquery Factoring

Filed under: CBO,Execution plans,Oracle,Subquery Factoring,Tuning — Jonathan Lewis @ 5:52 pm BST Feb 1,2012

It’s always worth browsing through the list of Oracle’s bug fixes each time a new release or patch comes out because it can give you clues about where to look for problems in your production release – and how to anticipate problems on the upgrade. This article is an example of a fix that I found while while looking at the note for (MOS licence required for link) quite recently.


January 30, 2012

Index Hash

Filed under: CBO,Indexing,Oracle,Troubleshooting — Jonathan Lewis @ 6:12 pm BST Jan 30,2012

You might think from the title that this little note is going to be about the index hash join – you would be half right, it’s also about how the optimizer seems to make a complete hash of dealing with index hash joins.

Let’s set up a simple data set and a couple of indexes so that we can take a closer look:

January 6, 2012

I wish

Filed under: CBO,Execution plans,Oracle,Wishlist — Jonathan Lewis @ 5:38 pm BST Jan 6,2012

A few days ago I published an example of the optimizer failing to handle an updateable join view because it didn’t recognise that a particular type of aggregate subquery would guarantee key-preservation.  Here’s another example where the human eye can see key-preservation, but the optimizer can’t (even in As usual we’ll start with some sample data – in this case two tables since I want to update from one table to the other.

January 3, 2012


Filed under: CBO,Histograms,Oracle,Statistics — Jonathan Lewis @ 5:56 pm BST Jan 3,2012

A recent comment on a note I wrote some time ago about faking histograms asked about the calculations of selectivity in the latest versions of Oracle. As I read the question, I realised that I had originally supplied a formula for calculating cardinality, rather than selectivity, so I thought I’d supply a proper example.

We’ll start with a script to create some data and stats – and I’m going to start with a script I wrote in Jan 2001 (which is why it happens to use the analyze command rather than dbms_stats.gather_table_stats, even though this example comes from an instance of

December 29, 2011

I wish

Filed under: CBO,Execution plans,Oracle,Wishlist — Jonathan Lewis @ 10:02 pm BST Dec 29,2011

Here’s a simple update statement that identifies a few rows in a table then updates a column where a matching value can be derived from another table – it’s an example of an update by correlated subquery:

December 16, 2011

All Things Oracle

Filed under: CBO,Oracle,Tuning — Jonathan Lewis @ 5:53 pm BST Dec 16,2011

Last year I wrote a few articles for Simpletalk, a web service created by Red Gate for users of SQL Server. This year, Red Gate is setting up a similar service called “All things Oracle” (I’ve added a link in my blogroll) for Oracle users, and I’ve volunteered to write articles for them occasionally.

Some of the stuff they publish will be complete articles on their website, some will be short introductions with links to the authors’ own websites. My first article for them has just been posted – it’s an article that captures a couple of key points from the optimizer presentation I did at the UKOUG conference a couple of weeks ago.

« Previous PageNext Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 4,015 other followers