Tune the App, Not the SQL – DBA Sherlock’s Adventures in Hibernate/jOOQ Land

Last weekend at Trivadis Tech Event, in addition to my session on Oracle 12c Client High Availability for Java (Application Continuity, Transaction Guard … choose your degrees of freedom”), I gave a quite different talk called Tune the App, Not the SQL – DBA Sherlock’s Adventures in Hibernate/jOOQ Land”.

In a nutshell, what is it all about? Well, if you’re a DBA, you might sometimes (or often, or seldom – depends on your job ;-)) do what we call “SQL Tuning”. It might look like this: You get a call that the application “is slow”. Fortunately, you’re able to narrow that down quickly, and it’s just “the click on that button” that is slow. Fortunately, too, the application is well instrumented, and getting traces for specific tasks is not a problem. So you get a SQL trace file, run tkprof on it, and obtain the problematic SQLs in order of elapsed time. Then you analyse the “Top SQL”. You may find it should be rewritten, or that it would benefit from an index. Perhaps adding additional data structures might help, such as creating a materialized view. Perhaps you’re unlucky and just an ugly hint will fix it.

So you tell the developers “please rewrite that, like this”, or you create that index. And after, it’s waiting and hoping that performance will improve – noticeably, that is. Because tuning that single statement might – if you’re unlucky – not make that much of a difference.

There is, however, another approach to tuning application performance (always talking about the DB related part of it here, of course), and that has to do with the application logic and not single statements. There is an excellent demonstration of this in Stéphane Faroults very recommendable book “Refactoring SQL applications”, in fact, this is right at the beginning of the book and something that immediately “draws you in” (it was like that for me :-)).

Application logic affects what, when, and how much data is fetched. Of course, there are many aspects to this – for example, it just may not be possible to replace two DB calls by one simply because another service has to be queried in between. Also, there will have to be a tradeoff between performance and readability/maintainability of the code. But often there will be a choice. And you will see in the presentation it is not always that combining two queries into one results in better performance.

In fact, it all depends. So the first conclusion is the ubiquitous “don’t just assume, but test”.

There is another aspect to this, though. While Stéphane Faroult, in his test case, uses plain JDBC, I am using – and comparing, in a way – two commonly used frameworks: Hibernate and jOOQ. (For an “interdisciplinary introduction” to Hibernate, see my talk from previous Tech Event, Object Relational Mapping Tools – let’s talk to each other!. Quite a contrast, jOOQ is a lightweight, elegant wrapper framework providing type safety and near-100% control over the generated SQL.)

Now while for a DBA looking at a raw trace file, it will always be a challenge to “reverse engineer” application logic from the SQL sent to the database (even if plain JDBC or jOOQ are being used), the difficulties rise to a new dimension with Hibernate :-). In the talk, I am showing a DBA – who doesn’t need to be convinced any more about the importance of application logic – trying to make sense of the flow of statements in a trace file: the only information he’s got. And it turns out to be very difficult …

But as he’s not just “some DBA”, but Sherlock, he of course has his informants and doesn’t remain stuck with his DB-only view of the world – which brings me to one of my ceterum censeo’s, which is “DBAs and developers, let’s talk to each other” :-).

The slides are here.

Advertisements

Wanted: Your Take on the Cy Bernetic Optimizer (CBO)

This is not really a technical blog post, it’s an invitation to discuss something. The “something” is what Tony Hasler calls “The Strategic Direction for the CBO”.
Short time ago I started reading his – very much worth reading, very entertaining, too – book on “Expert Oracle SQL”, where without much ado he says, regarding that direction:

Despite the concerns about predictability that customers started raising from the outset and continue to raise today, the strategic direction of the CBO continues to be based on adaptability. Features like dynamic sampling and bind variable peeking started to appear. When 11g arrived it became very clear that the CBO team was looking towards ever more adaptable and ever less predictable behaviors. The signals came in the form of Adaptive Cursor Sharing in 11gR1 and Cardinality Feedback in 11gR2. […] As if this wasn’t bad enough, 12cR1 introduced Adaptive Execution Plans. This feature might result in the execution plan of a statement changing in mid-flight! We don’t want these features!

