I've published the final part
of my video tutorial and the final part
of my mini series "Parallel Execution Skew" at AllThingsOracle.com
concluding what I planned to publish about the topic of Parallel Execution Skew.
Since all this was published over a longer period of time this post therefore is a summary with pointers to the material.
If you want to get an idea what the material is about, the following video summarizes the (more...)
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 = 184.108.40.206. Here is the list of internal optimizer parameters and fix controls that are different between 220.127.116.11
_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...)
A minor update 4.01 to the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
These are the notes from the change log:
- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data
- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part (more...)
I've started a new mini series about "Parallel Execution Skew"
. In order to avoid bloating the articles too much there I'll post some accompanying notes here on my blog.If you follow the initial post
you'll see that the sample query demonstrated there scales almost perfectly with Parallel Execution - the serial execution
takes 57 seconds
on my test system, whereas the a Parallel Execution at a DOP of 4
takes something (more...)
Along the new mini series "Parallel Execution Skew"
that provides some information what you can do if you happen to have a parallel SQL execution that is affected by work distribution problems I'm publishing a series of video tutorials that explain how you can actually detect and measure whether a SQL execution is affected by skew or not.
Originally this tutorial was planned as one part (Part 5 actually) of the XPLAN_ASH (more...)
A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post
includes links to video tutorials explaining what the tool is about.As usual the latest version can be downloaded here.
The new version comes with numerous improvements and (more...)
When the optimizer has to estimate the data volume (the BYTES
column in the plan output), it usually bases this information on the column statistics
, if applicable and available (think of complex expressions).However, whenever there is a VIEW operator
in an execution plan, that represents an unmerged view, the optimizer obviously "loses" this information and starts applying defaults that are based on the column definition
.Depending on the actual content of the columns (more...)
The TIMESTAMP WITH TIME ZONE data type that got introduced a long time ago is known for some oddities, for example Tony Hasler has a nice summary of some of them here
.Here is another oddity that shows up when trying to aggregate on such a data type. Have a look at the following simple example:
create table t
rownum as id
, date '2000-01-01' + rownum - 1 as some_date
, cast(date (more...)
A new major release (version 3.0) of my XPLAN_ASH tool is available for download.You can download the latest version here.
In addition to many changes to the way the information is presented and many other smaller changes to functionality there is one major new feature: XPLAN_ASH now also supports S-ASH
, the free ASH implementation.
If you run XPLAN_ASH in a S-ASH repository owner schema, it will automatically detect that and adjust accordingly. (more...)
When using Locally Managed Tablespaces
(LMT) with variable, system managed extent sizes (AUTOALLOCATE
) and data files residing in ASM
the Allocation Unit (AU)
size can make a significant difference to the algorithm that searches for free extents.The corresponding free extent search algorithm when searching for free extents >= the AU size
seems to only search for free extents on AU boundaries
in order to avoid I/O splitting
.Furthermore the algorithm seems to (more...)
It's webinar time again.
Join me on Wednesday, May 8th at AllThingsOracle.com
for an overview session on the specifics of Oracle Parallel Execution.
The session starts at 16:00 UK (17:00 Central European) time. The webinar is totally free and the recording will made available afterwards.
Here's the link to the official landing page
where you can register and below is the official abstract:
Oracle Parallel Execution, a feature of the Enterprise Edition, allows (more...)
In the previous post
I've demonstrated an unexpected Nested Loop Join caused by an extreme data distribution. Although unexpected at first sight, the performance of the execution plan selected by the optimizer is decent - provided the estimates are in the right ballpark.Here is another case of an unexpected execution plan, this time about Merge Joins
In order to appreciate why the execution plan encountered is unexpected, first a quick summary about (more...)
Recently I came across some interesting edge cases regarding the costing of joins. They all have in common that they result in (at first sight) unexpected execution plans, but only some of them are actual threats to performance.
The first one is about outer joins
with an extreme data distribution. Consider the following data setup:
create table t1
rownum as id
, rpad('x', 100) as filler
, case when rownum > (more...)