Hints and sorts |
Jan 2006 |
There is white paper on OTN at present about the improved sorting algorithm used by Oracle in 10g Release 2. The file name of this paper is twp_general_sort_performance_10gr2_0605.pdf and, as the name indicates, it was written about six months ago to describe the general improvements in the sort algorithms of Oracle 10g Release 2.
Before I say anything else, I want to say very firmly that the point made by the paper is correct – there is a new sorting algorithm (which gets reported in various places as a version 2 sort) that is faster and more memory efficient than the previous sort algorithm. So when you’ve finished reading my notes, please don’t think that they invalidate the conclusion of the white paper – it seems likely that the whitepaper was written before the feature I will be talking about actually appeared.
As a demonstration of the sort performance, the paper uses the following (pseudo-)SQL as the test case:
select count(*)
from (
select /*+ NO_MERGE
*/
<column(s)>
from <table>
order by
<column(s)>
);
Now I thoroughly approve test cases, and if someone offers me the chance to test their claims by quoting their source code I’m always ready to try it out for myself, so that’s what I did in this case. I built a test table of 1,000,000 rows on 10gR1 and ran a query like the above, then repeated the test on 10gR2. Both databases were running on the same hardware, with the same set of non-default spfile parameters and, just like the whitepaper said, the performance on 10gR2 was fantastic compared to the performance on 10gR1. The exact query I ran was as follows:
select
count(*)
from
(
select /*+ no_merge
*/
key_value, padding
from
t1
order by
key_value, padding
)
;
Of course, I never trust the clock entirely, so I enabled a few traces to see what was going on (10032 and 10033 are particularly useful for sorting) checked the execution plan for differences, and did several other test. Forget the traces and tricky stuff – this is what dbms_xplan.display() told me:
----------------------------------------------------
| Id | Operation | Name | Rows | Cost
|
----------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 50 |
| 1 | SORT AGGREGATE |
| 1 | |
| 2 |
VIEW | | 65536 | 50 |
| 3 |
TABLE ACCESS FULL| T1 | 65536
| 50 |
----------------------------------------------------
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=423 Card=1)
1
0 SORT (AGGREGATE)
2
1 VIEW (Cost=423 Card=65536)
3
2 SORT (ORDER BY) (Cost=423
Card=65536 Bytes=1966080)
4 3 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=50 Card=65536 Bytes=1966080)
Spot the very important difference. Look at line 3 of the 10.1.0.4 plan, and try to find the matching line in the 10.2.0.1 plan – it isn’t there. Oracle is NOT sorting the data in 10.2. This was a little worrying; after all, there is a no_merge hint in the SQL, which means “do not do complex view merging with this query, instantiate the inline view before doing the rest of the query”. So Oracle has to do the sort (order by) to create the view identified in line 2 of the 10.1.0.4 plan.
So is the optimizer ignoring a hint? Of course not – the optimizer never ignores hints … so long as they’ve been used properly. In fact, even if we assume that the optimizer is ignoring the hint in this case the plan still wouldn’t really make sense. So what’s going on? The answer lies in a new parameter: _optimizer_order_by_elimination_enabled which defaults to true, and has the description: “Eliminates order bys from views before query transformation”. By looking at the 10.2 trace file (which is far more informative than the 10.1 equivalent) we can see that the optimizer has an explicit declaration (hacked around here) of this feature:
OBYE - order by elimination
OBYE: Removing order by from
query block SEL$2 (#0) (order not used)
Registered qb: SEL$73523A42 0x7be6390 (ORDER BY REMOVED FROM QUERY BLOCK SEL$2; SEL$2)
We can even see a line telling use that complex view merging has been bypassed because of a NO_MERGE hint. How’s that for helpful !
In this example, the optimizer could work out that the count(*) would be the same whether or not we sorted the data – so it eliminated the sort completely as a waste of effort – which would make the sorting in 10.2 seem a lot faster if you had relied on the clock and hadn’t checked carefully under the covers.
If you want to modify the code so that the sort takes place – and gives you a fair test – you could do the following:
select
count(*)
from (
select
/*+
no_merge
qb_name(my_view)
no_eliminate_oby(@my_view)
*/
key_value, padding
from
t1
order by
key_value
)
;
The hints in this query give a name to the inline query block qb_name(my_view), and then tell the optimizer that it is not allowed to eliminate the order by clause in the query block called my_view. (There is a corresponding hint eliminate_oby(@blockname) if the optimizer isn’t eliminating an order by when it should – neither of these hints appear to be documented as yet)