Re: Unshared cursors redux

From: goran bogdanovic <goran00_at_gmail.com>
Date: Tue, 27 Feb 2007 15:54:40 +0100
Message-ID: <6d0a3ba80702270654p6a3cd089g4e309b871650124a@mail.gmail.com>

problems with formatting...resending again...

well, I have a different experience - at least at 10.2.0.2/Linux...bindvariable length do count in v$sql_shared_cursor...

GB_at_xxxxxx > var b varchar2(100);
GB_at_xxxxxx > select /* goran2 */ count(9) from brisi where instanceid# = :b;

  COUNT(9)

---

         0

GB_at_xxxxxx > var b varchar2(200);

GB_at_xxxxxx > select /* goran2 */ count(9) from brisi where instanceid# = :b;

  COUNT(9)

---

         0

GB_at_xxxxxx > select sql_text, version_count, sql_id   2 from v$sqlarea
  3 where sql_text like '%goran2%'
  4 ;

SQL_TEXT                       VERSION_COUNT SQL_ID
------------------------------ ------------- -------------
select /* goran2 */ count(9) f             2 dv7phzzypq13t

rom brisi where instanceid# =
:b

select sql_text, version_count 1 ghqmf9yaw3ju6 , sql_id from v$sqlarea where
1=1 and sql_text like '%goran2
%'

GB_at_xxxxxx > select sa.sql_text, sc.bind_mismatch, sc.child_number   2 from v$sql_shared_cursor sc, v$sqlarea sa   3 where sc.sql_id = 'dv7phzzypq13t'
  4 and sc.sql_id = sa.sql_id
  5 ;

SQL_TEXT                       B CHILD_NUMBER
------------------------------ - ------------
select /* goran2 */ count(9) f N            0

rom brisi where instanceid# =
:b

select /* goran2 */ count(9) f Y 1 rom brisi where instanceid# =
:b

GB_at_xxxxxx > spool off

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 27 2007 - 08:54:40 CST