Gvbindcap (gvbindcap.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script displays captured bind variable values from the shared pool, essential for troubleshooting SQL performance issues and understanding actual parameter values used in queries.

The Script

rem  gvbindcap.sql
rem
ttitle 'Bind Parameter Report'
rem
set linesize 160
rem
col inst_id format 99 heading 'RAC|INST'
col sql_id FORMAT   a15 HEADING  'SQL ID'
col hash_value FORMAT   99999999999 HEADING  'SQL|Hash Value'
col datatype_string  FORMAT   a14 HEADING  'Bind|Variable|Format'
col name  format a20 Heading 'Bind|Variable|Name'
col value_string format a50 heading 'Bind|Variable|Value'
col last_captured format a14 heading 'Last|Caputured'
rem
select * from
(
SELECT INST_ID
     , to_char(LAST_CAPTURED,'MM/DD HH24.MI.SS') last_captured
     , sql_id
     , hash_value
     , name
     , value_string
     , DATATYPE_STRING
  FROM gv$sql_bind_capture
 WHERE
  sql_id like nvl('&sql_id','%')
--  inst_id like nvl('&inst_id','%')
--   and hash_value like nvl('&hash_value','%')
 order by last_captured desc
)
where rownum < nvl('&rownum','81') ;

--   and sql_id like nvl('&sql_id','%')

Parameters

The script prompts for:

• &SQL - ID to filter (use % for all)
• &Maximum - number of rows to display (optional, defaults to 80)

Usage

-- Basic usage
@gvbindcap.sql

-- When prompted, enter:
-- sql_id: SQL ID to filter (use % for all)
-- rownum: Maximum number of rows to display (optional, defaults to 80)

Sample Output

Bind Parameter Report

RAC              Last                                   SQL                    Bind                 Bind                                              Bind
INST         Caputured       SQL ID          Hash Value Variable             Variable                                              Variable
---- -------------- --------------- ----------- -------------------- -------------------------------------------------- --------------
   1 01/05 14.35.22 4yu2d9fkxqp6g     123456789 :1                   100                                                NUMBER
   1 01/05 14.35.22 4yu2d9fkxqp6g     123456789 :2                   ACTIVE                                             VARCHAR2(32)
   1 01/05 14.35.22 4yu2d9fkxqp6g     123456789 :3                   2025-01-05                                         DATE
   2 01/05 14.32.15 8abc3def5hij2     987654321 :B1                  SMITH                                              VARCHAR2(128)
   2 01/05 14.32.15 8abc3def5hij2     987654321 :B2                  50000                                              NUMBER
   2 01/05 14.32.15 8abc3def5hij2     987654321 :B3                  10                                                 NUMBER
   1 01/05 14.30.45 2xyz9mno7pqr3     456789123 :DEPT_ID             20                                                 NUMBER
   1 01/05 14.30.45 2xyz9mno7pqr3     456789123 :START_DATE          2024-01-01                                         DATE
   1 01/05 14.30.45 2xyz9mno7pqr3     456789123 :END_DATE            2024-12-31                                         DATE