The idea for this blog post started a few weeks ago when i had to troubleshoot some Oracle database / SQL performance issues at client site. The SQL itself included several views and so placing hints (for testing purpose) into the views was not possible, especially as the views were used widely and not only by the SQL with the performance issue. In consequence this blog post is about the difference between embedded global and local hints and how to use them.
The idea for this blog post is based on a recent Twitter discussion with Martin Berger, Martin Bach and Mauro Pagano about revealing SQL Plan Directive details for an existing cursor as walking through the standard Oracle data dictionary views can be very time consuming/slow and there are still some details missing about the dynamic sampling task itself, even if you have found what you are looking for.
We already took a (tiny) closer look at the cost based optimizer in my first series called "DB Optimizer". So i decided to start another tiny series about the SQL execution engine, which executes the calculated and generated execution plan. In the first part of this series i would like to cover a common ABAP coding "mistake", that i notice on regular basis and how it effects the execution / processing by using hash join(s) for two or more tables.
Let's check what hash joins are about before we start to take a look at the execution of hash joins. The following explanation is extracted from the official oracle documentation: