Re: version_count

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Fri, 4 Apr 2008 18:57:52 -0700 (PDT)
Message-ID: <e1787df8-8b6a-4a19-b482-a6241f20b914@g1g2000pra.googlegroups.com>


The commonest reason for bind mismatch is the length variation of varchar data type.
Visit here.
http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/

Check following test case:
-- Create sample table
DROP TABLE t_bind_mismatch;

CREATE TABLE t_bind_mismatch(

	name1	VARCHAR2(100),
	name2	VARCHAR2(100),
	name3	VARCHAR2(100)

);
  • Flush Shared Pool ALTER SYSTEM FLUSH shared_pool;
  • Execute Sample Statements
  • Sample 1 VAR name1 VARCHAR2(10); VAR name2 VARCHAR2(10); VAR name3 VARCHAR2(10);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';

INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 2 VAR name1 VARCHAR2(50); VAR name2 VARCHAR2(10); VAR name3 VARCHAR2(10);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 3 VAR name1 VARCHAR2(50); VAR name2 VARCHAR2(50); VAR name3 VARCHAR2(10);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 4 VAR name1 VARCHAR2(50); VAR name2 VARCHAR2(50); VAR name3 VARCHAR2(50);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 5 VAR name1 VARCHAR2(200); VAR name2 VARCHAR2(50); VAR name3 VARCHAR2(50);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 6 VAR name1 VARCHAR2(200); VAR name2 VARCHAR2(200); VAR name3 VARCHAR2(50);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 7 VAR name1 VARCHAR2(200); VAR name2 VARCHAR2(200); VAR name3 VARCHAR2(200);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 8 VAR name1 VARCHAR2(2500); VAR name2 VARCHAR2(200); VAR name3 VARCHAR2(200);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';

INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 9 VAR name1 VARCHAR2(2500); VAR name2 VARCHAR2(2500); VAR name3 VARCHAR2(200);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Sample 10 VAR name1 VARCHAR2(2500); VAR name2 VARCHAR2(2500); VAR name3 VARCHAR2(2500);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';


INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);

  • Check V$SQLAREA and version count SELECT version_count, executions, sql_text FROM v$sqlarea WHERE sql_text like 'INSERT INTO t_bind_mismatch VALUES%';

On 4월5일, 오전4시13분, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> joel garry wrote:
>
> | A view exists to tell you why:
> |http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynvie...
>
> Is there a better explanation of what each column means? For example I
> see a lot with BIND_MISMATCH = 'Y'. The doc simply says "The bind
> metadata does not match the existing child cursor". What is the bind
> metadata? Is it trying to say that maybe different data types were used
> for the bind variables by different executions of the SQL?
>
> I also see a lot where all columns = 'N' which still leaves me wondering
> why the cursor wasn't shareable.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (GNU/Linux)
> Comment: Using GnuPG with PCLinuxOS -http://enigmail.mozdev.org
>
> iEYEARECAAYFAkf2ffYACgkQzIf+rZpn0oTUMACaAgB0AU0KLrIJRDK8GlRvQgZu
> A28AnjfZL7iQnmK0fn5HdbLBUL4REMZW
> =X9ir
> -----END PGP SIGNATURE-----
Received on Fri Apr 04 2008 - 20:57:52 CDT

Original text of this message