Some background.
I have a customer that is using an earlier release of our system that has
Derby 10.8.2.1 installed. Because of issues like
https://issues.apache.org/jira/browse/DERBY-5680,
it has been running with the indexStat daemon disabled.
We are going to have a new release soon and it will be installing Derby
10.9.1.0 with the indexStat
daemon enabled. I recently got a copy of the customer's database (132Gb) and
ran into
a very long query. I manually ran SYS_UTIL.SYSCS_UPDATE_STATISTICS on all of
the tables
in the query to ensure that the statistics are up to date.
The tables in the query look like:
// 62,908,433 Rows
CREATE TABLE "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
(
CONFIGURATIONBUNDLE_ID int NOT NULL,
CONFIGURATIONSET_ID int NOT NULL,
CONSTRAINT CONFIGURATIONSET_CONFIGURATIONBUNDLE_PK PRIMARY KEY
(CONFIGURATIONBUNDLE_ID,CONFIGURATIONSET_ID)
);
ALTER TABLE "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
ADD CONSTRAINT CONFIGURATIONSET_CONFIGURATIONBUNDLE_FK_2
FOREIGN KEY (CONFIGURATIONSET_ID)
REFERENCES "CORE_V1"."CONFIGURATION_SET"(ID);
ALTER TABLE "CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
ADD CONSTRAINT CONFIGURATIONSET_CONFIGURATIONBUNDLE_FK_1
FOREIGN KEY (CONFIGURATIONBUNDLE_ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID);
CREATE INDEX SQL100922214558160 ON
"CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"(CONFIGURATIONSET_ID);
CREATE INDEX SQL100922215312050 ON
"CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"(CONFIGURATIONBUNDLE_ID);
CREATE UNIQUE INDEX SQL081029110254290 ON
"CORE_V1"."CONFIGURATIONSET_CONFIGURATIONBUNDLE"
(
CONFIGURATIONBUNDLE_ID,
CONFIGURATIONSET_ID
);
// 4 Rows
CREATE TABLE "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"
(
ID int PRIMARY KEY NOT NULL,
LLQ_CONFIG_ENTRY_ID int NOT NULL
);
ALTER TABLE "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"
ADD CONSTRAINT INGRESS_COS_QUEUE_CONFIG_BUNDLE_FK_2
FOREIGN KEY (LLQ_CONFIG_ENTRY_ID)
REFERENCES "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_ENTRY"(ID);
ALTER TABLE "PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"
ADD CONSTRAINT INGRESS_COS_QUEUE_CONFIG_BUNDLE_FK_1
FOREIGN KEY (ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID);
CREATE INDEX SQL130327093508050 ON
"PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"(ID);
CREATE INDEX SQL130327093508060 ON
"PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"(LLQ_CONFIG_ENTRY_ID);
CREATE UNIQUE INDEX SQL130327093437740 ON
"PKG_9145E10G"."INGRESS_COS_QUEUE_CONFIG_BUNDLE"(ID);
// 197,363 Rows
CREATE TABLE "PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"
(
ID int PRIMARY KEY NOT NULL
);
ALTER TABLE "PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"
ADD CONSTRAINT CONFIGURATION_BUNDLE_9145E10G_FK_1
FOREIGN KEY (ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID);
CREATE UNIQUE INDEX SQL100226025356610 ON
"PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"(ID);
CREATE INDEX SQL100922215631540 ON
"PKG_9145E10G"."CONFIGURATION_BUNDLE_9145E10G"(ID);
// 6,740,327 Rows
CREATE TABLE "CORE_V1"."CONFIGURATION_BUNDLE"
(
DTYPE varchar(64),
OPLOCK int DEFAULT 0 NOT NULL,
BUNDLE_NAME varchar(64) NOT NULL,
ID int PRIMARY KEY NOT NULL
);
CREATE INDEX CONFIGURATION_BUNDLE_IX_2 ON
"CORE_V1"."CONFIGURATION_BUNDLE"(BUNDLE_NAME);
CREATE INDEX CONFIGURATION_BUNDLE_IX_1 ON
"CORE_V1"."CONFIGURATION_BUNDLE"(DTYPE);
CREATE UNIQUE INDEX SQL100922215131580 ON "CORE_V1"."CONFIGURATION_BUNDLE"(ID);
For some reason, the optimizer is doing a table scan across the
CORE_V1.CONFIGURATIONSET_CONFIGURATIONBUNDLE table
which has 64 million rows and it even goes across this 4 times!.
One issue is that this query is generated by Eclipselink (JPA) and I have no
control on the query generation.
So any ideas on why it would chose this path and what I might be able to do
about it? In playing
around and trying to find the issue, I notice that if I remove the
t0.DTYPE = 'INGRESS_COS_QUEUE_CONFIG_BUNDLE_9145E10G')
constraint, then the query returns 0 rows (it should even with this constraint
as there are no rows
that are acceptable) and returns them in about .05 seconds. Note
CORE_V1.CONFIGURATION_BUNDLE does
have a non-unique index on DTYPE.
Ok, just on a whim, I removed the index on DTYPE and the query came back .06
seconds. That column is used
by Eclipselink in determining which of a "derived" object to return so I don't
have much control on it.
----
Statement Name:
SQL_CURLH000C1
Statement Text:
SELECT t0.ID, t0.DTYPE, t0.BUNDLE_NAME, t0.OPLOCK, t1.ID, t2.ID,
t2.LLQ_CONFIG_ENTRY_ID FROM CORE_V1.CONFIGURATIONSET_CONFIGURATIONBUNDLE t3,
PKG_9145E10G.INGRESS_COS_QUEUE_CONFIG_BUNDLE t2,
PKG_9145E10G.CONFIGURATION_BUNDLE_9145E10G t1, CORE_V1.CONFIGURATION_BUNDLE t0
WHERE (((t3.CONFIGURATIONSET_ID = 4000) AND (t0.ID =
t3.CONFIGURATIONBUNDLE_ID)) AND (((t2.ID = t0.ID) AND (t1.ID = t0.ID)) AND
(t0.DTYPE = 'INGRESS_COS_QUEUE_CONFIG_BUNDLE_9145E10G')))
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 (9):
Number of opens = 1
Rows seen = 0
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
optimizer estimated row count: 0.00
optimizer estimated cost: 8.05
Source result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.00
optimizer estimated cost: 8.05
Left result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.00
optimizer estimated cost: 8.05
Left result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 4
Rows seen from the right = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.00
optimizer estimated cost: 8.05
Left result set:
Index Row to Base Row ResultSet for
CONFIGURATION_BUNDLE:
Number of opens = 1
Rows seen = 4
Columns accessed from heap = {0, 1, 2, 3}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.00
optimizer estimated cost: 8.05
Index Scan ResultSet for
CONFIGURATION_BUNDLE using index CONFIGURATION_BUNDLE_IX_1 at read committed
isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
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=5
Number of rows qualified=4
Number of rows visited=5
Scan type=btree
Tree height=5
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
optimizer estimated row count:
0.00
optimizer estimated cost: 8.05
Right result set:
Table Scan ResultSet for
CONFIGURATIONSET_CONFIGURATIONBUNDLE at read committed isolation level using
instantaneous share row locking chosen by the optimizer
Number of opens = 4
Rows seen = 0
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of pages visited=670838
Number of rows qualified=0
Number of rows visited=262429876
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 0.00
optimizer estimated cost: 0.00
Right result set:
Table Scan ResultSet for
INGRESS_COS_QUEUE_CONFIG_BUNDLE at read committed isolation level using
instantaneous share row locking chosen by the optimizer
Number of opens = 0
Rows seen = 0
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 0.00
optimizer estimated cost: 0.00
Right result set:
Table Scan ResultSet for CONFIGURATION_BUNDLE_9145E10G at read
committed isolation level using instantaneous share row locking chosen by the
optimizer
Number of opens = 0
Rows seen = 0
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 0.00
optimizer estimated cost: 0.00
