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?
Leave a Reply