E9.2 Select on BSVW with outer join causes memory leak

craig_welton

craig_welton

Legendary Poster
Interesting one this week. Upgrading from 9.0 to 9.2 (Wintel, SQL Server). A custom UBE that explodes Bills Of Materials was crashing with memory allocation errors. The process mem usage per SM was above 3.5GB, so yeah, on a 32bit install. The "culprit" seemed to be a BSVW joining F3002, F4102 and outer join to F3003. But this seems to be deeper tools issue. Do a table IO select on a view with an outer join and you get a leak.

Just do a loop around Select.V4101DC (no need to fetch) and watch the memory usage rise and eventually it will crash. I understand it takes many iterations to hit the limit, but this is a time bomb.

Added Note: This only happens in a UBE or BSFN

I only have 9.2.4.3 to reference but I replicated the client issue in our lab. I'll be on an earlier 9.2 TR tomorrow and will test there, but this was not an issue in 9.0

FYI
Craig
 
Hmmm concerning. Thanks for the heads up. Have you logged an SR with Oracle? This is probably going to be one of those SRs thats going to be hard to get Oracle to admit they have a problem. We just upgraded to 9.2, about to go live next week. We have a quite a few BSFNs that do table I/O against views.

Is it an NER generation thing or do you see it in C BSFNs as well? In other words do you not see memory free'd when JDB_CloseView (or whatever the call is) is called? Also is this across DB servers, could this be specific to just AS400 or MS SQL Server for example?
 
Hi Brian,

No SR yet. The issue happens in NER and event rule code. I haven't tested in a C BSFN, but it appears to be a problem with the lower level APIs called by SelectKeyed. I only see it happen when the view contains an OUTER JOIN.

Easy to duplicate. This is from a UBE based over F4101

Code:
00001 While SL UserID is equal to SL UserID
00002 |    V4101DC.Select
      |         BC Item Number - Short (F4101.0) [ITM] = TK Item Number - Short [ITM][F4101.0]
00003 End While

Not sure about DB or host type. Only had a chance on Wintel, SQL Server. 32bit JDE. I imagine the same will happen in 64bit, just a lot more memory available.
 
Not sure if it is related since this is on the JAS server but we have an SR open with JAS generating mal-formed SQL statements when the query times out and is re-issued with the NOLOCK qualifier. It has since become less of an issue since we have effectively disabled the whole timeout/re-issue thing. Another case though of issues with SQL statements. Not sure if outer join is a common culprit but were we saw the issue the bsvw contained them.

Example:
Code:
[TABLE]
[TR]
[TD]
Again. Here is the query that initially gets created by the JAS engine. This query is fine, no errors.

29 May 2020 10:22:11,449 [APP ] ERILEY - [JDBJ] SELECT * --field list editted/ommitted for brevity
FROM DVDTA.F4215 T1
INNER JOIN DVDTA.F4941 T0 ON T1.XHSHPN = T0.RSSHPN
LEFT OUTER JOIN DVDTA.F4942 T2 ON T1.XHSHPN = T2.ISSHPN
LEFT OUTER JOIN DVDTA.F5649421 T3 ON T2.ISKCOO = T3.ADKCOO AND T2.ISDOCO = T3.ADDOCO AND T2.ISDCTO = T3.ADDCTO AND T2.ISLNID = T3.ADLNID
LEFT OUTER JOIN DVDTA.F564311T T4 ON T2.ISDOCO = T4.DTDOCO AND T2.ISKCOO = T4.DTKCOO AND T2.ISDCTO = T4.DTDCTO AND T2.ISLNID = T4.DTLNID
WHERE (((((T1.XHSSTS >= ? AND T1.XHSSTS <= ? ) AND T0.RSLDNM < ? ) AND T1.XHMCU = ? )))
ORDER BY T1.XHSHPN ASC , T0.RSRSSN ASC
29 May 2020 10:22:11,449 [APP ] ERILEY - [JDBJ] SQL statement parameter marker values are :
29 May 2020 10:22:11,449 [APP ] ERILEY - [JDBJ] Param1 : 10_Types.VARCHAR,
29 May 2020 10:22:11,449 [APP ] ERILEY - [JDBJ] Param2 : 99_Types.VARCHAR,
29 May 2020 10:22:11,449 [APP ] ERILEY - [JDBJ] Param3 : 99999999_Types.NUMERIC,
29 May 2020 10:22:11,449 [APP ] ERILEY - [JDBJ] Param4 : 166300_Types.VARCHAR,


And here is the SAME query after it is re-issued with NO LOCK qualifiers by the JAS engine after the initial query times out. You will notice that it misplaces the “WITH (NO LOCK)” clauses, specifically it adds NO LOCK clauses after the join clauses in addition to the ones placed after the table identifiers.

29 May 2020 10:22:31,470 [SEVERE] ERILEY - [JDBJ] MS SQL query failed to execute within the specified timeout of 10000 ms. MS SQL query has been retried with this timeout 2 times. MS SQL query will now be executed with the NOLOCK option.
29 May 2020 10:22:31,477 [APP ] ERILEY - [JDBJ] SELECT * --field list editted/ommitted for brevity
FROM DVDTA.F4215 T1 WITH (NOLOCK)
INNER JOIN DVDTA.F4941 T0 WITH (NOLOCK) ON T1.XHSHPN = T0.RSSHPN WITH (NOLOCK)
LEFT OUTER JOIN DVDTA.F4942 T2 WITH (NOLOCK) ON T1.XHSHPN = T2.ISSHPN WITH (NOLOCK)
LEFT OUTER JOIN DVDTA.F5649421 T3 WITH (NOLOCK) ON T2.ISKCOO = T3.ADKCOO AND T2.ISDOCO = T3.ADDOCO AND T2.ISDCTO = T3.ADDCTO AND T2.ISLNID = T3.ADLNID WITH (NOLOCK)
LEFT OUTER JOIN DVDTA.F564311T T4 WITH (NOLOCK) ON T2.ISDOCO = T4.DTDOCO AND T2.ISKCOO = T4.DTKCOO AND T2.ISDCTO = T4.DTDCTO AND T2.ISLNID = T4.DTLNID
WHERE (((((T1.XHSSTS >= ? AND T1.XHSSTS <= ? ) AND T0.RSLDNM < ? ) AND T1.XHMCU = ? )))
ORDER BY T1.XHSHPN ASC , T0.RSRSSN ASC
29 May 2020 10:22:31,477 [APP ] ERILEY - [JDBJ] SQL statement parameter marker values are :
29 May 2020 10:22:31,477 [APP ] ERILEY - [JDBJ] Param1 : 10_Types.VARCHAR,
29 May 2020 10:22:31,477 [APP ] ERILEY - [JDBJ] Param2 : 99_Types.VARCHAR,
29 May 2020 10:22:31,477 [APP ] ERILEY - [JDBJ] Param3 : 99999999_Types.NUMERIC,
29 May 2020 10:22:31,477 [APP ] ERILEY - [JDBJ] Param4 : 166300_Types.VARCHAR[/TD]
[/TR]
[/TABLE]
 
Can you wrap this in Open Close yourself in the NER.
Or would that have no effect?

I always wrap selects in Open Close. A Fetch Single does this automatically
 
Can you wrap this in Open Close yourself in the NER.
Or would that have no effect?

I always wrap selects in Open Close. A Fetch Single does this automatically

Open and Close did not have any effect. I also got it to happen if you create a conditional section using V4101DC and put data selection where ITM = 0. Call the conditional section in a loop and it will leak. So it appears to be in the JDB_OpenView API or below.
 
Back
Top