Skip to main content

Filter and access predicates

More than just column projections

When we look around for further pointers in the tree nodes, we find more pieces resembling the column projections we have seen so far. With some experimenting, we will find out that these are access predicates and filters.

First of all, the location of these pointers is not always the same, it seems that the value at 0x34 is some kind of flag, indicating whether filters and/or access predicates are present, and where. Or whether there is just one, or more of them.  It probably also indicates what other info is present, but I have no idea what info that would be or what each value means.

Resembling, but different

The data we see as predicates are not columns; after all, a predicate is a condition, not a single column. But the structure is similar to what we have seen with columns, and if we follow pointers further, we eventually build a tree, and some of the leaves are indeed just column projections.
After some contemplation, we realize it's all the same structure - just different flavors of it. The first byte indicates the type - 0xb was column projection, 0xc is a function, and we could see others (the most common is 0xe "new column" - what you see as STRDEF or ITEM_1 in subqueries, unions etc.).

Decoding one of the filter predicates, we for example see
We see type of 0xc (function), we see function if 0x206 - which is  easy to look up in v$sqlfn_metadata (although there is quite a lot of functions that are far from obvious). And that it has 02 arguments, with pointers again pointing to further tree nodes.
When we draw the one of the trees in the entirety, we see:
(OPTTINLO is IN-list operator, it's representation is kind of strange - first argument is left side, second argument is STRDEF of ORs of AND conditions for each possible value, i.e. col1 in (1,2) looks similar to "col1 in (strdef(i=1 or i=2))".

Forest of trees

We have a tree of execution plan rows; and we have trees for predicates, for filters, and single-leaf trees for each column. How do they relate?
The answer is - a lot. If we start at a leaf node of an execution plan, we might see it just pointing to some column projections, for example. Its parent will also have column projections - and in case they are the same columns, they are the same data blocks. If it has filters, it references the same column data.
What this means is that looking at the predicate tree, we can identify where (from which plan node) each of the column/block originates from - and thus even if the predicate is complex and uses columns from multiple subqueries, unions, etc., we can always drill down and see what is the original source of the data, we can see through all the STRDEFs, ITEM_1s, non-unique names and so on. This is something that the usual plan tools don't show, as they show the names just as plain text.


What we have seen is just a very basic primer into the execution plan representation. Doing it properly, reliably, supporting real-world use cases, that all means a lot of work. And frankly, this is usually way too complicated for the mere mortal. But nevertheless, it must be a lot of fun ;-)

I started with complains about missing functions in dbms_xplan or even explain plan (by the way, that was TO_TIMESTAMP, parsed into OPTDAT2TS1 "Srray Date=>Array Timestamp" in the tree). But the real benefit is the ability to drill down and trace each piece of the syntax tree, find out what table it comes from, through all aggregation, subqueries, set operations and functions. Unfortunately the real use cases feature very complex queries - something way out of scope for a humble blog like this, and also pretty hard to parse (and understand) due to sheer number of tree nodes (with all the different node types, functions, data types). We did not touch on constants, PL/SQL, sequences, ROWIDs, partition keys...

Anyway, let me know if you enjoyed this series and how far you got! Or if it got you interested so much that you want to go deeper and actually implement it in some form or other - it's always nice to see people do crazy deep-level technical stuff ;-)


Popular posts from this blog

ORA-27048: skgfifi: file header information is invalid

I was asked to analyze a situation, when an attempt to recover a 11g (standby) database resulted in bunch of "ORA-27048: skgfifi: file header information is invalid" errors.

I tried to reproduce the error on my test system, using different versions (EE, SE,,, but to no avail. Fortunately, I finally got to the failing system:

SQL> recover standby database;
ORA-00279: change 9614132 generated at 11/27/2009 17:59:06 needed for thread 1
ORA-00289: suggestion :
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-00280: change 9614132 for thread 1 is in sequence #208

Interestingly, nothing interesting is written to alert.log n…

Reading data from PGA and SGA

Overview For our investigation of execution plan as it is stored in memory, we need in the first place to be able to read the memory.

We have the options of
x$ksmmem, reading SGA using SQL. Personally I don't like it, it's cumbersome and SGA read: obviously reading SGA only; it's fast and easy to doread process memory: can read PGA, process stack - and since the processes do map the SGA, too, you can read it as well. Unfortunately ptrace sends signals to the processes and the process is paused when reading it, but so far all my reads were short and fast and the processes did not notice. Some OS configurations can prevent you from using ptrace (e.g. docker by default), google for CAP_SYS_PTRACE.gdb: using your favorite debugger, you can read memory as well. Useful when investigating. Direct SGA read I always considered direct SGA read of some dark magic, but the fundamentals are actually very easy. It still looks like sorcery when actually reading the Oracle in…

Reading execution plan from SGA and PGA - teaser

Some of you have seen my presentation about hidden parts of Oracle execution plans and how to access the plan in the memory directly and parse it. I presented it at OakTable World 2017  and it will be also presented in Wellington and Acukland this November.  You can download the presentation at

I realize that many people did not have the chance to attend... and that the slides need quite a lot of explaining, it's hard to understand this internals without more explanation.

Thus you can look forward to seeing a handful of blog posts, inspired by this presentation, and explain some of the aspects of this whole topic.

We will start with some basic tools; accessing SGA and PGA, which you may find useful for many other tasks, too. Then we will look at some details of the execution plan. This is not - and neither the presentation is - an exhaustive guide to the execution plan internals. It would be a multi-year project to t…