A couple of weeks ago I was at a MasterClass from Jonathan Lewis about the Cost Based Optimizer. It was a very interesting two days and I like to give some nice highlights on the masterclass.
First of all it is astonishing how Oracle is able to change the Cost Based optimizer in every release, and even behaviour can be changed by a single rdbms patch (even if you’re not aware of it). Due to the master class I became even more aware of the fact that testing business critical sql statements between rdbms patchsets is even more important than on first notice. For example a subquery in the following query can be unnested (when unnesting a temp view will be created and a hash join will be used), but the optimizer version defaults are completely different:
select o.*
from emp o
where o.sal > (
select avg(i.sal)
from emp i
where i.dept_no = o.dept_no
);
– In 8.1.7.4 the above query will not be unnested, or you have to give the hint /*+ unnest */ in the subquery.
But if you give the hint the cost for the unnest will be zero (what is wrong).
– In 9.2.0.6 the above query will be unnested, but the cost will also be zero.
– In 10G the above query will be unnested and the cost will be calculated to do this.
Due to all these difference in behaviour a lot of different choises may follow when your rdbms is upgraded. In the example above the difference between 8i and 9i is caused by the underscore parameter _unest_subquery.
As a second thing I really like to mention is the fact that Hash Joins are really nice but have some counter part. It is really important to checkout in a system if there are big hash joins which look not to perform as they should. This kind of hash joins might be slow due to wrong memory usage. A nice event to use for this is event 10104. This event makes it possible to do better research in the memory management within a hash join.
As a third thing I like to mention and something I really know much about is the fact that expain plan’s can be really put you on the wrong feet. When investigating performance issues it is important too use the real row source information, such as in v$sql_plan and the trace files from 10046. To investigate some nice events have been enhanced in 10GR2, event 10132 (shows optimizer environment settings) and event 10053 (information regarding the execution plan).
In the end I really like to recommend the book "Cost-Based Oracle Fundamentals" by Jonathan Lewis, and of course to be present at a master class of Jonathan Lewis.
Regards, Gerwin Hendriksen
