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

Advertisements

One thought on “Did You Know #22 – Reproduce SQL Issue

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s