We have a java application in our production enviroment using Spring framework which is configured with DBCP connection pool and the backend is Oracle database( version 11.2.0.3). Recently we encountered an occasionally happened situation(about once every week) and can be described as below:
The application schedules a task that runs every 10 minutes. And during task execution, the application would issue a SQL query of which the result is expected to be got within 10 seconds. At 11:51 a.m Oct 13th, however, the application failed to get the result within expected time, and 2 hours later, at 13:51, the result was finally returned to application. Due to lack of information at that time, we were not able to reproduce the same problem in test environment. At 15:31 p.m, Oct 29th, it happend again and this time we grasped all the information including thread dump and Oracle diagnostic information. We could found that:
1. Through oracle v$sql view, we could find that the SQL query is executed twice, at 15:31 and 17:31 respectively.
2. By analyzing TCP packets provided by network monitoring tools, it can be concluded that the request TCP packet containing SQL statement had been sent to Oracle and get executed, but after that JDBC only fetched first 80000 records out of 90000 records in total and then it stopped, didn't send any more request to Oracle to fetch rows. 2 hours later, Oracle sent a TCP keep alive packet and JDBC driver resume fetching remaining rows using the same connection(which can be confirmed by comparing source port of packets).
3. We dumped the thread at which JDBC hangs at socket read of JDBC driver
Does any one has the same problem before? Is it a bug of Oracle JDBC provider? How can we fix it or workaround this?
By the way, the version of JDBC we use is 11.2.0.1 and JDK version is 1.5.0_22.
The SQL statement is very simple:
select sum(n.netvalue) npvi,
n.HISCENEID hsid,
n.counterpartyid cid,
n.productid pid,
n.opendays
from newtradenpv n
where n.hisceneid is not null
and n.entrustdealid in
(13877, 13842, 13954, 13959, 13929, 13831, 13893, 13940, 13930, 13876,
13898, 13896, 13879, 13943, 13895, 13841, 13830, 13874, 13960, 13937,
13894, 13926, 13873, 13882, 13843, 13944, 13925, 13889, 13884, 13840,
13871, 13958, 13953, 13844, 13900, 13883, 13939, 13899, 13872, 13956,
13878, 13906, 13955, 13922, 13962, 13824, 13866, 13890, 13921, 13828,
13888, 13903, 13839, 13904, 13976, 13829, 13838, 13865, 13863, 13961,
13927, 13837, 13902, 13934, 13948, 13823, 13852, 13963, 13945, 13935,
13832, 13947, 13864, 13964, 13853, 13835, 13892, 13936, 13914, 13885,
13875, 13891, 13833, 13901, 13886, 13834, 13933, 13855, 13919, 13858,
13974, 13857, 13856, 13836, 13920, 13913, 13847, 13911, 13822, 13946,
13924, 13860, 13848, 13821, 13975, 13854, 13909, 13827, 13862, 13887,
13846, 13912, 13916, 13861, 13928, 13825, 13942, 13826, 13923, 13915,
13970, 13845, 13977, 13905, 13941, 13931, 13957, 13867, 13952, 13908,
13972, 13849, 13932, 13868, 13850, 13938, 13910, 13918, 13881, 13966,
13951, 13897, 13870, 13880, 13851, 13907, 13949, 13917, 13965, 13869,
13950, 13859)
group by n.counterpartyid, n.productid, n.hisceneid, n.opendays