Skip to main content

Execution plan rows

The plan

As mentioned in previous post, our example is based on the sample SH schema, with an added table FOOBAR (id number, key varchar2(30)):

SELECT prod_id, key FROM products CROSS JOIN foobar WHERE prod_id in (143,144,id) and id in (1,2,3);

In all reverse engineering, it's good to start with something simple and to know what we to look for. Thus we want to know what the execution plan should look like; and the more unique numbers/ids we can find, the better. It's much easier to look for a number like 0x12fa1893 than for 0x0 or 0x1.

The execution plan, obtained using:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL')); 
is
I have added the CPU cost from the full detail of the execution plan in  v$sql_plan / x$kqlfxpl.

Looking at the numbers, we also have rows (1 and 2 ... not very unique), bytes (34 / 30 / 8 - not bad) and what is not shown here, we also know object ids of the index and the table: 94765 and 92749 (nice).
We did not use any temp space and no partitions.

Tanel Põder pointed out long time ago that the plan operations consist of operations and options and where to find their numeric ids (x$xplton, x$xpltoo). In our case, we have:

Operations:
0x37 SELECT STATEMENT
0x02 NESTED LOOPS
0x26 TABLE ACCESS
0x17 INDEX
Options:
0x18 FULL
0x0b FULL SCAN

And why do we want to know? Again, we will be looking for these numbers. If we see all these numbers together, we can assume it has something to do with the execution plan and then go on and guess the structure of the plan and so on.

Where do we start

There is a big hurdle to overcome - where do we start? The SGA is huge.
Generally, we can go with:
  • The x$ table is filled by a helper function, and Google or My Oracle Support show that it's kqlfgx. Tracing this function can show us what data it is reading.
  • Brute force - we know where the SGA is, so we can look for some interesting numbers
  • We know that PGA knows about current cursor executed. And such cursor must have an execution plan associated. We also know that the plan is in SGA. This is the easiest and fastest way, so let's use it.

From PGA to the plan

We start with kxscio (see previous post) and we look at data stored there - especially any pointers going to the SGA (private process memory and SGA sit in a very different parts of memory, so one can see easily with own naked eye whether the address is in SGA or PGA/stack). And then we look at the data there and look for any of the "magic" numbers from the plan.

And indeed, it does not take long to find something at 0x2d0 (obviously the exact offset is version and platform dependent, this is 12.1 on Linux 64-bit):

To a trained eye, this looks like a gold mine, although I suppose you really do need a trained eye to appreciate it :-)
Let's try to guess what we are looking at. Please note that in real life, it takes quite some time to get this insight, and you want to run at least a dozen of different execution plans to get some kind of confidence:

For starters, we see plan operations and options: 26 18, 17 0b. There is no 37 - apparently the top operation is not part of the plan. There are 02s, but that's not unique, so wait with that.
Just next to the operations, we see 02 02, 02 03... which are depth and plan row. So there should be 01 01 for the first row, followed by 02... and there it is, near the start! This looks promising, so let's continue.

 Let's look at the CPU costs. These should be 0x997d, 0xbd1, 0x7dab (I just converted the decimal values from the plan). The values in the hex dump are kind of there: 99 7d, 9b d1, 7d ab. And we don't see any zeroes around them... how would Oracle store larger numbers, if there is no free space where to put them (like it would be if these were proper say 32 or 64-bit numbers?). And what about the extra 9 in the second cost?

Similar, looking at the object ids (only rows 2 and 3, as row 1 does not have any object in the plan), we find c1 72 2d, c1 6a 4d. Things now become clear - it's using some kind of variable-length encoding, similar to what UTF-8 uses.

We also see that the first byte is 8f, and it's there 3 times, always 3 bytes before depth + row id. And somewhere after the last row, there is 8e. So now we know where each row starts.
Now let's ponder for a bit: If we were run for example a partitioning query, we would see partition ids here. Or we would see temp space. But we don't see anything like that here... it would be a waste of space. So how do we/Oracle know/s what is stored here?
The answer is in the first two bytes of each row - it's a bitmap denoting what pieces of data are present.

Putting it all together, we can identify row start, the bitmap, depth, row id, operation, option, cost, CPU cost, I/O cost, cardinality, bytes. The bitmap, if we try very hard, also indicates how long each row is - but for visual check, just finding the next 8f is enough. (Note: the bitmap for the first row indicates some extra data present - and the 55 shows length of the data.)
There are some unidentified values - and that's just the life in reverse engineering. Maybe further investigation would identify them, maybe not. We probably don't need them for parsing the plan, so we just leave that as it is.


Comments

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

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

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