Thank you for posting this example. I had not considered the presence of a profile as the cause… and that might explain a question like this “why is my query slow when I list the columns in one order, but fast when I swap the order of the first two columns.”

I think that a parallel problem might be optimizing for performance when the data volumes are small (possibly with the help of hints or a profile), and then finding that as the data volumes grow (or the data changes significantly) the original optimizations actually hurt performance. Or, as Donatello demonstrated, if the data is loaded in such a way that a lower clustering factor is achieved, the execution plan is frozen, and then as the data is later inserted into the tables in a more or less random fashion, the clustering factor no longer approximates its original value, and thus causing the original optimizations to actually hinder performance.

]]>By: Chris Saxonhttps://hoopercharles.wordpress.com/2011/01/10/query-executes-in-seconds-with-the-rule-hint-and-several-minutes-otherwise-what-would-you-do/#comment-2661
Tue, 11 Jan 2011 20:52:47 +0000http://hoopercharles.wordpress.com/?p=4240#comment-2661Other hints would also “break” the profile as shown above, of course. I’m not trying to imply that this the only way to stop the profile being used, just that adding it does cause the plan to change (in this case), and therefore is a possible reason for the differences.
]]>By: Martin Preisshttps://hoopercharles.wordpress.com/2011/01/10/query-executes-in-seconds-with-the-rule-hint-and-several-minutes-otherwise-what-would-you-do/#comment-2660
Tue, 11 Jan 2011 20:10:10 +0000http://hoopercharles.wordpress.com/?p=4240#comment-2660until 10.2 there were still some rule hints in the abysses of the server: http://jonathanlewis.wordpress.com/2010/04/13/rule-rules/. If my memory serves me well I optimized some monitoring queries on v$-views by adding rule hints some years ago (in 10.2). But the only reference I could find now for this “technique” is http://www.dba-oracle.com/t_tuning_oracle_dictionary_sql.htm – and so this remains anecdotal evidence …
]]>By: Chris Saxonhttps://hoopercharles.wordpress.com/2011/01/10/query-executes-in-seconds-with-the-rule-hint-and-several-minutes-otherwise-what-would-you-do/#comment-2658
Tue, 11 Jan 2011 16:05:37 +0000http://hoopercharles.wordpress.com/?p=4240#comment-2658SQL Profiles may also cause adding a rule hint to be faster.

If a profile was created that no longer is the optimal plan (say before an index was created), then adding the rule hint will stop the profile being used. Consider (on an 11.2.0.1 database):

1) The performance improvement caused by the addition of the RULE hint could just be an illusion, for the reason that you identified.
2) Histograms – I had not even considered histograms. I am guessing that the optimizer did not collect histograms in this case, but the presence of histograms might very well apply to other cases.
3) It is true that the RULE based optimizer does not know what to do with a function based index, although Martin Preiss (in a comment below) found that the RULE based optimizer in Oracle Database 11.1.0.7 *will* use the function based index. So far I have tested Oracle Database 10.2.0.2, 10.2.0.5, 11.1.0.7, 11.2.0.1, and 11.2.0.2 – only 11.1.0.7 used the function based index with the RULE based optimizer. If I set a default parallel degree for the table the cost based (ALL_ROWS) optimizer will be used when the RULE hint is specified.
4) Another very good point. I used a single byte characterset – while it probably would not make a difference in this test case, it might make a difference in production system performance issues.
5) Nice answer. OK, then set the MBRC system statistic to a very low value, such as 1 or 2 (then be certain to change it back afterward).

If I re-executed the script, I might see different results. The result with the “__FAST=TRUE” hint, I believe, was just a lucky coincidence.

That is a good idea to compare the CLUSTERING_FACTOR, which is a statistic that is not considered by the RULE based optimizer. It could be the case that the CLUSTERING_FACTOR is just poorly calculated (due to the way the statistic is calculated, if Oracle finds that it is jumping back and forth between a small number of table blocks as the index is read, that will cause the CLUSTERING_FACTOR to increase significantly, while the actual performance impact can be quite small) – in which case a manual change to the index’s calculated CLUSTERING_FACTOR statistic might help if the index was used by the RULE based optimizer and not the cost based optimizer (as might be the case when the test case is executed on 11.1.0.7, or if a normal index were used).

