You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2007/05/16 07:50:14 UTC
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
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;
}
/**