| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: MORE ABOUT: There is NOT value in using BOUND variables!!! ??? (WRONG -- this is ALOT of value)
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
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 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 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
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
 |
 |