Did You Know #22 – Reproduce SQL Issue

During OOW17 a customer ran into a wrong result issue in 11.2.0.4, which is very bad. I diagnosed this and found out that it happens because of bloom filter, so we just disabled that and it was solved. But I still opened an SR so Oracle can find and fix this bug.

Wrong results bug are terrible, not only that you get the wrong results, you don’t even know that. There is no error or message, so you think that everything is fine, you rely on the query output, while it is completely wrong. In this case “select a.* from (…) a” returned 2 rows, but “select a.*,rownum from (…) a” returned no rows. I generated the execution plan and saw “JOIN FILTER CREATE” in the bad one, so I realized it’s probably bloom filter.
Anyway, in the SR, Oracle asked me to reproduce the problem and then provide the steps to reproduce it. It’s sometimes quite difficult to reproduce such things, but apparently, Oracle helps us here. The package DBMS_SQLDIAG includes a few procedures that help us to do that. You use EXPORT_SQL_TESTCASE to perform export of the tables and related information, and then IMPORT_SQL_TESTCASE to import everything to a different environment for the reproduction.
You can read more about how to perform the reproduction in MOS note 727863.1