Hard Parses when Using Bind Variables?

December 31, 2009

Assume that the following tables are created and then statistics are gathered:

CREATE TABLE T3 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE TABLE T4 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE INDEX IND_T4 ON T4(C1);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4',CASCADE=>TRUE)

If you then set up SQL*Plus with the following commands:

VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
SET AUTOTRACE TRACEONLY STATISTICS
SET ARRAYSIZE 1000

How many hard parses would you see for the session, and how many child cursors for the SQL statement will be in the library cache, if you do the following in SQL*Plus:

EXEC :N1 := 1
EXEC :N2 := 1
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;

Now, repeat the above 9,999 times in the same session, specifying random values for N1 and N2 such that:

• N1 >= 1
• N2 <= 10,000
• N1 <= N2

Does it matter if you test with Oracle 8.0.5 (assuming that you use the ANALYZE command rather than DBMS_STATS), 11.2.0.1, or something in between?