Long running query - Session remains active

partlycloudy

ORDS 20.4 on Oracle 12.1

When one of our applications executes an ORDS API that takes a long time to run, we find that the load balancer component between the client and ORDS times out and returns a HTTP/504 back to the caller. When this happens, the ORDS handler's PL/SQL code continues executing in the database and the session shows as ACTIVE. These sessions build up over time and cause other issues.

Is there a way to kill these sessions automatically?

@thatJeffSmith-Oracle

Thanks

select 
 username,
 status,
 sql_id,
 sql_exec_start,
 action,
 module,
 client_identifier,
 blocking_session_Status
from gv$session
where program like 'Oracle REST%'
and status = 'ACTIVE'

thatJeffSmith-Oracle

Yes, use resource consumer groups in the database. And please don't tag me, if you want to talk to me, send me a note via email or on m blog. I read all these messages here.

thatJeffSmith-Oracle

Yes, use resource consumer groups in the database. And please don't tag me, if you want to talk to me, send me a note via email or on m blog. I read all these messages here.