It is common to use Bind variables in our programs, The biggest advantage is Bind
variables allow sharing of cursors in the Library cache and hence avoids Hard
parsing of queries.
When we have performance issues with any query, we will be somehow
able retrieve the SQL Text but unfortunately we won’t get the actual values
used during the execution of query.
In Oracle 11G and above, this is possible using DBMS_XPLAN.DISPLAY_CURSOR(sql_id
VARCHAR2,cursor_child_no NUMBER,format VARCHAR2)
We need to pass sql_id of the query to get the actual value
of Bind variable.
Below is a Demo:
SELECT * FROM emp where ename = :ragh ;
From the Explain plan it’s not possible to determine the value
passed at runtime.
Below is output of explain plan –
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 11 | 2
(0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1
| 11 | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("ENAME"=:RAGH) à
Actual value is not displayed.
----------------
From the v$session views, I will get the sql_Id for this
query and then
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f0g0xnq3ynd4u',
null, 'ADVANCED'));
SQL_ID f0g0xnq3ynd4u,
child number 0
-------------------------------------
SELECT * FROM emp where ename = :ragh
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 2 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1
| 11 | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :RAGH (VARCHAR2(30),
CSID=31): 'Raghu'
From this we can get the actual literal values of the
variables used at runtime.
