Guest User

Due to large number of fetch, OOM occurred. While I was finding what caused the error, I noticed the below. As you can see, the fetch time took 150,000 ms at 34th and two extra 83,000,000 ms.
There are insert and SQL-EXECUTE-QUERY at 32th and 33th. They were supposed to be located after the select query. But they exist after the insert query. I guess they did not appear between 32th to 32th due to the select query. This is only my guessing. I wonder if large number of fetch were actually occurred at the insert query. If I am wrong, when this could occur.

34th
0034[10:54:06 439][152,460][62,380] FETCH[83886081/83886086] [152,460 ms]
32th
0032[10:51:33 974][ 0][ 1] SQL-PREPARE_STMT{5}
INSERT INTO
param1:[Y,]
33th
0033[10:51:33 979][ 5][ 1] SQL-EXECUTE-QUERY{5} [5 ms]

param1:[Y,] param2:[‘0800201618’,‘U800001257’]
UUID: 3489c5c08032a244(3785774392356807236) server time: 10:54:02 829(1424051642829)
agent: AA1 application: xxxxxx.jsp(-246879313)
client ID: 11aa3aca65f4e8fa(1272894485653940474) client IP: 00.00.00.00 user ID::
calling time: 10:51:33 947 terminated time: 10:54:06 447 response time: 152,500
CPU time: 62,380 SQL time: 28 Fetch time: 152,460 TX time: 0
exception: W exception: WARNING_DB_TOOMANY_FETCH
Http User-Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NETCLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0)
________________________________________


[ NO ][ START_TIME ][ GAP][CPU_T]


947][ 0][ 0] START 947][ 0][ 0] webtob2-hth0(172.16.21.26:9900)-w21 [callbill02-419][native:16f9e] 948][ 1][ 1] GET-CONNECTION[jeus.jdbc.connectionpool.ConnectionPoolImpl.getConnection] [1 ms] 948][ 0][ 1] SQL-PREPARE_STMT{0} SELECT ID_CUST … param1:[99991231,YYYYMMDD,1,1,] 964][ 16][ 1] SQL-EXECUTE-QUERY{0} [16 ms] param1:[99991231,YYYYMMDD,1,1,] param2:[‘0800201618’] 964][ 0][ 1] FETCH[1/1] 964][ 0][ 1] CLOSE-CONNECTION 964][ 0][ 0] CLOSE-CONNECTION 965][ 1][ 1] GET-CONNECTION[jeus.jdbc.connectionpool.ConnectionPoolImpl.getConnection] [1 ms] 965][ 0][ 1] SQL-PREPARE_STMT{1} SELECT CD_PAY_METHOD, FN_CD_NAME(‘$’ , CD_PAY_METHOD) … param1:[CU440,99991231,20,30,] 966][ 1][ 1] SQL-EXECUTE-QUERY{1} [1 ms] param1:[CU440,99991231,20,30,] param2:[‘2200’,‘2200’] 966][ 0][ 1] FETCH[2/3] 966][ 0][ 1] CLOSE-CONNECTION 966][ 0][ 0] CLOSE-CONNECTION 967][ 1][ 1] GET-CONNECTION

[jeus.jdbc.connectionpool.ConnectionPoolImpl.getConnection] [1 ms]

967][ 0][ 1] SQL-PREPARE_STMT{2} SELECT FN_CD_NAME(‘$’,CD_VAN), ID_STORE … param1:[CU731,1,1,A,1,] 969][ 2][ 1] SQL-EXECUTE-QUERY{2} [2 ms] param1:[CU731,1,1,A,1,] param2:[‘2200’] 969][ 0][ 1] FETCH[1/4] 969][ 0][ 1] CLOSE-CONNECTION 969][ 0][ 0] CLOSE-CONNECTION 970][ 1][ 1] GET-CONNECTION

[jeus.jdbc.connectionpool.ConnectionPoolImpl.getConnection] [1 ms]

970][ 0][ 1] SQL-PREPARE_STMT{3} SELECT … param1:[] 971][ 1][ 1] SQL-EXECUTE-QUERY{3} [1 ms] param1:[] param2:[‘0800201618’] 971][ 0][ 1] FETCH[1/5] 971][ 0][ 1] CLOSE-CONNECTION 971][ 0][ 0] CLOSE-CONNECTION 972][ 1][ 1] GET-CONNECTION

[jeus.jdbc.connectionpool.ConnectionPoolImpl.getConnection] [1 ms]

972][ 0][ 1] SQL-PREPARE_STMT{4} select SUBSTR, … param1:[6,Y,67,] 973][ 1][ 1] SQL-EXECUTE-QUERY{4} [1 ms] param1:[6,Y,67,] param2:[‘U800001257’] 973][ 0][ 1] FETCH[0/5] 973][ 0][ 1] CLOSE-CONNECTION 973][ 0][ 0] CLOSE-CONNECTION 974][ 1][ 1] GET-CONNECTION

[jeus.jdbc.connectionpool.ConnectionPoolImpl.getConnection] [1 ms]

974][ 0][ 1] SQL-PREPARE_STMT{5} INSERT INTO … param1:[Y,] 979][ 5][ 1] SQL-EXECUTE-QUERY{5} [5 ms] param1:[Y,] param2:[‘0800201618’,‘U800001257’] 439][152,460][62,380] FETCH[83886081/83886086] [152,460 ms] 439][ 0][62,380] CLOSE-CONNECTION 439][ 0][ 0] CLOSE-CONNECTION 443][ 4][62,380] GET-CONNECTION

[jeus.jdbc.connectionpool.ConnectionPoolImpl.getConnection] [4 ms]

443][ 0][ 0] SET_AUTO_COMMIT: false 443][ 0][62,380] SQL-PREPARE_STMT{6} INSERT /*+ APPEND */ … param1:[] 445][ 2][62,380] SQL-EXECUTE-UPDATE{6} [2 ms] param1:[] param2:

[charstream/j:10,charstream/j:6,charstream/j:8,charstream/j:0,charstream/j:1310]

446][ 1][ 0] COMMIT 446][ 0][62,380] CLOSE-CONNECTION 446][ 0][ 0] CLOSE-CONNECTION 447][ 1][ 0] END

TOTAL[152,500][62,380]
Khalid Saeed

According to your profile, I noticed two CLOSE-CONNECTION are applied at single GET-CONNECTION. This is not normal. SQL-EXECUTE-QUEERY{5} did actual task. You need to check how the source is arranged. Over 80,000,000 ms were put in place for sure.

Guest User

It seems the SQL is missing. Do I need to apply additional setting related with SQL?
If so, where should I look into?