Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: MORE ABOUT: There is NOT value in using BOUND variables!!! ??? (WRONG -- this is ALOT of value)

Re: MORE ABOUT: There is NOT value in using BOUND variables!!! ??? (WRONG -- this is ALOT of value)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 13 Nov 1999 11:11:10 -0500
Message-ID: <wIktOMwRH06fPO2gruz2tRqKKNxu@4ax.com>


A copy of this was sent to Deja User <dejacom_at_my-deja.com> (if that email address didn't require changing) On Fri, 12 Nov 1999 19:05:37 GMT, you wrote:

>In article <EtsqOJOd3O=bBEnRTOoilbhnPD5l_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>[snip]
>> 3) you didn't take into account that select .... where field
>>(:1,:2,:3,....)
>> would generate a different plan then where field in
>> (1,2,3,4,5,6,0,0,0,0,0,0,0....). The optimizer sees the dup zeros in
>>the second
>> on and turns it into the more simple (1,2,3,4,5,6,0). The
>>differences in run
>> times were due to differences in optimizer plans. A little tuning
>>and the bind
>> variable query will run faster.
>[snip]
>
>Thanks to Thomas Kyte for, AS ALWAYS, an excellent and very informative
>reply. Following are a couple of questions that come to mind after
>reading his post.
>
>As indicated in the quoted portion above, he is saying that "...a
>little tuning and the bind variable query will run faster...". What
>sort of tuning are we talking about? Any help would be greatly
>appreciated.
>
>In our case, we have a large number of queries which are run by upto 40
>concurrent users. All of these queries are EXACTLY the same except for
>the criteria specified in the "IN" list. So if we were to move to the
>BOUND variables to take full advantage of the savings, we have to use a
>long "IN" list (e.g. :1, :2, :3, .... :255) and a major portion of
>these values could be all zeros. As Thomas' own results confirm that
>such a query with a lot of dummy zeros runs slower than the query
>without zeros (0.68 vs 11.90 secs). How can I solve this problem then?
>
>Regards,
>Mike.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

One method is to use a "where x in ( select * from plsql_function )" instead of a large in-list. large in-lists use concatenation of individual index reads. If we use "where x in (query)" it tends to use nested loops and goes faster.

Additionally -- we can vary the amount of data returned by the plsql function from call to call -- if we have 6 elements, we return 6. if we have 256, we return 256. We do not have the limits of X elements in the in list, it is purely a function of the number of elements we return from the function.

Here is an example of using this 'trick':

tkyte_at_8i> create or replace type myTableType as table of number;   2 /

Type created.

tkyte_at_8i>
tkyte_at_8i> create or replace function getMyTableType return myTableType   2 as
  3 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );   4 begin
  5 return l_x;
  6 end;
  7 /

Function created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> select a.column_value  val

  2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   3 /

       VAL


         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

I would write a small package that has functions to clear the table, add to the table and return the table. I would then select from this pkg.in_memory_table. Running the same tests I did before with 250 bind variables (6 = random, 245 = zero AND 250 = random) the results are pretty good:

using "where field in (select * from plsql function)" with 6 random and then 250 random values:

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

total     3001      1.05       1.06          0      12007       8000        1000
total    86001     15.21      14.71          0     487000       8000       84000

using "where field in ( :1, :2, ...., :255 )" with 6 random and then 250 random values:

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

total     3744      8.98       8.96          0     495499          0        1743
total    82730     26.46      26.01          0     653471          0       80729


The plsql function out-performs the in every case (and it is 100% bind variable friendly). Even when you account for the differences in the rowcounts -- random numbers make it impossible to get the exact same results). that is due to the differences in the query plans. where x in ( query ) does nested loops, where x in ( a,b,c,d) does index concat.

Here is the full tkprof showing the differences:


SELECT X,Y,Z
FROM
 ABC WHERE FIELD IN (SELECT A.COLUMN_VALUE VAL FROM THE(SELECT   CAST(T_PKG.GET_SPARSE_TABLE AS MYTABLETYPE) FROM DUAL ) A ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          7          0           0
Execute   1000      0.58       0.58          0       2000       8000           0
Fetch     2000      0.46       0.47          0      10000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3001      1.05       1.06          0      12007       8000        1000

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 20774 (TKYTE) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1000 NESTED LOOPS
   7000 VIEW
   7000     SORT (UNIQUE)
   6000      COLLECTION ITERATOR (PICKLER FETCH)
   3000       TABLE ACCESS (FULL) OF 'DUAL'
   1000    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   7000     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)

********************************************************************************

SELECT X,Y,Z
FROM
 ABC WHERE FIELD IN (SELECT A.COLUMN_VALUE VAL FROM THE(SELECT   CAST(T_PKG.GET_DENSE_TABLE AS MYTABLETYPE) FROM DUAL ) A ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute   1000      1.11       1.11          0       2000       8000           0
Fetch    85000     14.10      13.60          0     485000          0       84000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    86001     15.21      14.71          0     487000       8000       84000



Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
  84000 NESTED LOOPS
 246000 VIEW
 246000     SORT (UNIQUE)
 245000      COLLECTION ITERATOR (PICKLER FETCH)
   3000       TABLE ACCESS (FULL) OF 'DUAL'
  84000    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
 329000     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)

********************************************************************************

SELECT X,Y,Z
FROM

 ABC  WHERE FIELD IN ( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,
  :b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26,:b27,
  :b28,:b29,:b30,:b31,:b32,:b33,:b34,:b35,:b36,:b37,:b38,:b39,:b40,:b41,:b42,
  :b43,:b44,:b45,:b46,:b47,:b48,:b49,:b50,:b51,:b52,:b53,:b54,:b55,:b56,:b57,
  :b58,:b59,:b60,:b61,:b62,:b63,:b64,:b65,:b66,:b67,:b68,:b69,:b70,:b71,:b72,
  :b73,:b74,:b75,:b76,:b77,:b78,:b79,:b80,:b81,:b82,:b83,:b84,:b85,:b86,:b87,
  :b88,:b89,:b90,:b91,:b92,:b93,:b94,:b95,:b96,:b97,:b98,:b99,:b100,:b101,
  :b102,:b103,:b104,:b105,:b106,:b107,:b108,:b109,:b110,:b111,:b112,:b113,
  :b114,:b115,:b116,:b117,:b118,:b119,:b120,:b121,:b122,:b123,:b124,:b125,
  :b126,:b127,:b128,:b129,:b130,:b131,:b132,:b133,:b134,:b135,:b136,:b137,
  :b138,:b139,:b140,:b141,:b142,:b143,:b144,:b145,:b146,:b147,:b148,:b149,
  :b150,:b151,:b152,:b153,:b154,:b155,:b156,:b157,:b158,:b159,:b160,:b161,
  :b162,:b163,:b164,:b165,:b166,:b167,:b168,:b169,:b170,:b171,:b172,:b173,
  :b174,:b175,:b176,:b177,:b178,:b179,:b180,:b181,:b182,:b183,:b184,:b185,
  :b186,:b187,:b188,:b189,:b190,:b191,:b192,:b193,:b194,:b195,:b196,:b197,
  :b198,:b199,:b200,:b201,:b202,:b203,:b204,:b205,:b206,:b207,:b208,:b209,
  :b210,:b211,:b212,:b213,:b214,:b215,:b216,:b217,:b218,:b219,:b220,:b221,
  :b222,:b223,:b224,:b225,:b226,:b227,:b228,:b229,:b230,:b231,:b232,:b233,
  :b234,:b235,:b236,:b237,:b238,:b239,:b240,:b241,:b242,:b243,:b244,:b245,   :b246 )

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute   1000      1.38       1.37          0          0          0           0
Fetch     2743      7.60       7.59          0     495499          0        1743
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3744      8.98       8.96          0     495499          0        1743

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 20774 (TKYTE) (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
   1743  CONCATENATION 
      0   TABLE ACCESS BY INDEX ROWID ABC 
   1000    INDEX RANGE SCAN (object id 66988)
      0   TABLE ACCESS BY INDEX ROWID ABC 
... lots of lines like the above 2 snipped
   1000     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
      0    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1000     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
    278    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1278     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
    295    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1295     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
    289    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1289     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
    277    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1277     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
    305    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1305     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
    299    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1299     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)

