Skip to main content

Posts

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
Post a Comment
Read more
Recent posts

Column projections

The lower half of the plan In the previous part, we've seen the data behind the execution plane table, i.e. all the operations, their cost, and the tree structure of the plan. Although interesting to investigate, it's all already available in the dbms_xplan output. Although possibly you can find cases when it does, perhaps with some more complex parallel queries, partitioning and so on. The more complex part is the "lower half" of the plan, i.e. the column projections, filters and access predicates. These can contain arbitrary operations/functions and it's also here where we can follow where a value comes from, starting from a single table, going through joins, aggregations, set functions and so on, up to the level where is it used (be it top-level column projection = in the select output, or as a filter or access predicate). In this blog post, let's look at the column projections. In other words, what columns are shown in the resulting select, as well a
Post a Comment
Read more

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
Post a Comment
Read more

But... we already have tools for explain plan

There are already ways to see execution plan This is very true. And for day to day use, they are more then sufficient. But sometimes the tools don't show the functions/conditions correctly. And some details are never present (e.g. in complex plans with multiple materialized subqueries, it's sometimes impossible to determine from which copy of a table/subquery a column comes). (And out of scope of this series, you may want to see bind values, or to know which line in the plan you are on = which (copy of) table is oracle just reading.) Examples in this blog are necessarily simple ones; and in such examples we see cases where the added value of such access to execution plan is not very high. But never forget that this can be extended to much more complex cases. Existing tools EXPLAIN PLAN EXPLAIN PLAN is long time with us. The main problem is that it shows plan for a new parse - and this can be different than what a session is actually executing, as it depends on b
Post a Comment
Read more

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 slow. direct SGA read: obviously reading SGA only; it's fast and easy to do read 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
Post a Comment
Read more

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 http://vitspinka.com/files/ReadingPlanFromSGA-OTWatOOW-2017.pdf . 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 p
Post a Comment
Read more

Oracle 12cR2 Multiteant book is out!

After all the waiting, the book is out! Available for example on  Amazon , both as Kindle ebook and as a paperback. You can see the actual table of contents in the Amazon preview; in short, it covers the whole range of multitenant related topics. Both multitenant-specific, as well as the old and proven features, that were updated to work with a multitenant database. With a bit of luck, you'll get the book sooner than me - my copy is still somewhere in the mail :-)
Post a Comment
Read more