Re: How to tell if bind variables are being used?

Ryan Gaffuri wrote:

>Are there any scripts that you can use to determine whether
>applications are using bind variables?
>
>also are there any scripts to determining whether people are
>committing too frequently?
>
>

A quick look at v_$open_cursor will also give you the answer. Here's a quick demo from my class likely plagiarized from Tom Kyte or someone else but whose name I can't remember at this moment:

ALTER SESSION SET session_cached_cursors = 0;

CREATE OR REPLACE PROCEDURE use_literal IS

x dual.dummy%type := 'X';
y dual.dummy%type;

BEGIN
   SELECT dummy
   INTO y
   FROM dual LITERAL
   WHERE dummy = 'X';
END useliteral;
/

EXEC use_literal;

CREATE OR REPLACE PROCEDURE use_bindvar IS

x dual.dummy%TYPE := 'X';
y dual.dummy%TYPE;

BEGIN
   SELECT dummy
   INTO y
   FROM dual BIND_VAR
   WHERE dummy = x;
END use_bindvar;
/

EXEC use_bindvar

Use this SQL to view how Oracle stores the SQL

SELECT sql_text
FROM v$open_cursor
WHERE sid = (

   SELECT sid
   FROM v$mystat
   WHERE ROWNUM = 1); The difference between the two procs is self-evident.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)