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.

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

Post a Comment

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, 11.1.0.6, 11.1.0.7), 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 : /u01/flash_recovery_area/T1/archivelog/2009_11_27/o1_mf_1_208_%u_.arc 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...
Post a Comment
Read more

Multitenant and standby: recover from subsetting

In the previous post we learnt how to exclude a PDB (or a datafile) from the standby database recovery. Of course, that might not be the real end goal. We may just want to skip it for now, but have the standby continue to be up-to-date for every other PDB, and eventually include the new PDB as well. Again, standard Oracle pre-12c DBA knowledge is helpful here. These files are just missing datafiles and thus a backup can be used to restore them. The new 12c features add some quirks to this process, but the base is just sound backup and recovery. Backup So let's start with a proper backup: rman target=/ Recovery Manager: Release 12.1.0.2.0 - Production on Mon Nov 16 12:42:38 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. backup database; connected to target database: CDB2 (DBID=600824249) Starting backup at 16-NOV-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=193...
Post a Comment
Read more

Multitenant and standby: subsetting

In the previous post we looked at managing new PDBs added to a standby database, by copying the files to the DR server, as required. However, there is another possible approach, and that is to omit the PDB from the standby configuration altogether. There are two ways of achieving this: 1. Do it the old-school way. A long time before 12c arrived on the scene one could offline a datafile on the standby database to remove it. The same trick is used in TSPITR (tablespace point-in-time recovery), so that you don't need to restore and recover the entire database if you are only after some tablespaces. 2. 12.1.0.2 adds the option to automatically exclude the PDB from standby(s). And 12.2 adds the option to be more specific in case of multiple standbys. For the sake of curiosity I started by setting standby file management to manual again. What I found is that there was very little difference, and the steps to take are exactly the same - it’s just the error message that is slightly ...
Post a Comment
Read more