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…

A few thoughts about OCM 12c upgrade

Yesterday I sat for the 12c OCM upgrade exam, which I mentioned in few blog posts before. The first step after checking your ID is of course signing the NDA, and thus you won't find much real information here.

This time I chose Utrecht as the place to take the exam. Not that I have any special preference, I took each of the exams in a different place so far. The only requirements were convenient time and location defined as 'somewhere in Europe'. But in the end, Utrecht turned out to be a good place. Oracle NL headquarters are easy accessible, it's a very new building, the lunch was good:-)
And the city is nice to see.

Regarding the exam, the usual important notes still hold true:

Arrive on time. It's a long day and you will have a lot of things to do.You will work hard the whole day. Get a good sleep before, be well rested.Review the exam topics well. Note that they may have change over time. There is for example an update as of January 1, 2016: Flex ASM was added…

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…