Here’s an extract from an execution plan I found on a client site recently. I’ve collapsed lines 5 to 42 into a single line representing the rowsource produced by a fairly messy execution plan, leaving just the last three stages of execution on view. Each of three operations joins the same collection variable (using the table() operator) to the row source – once through a hash join, then twice more (joining to two other columns) through nested loop outer joins:
The resulting estimates of row counts and costs are quite entertaining and, fortunately, not very accurate:
In an earlier article introducing the index join I raised a question that came up at the first ES2N virtual conference:
“If you hint an index hash join, is there any way of telling Oracle the order in which it should use the indexes?”
Consider the following example:
Here’s an example of “creative SQL” that I wrote in response to a question on OTN about combining data from two indexes to optimise access to a table. It demonstrates the principle that you can treat an index as a special case of a table – allowing you to make a query go faster by referencing the same table more times.
Unfortunately you shouldn’t use this particular example in a production system because it relies on the data appearing in the right order without having an “order by” clause. This type of thing makes me really keen to have a hint that says something like: /*+ qb_name(my_driver) assume_ordered(@my_driver) */ so that you could tell the optimizer that it can assume that the rowset from a given query block will appear in the order of the final “order by” clause as it does, for example, with sorted hash clusters.
I’ve previously published a couple of notes (hereand here) about the use of the driving_site() hint with distributed queries. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a distributed query. I’ve just found another case where the hint is ignored – this time in a simple SELECT statement.
Try running an ordinary distributed query from the SYS account, and then try using the driving_site() hint to make it run at the remote site. When I tried this a few days ago I ended up wasting half an hour translating some SQL from ANSI to Oracle dialect because I thought that the ANSI was making Oracle transform the query in a way that lost the hint – then I discovered that both versions of the code worked correctly if I logged in as a different user.
I was running my queries between two databases using 22.214.171.124 – I won’t guarantee you get the same results on other versions, but it looks like SYS doesn’t honour the driving_site() hint. I can’t think of a robust argument why this should be the case, but if I were forced to do some vague hand-waving I’d probably mumble something about potential security loopholes.
Footnote: I should, of course, have mentioned that there are all sorts of things that behave in unexpected ways if you are logged on as SYS, and that you shouldn’t be logged on as SYS – especially in a production system.
[Further reading on “ignoring hints”]
Here’s a simple piece of code demonstrating an irritating problem. I’ve created a table, a function-based index, collected stats (without histograms), and then run a query that should use that index – but doesn’t.
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.
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.
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.
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.
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 |
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.
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.
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.
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”]
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.