db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r538441 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/tests/lang/StalePlansTest.java functionTests/tests/lang/_Suite.java junit/RuntimeStatisticsParser.java
Date Wed, 16 May 2007 05:50:14 GMT
Author: kahatlen
Date: Tue May 15 22:50:12 2007
New Revision: 538441

URL: http://svn.apache.org/viewvc?view=rev&rev=538441
Log:
DERBY-2641: Convert lang/staleplans.sql to JUnit

Added StalePlansTest and enabled it as part of the lang suite.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StalePlansTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StalePlansTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StalePlansTest.java?view=auto&rev=538441
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StalePlansTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StalePlansTest.java
Tue May 15 22:50:12 2007
@@ -0,0 +1,306 @@
+/*
+ * Class org.apache.derbyTesting.functionTests.tests.lang.StalePlansTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Properties;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.functionTests.util.Formatters;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SQLUtilities;
+
+/**
+ * This is the test for stale plan invalidation. The system determines at
+ * execution whether the tables used by a DML statement have grown or shrunk
+ * significantly, and if so, causes the statement to be recompiled at the next
+ * execution.
+ */
+public class StalePlansTest extends BaseJDBCTestCase {
+    public StalePlansTest(String name) {
+        super(name);
+    }
+
+    /**
+     * Create the test suite. This test is not run in client/server mode since
+     * it only tests the query plans generated by the embedded driver.
+     */
+    public static Test suite() {
+        Properties props = new Properties();
+        props.setProperty("derby.language.stalePlanCheckInterval", "10");
+        props.setProperty("derby.storage.checkpointInterval", "100000");
+        Test suite = new DatabasePropertyTestSetup(
+            new TestSuite(StalePlansTest.class), props, true);
+        return new CleanDatabaseTestSetup(suite) {
+            protected void decorateSQL(Statement s) throws SQLException {
+                // Create and populate a table to be used for flushing the
+                // cache. Flushing the cache causes all row count changes to be
+                // written, which is necessary for the results of this test to
+                // be stable (because otherwise the row count changes would be
+                // written asynchronously)
+                s.executeUpdate("create table flusher (c1 varchar(3000))");
+                PreparedStatement ps = getConnection().prepareStatement(
+                    "insert into flusher values ?");
+                ps.setString(1, Formatters.padString("a", 3000));
+                for (int i = 0; i < 64; i++) {
+                    ps.executeUpdate();
+                }
+            }
+        };
+    }
+
+    /**
+     * Create tables and indexes needed by the test cases. Enable collection of
+     * run-time statistics.
+     */
+    protected void setUp() throws SQLException {
+        getConnection().setAutoCommit(false);
+        Statement stmt = createStatement();
+        stmt.executeUpdate("create table t (c1 int, c2 int, c3 varchar(255))");
+        stmt.executeUpdate("create index idx on t (c1)");
+        stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+        stmt.close();
+        commit();
+    }
+
+    /**
+     * Drop tables used in the test.
+     */
+    protected void tearDown() throws Exception {
+        Statement stmt = createStatement();
+        stmt.executeUpdate("drop table t");
+        commit();
+        super.tearDown();
+    }
+
+    /**
+     * Flush the cache so that row count changes are visible.
+     */
+    private void flushRowCount(Statement stmt) throws SQLException {
+        JDBC.assertFullResultSet(
+            stmt.executeQuery("select count(c1) from flusher"),
+            new String[][] { { "64" } });
+    }
+
+    /**
+     * Negative test - set stalePlanCheckInterval to a value out of range.
+     */
+    public void testStalePlanCheckIntervalOutOfRange() throws SQLException {
+        Statement stmt = createStatement();
+        assertStatementError("XCY00", stmt,
+                             "call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
+                             "'derby.language.stalePlanCheckInterval', '2')");
+        stmt.close();
+    }
+
+    /**
+     * Test that the query plan is changed when the size of a small table
+     * changes.
+     *
+     * <p><b>Note:</b> This test is outdated since Derby now tries to use
index
+     * scans whenever possible on small tables (primarily to avoid table locks
+     * for certain isolation levels, but also because a small table is likely
+     * to grow).
+     */
+    public void testStalePlansOnSmallTable() throws SQLException {
+        Statement stmt = createStatement();
+
+        PreparedStatement insert =
+            prepareStatement("insert into t values (?,?,?)");
+        insert.setInt(1, 1);
+        insert.setInt(2, 100);
+        insert.setString(3, Formatters.padString("abc", 255));
+        insert.executeUpdate();
+        commit();
+
+        // Make sure row count from insert is flushed out
+        flushRowCount(stmt);
+
+        PreparedStatement ps =
+            prepareStatement("select count(c1 + c2) from t where c1 = 1");
+
+        // Expect this to do an index scan
+        String[][] expected = {{ "1" }};
+        JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedIndexScan());
+
+        // Execute 11 more times, the plan should not change
+        for (int i = 0; i < 11; i++) {
+            JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        }
+
+        // Expect index scan
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedIndexScan());
+        commit();
+
+        // Now increase the size of the table
+        insert.setInt(2, 100);
+        for (int i = 2; i <= 10; i++) {
+            insert.setInt(1, i);
+            insert.executeUpdate();
+        }
+        commit();
+
+        // Make sure row count from inserts is flushed out
+        flushRowCount(stmt);
+
+        // Execute 11 times, the plan should not change
+        for (int i = 0; i < 11; i++) {
+            JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        }
+
+        // Expect this to use index
+        JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedIndexScan());
+        commit();
+
+        // Now shrink the table back to its original size
+        stmt.executeUpdate("delete from t where c1 >= 2");
+
+        // Execute 11 times, the plan should not change
+        for (int i = 0; i < 11; i++) {
+            JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        }
+
+        // Expect this to do an index scan
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedIndexScan());
+
+        stmt.close();
+        ps.close();
+        insert.close();
+    }
+
+    /**
+     * Test that the query plan changes when a large table is modified.
+     */
+    public void testStalePlansOnLargeTable() throws SQLException {
+        Statement stmt = createStatement();
+
+        PreparedStatement insert =
+            prepareStatement("insert into t values (?,?,?)");
+        insert.setInt(1, 1);
+        insert.setInt(2, 1);
+        insert.setString(3, Formatters.padString("abc", 255));
+        insert.executeUpdate();
+
+        PreparedStatement insert2 =
+            prepareStatement("insert into t select c1+?, c2+?, c3 from t");
+        for (int i = 1; i <= 512; i *= 2) {
+            insert2.setInt(1, i);
+            insert2.setInt(2, i);
+            insert2.executeUpdate();
+        }
+
+        commit();
+
+        // Make sure row count from inserts is flushed out
+        flushRowCount(stmt);
+
+        PreparedStatement ps = prepareStatement(
+            "select count(c1 + c2) from t where c1 = 1");
+
+        // Expect this to use index
+        String[][] expected = {{ "1" }};
+        JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedIndexScan());
+        commit();
+
+        // Change the row count a little bit
+        for (int i = 1025; i <= 1034; i++) {
+            insert.setInt(1, i);
+            insert.setInt(2, i);
+            insert.executeUpdate();
+        }
+        commit();
+
+        // Change the data so a table scan would make more sense.
+        // Use a qualifier to convince TableScanResultSet not to
+        // update the row count in the store (which would make it
+        // hard for this test to control when recompilation takes
+        // place).
+        stmt.executeUpdate("update t set c1 = 1 where c1 > 0");
+
+        // Make sure row count from inserts is flushed out
+        flushRowCount(stmt);
+
+        // Execute 11 more times, the plan should not change
+        for (int i = 0; i < 11; i++) {
+            JDBC.assertFullResultSet(ps.executeQuery(),
+                                     new String[][] { { "1034" } });
+        }
+
+        // Expect this to use table scan, as the above update has basically
+        // made all the rows in the table be equal to "1", thus using the index
+        // does not help if all the rows are going to qualify.
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedTableScan());
+
+        // Change the row count significantly
+        stmt.executeUpdate("insert into t select c1,c2,c3 from t where c1<128");
+
+        // Make sure row count from inserts is flushed out
+        flushRowCount(stmt);
+
+        // Execute 11 times, the plan should change
+        for (int i = 0; i < 11; i++) {
+            JDBC.assertFullResultSet(ps.executeQuery(),
+                                     new String[][] { { "2068" } });
+        }
+
+        // Expect this to do table scan
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedTableScan());
+
+        // Change the distribution back to where an index makes sense
+        stmt.executeUpdate("update t set c1 = c2");
+
+        // Change the row count significantly
+        stmt.executeUpdate("insert into t select c1, c2, c3 from t");
+
+        // Make sure row count from inserts is flushed out
+        flushRowCount(stmt);
+
+        // Execute 11 times, the plan should change
+        for (int i = 0; i < 11; i++) {
+            JDBC.assertFullResultSet(ps.executeQuery(),
+                                     new String[][] { { "4" } });
+        }
+
+        // Expect this to do index to baserow
+        assertTrue(SQLUtilities.
+                   getRuntimeStatisticsParser(stmt).usedIndexRowToBaseRow());
+
+        stmt.close();
+        insert.close();
+        insert2.close();
+        ps.close();
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StalePlansTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=538441&r1=538440&r2=538441
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Tue May 15 22:50:12 2007
@@ -89,6 +89,7 @@
         suite.addTest(UnaryArithmeticParameterTest.suite());
         suite.addTest(HoldCursorTest.suite());
         suite.addTest(ShutdownDatabaseTest.suite());
