staleplans.out

derby database source code.good for you.

ij> -- 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.
run resource 'createTestProcedures.subsql';
ij> CREATE FUNCTION  PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> autocommit off;
ij> -- 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)
create table flusher (c1 varchar(3000));
0 rows inserted/updated/deleted
ij> insert into flusher values (PADSTRING('a',3000));
1 row inserted/updated/deleted
ij> -- 1 row
insert into flusher select c1 from flusher;
1 row inserted/updated/deleted
ij> -- 2 rows
insert into flusher select c1 from flusher;
2 rows inserted/updated/deleted
ij> -- 4 rows
insert into flusher select c1 from flusher;
4 rows inserted/updated/deleted
ij> -- 8 rows
insert into flusher select c1 from flusher;
8 rows inserted/updated/deleted
ij> -- 16 rows
insert into flusher select c1 from flusher;
16 rows inserted/updated/deleted
ij> -- 32 rows
insert into flusher select c1 from flusher;
32 rows inserted/updated/deleted
ij> -- 64 rows
commit;
ij> -- Negative test - try setting stalePlanCheckInterval to a value out of range
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.stalePlanCheckInterval', '2');
ERROR XCY00: Invalid value for property 'derby.language.stalePlanCheckInterval'='2'.
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 3500;
ij> -- Make it check for stale plans every 10th execution.  The default is 100,
-- which would force the test to take a lot longer to run, due to more
-- executions.
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.stalePlanCheckInterval', '10');
0 rows inserted/updated/deleted
ij> commit;
ij> create table t1 (c1 int, c2 int, c3 varchar(255));
0 rows inserted/updated/deleted
ij> commit;
ij> create index t1x on t1(c1);
0 rows inserted/updated/deleted
ij> commit;
ij> insert into t1 values (1, 100, PADSTRING('abc',255));
1 row inserted/updated/deleted
ij> commit;
ij> -- Make sure row count from insert is flushed out
select count(c1) from flusher;
1          
-----------
64         
ij> prepare s1 as 'select count(c1 + c2) from t1 where c1 = 1';
ij> execute s1;
1          
-----------
1          
ij> -- Expect this to do a table scan
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select count(c1 + c2) from t1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = false
	Source result set:
		Project-Restrict ResultSet (4):
		Number of opens = 1
		Rows seen = 1
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
		Source result set:
			Index Row to Base Row ResultSet for T1:
			Number of opens = 1
			Rows seen = 1
			Columns accessed from heap = {0, 1}
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				Index Scan ResultSet for T1 using index T1X at read committed isolation level using instantaneous share row locking chosen by the optimizer
				Number of opens = 1
				Rows seen = 1
				Rows filtered = 0
				Fetch Size = 16
					constructor time (milliseconds) = 0
					open time (milliseconds) = 0
					next time (milliseconds) = 0
					close time (milliseconds) = 0
					next time in milliseconds/row = 0
				scan information: 
					Bit set of columns fetched=All
					Number of columns fetched=2
					Number of deleted rows visited=0
					Number of pages visited=1
					Number of rows qualified=1
					Number of rows visited=1
					Scan type=btree
					Tree height=1
					start position: 
	>= on first 1 column(s).
	Ordered null semantics on the following columns: 
					stop position: 
	> on first 1 column(s).
	Ordered null semantics on the following columns: 
					qualifiers:
None
ij> -- Execute 11 more times, the plan should not change
execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> execute s1;
1          
-----------
1          
ij> -- Expect this to do a table scan
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select count(c1 + c2) from t1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = false
	Source result set:
		Project-Restrict ResultSet (4):
		Number of opens = 1
		Rows seen = 1
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
		Source result set:
			Index Row to Base Row ResultSet for T1:
			Number of opens = 1
			Rows seen = 1
			Columns accessed from heap = {0, 1}
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				Index Scan ResultSet for T1 using index T1X at read committed isolation level using instantaneous share row locking chosen by the optimizer
				Number of opens = 1
				Rows seen = 1
				Rows filtered = 0
				Fetch Size = 16
					constructor time (milliseconds) = 0
					open time (milliseconds) = 0
					next time (milliseconds) = 0
					close time (milliseconds) = 0
					next time in milliseconds/row = 0
				scan information: 
					Bit set of columns fetched=All
					Number of columns fetched=2
					Number of deleted rows visited=0
					Number of pages visited=1
					Number of rows qualified=1
					Number of rows visited=1
					Scan type=btree
					Tree height=1
					start position: 
	>= on first 1 column(s).
	Ordered null semantics on the following columns: 
					stop position: 
	> on first 1 column(s).
	Ordered null semantics on the following columns: 
					qualifiers: