Oracle Scratchpad

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.

(more…)

May 2, 2008

Rules for Hinting

Filed under: Hints — Jonathan Lewis @ 8:13 am BST May 2,2008

I’ve written a number of notes about hinting in fact, by using at the “Select Category” list to the right, I see that I have (so far) tagged 26 different articles (and this will be the 27th) with the hints tag. So I’ve decided it was time that I made clear my basic guidelines on safe hinting, as follows:

  1. Don’t
  2. If you must use hints, then assume you’ve used them incorrectly.
  3. On every patch or upgrade to Oracle, assume every piece of hinted SQL is going to do the wrong thing … because of (2) above. You’ve been lucky so far, but the patch/upgrade lets you discover your mistake.
  4. Every time you apply some DDL to an object that appears in a piece of hinted SQL assume that the hinted SQL is going to do the wrong thing … because of (2) above. You’ve been lucky so far, but the structural change lets you discover your mistake.

You will appreciate from these guidelines that I don’t really approve of using hints. The only reason that I leave them in place on a production system is when I’m sure that there is no alternative safe mechanism for making the optimizer do what I want. (And that does mean that I will use hints sometimes on a production system.)

What I use them for on test systems is to check whether a particular execution plan is actually possible, and to track down bugs in the optimizer.

Finally, for the purposes of education, I use them to demonstrate execution plans without first having to craft data sets and set database parameters to make a plan appear ‘spontaneously’.

Always be cautious about adding hints to production systems.

March 9, 2008

Cursor_sharing

Filed under: CBO,Execution plans,Hints,Troubleshooting — Jonathan Lewis @ 9:13 pm BST Mar 9,2008

From time to time I’ve commented on the fact that setting cursor_sharing to force or similar may be a temporary workaround to bad coding practises, but that it can introduce problems, has a few associated bugs, and shouldn’t  be viewed as a guaranteed, or long-term, solution.

(more…)

February 17, 2008

Pushing Predicates

Filed under: CBO,Execution plans,Hints — Jonathan Lewis @ 8:32 pm BST Feb 17,2008

Some time ago I wrote a note (on my website) about the push_pred() and no_push_pred() hints. I’ve recently discovered a bug in the 9.2 optimizer that means you may find that Oracle will not use “join predicate pushdown (JPPD)” when it is obviously a good idea.

This note discusses a sequence of execution plans, taken from a system running 9.2.0.8, to demonstrate the point.
(more…)

July 26, 2007

Subquery Factoring (2)

Filed under: Hints,Oracle,Performance,Subquery Factoring,Tuning — Jonathan Lewis @ 8:24 pm BST Jul 26,2007

I’ve written about subquery factoring a few times in the past and commented on the use of the /*+ materialize */ hint. Recently I had time to think about what it would take for the Cost Based Optimizer to decide to materialize a subquery without hinting.

I doubt if I have a complete answer yet, and I sometimes wonder if the optimizer code for handling subquery factoring is not yet complete, but my observations are as follows.

(more…)

July 7, 2007

Functions

Filed under: Hints,Performance,Statspack,Tuning — Jonathan Lewis @ 8:25 pm BST Jul 7,2007

The following question appeared on the Oracle Forums recently:

The use of functions – a function with other selects (eg. calculate availability of a part) – is slowing down our system when we do a select over our product file.
Is there some kind of rule when functions should be used or when we should try to create a more complex – combined – SQL that does not use the function.
Can functions be used in the where clause without loosing a lot of speed?

It’s a really good question, because it prompts such a lot of ideas that need to be tied together, so I thought I’d jot down a few thoughts.

(more…)

June 25, 2007

qb_name

Filed under: Execution plans,Hints,Parallel Execution,Tuning — Jonathan Lewis @ 8:15 pm BST Jun 25,2007