********************************************************************************

SELECT Z,Y,X
FROM

 ABC  WHERE FIELD IN ( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,
  :b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26,:b27,
  :b28,:b29,:b30,:b31,:b32,:b33,:b34,:b35,:b36,:b37,:b38,:b39,:b40,:b41,:b42,
  :b43,:b44,:b45,:b46,:b47,:b48,:b49,:b50,:b51,:b52,:b53,:b54,:b55,:b56,:b57,
  :b58,:b59,:b60,:b61,:b62,:b63,:b64,:b65,:b66,:b67,:b68,:b69,:b70,:b71,:b72,
  :b73,:b74,:b75,:b76,:b77,:b78,:b79,:b80,:b81,:b82,:b83,:b84,:b85,:b86,:b87,
  :b88,:b89,:b90,:b91,:b92,:b93,:b94,:b95,:b96,:b97,:b98,:b99,:b100,:b101,
  :b102,:b103,:b104,:b105,:b106,:b107,:b108,:b109,:b110,:b111,:b112,:b113,
  :b114,:b115,:b116,:b117,:b118,:b119,:b120,:b121,:b122,:b123,:b124,:b125,
  :b126,:b127,:b128,:b129,:b130,:b131,:b132,:b133,:b134,:b135,:b136,:b137,
  :b138,:b139,:b140,:b141,:b142,:b143,:b144,:b145,:b146,:b147,:b148,:b149,
  :b150,:b151,:b152,:b153,:b154,:b155,:b156,:b157,:b158,:b159,:b160,:b161,
  :b162,:b163,:b164,:b165,:b166,:b167,:b168,:b169,:b170,:b171,:b172,:b173,
  :b174,:b175,:b176,:b177,:b178,:b179,:b180,:b181,:b182,:b183,:b184,:b185,
  :b186,:b187,:b188,:b189,:b190,:b191,:b192,:b193,:b194,:b195,:b196,:b197,
  :b198,:b199,:b200,:b201,:b202,:b203,:b204,:b205,:b206,:b207,:b208,:b209,
  :b210,:b211,:b212,:b213,:b214,:b215,:b216,:b217,:b218,:b219,:b220,:b221,
  :b222,:b223,:b224,:b225,:b226,:b227,:b228,:b229,:b230,:b231,:b232,:b233,
  :b234,:b235,:b236,:b237,:b238,:b239,:b240,:b241,:b242,:b243,:b244,:b245,   :b246 )

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.02       0.02          0          0          0           0
Execute   1000      2.09       2.09          0          0          0           0
Fetch    81729     24.35      23.90          0     653471          0       80729
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    82730     26.46      26.01          0     653471          0       80729

Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 20774 (TKYTE) (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
  80729 CONCATENATION
   1000 TABLE ACCESS BY INDEX ROWID ABC    2000 INDEX RANGE SCAN (object id 66988)

      0 TABLE ACCESS BY INDEX ROWID ABC    1000 INDEX RANGE SCAN (object id 66988)

      0 TABLE ACCESS BY INDEX ROWID ABC    1000 INDEX RANGE SCAN (object id 66988)

      0 TABLE ACCESS BY INDEX ROWID ABC ... lots of lines snipped...

   1305     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)
    298    TABLE ACCESS (BY INDEX ROWID) OF 'ABC'
   1298     INDEX (RANGE SCAN) OF 'ABC_FIELD_IDX' (NON-UNIQUE)




--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Nov 13 1999 - 10:11:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US