Bind variables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Bind variables

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Bind variables

    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>

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    if the literals dont change then you dont need to bind

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Devay, Thanks. It makes sense.

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    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.

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.)

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Did a quick Google and found:

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

    By way of explanation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width