Home » RDBMS Server » Performance Tuning » Bind by position (Oracle 19c, Windows)
Bind by position [message #687701] Sun, 14 May 2023 14:37 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi DBAs,
I've come across a couple of applications where I encountered what was until then unfamiliar to me:

Same bind name appears multiple times, bind to different values, in the same query.

As I've read a bit, and practiced some, I got to know that these applications are *Binding by position*

Nevertheless, I could never replicate it myself.

My testcase:
L>
SQL> var v1 number;
SQL>
SQL> exec :v1 := 1;

PL/SQL procedure successfully completed.

SQL> exec :v1 := 2;

PL/SQL procedure successfully completed.

SQL>
SQL> select :v1 + :v1 from dual;

   :V1+:V1
----------
         4

SQL>
This obviously didn't go as expected ( I expected to bind first time value of 1, then value of 2.. and get 3 total )



I tried another way, using substitute variables:

SQL>
SQL> select &v1 + &v1 from dual;
Enter value for v1: 1
Enter value for v1: 2
old   1: select &v1 + &v1 from dual
new   1: select 1 + 2 from dual

       1+2
----------
         3

SQL>

Well, this went a little better.

However, if I have a query with 35 bind variables, it could get a bit frustrating and prone to human error,
to manually bind 35 values.

Is there a way to automatically bind 35 values of a query, so I'll get the "bind by position" replicated realistically ?

Thanks
Andrey



Is there a sensible approach to this, in order to perform preparation of a query ( that has a known SQL_ID ) to re-execute while replicating the binding by position with the values from v$sql_Bind_CAPTURE ?
Re: Bind by position [message #687702 is a reply to message #687701] Sun, 14 May 2023 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The second way, using substitution variables, is NOT a bind method: SQL*Plus substitute the variables by their value BEFORE sending it to Oracle, so Oracle gets only constants (in the way you use this method) as you can see in the "new" section.

SQL*Plus does not know bind by position, only by name.

See this topic for some examples of using bind by position.

Re: Bind by position [message #687703 is a reply to message #687702] Sun, 14 May 2023 15:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is another interesting link that demonstrates how execute immediate binds by position and dbms_sql binds by name.

https://asktom.oracle.com/pls/apex/asktom.search?tag=dbms-sqlbind-variable
Re: Bind by position [message #687789 is a reply to message #687703] Fri, 02 June 2023 15:02 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Barbara Boehmer wrote on Sun, 14 May 2023 23:10
Here is another interesting link that demonstrates how execute immediate binds by position and dbms_sql binds by name.

https://asktom.oracle.com/pls/apex/asktom.search?tag=dbms-sqlbind-variable
Thanks!

I tried the example, and it seems to work, if I want to display some bind values:

SQL>
SQL> set serveroutput on
SQL> declare
  2       l_n1 number;
  3       l_n2 number;
  4       l_n3 number;
  5   begin
  6       execute immediate 'select :x, :x, :x from dual'
  7       into l_n1, l_n2, l_n3
  8       using 1, 2, 3
  9       ;
 10  --
 11       dbms_output.put_line( l_n1 || ', ' || l_n2 || ', ' || l_n3 );
 12   end;
 13   /
1, 2, 3

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
So far so good.

But what If I have a testcase with table TEST that I want to query from, and check its execution plan ?


I tried to use Tom Kyte's advice and do "If you want to bind by name, but have the ease of native dynamic sql for fetching, or to bind by position but use the procedural access of dbms_sql - you can use to_refcursor/to_cursor API calls in dbms_sql to flip/flop between the two approaches midway."

What I got was this:

SQL> VAR X1 REFCURSOR
SQL>
SQL>
SQL> set serveroutput on timing on
SQL> declare
  2       v1 varchar2(100) := 'somevalue200';
  3  --
  4   begin
  5   open :x1 for select count(*) from dual where 'somevalue200' = v1 ;
  6  --
  7   end;
  8   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> print :x1

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01
SQL>
SQL>
SQL>

Which is OK, if I want to bind by name, which I don't... How can I bind by position with this technique, to actually run an SQL with a positioned bind, so that I can also capture it's execution plan etc ??

Thanks
Andrey

Re: Bind by position [message #687790 is a reply to message #687789] Sat, 03 June 2023 03:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Perhaps something like the following:


bind by position:
SCOTT@orcl_12.1.0.2.0> var x1 refcursor
SCOTT@orcl_12.1.0.2.0> begin
  2    open :x1 for
  3    'select * from emp where deptno in (:x, :x, :x) order by deptno, empno'
  4    using 10, 20, 40;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> print :x1

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09 00:00:00       3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12 00:00:00       1100                    20
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

8 rows selected.

explain plan:
SCOTT@orcl_12.1.0.2.0> select t.*
  2  from   (select distinct s.sql_id, s.child_number
  3  	     from   v$sql s, v$sql_plan p
  4  	     where  s.sql_id = p.sql_id
  5  	     and    s.child_number = p.child_number
  6  	     and    UPPER (s.sql_text) like '%SELECT * FROM EMP WHERE DEPTNO IN%') v,
  7  	    table (dbms_xplan.display_cursor(v.sql_id, v.child_number)) t
  8  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5rn5ykq18p0t1, child number 0
-------------------------------------
select * from emp where deptno in (:x, :x, :x) order by deptno, empno

Plan hash value: 150391907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
|   1 |  SORT ORDER BY     |      |     9 |   342 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     9 |   342 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("DEPTNO"=:X OR "DEPTNO"=:X OR "DEPTNO"=:X))


19 rows selected.

values of bind variables by position:
SCOTT@orcl_12.1.0.2.0> column value_string format a30
SCOTT@orcl_12.1.0.2.0> select b.position, b.value_string
  2  from   (select distinct s.sql_id, s.child_number, s.hash_value, s.child_address
  3  	     from   v$sql s, v$sql_plan p
  4  	     where  s.sql_id = p.sql_id
  5  	     and    s.child_number = p.child_number
  6  	     and    UPPER (s.sql_text) like '%SELECT * FROM EMP%') v,
  7  	    v$sql_bind_capture b
  8  where  b.hash_value = v.hash_value
  9  and    b.child_address = v.child_address
 10  /

  POSITION VALUE_STRING
---------- ------------------------------
         1 10
         2 20
         3 40

3 rows selected.
Re: Bind by position [message #687940 is a reply to message #687701] Fri, 28 July 2023 06:14 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Thanks Barbara and Michel.

Very helpful, and much appreciated.

Regards,
Andrey
Previous Topic: Cannot force indexing on alert log query
Next Topic: How to reduce query Cost
Goto Forum:
  


Current Time: Fri Mar 29 03:01:27 CDT 2024