(See below for a longer quotation, providing more context).

I found this very interesting – normally what you see is people getting annoyed with / frustrated by / furious over

  • the bugs accompanying these new features,
  • those execution plan changes that are (1) for the worse and (2) get noticed by users (or monitoring systems),

and the new features are getting disabled, at a varying granularity, after a varying amount of analysis/troubleshooting.
(As of today, what percentage of systems upgraded to 12c might be running with optimizer_adaptive_features turned off?).

But this is not about bugs or even how well it works, but about the “principle”. Assume they worked fine, performance was for the better most all the time – how would you like these optimizer self-tuning features?

Here is the Tony Hasler citation again, the complete paragraph this time:

One of the key things about the old Rule-Based Optimizer (RBO) was that the same statement resulted in the same execution plan every time. The CBO, on the other hand, is designed to be adaptable. The CBO tries to get the optimal execution plan each and every time, and if the object statistics suggest that the data has changed then the CBO will quite happily consider a new execution plan.
Despite the concerns about predictability that customers started raising from the outset and continue to raise today, the strategic direction of the CBO continues to be based on adaptability. Features like dynamic sampling and bind variable peeking started to appear. When 11g arrived it became very clear that the CBO team was looking towards ever more adaptable and ever less predictable behaviors. The signals came in the form of Adaptive Cursor Sharing in 11gR1 and Cardinality Feedback in 11gR2. Both Adaptive Cursor Sharing and Cardinality Feedback can result in the execution plan selected for the first execution of a statement being “upgraded” for the second and subsequent executions, even with no change to object statistics! Usually, these changes are for the better, but there are no guarantees.
As if this wasn’t bad enough, 12cR1 introduced Adaptive Execution Plans. This feature might result in the execution plan of a statement changing in mid-flight! We don’t want these features!
– On a test system we don’t want these features because they might mask problems that will impact our production service.
– On a production system we don’t want these features because the execution plans that we have tested are satisfactory, whatever the CBO thinks; we don’t want to risk using an untested plan that might cause performance to degrade.

With the above argumentation, convincing though it sounds, I have two concerns, which in the end are closely interrelated.

First, the “real world” one. There are, of course, many real worlds, but in how many of them is it that the teams of software developers and DBAs, collaborating closely, after thorough, institutionalized testing on the integration systems, can say: Our new code introduced those new execution plans, they are good (or even the best possible), we can safely release our code in production?
One real world I know is where developers develop, and DBAs are busy patching, upgrading databases, working around necessary OS upgrades, providing test systems, and ex- and importing data – only dealing with SQL performance if and when problems arise.

There is this section in the book about a Connor McDonald presentation culminating in

Of course the fictitious execution plan changed was caused by an explicitly run job to gather statistics. No authorization, no impact analysis, no testing, nothing. Connor asked the perfectly reasonable question as to why IT organizations routinely precipitate untested execution plan changes on their database systems while at the same time placing most other types of changes under the highest scrutiny. It seemed to Connor that that the world had gone mad. I agree!

(I told you the book was fun to read.) Now of course the world is mad, but it’s easy to understand the human and organizational factors leading to this special thing. Colors, phrasings, menus on a website are more tangible than execution plans, for one … But that’s another discussion.

So in a practical way, I imagine there’s an idea of easing the DBA’s job because anyway, her job is complex, and getting more complex – in an infrastructure way – all the time (think of the whole Big Data integration topic coming up).

So that leads to the second point. If the intention is to ease the DBA’s job, instead of making it more difficult, introducing problems etc., then adaptivity, or machine learning, sounds like a good idea. With supervised learning, me the DBA, I have analyzed a statement, know the best solution and tell the optimizer how to run it. With unsupervised learning, the optimizer finds out itself.
If we agree that a self-learning optimizer may be a good thing, the question is good are its methods/algorithms? Not in the way of “are they buggy”, but are they, in principle, good techniques that, when bugs have been removed, fulfill their purposes?

