| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: The bind variables in a query
I want to point out that V$SQL_BIND_CAPTURE in 10G does not show the
bind values used by all the sessions.
It would only show one set of bind values for each child cursor.
And one child cursor can be used by many sessions (when there is no
distinction in the factors specified in v$sql_shared_cursor).
So if the same SQL is issued by 2 sessions and all factors specified in
v$sql_shared_cursor are satisfied then the 2 sessions end up using the
same child
cursor.
This means that even if these 2 sessions use different bind values,
only one set (the first one) would appear in V$SQL_BIND_CAPTURE.
Following is an illustration.
Execute these 2 pl/sql program units from 2 different sessions
declare
vx varchar2(10) := '123';
vy varchar2(10) := '123';
begin
select 1 into vy from dual where vx=vy;
end;
/
declare
vx varchar2(10) := '789';
vy varchar2(10) := '789';
begin
select 1 into vy from dual where vx=vy;
end;
/
Monitoring from another session i observe that there is only one child cursor for this SQL.
Chi Par Loa
ld First sing Reason For ded Open Kept Users Users
Num Load User Cursor Not Vers Vers Vers Ope Execu ADDRESS CHILD_AD Hash Value ADDRESS CHILD_AD SUBSTR(SQL_TEXT,1,25)
ber Time id Being Shared ions ions ions ning ting
-------- -------- ------------ -------- --------
------------------------- ---- ---------- ----- --------------- ----
---- ---- ----- -----
69226EF8 6964DBE4 1539601012 69226EF8 6964DBE4 SELECT 1 FROM DUAL
WHERE 0 2005-11-14 0 0 1 0 2 0
/22:51:10
This is the data from V$SQL_BIND_CAPTURE
col value_string form a110
col name form a5
col value_string form a20
select
HASH_VALUE,CHILD_ADDRESS,CHILD_NUMBER,NAME,POSITION,DATATYPE_STRING, VALUE_STRING,MAX_LENGTH, LAST_CAPTURED,WAS_CAPTURED from V$SQL_BIND_CAPTURE where HASH_VALUE=1539601012 order by child_number,position
Chi
ld
Num
Hash Value CHILD_AD ber NAME POSITION DATATYPE_STRING
VALUE_STRING MAX_LENGTH LAST_CAPT WAS
------------ -------- ---- ----- ---------- ---------------
-------------------- ---------- --------- ---
1539601012 698611D4 0 :B2 1 VARCHAR2(32) 789
32 14-NOV-05 YES
1539601012 698611D4 0 :B1 2 VARCHAR2(32) 789
32 14-NOV-05 YES
So you see only one set of values. If you had executed y1.sql first you would have seen 789 instead of 123.
I can further illustrate the above example by using different bind lengths to force different child cursors.
Execute these 4 pl/sql program units from 4 different sessions
declare
vx varchar2(10) := '123';
vy varchar2(10) := '123';
begin
select 1 into vy from dual where vx=vy;
end;
/
declare
vx varchar2(50) := '789111111111';
vy varchar2(50) := '789111111111';
begin
select 1 into vy from dual where vx=vy;
end;
/
declare
vx varchar2(129) := '78911111111111';
vy varchar2(129) := '78911111111111';
begin
select 1 into vy from dual where vx=vy;
end;
/
declare
vx varchar2(200) :=
'789111111111111111111111111111111111111111111111111789111111111111111111111111111111111111111111111111';vy varchar2(200) :=
'789111111111111111111111111111111111111111111111111789111111111111111111111111111111111111111111111111';begin
Now v$sql_shared_cursor shows 3 child cursors (And the reason for having the seperate child cursors is because of the bind metadata mismatch)
Chi Par Loa
ld First sing Reason For ded Open Kept Users Users
Num Load User Cursor Not Vers Vers Vers Ope Execu ADDRESS CHILD_AD Hash Value ADDRESS CHILD_AD SUBSTR(SQL_TEXT,1,25)
ber Time id Being Shared ions ions ions ning ting
-------- -------- ------------ -------- --------
------------------------- ---- ---------- ----- --------------- ----
---- ---- ----- -----
6915868C 69B14E90 1539601012 6915868C 69B16C70 SELECT 1 FROM DUAL
WHERE 0 2005-11-14 0 1 1 0 1 0
/23:51:36
6915868C 69B14E90 1539601012 6915868C 69B14E90 SELECT 1 FROM DUAL
WHERE 1 2005-11-14 0 -Bind Metadata 1 1 0 1 0
/23:51:36 Mismatch-
6915868C 69B14E90 1539601012 6915868C 69B16910 SELECT 1 FROM DUAL
WHERE 2 2005-11-14 0 -Bind Metadata 1 1 0 1 0
/23:51:36 Mismatch-
The reason there are only 3 child cursors is because 3 & 4 are sharing the same child cursor 3 - because the bind metadata is the same for varchar2(129) & varchar2(200)
This is the data from V$SQL_BIND_CAPTURE
col value_string form a110
col name form a5
col value_string form a20
select
HASH_VALUE,CHILD_ADDRESS,CHILD_NUMBER,NAME,POSITION,DATATYPE_STRING, VALUE_STRING,MAX_LENGTH, LAST_CAPTURED,WAS_CAPTURED from V$SQL_BIND_CAPTURE where HASH_VALUE=1539601012 order by child_number,position
Chi
ld
Num
Hash Value CHILD_AD ber NAME POSITION DATATYPE_STRING
VALUE_STRING MAX_LENGTH LAST_CAPT WAS
------------ -------- ---- ----- ---------- ---------------
-------------------- ---------- --------- ---
1539601012 69B16C70 0 :B2 1 VARCHAR2(32) 123
32 14-NOV-05 YES
1539601012 69B16C70 0 :B1 2 VARCHAR2(32) 123
32 14-NOV-05 YES
1539601012 69B14E90 1 :B2 1 VARCHAR2(128)
789111111111 128 14-NOV-05 YES
1539601012 69B14E90 1 :B1 2 VARCHAR2(128)
789111111111 128 14-NOV-05 YES
1539601012 69B16910 2 :B2 1 VARCHAR2(2000)
78911111111111 2000 14-NOV-05 YES
1539601012 69B16910 2 :B1 2 VARCHAR2(2000)
78911111111111 2000 14-NOV-05 YES
6 rows selected.
Since 3 & 4 share the same child cursor, you would only see one set of bind values for that child cursor.
So even in 10G you would not be able to see the bind values used by one
session from a different session.
The reason is that the bind values used by each session are stored in
its UGA/PGA. (for Shared server in SGA)
So views like V$SQL_BIND_DATA & V$SQL_BIND_METADATA which get data from
this part of memory are limited to a specific session.
That is you cannot see one session's bind values (which are stored in a
different PGA) from another session.
So if you query V$SQL_BIND_DATA or V$SQL_BIND_METADATA you get that
specific sessions bind values.
>From 10g Oracle is also storing the bind values along with each child
cursor.
Only one set of bind values are stored (usually the first set of
values)
This is what you see in V$SQL_BIND_CAPTURE. And this doesnt show each
sessions bind values
regards
srivenu
Received on Mon Nov 14 2005 - 23:01:41 CST
 |
 |