Oracle Scratchpad

April 13, 2010

Rule Rules

Filed under: Execution plans,Hints,Troubleshooting — Jonathan Lewis @ 6:39 pm BST Apr 13,2010

Everybody knows you shouldn’t be using the Rule-based optimizer (RBO) any more – everyone, that is, except some of the folks at Oracle Corp.

I had a conversation a few weeks ago with someone who was having a problem with their standby database on 10.2 because a query against v$archive_gap was taking a very long time to complete. Now that’s an easy to address (in principle) – collect stats on the underlying X$ objects using the call dbms_stats.gather_fixed_objects_stats() and the magic of cost-based optimisation takes over and solves everything.

February 23, 2010

Dynamic Sampling

Filed under: CBO,Hints,Oracle — Jonathan Lewis @ 7:41 pm BST Feb 23,2010

If you read the manual pages about “dynamic sampling” it’s easy to get just a little lost in the detail; so this is a brief overview of the variations in the strategies used.


February 11, 2010

Ignoring Hints – 2

Filed under: CBO,Execution plans,Hints,Ignoring Hints — Jonathan Lewis @ 7:28 pm BST Feb 11,2010

Here’s a little puzzle that someone sent to me a couple of days ago – it’s a case where the optimizer seems to be ignoring a hint.


February 4, 2010

SQL Server

Filed under: CBO,Execution plans,Hints,Infrastructure,Oracle,SQL Server,Statistics,Wishlist — Jonathan Lewis @ 7:07 pm BST Feb 4,2010

A few days ago I did a presentation on SQL Server. This probably sounds a little strange given my status as an Oracle specialist – but the nice people at Microsoft asked me if I would contribute to one of their seminars so I downloaded and installed the 180 day free trial copy of the Enterprise version, then downloaded the “Books Online” manuals and started to play.

It was an interesting experience – and I think the audience (and organisers) found my presentation useful. The title was “What the Enterprise needs in an RDBMS” – and that’s something I do know about – and the presentation was about whether or not you could find everything you needed in SQL Server 2008, where you’d have to look in the manuals, and supplementary questions you’d have to ask.


October 2, 2009

Quiz Night

Filed under: Hints,Ignoring Hints — Jonathan Lewis @ 6:15 pm BST Oct 2,2009

Why is Oracle ignoring my hints ?
I have a table and want to count the rows, so here’s the query and execution plan I get on the first attempt:

select /*+ full(t) */ count(*) from t1 t;

| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT      |       |     1 |    79   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_N2 | 47343 |    79   (2)| 00:00:01 |


August 7, 2009

Rownum effects

Filed under: CBO,Execution plans,Hints — Jonathan Lewis @ 7:21 pm BST Aug 7,2009

A few months ago, I wrote a note about setting the optimizer_mode to one of the first_rows_N values (first_rows_1, first_rows_10, first_rows_100, or first_rows_1000).

One of the effects associated with this parameter is that the first_rows(N) hint and the predicate “rownum <= N” use the same first_rows_N arithmetic (although N can take any value for the rownum or hint).

In a recent follow-up, Timur Akhmadeev supplied a link to a discussion on the Oracle Forum about this topic, starting with a problem that a rownum predicate was causing and ending with a resolution through the row_number() analytic function.

I thought it would be worth making it easier for future researches to find the discussion by creating a specific blog item to point to it.

July 1, 2009

Distributed Queries

Filed under: distributed,Execution plans,Hints — Jonathan Lewis @ 7:20 am BST Jul 1,2009

Some time ago I wrote a note about distributed DML, pointing out that the driving_site() hint works with distributed queries but not with DML based on distributed queries; so insert as select, or create as select and so on will “ignore” the hint.

This is just a little follow-up to give you an  idea of what execution plans for distributed queries look like so that you can tell whether your query is going to work locally or remotely.


June 14, 2009

Undocumented Hints

Filed under: Hints,Oracle — Jonathan Lewis @ 6:26 pm BST Jun 14,2009

The bits of Oracle which aren’t documented always seem to be the bits that are hard to resist, so I thought I’d make a brief comment on undocumented hints.

Of course, you should not take advantage of any undocumented feature without first getting approval from Oracle support, but some hints seem to me to fall into a special category where you are more likely to get that approval – and here are my thoughts on why.


June 1, 2009

Hints and Nulls

