A new version 4.1 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.
Here are the notes from the change log:
- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in (more...)
This is just a short note that the next part of the mini series
about Parallel Execution skew has been published at AllThingsOracle.com
After having shown in the previous instalment
of the series that Oracle 12c added a new feature that can deal with Parallel Execution skew (at present in a limited number of scenarios) I now demonstrate in that part how the problem can be addressed using manual query rewrites, in particular the (more...)
This is just an addendum to the previous post
demonstrating one example (out of many possible) where the join skew handling feature fails. The test case setup is the same as in the previous post.As mentioned in the AllThingsOracle.com article
and in the introduction of the previous post, the feature at present only applies to a rather limited number of scenarios. To wrap things up and to give an idea what can happen (more...)
Besides the officially available information about new optimizer features in 12c it is always a good idea to have a look at the internal optimizer parameters that show what features are enabled when running with OPTIMIZER_FEATURES_ENABLE = 220.127.116.11. Here is the list of internal optimizer parameters and fix controls that are different between 18.104.22.168
_optimizer_partial_join_eval partial join evaluation parameter
_optimizer_unnest_scalar_sq enables (more...)
Oracle 12c introduces several new features in the area of Parallel Execution. Over the next couple of weeks I attempt to publish more about them - Jonathan Lewis for example already published a note
about the new "PQ Replication" feature that applies to the BROADCAST distribution of small tables.One important new feature is the automatic skew handling
for parallel joins. I've already given an overview of the feature in my mini-series "Parallel Execution Skew" (more...)
This is just a short notice that the next part of the mini-series
"Parallel Execution Skew" is published at AllThingsOracle.com
This is the third part of the video tutorial "Analysing Parallel Execution Skew". In this part I show how to analyse a parallel SQL execution regarding Parallel Execution Skew.
If you don't have a Diagnostics / Tuning Pack license the options you have for doing that are quite limited, and the approach, as demonstrated in the tutorial, has several limitations and shortcomings.
Here is the video:
If you want to reproduce or play around with (more...)
This is the second part of the video tutorial "Analysing Parallel Execution Skew". In this part I introduce the concept of "Data Flow Operations (DFOs)" and "DFO Trees", which is what a Parallel Execution plan is made of. DFOs / DFO Trees are specific to Parallel Execution and don't have any counterpart in a serial execution plan.
Understanding the implications of DFOs / DFO Trees is important as prerequisite for understanding some of the effects (more...)