I had a user in our production server that performed a query through a web
service interface that requested to retrieve and perform a multi-level sort on
probably 30 million records. The tables being queried on are in a UNION
(basically one table for each week of the year and the query was working across
the this week and the previous week so it probably could not use the indexes to
avoid the sort.
Derby reported OutOfMemory errors. In derby.log are errors such as:
Tue Feb 14 20:15:12 EST 2012 Thread[DRDAConnThread_68,5,main] (XID =
861337977), (SESSIONID = 45137761), (DATABASE = csemdb), (DRDAID =
????????.??-866660124046835337{112060}), Failed Statement is: SELECT
r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId,
LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime,
TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets,
OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency,
TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2,
LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5,
LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8,
LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement,
LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2,
LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4,
LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6,
LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8,
LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter,
TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1,
RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3,
RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5,
RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7,
RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9,
MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit
as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN
PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN
PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN
PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN
PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE
ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0'
AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER
BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName,
StartDateTime
java.lang.OutOfMemoryError: GC overhead limit exceeded
Tue Feb 14 23:48:28 EST 2012 Thread[DRDAConnThread_122,5,main] (XID =
861195891), (SESSIONID = 45103057), (DATABASE = csemdb), (DRDAID =
????????.??-448669783631952048{111972}), Failed Statement is: SELECT
r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId,
LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime,
TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets,
OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency,
TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2,
LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5,
LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8,
LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement,
LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2,
LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4,
LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6,
LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8,
LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter,
TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1,
RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3,
RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5,
RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7,
RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9,
MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit
as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN
PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN
PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN
PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN
PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE
ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0'
AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER
BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName,
StartDateTime
java.lang.OutOfMemoryError: Java heap space
The server is a Oracle M3000 and the Derby engine heap is setup to be 8Gb.
I know the user should not have run that query and I can correct and prevent
that.
I am curious as to why however that the query just did not consume disk space
for on disk sorting and take a really long time to return. The result of the
query is being read by a Java application using a ResultSet that is configured
as (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY). Each record is
being read and is producing an XML file from the data.
I would assume that Derby could query and sort this many results and present
them through the record set interface although it may take a long time and may
take significant disk space. Am I wrong about this?
Any comments will be most appreciated.
Brett
