« New very professional blog from ex-Pythian DBA team leader. Worth reading!
Oracle RAC main components… (quick self memo) »

DBMS_XPLAN.DISPLAY_CURSOR() is only for queries…

It seems not so well know fact that the DBMS_XPLAN.DISPLAY_CURSOR() function won’t show bind variables for DML-type queries. It is documented that V$SQL_BIND_CAPTURE will capture bind variables only for SQL *queries* when bind variables are in HAVING or WHERE clauses (!). Demonstration:

SQL> drop table t;

Table dropped.

SQL> create table t (id number(10));

Table created.

SQL> insert into t values ('1');

1 row created.

SQL> commit;

Commit complete.

SQL> variable x number;
SQL> exec : x := 5;

PL/SQL procedure successfully completed.

SQL> select /* TESTMARK1 */ count(1) from t where id=:x;

  COUNT(1)
----------
         0

SQL>
SQL> select sql_id,sql_fulltext from v$sqlarea where sql_fulltext like 'select /* TESTMARK1%';

SQL_ID        SQL_FULLTEXT
------------- ------------------------------------------------------------
arjxpk7ghccxn select /* TESTMARK1 */ count(1) from t where id=:x

SQL> select * from table(dbms_xplan.display_cursor('&sql_id', 0, 'basic +PEEKED_BINDS'));
Enter value for sql_id: arjxpk7ghccxn
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', 0, 'basic +PEEKED_BINDS'))
new   1: select * from table(dbms_xplan.display_cursor('arjxpk7ghccxn', 0, 'basic +PEEKED_BINDS'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* TESTMARK1 */ count(1) from t where id=:x

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 5

19 rows selected.

SQL>
SQL>
SQL> select sql_id,sql_fulltext from v$sqlarea where sql_fulltext like 'insert /* TESTMARK2%';

SQL_ID        SQL_FULLTEXT
------------- ------------------------------------------------------------
13duk396qjv59 insert /* TESTMARK2 */ into t values (:z)

SQL> select * from table(dbms_xplan.display_cursor('&sql_id', 0, 'ADVANCED'));
Enter value for sql_id: 13duk396qjv59
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', 0, 'ADVANCED'))
new   1: select * from table(dbms_xplan.display_cursor('13duk396qjv59', 0, 'ADVANCED'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  13duk396qjv59, child number 0
-------------------------------------
insert /* TESTMARK2 */ into t values (:z)

-------------------------------------------------
| Id  | Operation                | Name | Cost  |
-------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |
|   1 |  LOAD TABLE CONVENTIONAL |      |       |
-------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - INS$1

Note
-----
   - cpu costing is off (consider enabling it)

21 rows selected.

SQL>
SQL> select NAME, DATATYPE_STRING, WAS_CAPTURED from v$sql_bind_capture where sql_id='13duk396qjv59';

NAME       DATATYPE_STRING                                              WAS
---------- ------------------------------------------------------------ ---
:Z         NUMBER                                                       NO

SQL>

This is consistent when using new DBMS_SQLTUNE_SQLTUNE.EXTRACT_BINDS() function.

SQL> variable x number;
SQL> exec : x := 9999999;

PL/SQL procedure successfully completed.

SQL> select * from dual where 1=:x;

no rows selected

SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql where sql_text like 'select * from dual where 1=:x';

BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 2, 'NUMBER', NULL, NULL, NULL, 22, '28-FEB-
09', 'NULL', ANYDATA()))

SQL>
SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql where sql_id='36kuvjkmjw13u';

BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET()

SQL>

Workaround is to setup 10046(level 12) tracing in desired session(s) for capturing binds also for DMLs.

This entry was posted on Monday, March 9th, 2009 at 7:56 pm.
And is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response.
Or trackback from your own site.

Leave a Reply