JL Computer Consultancy

Hints and sorts

Jan 2006


There is a 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 around June 2005 to describe the impact of the new algorithm.

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 of test cases and if someone offers me the chance to test their claims by supplying 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 unbelievably good 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 (events 10032 and 10033 are particularly useful for sorting) checked the execution plan for differences, and did several other test. Forget the traces and clever stuff though – this is what autotrace told me:

10.2.0.1 (with 65536 rows)

----------------------------------------------------

| 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 |

----------------------------------------------------

 

10.1.0.4 (with 65536 rows)

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 hidden 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 10053 trace file from 10.2 (which is far more informative than the 10.1 equivalent) we can see that the optimizer has an explicit declaration (cosmetically altered for readability) 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 us that complex view merging has been bypassed because of a NO_MERGE hint.

CVM: Considering view merge in query block SEL$1 (#0)

CVM:   Checking validity of merging SEL$73523A42 (#0)

CVM: Considering view merge in query block SEL$73523A42 (#0)

CVM:     CVM bypassed: Query 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 the optimizer moved inside the non-mergeable view to eliminate 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)


Back to Index of Topics