Oracle Scratchpad

March 31, 2013

Index Selectivity

Filed under: CBO,Oracle,Troubleshooting — Jonathan Lewis @ 6:42 pm BST Mar 31,2013

Here’s a summary of a recent posting on OTN:

I have two indexes (REFNO, REFTYPESEQNO) and (REFNO,TMSTAMP,REFTYPESEQNO). When I run the following query the optimizer uses the second index rather than the first index – which is an exact match for the predicates, unless I hint it otherwise:

(more…)

January 6, 2013

Blog advert

Filed under: Bugs,CBO,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 9:10 pm BST Jan 6,2013

Just a quick note to say that I found a blog over the weekend with a number of interesting posts, so I thought I’d pass it on: http://www.bobbydurrettdba.com/

There’s a really cute example (complete with test case) of an optimizer bug (possibly only in 11.1)  in the December archive: http://www.bobbydurrettdba.com/2012/12/04/index-causes-poor-performance-in-query-that-doesnt-use-it/

January 3, 2013

Skip Scan 2

Filed under: CBO,Indexing,Oracle — Jonathan Lewis @ 6:42 pm BST Jan 3,2013

Here’s a question that is NOT a trick question, it’s demonstrating an example of optimizer behaviour that might come as a surprise.
I have an index (addr_id0050, effective_date), the first column is numeric, the second is a date. Here’s a query with an execution plan that uses that index:
(more…)

October 23, 2012

Skip Scan

Filed under: CBO,Indexing,Oracle — Jonathan Lewis @ 5:55 pm BST Oct 23,2012

A recent question on OTN asked how you could model a case where Oracle had the choice between a “perfect” index for a range scan and an index that could be used for an index skip scan and choose the latter path even though it was clearly (to the human eye) the less sensible choice. There have been a number of wierd and wonderful anomalies with the index skip scan and bad choice over the years, and this particular case is just one of many oddities I have seen in the past – so I didn’t think it would be hard to model one (in fact, I thought I already had at least two examples somewhere in my library – but I couldn’t find them).

Take a data set with two columns, call them id1 and id2, and create indexes on (id1), and (id2, id1). Generate the id1 column as a wide range of cyclic values, generate the id2 set with a small number of repetitive values so that a large number of physically adjacent rows hold the same value. The clustering_factor on the (id1) index will be very large, the clustering_factor on the (id2, id1) index will be relatively small because it will be controlled largely by the repetitive id2 value. Here’s the data set:
(more…)

August 19, 2012

Compression Units – 5

Filed under: CBO,Exadata,HCC,Indexing,Oracle — Jonathan Lewis @ 6:02 pm BST Aug 19,2012

The Enkitec Extreme Exadata Expo (E4) event is over, but I still have plenty to say about the technology. The event was a great success, with plenty of interesting speakers and presentations. As I said in a previous note, I was particularly keen to hear  Frits Hoogland’s comments  on Exadata and OLTP, Richard Foote on Indexes, and Maria Colgan’s comments on how Oracle is making changes to the optimizer to understand Exadata a little better.

All three presentations were interesting – but Maria’s was possiby the most important (and entertaining). In particular she told us about two patches for 11.2.0.3, one current and one that is yet to be released (unfortunately I forgot to take  note of the patch numbers – ed: but they’ve been supplied by readers’ comment below).
(more…)

July 23, 2012

Compression Units – 2

Filed under: CBO,Exadata,HCC,Indexing,Oracle — Jonathan Lewis @ 4:41 pm BST Jul 23,2012

When I arrived in Edinburgh for the UKOUG Scotland conference a little while ago Thomas Presslie, one of the organisers and chairman of the committee, asked me if I’d sign up on the “unconference” timetable to give a ten-minute talk on something. So I decided to use Hybrid Columnar Compression to make a general point about choosing and testing features. For those of you who missed this excellent conference, here’s a brief note of what I said.
(more…)

July 16, 2012

ANSI Outer 2

Filed under: ANSI Standard,CBO,Execution plans,Oracle — Jonathan Lewis @ 4:55 pm BST Jul 16,2012

A comment on a recent post of mine pointed me to a question on the OTN SQL and PL/SQL Forum where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables (plus a few extras) are all available on OTN. If you create only the four tables needed and all their indexes you will need about 1.3GB of space.

The core of the problem is this: there is a three table join which does a hash join involving an index fast full scan on a particular index; when you add a fourth table to the join this fast full scan turns into a full tablescan for no obvious reason. Here are the queries, with the plans that I got when running 10.2.0.3. (My final plan is slightly different from the plan shown on OTN – I have a right outer hash join to the last table where the OP had a nested loop outer – but the difference is not significant). The queries, with their execution plans, are below- the three table join first:
(more…)

July 10, 2012

Random Plans

Filed under: CBO,humour,Oracle — Jonathan Lewis @ 5:32 pm BST Jul 10,2012

Have you ever felt that the optimizer was persecuting you by picking plans at random ? Perhaps you’re not paranoid, perhaps it’s part of Oracle Corp’s. master plan to take over the world. If you look closely at the list of hidden parameters you’ll find that some details of this cunning plan have leaked. In 10.1.0.2 Oracle created a new parameter _optimizer_random_plan with the description “optimizer seed value for random plans”. Who knows what terrible effects we may see when the default value of this parameter changes.

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 10.2.0.3 – but the same type of thing happens on newer versions):
(more…)

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:

select
	column_name,
	avg_col_len
from
	dba_subpart_col_statistics
where
	owner             = 'TEST_USER'
and 	table_name        = 'TEST_COMP'
and	subpartition_name = 'P_MAX_D'

(more…)

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 was running this test on 11.2.0.3):
(more…)

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

(more…)

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
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1000)
select
	rownum			id,
	mod(rownum,100)		n1,
	mod(rownum,100)		n2,
	mod(rownum,100)		n3,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1000000;

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

-- collect stats, no histograms.

(more…)

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 11.1.0.7:

(more…)

February 20, 2012

Upgrades

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

(more…)

« Previous PageNext Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers