Prompted by a (not really that) recent discussion on the OTN
forum I've decided to publish this note.Sometimes you have the task of comparing column values and handling the NULL value cases correctly makes this rather cumbersome for columns that are allowed to be NULL.The "official" SQL way of comparing two column values and to find out whether they are equal or not - under the assumption that having NULL in both columns (more...)
A new version 4.23 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version comes only with minor changes, see the change log below.
Here are the notes from the change log:
- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)
Quite often you can get into trouble with Oracle when you start combining different features.In this case of one my clients it is the combination of user-defined PL/SQL functions
that can raise exceptions
(think of currency conversion and a non-existent currency code gets passed into the function), DML error logging
and attempting to improve performance by wrapping the PL/SQL function call into a scalar subquery
to benefit from the built-in scalar subquery caching feature (more...)
As Oracle ACE Director I got an extended trial license for Oracle's Cloud offerings, in particular the "Database as a Service" offering. As part of the (ongoing) evaluation I try to get an idea how consistent the performance of such an service is, which might be one of the concerns one might have when considering cloud offerings in general.
For my tests I've set up a 184.108.40.206 single instance database using "4 (more...)
I will be one of the speakers at the first SOUG performance training day, together with Christian Antognini, Franck Pachot and Clemens Bleile.
The event will take place in May this year in Switzerland at two different locations / days (one in German language, one in French, except mine, which will be in English).
My presentations will be:
- Analyzing and Troubleshooting Oracle Parallel Execution
- Advanced Oracle Troubleshooting
Hope to see you there!
I got invited by Luxoft Training
to deliver my one day seminar "Advanced Oracle Troubleshooting" in Moscow end of March.
More details about what I cover in this seminar can be found here
If you're interested, Luxoft Training has set up a page in Russian
where you can find more details - but note that the seminar language will be English.
This probably only is relevant for customers that run Oracle on big servers with lots of cores, like some of my clients that make use of the Exadata Xn-8 servers, like a X4-8 with 120 cores / 240 CPUs per node.
They recently came up with a re-write of a core application functionality. Part of this code did start the same code path for different data sets potentially several times concurrently ending up with many (more...)
It's probably not that well known that Oracle can actually rollback / re-start the execution of a DML statement should the cursor become invalidated. By rollback / re-start I mean that Oracle actually performs a statement level rollback (so any modification already performed by that statement until that point gets rolled back), performs another optimization phase
of the statement on re-start (due to the invalidation) and begins the execution of the statement from scratch. Note (more...)
As mentioned in the first
part of this instalment the different available distribution methods of the new parallel FILTER are selected automatically by the optimizer - in this last post of this series I want to focus on that optimizer behaviour.It looks like there are two new optimizer related parameters that control the behaviour of the new feature: "_px_filter_parallelized"
is the overall switch to enable/disable the new parallel filter capability - and (more...)
A new version 4.22 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version primarily addresses an issue with 12c - if the HIST mode got used to pull ASH information from AWR in 12c it turned out that Oracle forgot to add the new "DELTA_READ_MEM_BYTES" columns to DBA_HIST_ACTIVE_SESS_HISTORY - although it got officially added to V$ACTIVE_SESSION_HISTORY in 12c. So now I had to implement (more...)
Thanks to the many attendees that came to my presentation "Oracle 12c Parallel Execution New Features" at the DOAG conference 2015. You can download the presentation material here in Powerpoint
format, as well as check the Slideshare
Note that the Powerpoint format adds value in that sense that many of the slides come with additional explanations in the notes section.
If you are interested in more details I recommend visiting this post (more...)
Picking up from the first part
of this instalment I'll focus in this post on the available distribution methods for the new parallel FILTER subquery feature.In this post I won't go into the details how the optimizer selects the distribution method automatically - this will be covered in the last part.Here I merely describe the different available methods and how to control them using the new PQ_FILTER
hint, which is also mentioned in (more...)
A recent case at a client reminded me of something that isn't really new
but not so well known - Oracle by default performs evaluation at the latest possible point in the execution plan.So if you happen to have expressions in the projection of a simple SQL statement that runs parallel
it might be counter-intuitive that by default Oracle won't evaluate the projection in the Parallel Slaves but in the Query Coordinator
- even (more...)
12c introduces another interesting new Parallel Execution feature - the parallel
evaluation of FILTER subqueries
. In pre-12c FILTER subqueries always had to be evaluated in the Query Coordinator
. This had several consequences, in particular the data driving the FILTER subquery always had to flow through the Query Coordinator, and hence represented a forced serial execution part of a parallel execution plan. This limitation also meant that depending on the overall plan shape the parallel plan (more...)
When certain SQL features get used in pre-12c
versions that force non-parallel evaluation
, like using ROWNUM
or certain Analytic Functions
like LAG/LEAD, then - depending on the overall plan shape - Oracle can start to decompose
the parallel execution plan into several so called DFO trees
(If you want learn more about DFO trees and DFOs I recommend watching my online tutorial
on my Youtube channel).Now having multiple
DFO trees in a single (more...)
Continuing my series on new 12c Parallel Execution features: I've already mentioned the new PX SELECTOR
operator as part of the new Concurrent UNION ALL feature
where it plays a key role. However, in general starting from 12c this new operator usually
will get used when it comes to executing a serial part of the execution plan, like a full scan of an object not marked parallel, or an index based operation that can't be (more...)