Many of the features concerned, and mentioned by Tony Hasler, are based on feedback loops. (That’s the reason for the cybernetic in the post’s title). Being based on feedback loops can mean one of two things here:

  • The feedback occurs after execution is complete. This means there was at least one bad execution. (In practice there may have been more). This is the case for Adaptive Cursor Sharing and Cardinality Feedback.
  • The feedback occurs mid-execution, as with 12c Adaptive Plans.

With the second form, in principle (again, bugs aside), I don’t have an issue. Join cardinality estimation is intrinsically difficult (just read up on Jonathan Lewis’ blog for that) – now you could either try to find a better generic, one-fits-all-and-handles-every-case algorithm, or you approach the question from another side – such as a control mechanism like the STATISTICS_COLLECTOR rowsource.

As for the first form, you (here meaning the software vendor, i.e., Oracle) could do several things to make it better.

  • Re-engineer the underlying algorithm so that fewer failures are needed before the good plan is found.
  • Persist the results, so we don’t start again from zero after the information (the cursor) is flushed from the shared pool.
  • Ideally, not even a single failure were needed – this is the same as conversion to the second form.

Now for improving the algorithms, this is I guess what’s happening between releases (I don’t have a testcase available, but I’d assume it could be shown for e.g. Adaptive Cursor Sharing between 11.1 and 12.1); for persisting results, look at the evolution from 11.1 cardinality feedback to 12.1 inclusion of that information (now renamed to “statistics feedback”) in SQL Plan directives; for converting to an in-flight algorithm, this may be feasible or not. If it’s feasible, it may still not be efficient.

Summing up, the direction seems to be “unsupervised learning, using mid-execution feedback / zero failure adaptive behavior where possible”. This does not look too bad to me. (By the way I’m not even sure that in the best (in-flight) form, this goes against testability as argued by Tony. 12c adaptive join methods use a deterministic inflection point calculation that should allow for reproducible tests given identical data.)

Did you notice the word “strategic” in the above quotation? Further down in the same chapter, Tony writes, with respect to SQL Plan Management

Although the CBO strategy is all about adaptability, Oracle, like any other company, has to do something to address the demands of its largest and most vocal customers. Here are the tactical plan stability measures that Oracle has taken to address customer concerns about predictability.

So there we have the strategy vs. tactics opposition. But do we necessarily need to see it like this? In the view I’m suggesting, of supervised vs. unsupervised learning, as long as SQL Plan Management goes “unattended” it is not fundamentally in contradiction to the adaptive features. With 12c SPM Evolve Advisor Task, this would be the case.

One digression before I wrap it up. Tony has another very interesting paragraph, about Graham Wood on frequency of statistics gathering:

In 2012 the Real World Performance team from Oracle did a round-the-world tour to try to dispel some myths about Oracle performance and to educate people about performance management. I attended the Edinburgh seminar and found it very stimulating. One of the most interesting suggestions came from Graham Wood. Graham’s observation was that many of Oracle’s customers gather statistics far too frequently. I want to make it clear that Graham did not say that gathering statistics was only required to prevent execution plan changes, and he most definitely didn’t liken gathering statistics to a game of Russian Roulette! However, Graham’s recommendation does suggest, quite correctly, that there is some risk to gathering statistics and that by doing it less often you can reduce that risk considerably.

The risk of statistics calculation refers to DBA_TAB_COL_STATISTICS.LOW_VALUE and DBA_TAB_COL_STATISTICS.HIGH_VALUE which when increasingly off will lead to increasingly wrong cardinality estimations (imagine having collected statistics at 10 P.M. and loading fresh, time-based data during the night – again read up on Jonathan Lewis’ blog for that topic). Here I must say this suggestion – don’t gather statistics too often – does not seem in line to me with the “general direction”. If I’m to trust Oracle to achieve good results with the “unsupervised learning approach”, I want to be able to assume this issue to be handled automatically, too – which would mean for Oracle to look into the algorithm related to how HIGH_VALUE and LOW_VALUE are used in cardinality estimation (introduce something like dynamic sampling here? in specific cases?).

So, that was my try on a take on that topic. What do you think? Do you agree with Tony that “we don’t want that”, or do you see value in the “unsupervised learning” approach? Please feel free to leave a comment to share your opinion!

Object Relational Mapping Tools – let’s talk to each other