+        suite.addTest(StalePlansTest.suite());
         suite.addTest(SystemCatalogTest.suite());
         suite.addTest(ForBitDataTest.suite());
         suite.addTest(DistinctTest.suite());

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java?view=diff&rev=538441&r1=538440&r2=538441
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
Tue May 15 22:50:12 2007
@@ -27,12 +27,14 @@
     private boolean distinctScan = false;
     private boolean eliminatedDuplicates = false;
     private boolean tableScan = false;
+    private final boolean indexScan;
+    private final boolean indexRowToBaseRow;
     private String statistics = "";
     private boolean scrollInsensitive = false;
 
     /**
      * Create a RuntimeStatistics object to parse the text and extract
-     * information. Currently only isolation level is the only thing extracted.
+     * information.
      * 
      * @param rts
      *            Runtime Statistics string
@@ -56,6 +58,10 @@
         if (rts.indexOf("Table Scan ResultSet") > 0) {
         	tableScan = true;
         }
+
+        indexScan = (rts.indexOf("Index Scan ResultSet") >= 0);
+        indexRowToBaseRow =
+            (rts.indexOf("Index Row to Base Row ResultSet") >= 0);
         
         if (rts.indexOf("Eliminate duplicates = true") > 0) {
         	eliminatedDuplicates = true;
@@ -85,6 +91,21 @@
      */
     public boolean usedTableScan() {
     	return tableScan;
+    }
+
+    /**
+     * Return whether or not an index scan result set was used in the query.
+     */
+    public boolean usedIndexScan() {
+        return indexScan;
+    }
+
+    /**
+     * Return whether or not an index row to base row result set was used in
+     * the query.
+     */
+    public boolean usedIndexRowToBaseRow() {
+        return indexRowToBaseRow;
     }
     
     /**



Mime
View raw message