4

In my PL/SQL have this big dynamic SQL using bind variables. I use the DBMS_SQL package to bind and execute the generated query.

Based on certain criteria, where-clauses are added to the dynamic SQL. When I just use an "in" or a "=" to match my bind variables all works fine and results are returned pretty fast.

Example:

(servedparty = :bv_ or servedpartyimsi = :bv_)

However, when I do the following:

(servedpartyimei like :bv_)

and provide a value like 12345679890% the query takes a very very very long time.

I've also tried somehting like this

(servedpartyimei like :bv_||'%')

and then provide the value without the '%' but it gives the same results

When I execute the query without bind variables but just put hardcoded values, result are also returned immediatly.

Am I doing something wrong here? Don't bind variables like the LIKE operator? Any ideas?

Thank you.

Tijs
  • 43
  • 1
  • 4
  • 1
    What data type is `servedpartyimei`? You've shown a search value that's a number (which makes sense for am IMEI, I think?) but you're treating it as a string. It might also be worth saying which version of Oracle you're using; and can you look at the execution plans for the fast and slow versions? – Alex Poole Nov 17 '16 at 17:31
  • The imei are stored as strings in the database. Database version is 11.2 – Tijs Nov 17 '16 at 17:38
  • 2
    You still need to look at the execution plans to see what it's doing. Maybe there are also filters on other (indexed) columns that it thinks will be more appropriate, or isn't using the index you expect for some other reason. We can't really guess what it's doing though, you need to find out. – Alex Poole Nov 17 '16 at 17:50
  • Examined the execution plans and they are similar. When I execute the same piece of PL/SQL on a different environment (the issue occurs on our DEV and now tried it on TEST) results are returned immediatly. So it will probably be an environment thing. Will check with the DBA's – Tijs Nov 18 '16 at 09:08

3 Answers3

5

I think you are suffering from Oracle bug 9197434 (BIND PEEKING NOT HAPPENING WHEN USING DBMS_SQL)

As far as I know, that has not been fixed.

Without the benefit of bind variable peeking, Oracle has no idea what value will be on the the right-hand side of your LIKE condition. It could just be '%', for instance. So, Oracle makes assumptions about how many rows will be LIKE a typical bind variable value. Those assumptions are pretty conservative and are likely forcing Oracle away from the fast plan you want (using an index, likely) to the slow plan you are getting (using a hash-join, likely).

I would recommend you use Native Dynamic SQL (i.e., EXECUTE IMMEDIATE), if possible, as that does not suffer from this bug. Otherwise, you may need to HINT your SQL.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • 1
    The 11.2 version of the same bug is bug# 13386678 (BIND PEEKING DOES NOT WORK IN DBMS_SQL). Oracle closed that bug as "not feasible to fix". So, you need a workaround. – Matthew McPeak Nov 17 '16 at 18:30
4

When you use LIKE with a bind variable, in some older versions Oracle has to make an assumption about how many rows will match. I can't remember what value it chooses (it may vary by version) it may be 5%, 10%, whatever. Now that may be way out of line with reality and lead to a poor plan.

There is an undocumented (and unsupported) optimizer parameter called _like_with_bind_as_equality that does what its name suggests - i.e. when set to true assumes the number of row returned by column like :bv is the same as would be returned by column = :bv. So if you use that you may get a faster plan. You can set it via alter session.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
1

I have faced the same problem, I tried the as below mentioned and seems working fine

V_QUERY := V_QUERY||' AND FIRST_NAME LIKE ''%''||:VAR2||''%''';

Thanks

Amol Shewale
  • 11
  • 1