Following up with the follow up of Trivadis Tech Event :-).
In addition to the five minute talk about Linux Tracing for Oracle, I presented on a quite different subject: “Object Relational Mapping Tools – let’s talk to each other”.
My original intention was to write 2 – 3 successive posts on the subject, but as always, time’s scarce and so I’ve decided to just upload the presentation, see here.

But allow me some words on the background. A database admin (though spare time coder, too ;-)), presenting on O/R Mappers? How could that be? At least then, these performance killers should really get pulled to pieces, right?
Well, no. They aren’t. Instead, my intentions have been the following:

  • Explain to a DBA audience why developers might want to use them.
  • Explain – to DBAs and developers both – what are probable difficulties, on a conceptual level (the infamous “Object-Relational Impedance Mismatch”)
  • Explain – again on a conceptual level, but with concrete examples, and to both audiences, too – what to watch out for when fetching data (i.e., doing SELECTs as opposed to data manipulation).

“What to watch out for” – this has actually been the talk’s main purpose. (I plan to follow up with load tests / “real empirical stuff” at a later time).
And how about the “let’s talk to each other”? You may have guessed already …

Monitoring execution plan changes

Times being a bit busy for me at the moment, unfortunately I have to postpone any “larger” projects, blogging-wise 😉 … but not wanting to lose the habit, I’ve thought I’d publish a little script I’ve written to track changes in execution plans.

The intention is to alert DBAs / developers to SQL statements that due to plan changes have experienced large variation in execution time.
When run, as I’d suggest, every day, the script quickly points out statements whose elapsed time at the most recent point of measurement is much different (in either direction) from elapsed times encountered in earlier snapshots that form part of the comparison moving window.

The moving window size, as well as the threshold (what kind of difference should be considered a big difference?) are parameterized. Different environments / applications will have different optimal values here, plus you will have to find a configuration where the amount of information (number of changed plans) can be handled by the DBAs 🙂

So, here’s the script.

set sqlblanklines on
set lines 800
set pages 1000

/* statements captured during last  days */
with samples as 
 (select *
  from dba_hist_sqlstat st
  join dba_hist_snapshot sn
  using (snap_id, instance_number) 
  where parsing_schema_name = '&schema_name'
  and module  'DBMS_SCHEDULER' -- no sql tuning task
  and executions_delta > 0
  and begin_interval_time between sysdate - '&num_days' and sysdate),

/* just statements that had at least 2 different plans during that time */
  sql_ids as 
   (select sql_id,
    count(distinct plan_hash_value) plancount
    from samples
    group by sql_id
    having count(distinct plan_hash_value) > 2),

/* per combination of sql_id and plan_hash_value, elapsed times per execution */
    plan_stats as 
     (select sql_id,
      plan_hash_value,
      count(snap_id) snap_count,
      max(end_interval_time) last_seen,
      sum(executions_delta) total_execs,
      sum(elapsed_time_delta) / sum(executions_delta) elapsed_per_exec_thisplan
      from sql_ids
      join samples
      using (sql_id)
      group by sql_id, plan_hash_value),

/* how much different is the elapsed time most recently encountered from other elapsed times in the measurement interval? */
      elapsed_time_diffs as 
       (select p.*,
        elapsed_per_exec_thisplan - first_value(elapsed_per_exec_thisplan)
          over(partition by sql_id order by last_seen desc) elapsed_per_exec_diff,
        (elapsed_per_exec_thisplan - first_value(elapsed_per_exec_thisplan)
          over(partition by sql_id order by last_seen desc)) / elapsed_per_exec_thisplan elapsed_per_exec_diff_ratio
        from plan_stats p),

/* consider just statements for which the difference is bigger than our configured threshold */
        impacted_sql_ids as 
         (select *
          from elapsed_time_diffs
          where abs(elapsed_per_exec_diff_ratio) > &threshold),

