By Franck Pachot . I’ll present about join methods at POUG and DOAG. I’ll show how the different join methods work in order to better understand them. The idea is to show Nested Loops, Hash Join, Sort Merge Join, Merge Join Cartesian on the same query. I’ll run a simple join between DEPT and EMP with the USE_NL, USE_HASH, USE_MERGE…
By Franck Pachot . More and more we can see crazy queries generated by ORM frameworks or BI query generators. They are build to be easily generated rather than being optimal. Then, the optimizer has to implement more and more transformations to get an efficient execution plan. Here is one new that appeared in Oracle 12cR2: Subquery Elimination when the…
By Franck Pachot . There are several bugs with the optimizer in FIRST_ROWS mode. Here is one I encountered during a 10.2.0.4 to 12.2.0.1 migration when a view had an ‘order by’ in its definition. Here is the test case that reproduces the problem. A big table: SQL> create table DEMO1 (n constraint DEMO1_N primary key,x,y) as select 1/rownum,’x’,’y’ from…
The Optimizer Statistics Advisor is a new Advisor in Oracle 12.2. The goal of this Advisor is to check the way you gather the statistics on your database, and depending on what is found, it will makes some recommendations on how you can improve the statistics gathering strategy in order to provide more efficient statistics to the CBO. This Advisor…
By Franck Pachot . By a strange coincidence, I’ve encountered several cases of long parse time recently. That was the occasion to give a closer look at a CBO trace enhancement that came with April 2016 PSU: Bug 16923858 Diagnostic enhancement to annotate 10053 trace with accumulated parse time I have a query that takes several seconds to parse and…
By Franck Pachot . Do you know the optimizer_features_enable parameter? What do you think about it? Good or bad to use it? If I tell you to set optimizer_features_enable parameter=11.2.0.4 when you upgrade to 12c, do you think it’s a very safe decision, or totally insane to upgrade and set behavior to previous version? It’s not an underscore parameter, you…
