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.
Summary
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 ;-)
Comments