Filed under: CBO,Hints,Ignoring Hints,Indexing,NULL,Troubleshooting — Jonathan Lewis @ 7:16 pm BST Jun 1,2009

From time to time I check the site statistics to see if they give me any clues about why people are coming to blog – and recently I noticed that over the last year a particular referral from the OTN Database forum was had appeared fairly regularly – and it’s one that covers a small but significant optimizer detail that combines two crtical questions:  why is the optimizer not using my index and why is the optimizer ignoring my hint under the heading “Index hint does not work”.

I’ll leave you to read the thread – but the short answer is NULL.

A hint is illegal if using it could produce the wrong answer, and indexes where every column is nullable won’t necessarily reference every row in its table.

[Further reading on “ignoring hints”]

May 13, 2009


Filed under: CBO,Execution plans,Hints,Tuning — Jonathan Lewis @ 7:15 pm BST May 13,2009

When I am in America for a few days I usually try to find a local user group and ask them if they’d like to arrange an evening slot for their members. (Last time I was at Oracle Open World I went so far as to do a day trip up to Calgary because I didn’t think I was likely to go there any other way).

Collaborate 09 was no exception, and I contacted the CFOUG who arranged an event on the Monday evening at which I did a presentation called “Optimising through Understanding” where I talk about the analysis that could go into one simple SQL statement, producing reasons why any of several different execution plans might be the most appropriate depending on circumstances.

The presentation is a variation of one I have now given several times – and there is even a voice-over webinar version of it that I linked to from an earlier blog item.

After the event James Taylor, the chairman of CFOUG, asked if I could let him have a copy of the presentation for their website – so I sent him a pdf file of the slides, and I’ve also posted the same pdf file on my blog.

May 9, 2009

Hints on Hints

Filed under: Execution plans,Hints,Tuning — Jonathan Lewis @ 5:58 pm BST May 9,2009

This is the title of a presentation I have given a few times in the last couple of years – most recently at Collaborate 09 in Orlando.

The IOUG has now posted on their website the presentation and a short article I wrote to go with it; but that’s for members only, so it seemed reasonable to publish the same thing on my own blog as well.

So here are links for a pdf file of the presentation, and the article in Word format.


April 28, 2009

Strategic Hints

Filed under: CBO,Execution plans,Hints,Oracle,Troubleshooting,Tuning — Jonathan Lewis @ 7:19 pm BST Apr 28,2009

I try to avoid hinting SQL if possible as it is very hard to do well, but there are a few hints that give an overview of how a query should operate without trying to control the detail of what the optimizer does. These are the hints that I call the “strategic hints” (possibly “query block hints” would be a better name – but there may be hints at the query block level that I wouldn’t qualify as strategic), and an example came up on the newsgroup recently that looks like an ideal example of how a couple of them could be used.

December 5, 2008

Distributed DML

Filed under: distributed,Hints,Infrastructure,Performance,Tuning — Jonathan Lewis @ 9:19 pm BST Dec 5,2008

Someone recently sent me a request about a piece of SQL they could not optimise. I don’t usually respond to private requests – it’s not an effective use of my time – but their example was something that pops up relatively frequently as a “bug” – so I thought I’d mention it here.

The SQL looked like this:

insert into tab3
select 				-- small result set
	tab1@dblink	t1	-- large data set
	tab1.col1 in (
			tab2	-- small data set


November 11, 2008


Filed under: CBO,Execution plans,Hints,Performance,Tuning — Jonathan Lewis @ 1:05 pm BST Nov 11,2008

When it comes to setting the optimizer_mode parameter you often hear people say that first_rows_N (for one of the legal values of N) should be used for OLTP systems and all_rows should be used for decision support and data warehouse systems.

There is an element of truth in the statement – but it’s really a hangover from the early days of CBO, and remembrance of  the old first_rows optimizer mode (** See footnote).


October 23, 2008

Manual Optimisation 3

Filed under: Execution plans,Hints,Oracle,Performance,sorting,Tuning — Jonathan Lewis @ 6:38 pm BST Oct 23,2008

[Back to Manual Optimisation part 2]

This little series started from a note I wrote about manual optimisation where I took advantage of a sort operation in a non-mergeable view to produce sorted data from a final nested loop join without including an “order by” that would have produced a large sort operation.

In fact, as I showed in a follow-up post, this was taking a convenient pagination mechanism to an extreme – and you might decide (with good reason, as Tom Kyte did) that it was an extreme that should not be used.


« Previous PageNext Page »

Powered by