Following on from my previous post in regards to CDB$VIEW, I think I’ve hit a bug with it where it doesn’t return the right data… or any data.

I was in the process of converting some scripts to use the CDB_ views. I have a buffer cache usage script and I wanted to group it up by con_id, block size, etc, etc.

I got interested in what happens to the buffer cache when using multiple PDB’s when I noticed this in the alert log after shutting a PDB down.

So here’s my little test case I’ve sent to Oracle support. The SQL script is supposed to be a buffer cache usage script but I’ve modified it slight just to give me counts for easier troubleshooting.

If you look carefully the first query has this bit commented out while the second does not.

Further testing reveals if I put in any subquery i get the right data. I don’t need to call cdb$view as I first thought.

In my eyes it looks like we’ve hit a bug. I’ve reproduced this over 2 CDB’s. One a single instance and the other a 2 node RAC. Waiting on confirmation from Oracle.

Update 07/02/2014

As suggested by Ric Van Dyke from Hotsos a look at the execution stats might give us a bit more detail in relation to what is happening. You can do that via a gather_plan_statistics hint or setting your session level to statistics_level=all;
You can then see the cardinality and execution statistics with the following

or something like @xia sql_id child_number from the fantastic script library from Tanel Poder
Here’s the output for both queries. First one is the buggy sql.

And now the good one….

So as expected the explain plains are slightly different. Of note is the hash join in the good query vs the buffer sort and nested loops in the bad.
You can see the row source operation of 13 and 28 in the bad query returning no rows. The parallel query coordinator used on X$CDBVW$71c40226 returns 0 in bad and 99130. This is the cdb_objects view which we can tell from the

Maybe the PQ is causing some issues. How about we run a test with PQ turned off?

And now after reboot with no parallel query in single instance with 2 PDB’s….

In 2 node RAC with 4 PDB’s

Both return the right results. And just to make sure lets switch on parallel query and run a test again.

And now after reboot of single instance….

Looks like some sort of bug with the parallel query processing which happens within the CDB$VIEW function.

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.