SQL query that seems to reawaken itself - Help needed

[johndanter]

Hi folks,

I have this query popping up every so often in our database (shown below) and we think we've traced it P47036.
The user is just entering a Sold To AN8 and clicking Find.

It's killing the server as it's a huge table and no index on it. (we'll come back to that later)

The DBA will then kill the Oracle session and CNC state the E1 session is dead, yet the SQL statement comes back to life!!!!!

Is this possible?
Or could it be another user is repeating the action?


FROM PRODDTA.F47036 T0, PRODDTA.F470371 T1 WHERE (((T1.SZHLVL = :1 AND T1.SZAN8 = :2 ))) AND (T0.SYEKCO = T1.SZEKCO AND T0.SYEDOC = T1.SZEDOC AND T0.SYEDCT = T1.SZEDCT) ORDER BY T0.SYEDOC ASC , T0.SYEDCT ASC , T0.SYEKCO ASC

Thanks

John

 

[Larry_Jones]

Well ... how did the DBA kill things.

Here's the first thing I'd try:

First Identify/Find the offending query in Oracle (using SQLPLUS, SQL Developer, ...). You must be a DBA to do these steps.

SELECT S.SID, S.SERIAL#, Q.SQL_TEXT
FROM GV$SESSION S, V$SQL Q
WHERE S.USERNAME IS NOT NULL
AND S.STATUS = 'ACTIVE'
AND S.SQL_ID IS NOT NULL
AND Q.SQL_ID = S.SQL_ID;

Using the Output from that CAREFULLY identify the offending SQL. Then KILL it using the SID and Serial# values:

ALTER SYSTEM KILL SESSION '{sid},{serial#}';

 

[johndanter]

CNC have found this

Looks like E1 is reconnecting. (and a good friend confirms this is normal for E1 actually)

23 Jun 2016 05:56:08,255[SEVERE][BASE]com.jdedwards.database.base.JDBLostConnectionException: [LOST_CONNECTION] Lost connection. com.jdedwards.database.base.JDBLostConnectionException: [LOST_CONNECTION] Lost connection.
23 Jun 2016 05:56:08,255[SEVERE][JDBJ]JDBRetryableReadOperation.attempt(): Retryable read operation failed because of a lost connection. com.jdedwards.database.base.JDBLostConnectionException: [LOST_CONNECTION] Lost connection.
23 Jun 2016 05:56:08,307[SEVERE][JDBJ]JDBRetryableReadOperation.attempt(): Retryable read operation failed because of a lost connection.JDBRetryableReadOperation.attempt(): Cleaned Connection Pool.
23 Jun 2016 05:56:08,849[SEVERE][JDBJ]JDBRetryableReadOperation.attempt(): Retryable read operation failed because of a lost connection.JDBRetryableReadOperation.attempt(): Cleaned Connection Pool.JDBRetryableReadOperation.attempt(): Re Established Lost Connection.

Does anyone know where this setting maybe controlled in the JAS INI files etc?

 

[johndanter]

Maybe the JDBJ.INi file controls this?


# resultSetTimeout=60000

; Retry interval property. Sets the interval (in milliseconds)
; to wait before retrying idempotent database operations. -1
; means not to retry, 0 means retry immediately.
;
; Valid values: -1 or greater

# retryInterval=0

; Retry maximum property. Sets the number of times to retry
; idempotent database operations. -1 means to retry
; indefinitely.
;
; Valid values: -1 or greater

# retryMaximum=5

; Spec consistency check property. Sets the level of spec
; consistency checking. Set this property to a higher level to
; improve exception messages relating to spec inconsistency
; problems. Set this to a lower level when it is not needed
; since it may degrade performance.
;
; Valid values: full, minimal, none