Home » SQL & PL/SQL » SQL & PL/SQL » Data Dictionary for Collection Bind variables (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
| Data Dictionary for Collection Bind variables [message #685087] |
Tue, 19 October 2021 04:38  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi All
In order to work on performance tuning part of one query , I need to get the bind variable values which are passed to the query at that time of execution.
I am making use the following views :
1:v$sql_bind_capture
2:dba_hist_sqlbind v
Is there any data dictionary which holds collection variable values ?
thanks
SaiPradyumn
|
|
|
|
| Re: Data Dictionary for Collection Bind variables [message #685091 is a reply to message #685087] |
Thu, 21 October 2021 14:34  |
Andrey_R
Messages: 419
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
saipradyumn wrote on Tue, 19 October 2021 12:38Hi All
In order to work on performance tuning part of one query , I need to get the bind variable values which are passed to the query at that time of execution.
I am making use the following views :
1:v$sql_bind_capture
2:dba_hist_sqlbind v
Is there any data dictionary which holds collection variable values ?
thanks
SaiPradyumn
I'm not sure what you mean. Can you post an example query in which you have collection data binded, that you want to be able to see values of ?
|
|
|
|
|
|
| Re: Data Dictionary for Collection Bind variables [message #685121 is a reply to message #685120] |
Wed, 27 October 2021 07:10 |
Andrey_R
Messages: 419
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
saipradyumn wrote on Wed, 27 October 2021 14:36Hi
Sorry for delay in response.
I have following sample query which contains the collection as bind variables
SELECT
type_key_value_pair(kv.key, nvl(SUM(c_points), 0))
FROM
t_user_ttransactions_gra gra, TABLE ( CAST ( :b1 AS t_nested_key_value_pair ) kv
WHERE
c_date (+) >= :b3
AND c_date (+) <= :b2 + 1 / 24
AND gra.session (+) = kv.c_key
GROUP BY
kv.c_key;
This kind of queries will be executed on production and captured in AWR reports with high buffers gets , CPU time , Elapsed time.
In order to investigate further on this we are getting the corresponding run time values for :b2, :b3 with the help of DBA_HIST_SQLBIND data dictionary views.
How ever nested tables values for :b1 are not captured in those dictionary views .
Is there any way to get those values as well to execute the single query individually with actual values like production .
Please post a replicatable SQL*Plus example , including the way you bind the variables, such as ":b1"
|
|
|
|
| Re: Data Dictionary for Collection Bind variables [message #685122 is a reply to message #685120] |
Wed, 27 October 2021 08:44 |
Andrey_R
Messages: 419
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi
With the following "disclaimer":
- The bind values will be captured only for retrieved rows, i.e "no rows selected" resulting query will *not* have any bind data captured
- The bind variable has to be used in the WHERE or HAVING clauses, otherwise will not be captured
Try this
Session 1:
C:\Users\myuser>sqlplus a/a
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Oct 27 16:37:00 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Oct 27 2021 16:35:07 +03:00
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL>
SQL> exec dbms_application_info.set_client_info ( 'MYSESSION5');
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC dbms_monitor.session_trace_enable(binds=>true);
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
SQL>
SQL> var V12 number;
SQL> exec :V12 := 12;
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT :V12 from dual where :V12=12;
:V12
----------
12
Session 2:
C:\Users\myuser>sqlplus a/a
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Oct 27 16:42:50 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Oct 27 2021 16:37:09 +03:00
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL>
SQL>
SQL> set lines 900 pages 20000
SQL> col client_info for a15
SQL> col bind_name for a15
SQL> col value_string for a15
SQL> col was_captured for a15
SQL> col last_captured for a15
SQL> col sql_id for a15
SQL> col sql_text for a50
SQL>
SQL>
SQL> select CLIENT_INFO,b.NAME as bind_name, b.value_string , dup_position , was_captured , last_captured,ss.sql_id,sq.SQL_TEXT
2 from gv$session ss, gv$sql sq, Gv$sql_Bind_Capture b
3 where b.INST_ID = ss.INST_ID
4 and b.SQL_ID = sq.SQL_ID
5 and b.CHILD_NUMBER = sq.CHILD_NUMBER
6 and exists (select 1
7 from gv$process p
8 where p.ADDR = ss.PADDR
9 and p.INST_ID = ss.INST_ID)
10 and sq.INST_ID = ss.INST_ID
11 and sq.SQL_ID = nvl(ss.SQL_ID, ss.PREV_SQL_ID)
12 and sq.CHILD_NUMBER = ss.SQL_CHILD_NUMBER
13 and ss.CLIENT_INFO = 'MYSESSION5';
CLIENT_INFO BIND_NAME VALUE_STRING DUP_POSITION WAS_CAPTURED LAST_CAPTURED SQL_ID SQL_TEXT
--------------- --------------- --------------- ------------ --------------- --------------- --------------- --------------------------------------------------
MYSESSION5 :V12 12 YES 27-OCT-21 8amdrautqax03 SELECT :V12 from dual where :V12=12
MYSESSION5 :V12 12 1 YES 27-OCT-21 8amdrautqax03 SELECT :V12 from dual where :V12=12
SQL>
|
|
|
|
|
|
|
|
|
|
| Re: Data Dictionary for Collection Bind variables [message #685154 is a reply to message #685127] |
Fri, 29 October 2021 09:19 |
|
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi Andrey_R, Michel,
The example was you had provide is working fine, but My object was not only the just bind variables but also wants to capture the collection nested tables data as well.
Here is example which I am trying :
Session 1 :
create or replace type TYPE_BAL_TYPE as object
( balance_type varchar2(50),
amount number
);
create or replace type TAB_BAL_TYPE as table of TYPE_BAL_TYPE;
CREATE OR REPLACE FUNCTION test_bind (
in_put_coll tab_balance_type
) RETURN TYPE_BAL_TYPE AS
l_result_coll wallet.TYPE_BAL_TYPE;
BEGIN
dbms_lock.sleep(10);
SELECT
TYPE_BAL_TYPE(balance_type, SUM(amount))
INTO l_result_coll
FROM
TABLE ( in_put_coll )
GROUP BY
balance_type;
RETURN l_result_coll;
END;
/
select sys_context('USERENV','SID') HH FROM DUAL; --2316
exec dbms_application_info.set_client_info ( 'BIND_CAPTURE_TEST');
EXEC dbms_session.session_trace_enable(binds=>true);
ALTER SESSION SET STATISTICS_LEVEL=ALL;
SELECT TEST_BIND ( TAB_BALANCE_TYPE( TYPE_BALANCE_TYPE('ABC', 100.45, 'INR') ,
TYPE_BALANCE_TYPE('ABC', 200.45, 'USD') ,
TYPE_BALANCE_TYPE('ABC', 300.45, 'EUR')
)
) RESULT_COLL FROM DUAL;
Session 3 :
1: select CLIENT_INFO,s.sql_id from v$session s where sid = 2316;
2 :select sql_fulltext from v$sql where sql_id in ('8cbt3yfucdanw');
3: select * from Gv$sql_Bind_Capture where sql_id in ('8cbt3yfucdanw');
4: select CLIENT_INFO,b.NAME as bind_name, b.value_string , dup_position , was_captured , last_captured,ss.sql_id,sq.SQL_TEXT
from gv$session ss, gv$sql sq, Gv$sql_Bind_Capture b
where b.INST_ID = ss.INST_ID
and b.SQL_ID = sq.SQL_ID
and b.CHILD_NUMBER = sq.CHILD_NUMBER
and exists (select 1
from gv$process p
where p.ADDR = ss.PADDR
and p.INST_ID = ss.INST_ID)
and sq.INST_ID = ss.INST_ID
and sq.SQL_ID = nvl(ss.SQL_ID, ss.PREV_SQL_ID)
and sq.CHILD_NUMBER = ss.SQL_CHILD_NUMBER
and ss.CLIENT_INFO = 'BIND_CAPTURE_TEST';
But gv$sql_Bind_Capture is not holding data.
Finally 3,4 queries not returning any data
Wants to get the Collections data with help of data dictionaries .
could you please help me on this
|
|
|
|
| Re: Data Dictionary for Collection Bind variables [message #685156 is a reply to message #685154] |
Fri, 29 October 2021 15:35 |
Andrey_R
Messages: 419
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
saipradyumn wrote on Fri, 29 October 2021 17:19Hi Andrey_R, Michel,
The example was you had provide is working fine, but My object was not only the just bind variables but also wants to capture the collection nested tables data as well.
Here is example which I am trying :
Session 1 :
create or replace type TYPE_BAL_TYPE as object
( balance_type varchar2(50),
amount number
);
create or replace type TAB_BAL_TYPE as table of TYPE_BAL_TYPE;
CREATE OR REPLACE FUNCTION test_bind (
in_put_coll tab_balance_type
) RETURN TYPE_BAL_TYPE AS
l_result_coll wallet.TYPE_BAL_TYPE;
BEGIN
dbms_lock.sleep(10);
SELECT
TYPE_BAL_TYPE(balance_type, SUM(amount))
INTO l_result_coll
FROM
TABLE ( in_put_coll )
GROUP BY
balance_type;
RETURN l_result_coll;
END;
/
It errors for me:
C:\Users\myuser>sqlplus a/a
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 29 23:32:58 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Fri Oct 29 2021 17:43:33 +03:00
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> create or replace type TYPE_BAL_TYPE as object
2 ( balance_type varchar2(50),
3 amount number
4 );
5 /
Type created.
SQL> reate or replace type TAB_BAL_TYPE as table of TYPE_BAL_TYPE;
SP2-0734: unknown command beginning "reate or r..." - rest of line ignored.
SQL>
SQL>
SQL> create or replace type TAB_BAL_TYPE as table of TYPE_BAL_TYPE;
2
3 /
Type created.
SQL> CREATE OR REPLACE FUNCTION test_bind (
2 in_put_coll tab_balance_type
3 ) RETURN TYPE_BAL_TYPE AS
4 l_result_coll wallet.TYPE_BAL_TYPE;
5 BEGIN
6
7 dbms_lock.sleep(10);
8 SELECT
9 TYPE_BAL_TYPE(balance_type, SUM(amount))
10 INTO l_result_coll
11 FROM
12 TABLE ( in_put_coll )
13 GROUP BY
14 balance_type;
15
16 RETURN l_result_coll;
17 END;
18 /
Warning: Function created with compilation errors.
SQL> show err
Errors for FUNCTION TEST_BIND:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/18 PLS-00201: identifier 'TAB_BALANCE_TYPE' must be declared
SQL>
|
|
|
|
Goto Forum:
Current Time: Thu Mar 30 10:32:15 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