When Oracle tries to optimize a complex SQL statement one of the first steps it takes is to transform it into something simpler. The ideal target (from the optimizer’s perspective) is to eliminate any subqueries and in-line views so that your SQL statement ends up as a list of tables in a single from clause. Every in-line view and subquery is a separate query block, and the optimizer really wants to turn the whole thing into a single query block. Unfortunately this isn’t always possible, and sometimes it isn’t even a good idea – which is why 10g introduced ‘cost base query transformation’.

(more…)

June 17, 2007

Hints – again

Filed under: Hints — Jonathan Lewis @ 8:03 pm BST Jun 17,2007

How well do you know your hints? When you see this one /*+ parallel(t1 4) */ what, exactly, does it mean ? The answer may not be what you think – and if you don’t know exactly what a hint means, how can you make it “work properly” ?

(more…)

March 9, 2007

Push_Subq

Filed under: CBO,Execution plans,Hints,Troubleshooting,Tuning — Jonathan Lewis @ 2:08 pm BST Mar 9,2007

Another little warning for those about to upgrade from 9i to 10g. Some hints will change their behaviour. Consider the following SQL.
(more…)

March 5, 2007

Web Presentation

Filed under: CBO,Execution plans,Hints,Tuning — Jonathan Lewis @ 11:45 pm BST Mar 5,2007

A few days ago, I recorded a Web presentation at Oracle’s offices in Reading. The topic I chose was the one that I ‘ve just repeated at the Hotsos 2007 event: dissecting one query to see how much you can learn about how the optimizer works, and how you have to think when dealing with a difficult problem in SQL.

The entire presentation – one hour long, audio synchronised with slide-show as a Macromedia Breeze presentation – can be heard at this rather long URL

The generic structure of the query used in the presentation was the one I first introduced in the blog item about Join Orders.

 

February 21, 2007

Ignoring Hints

Filed under: Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 9:04 pm BST Feb 21,2007

Here’s a whimsical, but very telling, example of Oracle “ignoring” hints.

I have the following query, which includes a hint to use a specific index when visiting a certain table. It’s the primary key index, so has no issues relating to null values making the hint invalid – yet Oracle does not use this index. Has it ignored the hint ?

(more…)

February 12, 2007

SQL Profiles – 2

Filed under: CBO,Execution plans,Hints,trace files — Jonathan Lewis @ 8:11 pm BST Feb 12,2007

Following yesterday’s note on SQL Profiles, someone asked how I detect that an opt_estimate hint had been used – with specific reference to the “index_scan” option. The reason for this particular choice is that other opt_estimate hints have a highly visible impact in the 10053 trace files, but this one doesn’t.

(more…)

February 11, 2007

SQL Profiles (10g)

Filed under: CBO,Execution plans,Hints,Performance,Statistics,Tuning — Jonathan Lewis @ 7:43 pm BST Feb 11,2007

[Forward to part 2]

When the Tuning Advisor suggests that you accept a SQL Profile, what is it offering you. If you want to find out, the following SQL seems to be the appropriate query to run before you accept the profile:
(more…)

January 24, 2007

Join Ordering – 1

Filed under: CBO,Execution plans,Hints,Oracle,Tuning — Jonathan Lewis @ 12:02 am BST Jan 24,2007

Here’s a little puzzle about execution plans. How many (significantly) different strategies are there for the following SQL – and how many of them will the Cost Based Optimizer be unable to find without being explicitly hinted ?
(more…)

January 18, 2007

Using 10053

Filed under: CBO,Execution plans,Hints,Ignoring Hints,trace files,Troubleshooting — Jonathan Lewis @ 8:51 pm BST Jan 18,2007

A little while ago I wrote a note about an anomaly that someone was seeing with the index_ss() hint. Since then, they forwarded me a 10053 trace file of the question.

I’ve posted brief analysis of it on my website, just to give you an idea of the sorts of things you can look out for when you’re trying to work out why Oracle seems to be ignoring a hint.

[Further reading on "ignoring hints"]

« Previous PageNext Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,910 other followers