Vit Spinka technical blog

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...

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...

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...

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...

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...

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-y...

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 :-)

A short update - I am now an ACE Associate, spreading the good word on Oracle technology:-) Another update is the upcoming book: I am co-authoring book on the Multitenant option in Oracle 12cR2. The book is for pre-order on Amazon (unfortunately Amazon seems to have troubles with putting my name there, grrr...). The original planned date was sometime around Open World 2016, but apparently nobody knows where 12cR2 will actually come out (and due to NDA, the book can be published only after 12cR2 is released to public), so there is now no estimated date at all. We just have to wait. However, the book is really taking shape, all of the contents is written and we are now finishing reading the proofs, catching last typos and mistakes. So stay tuned for 13 chapters or 400 pages or so, focusing all on Multitenant - and there is a lot of things to cover!

When 12.1.0.1 came out, one of the gripes was that upon a CDB start, all the PDBs were in the mounted mode. The DBA had to open them manually, or use a database trigger to do that. 12.1.0.2 introduced SAVE STATE - according to the docs: For example, if a PDB is in open read/write mode before the CDB is restarted, then the PDB is in open read/write mode after the CDB is restarted; if a PDB is in mounted mode before the CDB is restarted, then the PDB is in mounted mode after the CDB is restarted. The trouble is that this is simply wrong, it does not work like this. Oracle has a table externalized as  DBA_PDB_SAVED_STATES and this stores the state . The table is updated only by the SAVE STATE command - and reflects the status when the SAVE STATE was issued, not when the database goes down. It simply stores the open  mode of the database and the CDB will open the database in this mode when the CDB opens. Lack of a row implies MOUNTED mode, i.e. the CDB won't do anything. ...

A friend of mine ( Deiby Gómez ) pointed me to an interesting article on MOS 1956103.1 - Warning: standby redo logs not configured for thread on db_unique_name/db_unique_name. It essentially describes the same issue I described in Don't trust the lying (Data Guard) broker  - the newly created SRLs are not assigned to a particular thread and the VALIDATE command does not like it, although the standby is perfectly happy and will grab the SRLs as necessary, as it always did before 12c. The Metalink note adds a solution - to assign the the SRLs to the threads manually during creation. The syntax is alter database add standby logfile thread 1 group 1 'file spec' size .... This thus disables the auto-assign to the thread that needs it, but that should not matter. We usually size all the threads uniformly and assign enough SRLs to all of them, in other words we expect even distribution of SRLs to threads. Thus doing it manually is not a bad thing.

Everybody knows that the limit for number of columns in an Oracle table is 1000. It is actually limit of all columns in the table, including internal ones, virtual, unused but not yet dropped and so on. But what is the limit for a query? Let's start with a simple table, called many_columns. It has 1000 columns, all NUMBERs, to make things easy. Columns are named COLUMN_0001 to COLUMN_1000. And I insert 1 row into the table: insert into many_columns(COLUMN_0001) values (1); commit; So what happens with an innocent query? select m.*, n.* from many_columns m, many_columns n; Well, nothing special - SQL*Plus is happy to return 2000 columns. Obviously, there must an upper limit, right? At the very maximum, OCI specifies value for column count as ub2, i.e. max 65535. However, SQL*Plus complains much sooner: the limit seems to be 8150. I added one more table - many_columns2 with just then columns. The first query to go over the limit, with 8151, fails with: select m01.*...

When doing various experiments with docker, I painfully realized that btrfs keeps a lot to be desired. Wonderful idea, terrible user experience. First of all, df lies, and you are supposed to run btrfs balance often. Maybe it's because of the way docker uses it - it creates a ton of large images. Eventually you touch all chunks and rebalance stops working completely. Now you desperately delete things, hoping to get chunk free and let rebalance get thins back to order. Or not - and you end up nuking the server and reinstalling. Or you perhaps end up crashing up the server and the btrfs won't mount anymore... So after going through 5 servers (OL7.1 in VBox), I moved onto docker-machine. Wonderful idea - and it does not use btrfs, yay! However, it also has it's bugs... and pretty ugly. First of all, the latest stable boot2docker 1.9 has a kernel bug that causes Java process to become zombies and docker container won't finish. See  https://github.com/docker/docker...

Inspired by Frits Hoogland's excellent article on Oracle running in Docker , I started building a lot of Oracle containers. It's nice to have multiple different Oracle versions available at your fingertips for research, product testing and so on. However, one thing annoys me with Docker: if you want any usable IPC, you need to use --ipc=host . This means that all the images share the same namespace and, furthermore, when a container exits it sometimes does not clean up the IPC entries. As you probably know the IPC is used by Oracle for SGA memory and semaphore sets. It identifies which belong to which instance, by combining SID and ORACLE_HOME. This in turn means that you cannot run two databases with the same SID and ORACLE_HOME at the same time... which is usually fine, but not so with Docker and --ipc=host . In this case we do want to run multiple containers built off the same image, or perhaps have multiple similar images with the same ORACLE_HOME, differing in minor...

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 AS...

One of the new 12c features is the "VALIDATE DATABASE" command. According to the documentation it should do many thorough checks and tell you if all is configured well and correctly. However, there is one catch - or to put it a little more bluntly - bug. Or two. You know that you need standby redo logs for SYNC (or the new FASTSYNC) transport mode. The validate command knows that, too. And you know that you should have one more standby redo log than online redo logs. The validate command seems to know this one as well. However, the checks appear to have one flaw: they test whether the threads (and let's talk here about a single-instance, so we have only thread #1) have enough standby redo logs (SRLs) assigned. But when you create an SRL with 'alter database add standby logfile', they are unassigned to any thread. In fact, you get 0 as thread#: select thread#, sequence# from V$STANDBY_LOG; THREAD# SEQUENCE# ------- --------- 0 0 0 ...

What a busy week! The UKOUG Tech15 conference made me busy for four days, postponing any other work and non work stuff. As usual, I met many people actually using our products - it's always a bit strange feeling and a strong confirmation seeing people trusting their data and apps to something a developer writes:-) And of course, seeing many old friends again was also very nice. Especially talking to Gluent guys (http://gluent.com) and seeing what they are up to was very interesting and promising - I hope they succeed in a big way and change the data landscape. And of course, the Twinkies...

If the new world of BigData, NoSQL and streaming has sparked your interest, you may have noticed one peculiarity - the lack of proper transactions in these contexts (or transactions at all!) Yes, durability is retained, but the other properties of ACID (Atomicity, Consistency, Isolation, Durability) leave a lot to be desired. One might think that in this new world perhaps applications are built in such a way that they no longer need it,  and in some cases this may be true. For example, if a tweet or an update to Facebook gets lost, then who cares, and we can simply continue on. But there is of course more important data that still requires transaction support and some NoSQL databases have limited support for this nowadays. However, this is still far from a full implementation, the likes of which everyone takes for granted in the Oracle database (e.g. you cannot modify just arbitrary rows in arbitrary tables in a single transaction). Of course, the huge benefit is that these datab...

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...