I tried your test case (with the ORDER BY 10 in the INSERT INTO SQL statement) on 10.2.0.2 and found that the index was not used automatically (but there is a reason for this). Below are my results for the modified test case:

As you can see from the above, the calculated cost for the full table scan access path is 1040, while the calculated cost for the index access path is 1081 – so a full table scan was still used. Why did this happen? Let’s take a look at the system (CPU) statistics:

The above shows that the cost based optimizer is now using the index access path, because we modified the MBRC system (CPU) statistic.

Let’s try a slight variation of the original test case, this time we will not create a function based index, I have removed the LOWER function from the WHERE clauses, and have reset the MBRC system statistic back to a value of 32 (you can optionally remove the ORDER BY 10 clause in the INSERT INTO statement):

I wonder if the comments provided by readers have now covered all of the reasons why a RULE hinted query might complete in seconds while an unhinted query might require considerably longer?

]]>By: Donatello Settembrinohttps://hoopercharles.wordpress.com/2011/01/10/query-executes-in-seconds-with-the-rule-hint-and-several-minutes-otherwise-what-would-you-do/#comment-2652
Tue, 11 Jan 2011 09:05:25 +0000http://hoopercharles.wordpress.com/?p=4240#comment-2652Hi Charles,
“adjust” the clustering_factor could be a solution (note the order by clause that I added in your test case in the INSERT statement).

I would say they have solved the problem, but I wonder if those who had the problem can be used
this solution.
In the real world may not be an applicable solution, depends on many factors ..
as the table is used, the queries that are executed for that table
and more…

]]>By: Charles Hooperhttps://hoopercharles.wordpress.com/2011/01/10/query-executes-in-seconds-with-the-rule-hint-and-several-minutes-otherwise-what-would-you-do/#comment-2649
Tue, 11 Jan 2011 03:02:24 +0000http://hoopercharles.wordpress.com/?p=4240#comment-2649Metalink (MOS) offers one more confirmation that the rule based optimizer should not have been able to use the IND_T5_C10_F function based index, as had happened in 11.1.0.7:
Doc ID 66277.1 – “Concepts and Usage of Function Based Indexes”
Doc ID 189702.1 – “Rule Based Optimizer is to be Desupported in Oracle10g” (also lists other features that are not supported by the rule based optimizer)

—-

This Metalink document appears to be just what the OP of the OTN thread wanted to know:
Doc ID 247743.1 – “Guidelines for Tuning: Query under Rule is fast Query under Cost is Slow”

Nice catch. I can confirm that in 10.2.0.5, 11.2.0.1, and 11.2.0.2 the RULE based optimizer cannot use the IND_T5_C10_F function based index, while the RULE based optimizer in 11.1.0.7 is able to use the function based index (this happens when the RULE optimizer is hinted and also when the OPTIMIZER_MODE is set to RULE at the session level.

However, the statement that I used to create the index IND_T5_C10_F is this:

CREATE INDEX IND_T5_C10_F ON T5(LOWER(C10));

The above syntax tells Oracle to create a function based index on LOWER(C10) (and store the result of that function in the index). When defined that way, the index will only be used when LOWER(C10) appears in the WHERE clause (or in a JOIN clause in an ANSI formatted SQL statement).

It is my understanding that the RULE based optimizer is not supposed to be able to use function based indexes. One of the outcomes that I originally hoped would have happened is that the cost-based optimizer would have automatically selected to use the (potentially much slower) function based index access path, while the RULE based optimizer would have had to use a full table scan. The test server, with the default value for OPTIMIZER_INDEX_COST_ADJ, and its fast random access SSD disk array, probably skewed the outcome a bit.