/* for those statements, get all required information */
          all_info as
           (select sql_id,
            plan_hash_value,
            snap_count,
            last_seen,
            round(elapsed_per_exec_thisplan / 1e6, 2) elapsed_per_exec_thisplan,
            round(elapsed_per_exec_diff / 1e6, 2) elapsed_per_exec_diff,
            round(100 * elapsed_per_exec_diff_ratio, 2) elapsed_per_exec_diff_pct,
            round(max(abs(elapsed_per_exec_diff_ratio))
              over(partition by sql_id), 2) * 100 max_abs_diff,
            round(max(elapsed_per_exec_diff_ratio) over(partition by sql_id), 2) * 100 max_diff,
            'select * from table(dbms_xplan.display_awr(sql_id=>''' || sql_id ||
            ''', plan_hash_value=>' || plan_hash_value || '));' xplan
            from elapsed_time_diffs
            where sql_id in (select sql_id from impacted_sql_ids))

/* format the output */
            select case sign(elapsed_per_exec_diff)
              when 0 then max_abs_diff ||
                case when max_abs_diff  max_diff then
                  '% worse than another plan found during the last 7 days'
                else
                  '% better than the next best plan during the last 7 days'
                end
             end status,
             a.sql_id,
             a.snap_count,
             to_char(last_seen, 'yy-mm-dd hh24:mi:ss') last_seen,
             to_char(a.elapsed_per_exec_thisplan, '999999.99') elapsed_per_exec_thisplan,
             to_char(a.elapsed_per_exec_diff, '999999.99') elapsed_per_exec_diff,
             to_char(a.elapsed_per_exec_diff_pct, '999999.99') elapsed_per_exec_diff_pct,
             xplan
             from all_info a
             order by sql_id, last_seen desc;

And this is how it might look (“pivoting” the output to make it readable here, and showing one statement only):

status                       	9073% worse than another plan found during the last 7 days 
sql_id   			698s47bt259sk
snap_count 			5
last_seen       		14-09-22  06:30:07
elapsed_per_exec_thisplan	315.74 
elapsed_per_exec_diff 		.00
elapsed_per_exec_diff_pct  	.00
xplan       			select * from table (dbms_xplan.dislay_awr(sql_id=>'698s47bt259sk', plan_hash_value=>3533318143));
                                                                                        
sql_id   			698s47bt259sk
snap_count 			5
last_seen       		14-09-22  06:30:07
elapsed_per_exec_thisplan	3.44 
elapsed_per_exec_diff 		-312.29
elapsed_per_exec_diff_pct  	-9072.77
xplan       			select * from table (dbms_xplan.dislay_awr(sql_id=>'698s47bt259sk', plan_hash_value=>3837578073));

Of course, the script would have to be adapted if you had to deal with statements using different literals on each execution (which was not the case for my usecase this time).

Speeding up a non-equijoin

This is just a small piece about an idea I’ve had to speed up the unfortunate “where tab1.col1 between tab2.col2 and tab2.col3” query.
This type of join is notoriously difficult because if you have a large amount of data in both tables, you will want Oracle to perform a hash join, which it can’t because we’re having a non-equijoin here.

Basically, the idea is to find a way to join the tables on a less precise identity condition, keeping false positives, and then filter out the rows that do not fulfill the real requirement. Instead of a sort merge join running endlessly, you want to end up with a hash join followed by filtering and sorting out duplicates.

While the idea is simple, the realization depends on finding such a “less precise condition” that will not lose any required rows – what we need is

f(tab1.col1) = f(tab2.col2) = f(tab2.col3)

and that might be difficult to find, or might necessitate a split into several subsets, each joined and filtered by itself, which again deteriorates the splendid performance we were hoping for. There might be, however, ways to find such a condition without bloating the query too much.

To demonstrate, I’ve built a test case analogous to the real case I’ve encountered, and I’ve populated the test tables in a way that did not make my life simple (this is to say, I did not “fake” the data to be easily handled by the idea, but generated random data that ended up having the same “problems” as encountered in the real world situation).

The test case

As in the real world example I’d encountered, the table containing the range definitions has two indexes, the “range start” column being the primary key, and the “range end” column having on it a unique index:

SQL> create table ranges (range_start number primary key, range_end number, label varchar2(10));
SQL> create unique index jointest.range_end_uk on jointest.ranges(range_end);
SQL> alter table ranges add constraint range_end_unique unique(range_end) using index jointest.range_end_uk;

SQL> create table cases (id number generated always as identity primary key, range_code number);

The amount of data inserted is a fraction of the real example’s size, adapting to the circumstances of the test being run on my laptop…

SQL> insert into ranges (range_start, range_end, label)
        with r (startnum, endnum, lbl) as
            (select 10000 startnum, 10000 + round(dbms_random.value(9000, 11000)) endnum,   dbms_random.string('a', 10) lbl from dual
             union all
             select endnum + 1, endnum + 1 + round(dbms_random.value(9000, 11000)), dbms_random.string('a', 10) from r
        where startnum < 1000000000)
        select * from r;
SQL> exec dbms_stats.gather_table_stats(user, 'ranges');

SQL> insert into cases (range_code, flag1, flag2)
        select round(dbms_random.value(10000, 1000000000)) from dual 
        connect by level < 100000;
SQL> exec dbms_stats.gather_table_stats(user, 'cases');

SQL> select table_name, num_rows from user_tables;

TABLE_NAME			 NUM_ROWS
------------------------------ ----------
CASES				    99999
RANGES				   100021

SQL> select table_name, column_name, num_distinct, histogram from user_tab_columns order by 1,2;

TABLE_NAME		       COLUMN_NAME		      NUM_DISTINCT HISTOGRAM
------------------------------ ------------------------------ ------------ ---------------
CASES			       ID				     99999 NONE
CASES			       RANGE_CODE			     99999 NONE
RANGES			       LABEL				    100021 NONE
RANGES			       RANGE_END			    100021 NONE
RANGES			       RANGE_START			    100021 NONE

The original query

Now first, for the original query.

SQL> select c.id, r.label from cases c join ranges r
  on (c.range_code between r.range_start and r.range_end);

For an output of 99999 rows, this query runs forever (40 mins on my laptop), doing a merge join between ranges (accessed via the primary key index) and cases on c.range_code>=r.range_start, while the join result is filtered on c.range_code<=r.range_end.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	54nqyafhsw8fp, child number 0
-------------------------------------
select c.id, r.label from jointest.cases c join jointest.ranges r on
(c.range_code between r.range_start and r.range_end)

Plan hash value: 3569056763

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	   | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		   |	  1 |	     |	     |	     | 34778 (100)|	     |	99999 |00:40:10.12 |   14854 |	 1500 |       |       | 	 |
|   1 |  MERGE JOIN		     |		   |	  1 |	2500M|	  86G|	     | 34778  (97)| 00:00:02 |	99999 |00:40:10.12 |   14854 |	 1500 |       |       | 	 |
|   2 |   TABLE ACCESS BY INDEX ROWID| RANGES	   |	  1 |	 100K|	2441K|	     |	 627   (1)| 00:00:01 |	  100K|00:00:04.38 |   13975 |	  625 |       |       | 	 |
|   3 |    INDEX FULL SCAN	     | SYS_C009905 |	  1 |	 100K|	     |	     |	 214   (1)| 00:00:01 |	  100K|00:00:01.26 |	6929 |	  214 |       |       | 	 |
|*  4 |   FILTER		     |		   |	100K|	     |	     |	     |		  |	     |	99999 |00:40:05.43 |	 879 |	  875 |       |       | 	 |
|*  5 |    SORT JOIN		     |		   |	100K|  99999 |	1171K|	3944K|	 699   (2)| 00:00:01 |	 4990M|00:25:59.14 |	 879 |	  875 |  3525K|   816K| 3133K (0)|
|   6 |     TABLE ACCESS FULL	     | CASES	   |	  1 |  99999 |	1171K|	     |	 240   (1)| 00:00:01 |	99999 |00:00:00.37 |	 879 |	  875 |       |       | 	 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("C"."RANGE_CODE"="R"."RANGE_START")
       filter("C"."RANGE_CODE">="R"."RANGE_START")

The workaround

So, I am looking for some function to apply to cases.range_code that will yield the same result when applied to range_start and range_end in the ranges table. Unfortunately, a simple

trunc(range_code/100000)

(lower numbers will work “even less”, while higher ones will render the hash join less effective) would lose rows:

SQL> select * from      
       (select range_start, range_end,
        trunc(range_start/100000) range_start_truncated,
        trunc(range_end/100000) range_end_truncated
        from ranges) 
        where range_start_truncated != range_end_truncated;


RANGE_START  RANGE_END RANGE_START_TRUNCATED RANGE_END_TRUNCATED
----------- ---------- --------------------- -------------------
...
903798841  903809794			9037		    9038
903899431  903908527			9038		    9039
903991927  904002717			9039		    9040
904095228  904104978			9040		    9041

9999 rows selected.

Still, the situation is not too bad: range_start and range_end, when divided by 100000 and truncated, differ by at most 1:

SQL> select * from      
       (select range_start, range_end,
        trunc(range_start/100000) range_start_truncated,
        trunc(range_end/100000) range_end_truncated
        from jointest.ranges) 
        where abs(range_start_truncated - range_end_truncated) > 1;

No rows selected.

which means I can simply divide up the hash join in two parts – trunc(range_code/100000) has to match either trunc(range_start/100000) or trunc(range_end/100000) – , union all the result set and look for distinct rows that match the original condition:

SQL> with pre_filter as
       (select c.id, c.range_code, r.range_start, r.range_end
        from cases c join ranges r
        on trunc(c.range_code/100000) = trunc(r.range_start/100000)
        union all
        select c.id, c.range_code, r.range_start, r.range_end
        from cases c join ranges r on
        trunc(c.range_code/100000) = trunc(r.range_end/100000))
          select distinct * from pre_filter
          where range_code between range_start and range_end;

This way, the query runs in one and a half seconds:

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation	      | Name   | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |	 |	 |	 |   374K(100)| 	 |  99999 |00:00:01.60 |    2670 |	 |	 |	    |
|   1 |  HASH UNIQUE	      |        |      1 |     50M|  1240M|  1915M|   374K  (2)| 00:00:15 |  99999 |00:00:01.60 |    2670 |  8288K|  3198K| 6597K (0)|
|   2 |   VIEW		      |        |      1 |     50M|  1240M|	 |  2570  (53)| 00:00:01 |    189K|00:00:01.37 |    2670 |	 |	 |	    |
|   3 |    UNION-ALL	      |        |      1 |	 |	 |	 |	      | 	 |    189K|00:00:01.30 |    2670 |	 |	 |	    |
|*  4 |     HASH JOIN	      |        |      1 |     25M|   620M|  2344K|  1285  (53)| 00:00:01 |  94838 |00:00:00.58 |    1335 |  6824K|  1791K|    9M (0)|
|   5 |      TABLE ACCESS FULL| CASES  |      1 |  99999 |  1171K|	 |   240   (1)| 00:00:01 |  99999 |00:00:00.03 |     879 |	 |	 |	    |
|   6 |      TABLE ACCESS FULL| RANGES |      1 |    100K|  1367K|	 |   137   (1)| 00:00:01 |    100K|00:00:00.03 |     456 |	 |	 |	    |
|*  7 |     HASH JOIN	      |        |      1 |     25M|   620M|  2344K|  1285  (53)| 00:00:01 |  95020 |00:00:00.54 |    1335 |  6824K|  1791K|    9M (0)|
|   8 |      TABLE ACCESS FULL| CASES  |      1 |  99999 |  1171K|	 |   240   (1)| 00:00:01 |  99999 |00:00:00.03 |     879 |	 |	 |	    |
|   9 |      TABLE ACCESS FULL| RANGES |      1 |    100K|  1367K|	 |   137   (1)| 00:00:01 |    100K|00:00:00.03 |     456 |	 |	 |	    |
-------------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(TRUNC("C"."RANGE_CODE"/100000)=TRUNC("R"."RANGE_START"/100000))
       filter(("C"."RANGE_CODE">="R"."RANGE_START" AND "C"."RANGE_CODE"="R"."RANGE_START" AND "C"."RANGE_CODE"<="R"."RANGE_END"))

Of course, the data might make things even more complicated in other cases. Still, even a union of several hash joins (where you carefully select the groups so no rows are lost) might run considerably faster than the sort merge join the optimizer has to do otherwise.