What is the impact/limitation of oracle select with large number of bind variables?

Question

We had our oracle server choking during processing a select statement with close to 3500(!!) bind variables.

This select is, obviously, built dynamically by code that we can't change. During the execution of this select the db server went to 100% cpu usage and our system almost halted.

We know how to reproduce this problem. So we can prevent this specific condition. But I am wondering if there is a way to protect the db ( by configuration) from this type of problems.

Update:

The select looks like that:

SELECT "FieldOfChar20"
FROM "TableOf111Krows"
WHERE (   "FieldOfChar20" BETWEEN :a0 AND :a1
    OR "FieldOfChar20" BETWEEN :a2 AND :a3
    OR "FieldOfChar20" BETWEEN :a4 AND :a5
    snip snip
    OR "FieldOfChar20" BETWEEN :a290 AND :a291
    OR "FieldOfChar20" BETWEEN :a292 AND :a293
   )
OR (   "FieldOfChar20" IN
          (:a294,
           :a295,
            snip snip
           :a1292,
           :a1293
          )
    OR "FieldOfChar20" IN
          (:a1294,
           :a1295,
           snip snip
           :a2292,
           :a2293
          )
    OR "FieldOfChar20" IN
          (:a2294,
           :a2295,
            snip snip
           :a3292,
           :a3293
          )
    OR "FieldOfChar20" IN
          (:a3294,
           :a3295,
           snip snip
           :a3476,
           :a3477
          )
   )

Oracle version is 10.2.0.2

Answer 1

In Oracle 8 and later, you can create usage profiles that control and limit the resources that any one session can consume. You create a profile and associate it to a user or role and the database will make sure that thing like logical/physical IO, CPU, and other limited resources are shared more equitably.

The interesting bits of a profile include:

[CPU_PER_SESSION           n|UNLIMITED|DEFAULT]     
[CPU_PER_CALL              n|UNLIMITED|DEFAULT]            
[CONNECT_TIME              n|UNLIMITED|DEFAULT]
[IDLE_TIME                 n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]  
[LOGICAL_READS_PER_CALL    n|UNLIMITED|DEFAULT]
[COMPOSITE_LIMIT           n|UNLIMITED|DEFAULT]
[PRIVATE_SGA               n [K|M]|UNLIMITED|DEFAULT]

As for bind variables, I'm not aware of any way to have the database or OCI client limit the use of these. In fact, bind variables are generally better for performance (and security) than embedded values in the SQL. Specifically, the reduce the number of hard parses that the database must perform when executing SQL that only varies in the values of parameters.

Answer 2

Since you have 3500 bind variables, it sounds like a massive query. I would assume that the complexity of the query (joins on tables / views, subqueries, etc...) would be much more of a troublemaker than having those bind variables. Indexes have to be examined for each join (if there are indexes). When the query is submitted for the first time the execution plan has to be built, and it sounds like Oracle is choking on that.

However, once the plan is built, it is stored in memory and doesn't need to be rebuilt (thanks to the bind variables). So having a large amount of bind variables may not be a bad thing.

Answer 3

What does the query look like?

I guess it is a "variable in list" query like where id in (:1,:2,:3,.....) query?

The developers of that app should have joined with a memory collection:

      select  /*+ cardinality(tab 10) */ *  
      from employees, table(:1) tab 
      where employees.id = tab.column_value";

More here: http://forums.oracle.com/forums/thread.jspa?messageID=3855830&#3855830

Is the number of binds always the same or is it always different? Sometimes 3500, sometimes 3499, sometimes 3520...?

The execution plan of the query will not be retrieved from the cache if the number of binds differs.