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

From: Ryan <rgaffuri_at_cox.net>
Date: Sat, 20 Sep 2003 11:20:42 -0400
Message-ID: <7f_ab.222$0Z5.207@lakeread03>

is there anyway to tell whether the application layer has auto-commit set in the ODBC or JDBC without having access to the application layer? Any indicators in the data dictionary that commits are happening too frequently?   "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1064042161.370156_at_yasure...   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)

Received on Sat Sep 20 2003 - 10:20:42 CDT