Re: version_count

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