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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #687789 is a reply to message #687703] |
Fri, 02 June 2023 15:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Barbara Boehmer wrote on Sun, 14 May 2023 23:10Here 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
Goto Forum:
Current Time: Sat Aug 31 21:48:30 CDT 2024
|