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 Go to next message
saipradyumn
Messages: 412
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
 
but few times my query contains the collection such as nested tables , varrays .How to capture the values for collections?
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 Go to previous messageGo to next message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

saipradyumn wrote on Tue, 19 October 2021 12:38
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
 
but few times my query contains the collection such as nested tables , varrays .How to capture the values for collections?
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 #685120 is a reply to message #685091] Wed, 27 October 2021 06:36 Go to previous messageGo to next message
saipradyumn
Messages: 412
Registered: October 2011
Location: Hyderabad
Senior Member
Hi

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 .


Re: Data Dictionary for Collection Bind variables [message #685121 is a reply to message #685120] Wed, 27 October 2021 07:10 Go to previous messageGo to next message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

saipradyumn wrote on Wed, 27 October 2021 14:36
Hi

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 Go to previous messageGo to next message
Andrey_R
Messages: 380
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 #685125 is a reply to message #685122] Wed, 27 October 2021 10:39 Go to previous messageGo to next message
saipradyumn
Messages: 412
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks for your quick reply .

When I am trying to implement this I am, getting following error :


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 27 21:03:40 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name: USER_NAME/PASSWORD@HOST_STING:1521/SERVICENAME
Last Successful login time: Wed Oct 27 2021 20:19:49 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> exec dbms_application_info.set_client_info ( 'MYSESSION5');

PL/SQL procedure successfully completed.

SQL>  select  banner  from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> EXEC dbms_monitor.session_trace_enable(binds=>true);
BEGIN dbms_monitor.session_trace_enable(binds=>true); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MONITOR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

How I can invoke these default packages .I am using both client and oracle server version 19

Thanks
SaiPradyumn
Re: Data Dictionary for Collection Bind variables [message #685126 is a reply to message #685125] Wed, 27 October 2021 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68052
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DBMS_MONITOR access is restricted, use DBMS_SESSION.SESSION_TRACE_ENABLE with same parameters instead.

Re: Data Dictionary for Collection Bind variables [message #685127 is a reply to message #685126] Wed, 27 October 2021 11:31 Go to previous messageGo to next message
saipradyumn
Messages: 412
Registered: October 2011
Location: Hyderabad
Senior Member


Thanks for continuous support. It went fine .
Re: Data Dictionary for Collection Bind variables [message #685154 is a reply to message #685127] Fri, 29 October 2021 09:19 Go to previous messageGo to next message
saipradyumn
Messages: 412
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; 
/
Session 2 :
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';

First two queries are working( V@SESSION, V$SQL) fine. Able to capture the data from my session .
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 Go to previous message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

saipradyumn wrote on Fri, 29 October 2021 17:19
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; 
/

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>
Previous Topic: Oracle SQL a=b and b=a
Next Topic: Parsing query
Goto Forum:
  


Current Time: Tue Dec 07 10:12:45 CST 2021