On 2/15/2012 8:45 AM, Bergquist, Brett wrote:
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
I have not personally encountered the GC overhead limit, but google
search indicates it is related to the amount of time spent on garbage
collection ("too much") and can be overridden.
http://www.petefreitag.com/item/746.cfm
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.
For a heap out of memory, I have seen occasional cases where Derby's
estimate of whether it can sort in memory is wrong. You'd need to look
at the plan and do some debugging with the actual query if you are able
to reproduce to find out if that is the case here.
