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’;

Capture

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’ ));

Capture

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’ ));

Capture

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

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’ ));

Capture

Yeeeeep Bind peeking kicked in this time : best join order TAB3->TAB1->TAB2.

As every new feature this can improve the performance of some query and may be have bad effect on some others.So watch out.

That’s it :D.

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