Bind variables

[pranavgovind]

All,

My understanding is, if we use bind variables in PLSQL, it should imporve the performance. As per the below example, it is not. so what circumstances we can use bind variables?. Please explain.

There are two procedure, first one is non bind variable, another one is bind variable. But Non bind variable procedure runs faster then the bind variable procedure.

SQLPLUS> DECLARE
2 v_cust_hierarchy VARCHAR2(50);
3 v_hkunnr VARCHAR2(50);
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 V_CNT NUMBER := 0;
6 BEGIN
7
8
9 SELECT HKUNNR INTO
10 v_cust_hierarchy
11 FROM KNVH
12 WHERE KUNNR = '0003760918'
13 AND VKORG = 'B001'
14 AND SPART = '01'
15 AND DATAB < TO_DATE('06-JAN-06')
16 AND DATBI > TO_DATE('06-JAN-06')
17 AND HZUOR = '00';
18
19
20 FOR I IN 1.. 10000 LOOP
21 V_CNT := V_CNT +1;
22 --dbms_output.put_line('loop');
23 BEGIN
24 SELECT HKUNNR INTO v_hkunnr FROM KNVH WHERE KUNNR = v_cust_hierarchy;
25 IF v_hkunnr IS NULL then
26 --dbms_output.put_line('found the customer hierarchy...');
27 EXIT;
28 END IF;
29 v_cust_hierarchy := v_hkunnr;
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 --dbms_output.put_line('no data foiund in sal hierracy logic...');
33 v_cust_hierarchy := NULL;
34 EXIT;
35 END;
36 END LOOP;
37 --DBMS_OUTPUT.PUT_LINE(v_cust_hierarchy);
38 dbms_output.put_line(TO_CHAR(V_CNT)||'TOTAL RUN TIME '||(ROUND(ROUND((DBMS_UTILITY.GET_TIME -
l_start)/100, 2)/60,3)));
39 END;
40 /
2TOTAL RUN TIME .005

PL/SQL procedure successfully completed.

SQLPLUS>
SQLPLUS>
SQLPLUS> declare
2 v_cust_hierarchy VARCHAR2(50);
3 v_hkunnr VARCHAR2(50);
4 V_CNT NUMBER := 0;
5 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
6 BEGIN
7
8 SELECT HKUNNR INTO
9 v_cust_hierarchy
10 FROM KNVH
11 WHERE KUNNR = '0003760918'
12 AND VKORG = 'B001'
13 AND SPART = '01'
14 AND DATAB < TO_DATE('06-JAN-06')
15 AND DATBI > TO_DATE('06-JAN-06')
16 AND HZUOR = '00';
17
18
19 FOR I IN 1.. 1000 LOOP
20 V_CNT := V_CNT +1;
21 BEGIN
22 EXECUTE IMMEDIATE 'SELECT HKUNNR FROM KNVH WHERE KUNNR = :kunnr'
23 INTO v_hkunnr USING v_cust_hierarchy;
24 END;
25 END LOOP;
26
27 dbms_output.put_line('TOTAL RUN TIME '||(ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)
/60,3)));
28 end;
29 /
TOTAL RUN TIME 2.049

PL/SQL procedure successfully completed.

SQLPLUS>
SQLPLUS> DECLARE
2 v_cust_hierarchy VARCHAR2(50);
3 v_hkunnr VARCHAR2(50);
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 V_CNT NUMBER := 0;
6 BEGIN
7
8
9 SELECT HKUNNR INTO
10 v_cust_hierarchy
11 FROM KNVH
12 WHERE KUNNR = '0003760918'
13 AND VKORG = 'B001'
14 AND SPART = '01'
15 AND DATAB < TO_DATE('06-JAN-06')
16 AND DATBI > TO_DATE('06-JAN-06')
17 AND HZUOR = '00';
18
19
20 FOR I IN 1.. 1000 LOOP
21
22
23 SELECT HKUNNR INTO v_hkunnr FROM KNVH WHERE KUNNR = v_cust_hierarchy;
24
25 END LOOP;
26
27 dbms_output.put_line('TOTAL RUN TIME '||(ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)
/60,3)));
28 END;
29 /
TOTAL RUN TIME .678

PL/SQL procedure successfully completed.

SQLPLUS>

[davey23uk]

if the literals dont change then you dont need to bind

[pranavgovind]

Devay, Thanks. It makes sense.

[mike9]

From my point of view it only makes sense to use bind variables in fast and often used queries. I dont see any real advantae of using bind variable with long ruunning queries or queries which are only executed once a day. Specially when you are usgin partitioning and/or histogramms it may be interesting for the CBO to know the exacte value of the query paramteres.

[JMac]

As far as I can see BOTH are using Bind Variables! (But - yes - Davey is 100% correct. Bind Vars do not make things 'run faster' per se, only if the Bound variable is a value that changes every time the statement is run.)

[JMac]

Did a quick Google and found:

http://asktom.oracle.com/pls/ask/f?p...:7832114438832

By way of explanation.