There is a few "depends" in here.
- Some applications simply abandon the connection, in which case the database simply doesn't know you've "gone away". It will run the query and when it comes to present the results to the client, it will find the client is no longer there. So in that sense, your DBA is right.
- Alternatively, the app may send an explicit cancel request to the database. Most of the time, the database will catch this and stop the query right there. I say "most of the time" because if the database is in a non-interruptible phase, it will still complete that part before your query will be terminated.
You could look at using the resource manager to kill off sessions when they exceed a certain threshold. Here is a sample script to do that
begin
dbms_resource_manager.create_pending_area();
--
dbms_resource_manager.create_consumer_group(
CONSUMER_GROUP=>'CG_EXEC_SHORT_LIMIT_CANCEL',
COMMENT=>'This is the consumer group that has smallest execution time per statement'
);
-- this group will be the one we switch into before running a possibly
-- long running SQL statement
dbms_resource_manager.create_consumer_group(
CONSUMER_GROUP=>'CG_EXEC_TIME_LIMIT_CANCEL',
COMMENT=>'This is the consumer group that has limited execution time per statement'
);
-- this group will be the one we switch back to after running the long statement
-- to avoid potentially cancelling a "genuine" long running one
dbms_resource_manager.create_consumer_group(
CONSUMER_GROUP=>'CG_EXEC_TIME_LIMIT_DEFAULT',
COMMENT=>'This is the consumer group that has no limits (default)'
);
-- and we need a resource plan:
dbms_resource_manager.create_plan(
PLAN=> 'EXEC_TIME_LIMIT',
COMMENT=>'Kill statement after exceeding total execution time'
);
-- now create a plan directive for that special user group
-- the plan will cancel the current SQL if it runs for more than 'n' sec
dbms_resource_manager.create_plan_directive(
PLAN=> 'EXEC_TIME_LIMIT',
GROUP_OR_SUBPLAN=>'CG_EXEC_SHORT_LIMIT_CANCEL',
COMMENT=>'Kill statement after exceeding total execution time',
SWITCH_GROUP=>'CANCEL_SQL',
SWITCH_TIME=>10,
SWITCH_ESTIMATE=>false,
SWITCH_FOR_CALL=> TRUE
);
-- now create a plan directive for that special user group
-- the plan will cancel the current SQL if it runs for more than 'n' sec
dbms_resource_manager.create_plan_directive(
PLAN=> 'EXEC_TIME_LIMIT',
GROUP_OR_SUBPLAN=>'CG_EXEC_TIME_LIMIT_CANCEL',
COMMENT=>'Kill statement after exceeding total execution time',
SWITCH_GROUP=>'CANCEL_SQL',
SWITCH_TIME=>90,
SWITCH_ESTIMATE=>false,
SWITCH_FOR_CALL=> TRUE
);
dbms_resource_manager.create_plan_directive(
PLAN=> 'EXEC_TIME_LIMIT',
GROUP_OR_SUBPLAN=>'CG_EXEC_TIME_LIMIT_DEFAULT',
COMMENT=>'leave others alone'
-- ,CPU_P1=>100
);
dbms_resource_manager.create_plan_directive(
PLAN=> 'EXEC_TIME_LIMIT',
GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
COMMENT=>'leave others alone'
-- ,CPU_P1=>100
);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;
/
so you would switch people into the group before running a query. If they exceed their time limit, they'll be cancelled. So if your app server timeout was (say) 30 seconds, you would set this to 35 seconds so queries will be cleaned up shortly after your app has lost interest in them