LIKE does not like bind variable

Today, I have learned this in a hard way. After the release, a query was time-out frequently. I have spent quite a lot of time to figure out what's going on. When checking the execution plan from the memory, there were always two plans, one good with nested loop join and one bad with hash join. Trying to add hint to force nested loop join, however, it did not work.

The problem is that the query has a predicate such as A.ORDER_ID LIKE 'ICN000104775%', but it is fired from application server and is using bind variable.

When checking why there were different versions of execution plan, I found out the following from the v$sql_shared_cursor in one occasion.

ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F L
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
000000054EF09208 0000000516791558 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0000000532A02020 0000000516791558 N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
00000005327D3808 0000000516791558 N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N

The forth field from the right is called USER_BIND_PEEK_MISMATCH

The following test demonstrated that LIKE operator is not getting along with bind variable:

1. In the case of literal, execution plan looks perfect

SQL> SELECT
2     a.ACCOUNT_ID
3       ,a.IO_ID
4       ,a.ORDER_ID
5       ,a.IS_MASTER
6       ,a.IS_PQ
7     FROM
8        TAB_A a,
9        TAB_B b
10     WHERE
11        a.ORDER_ID LIKE  'ISPT143730263%'
12     AND a.io_id = b.io_id
13     AND (a.IS_VALID='Y' OR (a.IS_VALID='N' AND b.ORDER_STATUS_ID=6));


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=45)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB_B' (Cost=3 Card=1 Bytes=10)
2    1     NESTED LOOPS (Cost=7 Card=1 Bytes=45)
3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TAB_A' (Cost=4 Card=1 Bytes=35)
4    3         INDEX (RANGE SCAN) OF 'TAB_A_IX5' (UNIQUE) (Cost=3 Card=1)
5    2       INDEX (RANGE SCAN) OF 'TAB_B_IX1' (NON-UNIQUE) (Cost=2 Card=1)

2. In the case of bind variable, totally wrong cardinality:

SQL>
SQL>
SQL> @s1
SQL> variable v varchar2(16);
SQL> exec :v :='ISPT143730263%';

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT
2     a.ACCOUNT_ID
3       ,a.IO_ID
4       ,a.ORDER_ID
5       ,a.IS_MASTER
6       ,a.IS_PQ
7     FROM
8        TAB_A a,
9        TAB_B b
10     WHERE
11        a.ORDER_ID LIKE  :v
12       AND a.io_id = b.io_id
13       AND (a.IS_VALID='Y' OR (a.IS_VALID='N' AND b.ORDER_STATUS_ID=6));


Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=227422 Card=833902 Bytes=37525590)
1    0   HASH JOIN (Cost=227422 Card=833902 Bytes=37525590)
2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_A' (Cost=45900 Card=1516186 Bytes=53066510)
3    2       INDEX (RANGE SCAN) OF 'TAB_A_IX5' (UNIQUE) (Cost=1064 Card=272913)
4    1     TABLE ACCESS (FULL) OF 'TAB_B' (Cost=170305 Card=30317333 Bytes=303173330)

We have a function index based on substr(order_id, 1, 13), so the fix is to use substr(order_id, 1,13)=:v as the predicate.

Ed. Sep 23, 2009 -

I come accross a paper "A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT " by Wolfgang Breitling . I have learned the filter factor (selectivity) of LIKE operator is as follows:

predicate               filter factor
-------------           -------------
c1 like  value          c1.density
c1 like :b1             0.05 OR c1.density

In the case of like with bind variable, the filter factor depends on the hidden parameter _LIKE_WITH_BIND_AS_EQUALITY. When it is false (the default), 0.05 is chosen, otherwise c1.density. This is why I saw unrealistic high cardinality in my case above.

Update: Feb 20, 2011

Today, caught by same issue again in the same database (only now we are in 10g not 9i) . Last night during code release, a query was changed:

--------------
before release
--------------

SELECT 
a.PSEFS_ID
, b.PSEFS_STATUS_ID
, TO_CHAR(b.PSEFS_CRFBTF_DATE, 'MM/DD/YYYY HH24:MI:SS') AS "PSEFS_CRFBTF_DATE"
, b.PRESALE_ACTIVITY_ID
, b.PSEFS_TYPE_ID
, b.PSEFS_SUB_TYPE_ID
, b.WORKWITH
, b.WORKWITH_GROUP_ID 
FROM PSEFS_MBSTFR a
   , PSEFS_DFTBJLS b 
WHERE substr(a.PSEFS_ID,1,13) = substr(:b,1,13) 
AND a.INT_SUPP_PSEFS_ID = 0 
AND a.INT_PSEFS_ID = b.INT_PSEFS_ID;

---------------
After release
--------------

SELECT 
  a.PSEFS_ID
, b.PSEFS_STATUS_ID
, TO_CHAR(b.PSEFS_CRFBTF_DATE, 'MM/DD/YYYY HH24:MI:SS') AS "PSEFS_CRFBTF_DATE"
, b.PRESALE_ACTIVITY_ID
, b.PSEFS_TYPE_ID
, b.PSEFS_SUB_TYPE_ID
, b.WORKWITH
, b.WORKWITH_GROUP_ID  
FROM PSEFS_MBSTFR a
   , PSEFS_DFTBJLS b  
WHERE a.PSEFS_ID like substr(:b,1,13)||'%' 
 AND a.INT_SUPP_PSEFS_ID = 0
 AND a.INT_PSEFS_ID = b.INT_PSEFS_ID
 ORDER BY a.INT_BASE_PSEFS_ID,a.IS_PQ;

So the predicate changed from : substr(a.PSEFS_ID,1,13) = substr(:b,1,13) to: a.PSEFS_ID like substr(:b,1,13)||'%'    Guess what? Now the new query uses hash join and time-out. The 0.05 filter factor makes CBO think from the predicate (a.PSEFS_ID like substr(:b,1,13)||'%' ) there will come out 580K rows, thus hash join is the best choice :-(. Fortunately, we are in 10g now, so I have the choice to use sql profile to freeze the good plan without the code change. But the predicate change in this case makes no sense, having asked the dev team to revert back in the next available maintainence window.