ORACLE 12C : Distributed Queries and bind variable peeking

In ORACLE 12C a new bug fix was introduced :

12534597

12.1.0.0

Bind Peeking is disabled for remote queries

select * from V$SYSTEM_FIX_CONTROL where bugno = ‘12534597’;

So from now on bind variable peeking will kick in also for distributed query.

Let’s run some test :

TEST DATABASE : 12.1.0.2.0
TEST DATABASE : 11.2.0.3.9

This test script will create 3 tables with the same size and a loop back database link :

create database link db_loop connect to hatem identified by ******** using ‘***********’;

DROP TABLE tab1;
DROP TABLE tab2;
DROP TABLE tab3;

CREATE TABLE tab1
AS
       SELECT LEVEL AS a, 1 AS b
         FROM DUAL
   CONNECT BY LEVEL < 2000;

CREATE TABLE tab2
AS
       SELECT LEVEL AS a, 1 AS b
         FROM DUAL
   CONNECT BY LEVEL < 2000;

CREATE TABLE tab3
AS
       SELECT LEVEL AS a, 1 AS b
         FROM DUAL
   CONNECT BY LEVEL < 2000;

EXEC dbms_stats.gather_table_stats(null,’TAB1′);
EXEC dbms_stats.gather_table_stats(null,’TAB2′);
EXEC dbms_stats.gather_table_stats(null,’TAB3′);

TEST CASE 1 : Test local query: 11.2.0.3.9

SET LINESIZE 600 PAGESIZE 900;
VARIABLE a1 VARCHAR2;
VARIABLE a2 VARCHAR2;
VARIABLE a3 VARCHAR2;

EXEC :a1 :=1;
EXEC :a2 :=1;
EXEC :a3 :=3000;

SELECT t1.a
FROM tab1 t1, tab2 t2, tab3 t3
WHERE     t1.a > :a1
       AND t2.a > :a2
       AND t3.a > :a3
       AND t1.b = t2.b
       AND t1.b = t3.b
       AND t2.b = t3.b;


select PLAN_TABLE_OUTPUT from table( DBMS_XPLAN.DISPLAY_cursor (format => ‘ +PEEKED_BINDS’ ));

Bind peeking kicked in like expected : best join order TAB3->TAB1->TAB2

TEST CASE 2 : Test distributed query : 11.2.0.3.9

SELECT t1.a
FROM tab1 t1, tab2 t2, tab3@db_loop t3
WHERE     t1.a > :a1
       AND t2.a > :a2
       AND t3.a > :a3
       AND t1.b = t2.b
       AND t1.b = t3.b
       AND t2.b = t3.b;


select PLAN_TABLE_OUTPUT from table( DBMS_XPLAN.DISPLAY_cursor (format => ‘ +PEEKED_BINDS’ ));

Bind peeking have not kick in we can check the wrong cardinality estimate for both local and remote table (Used the default 5% selectivity) and that caused a bad join order to be chosen TAB1->TAB2->TAB3.

TEST CASE 2 : Test distributed query : 12.1.0.2