Sometimes the most interesting findings start with a simple question. After publishing my recent post on Oracle 26ai JOIN TO ONE, Iudith Mentzel pointed out something in one of the query transformations that looked perfectly logical – but turned out not to describe what Oracle actually does.
A small test led me one level deeper, from DBMS_UTILITY.EXPAND_SQL_TEXT to the CBO trace, and revealed another interesting aspect of JOIN TO ONE: the way Oracle handles its implicit LEFT OUTER JOIN.
For me, building efficient data pipelines means thinking about performance from day one. And that means a conscious and thoughtful approach to optimizer statistics. It is not just this Autotask, which always runs in the database in the evenings or at weekends and is the responsibility of the DBAs. Every data engineer knows the data to be processed better, knows when and how it is loaded and processed. They should also be better able to recognize when and whether the statistics are critical at certain process steps and therefore when and how the statistics should be collected in the best possible way. That’s their job too! This is my point of view, which I would like to explain and emphasize with the posts in this series
Continue reading
As my electrical engineering lecturer at university used to joke, there are only two faults in electricity: 1) No contact where it is needed. 2) There is contact where it is not needed. You can often think the same way when looking for the causes of a poor execution plan: no stats were gathered when the optimizer needed them for a good execution plan or someone gathered stats when it was inappropriate.
Continue reading
In the previous post I showed how to get to the final query after SQL macro expansion using CBO trace. But there are still a few interesting things in the trace file that are worth looking at.
Continue reading