![]() ![]() This example prevents runaway queries from consuming too many resources.ĭBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( The following PL/SQL block creates a resource plan directive for the OLTP group that kills (terminates) any session that exceeds 60 seconds of CPU time. Should the above theory not apply to any invocation of switching to KILL_SESSION? I am wondering how the database is able to achieve it when configured like mentioned in MOS note Managing and Monitoring Runaway Query Using Resource Manager (Doc ID 1600965.1) ![]() While I understand the theory behind your below commentĪ kill session is something I don't a session could do to itself, it needs an external party to initiate that. 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 ![]() If they exceed their time limit, they'll be cancelled. So you would switch people into the group before running a query. GROUP_OR_SUBPLAN=>'CG_EXEC_TIME_LIMIT_DEFAULT',ĭBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA ĭBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA() GROUP_OR_SUBPLAN=>'CG_EXEC_TIME_LIMIT_CANCEL', GROUP_OR_SUBPLAN=>'CG_EXEC_SHORT_LIMIT_CANCEL',ĬOMMENT=>'Kill statement after exceeding total execution time', the plan will cancel the current SQL if it runs for more than 'n' secĭbms_resource_manager.create_plan_directive( now create a plan directive for that special user group to avoid potentially cancelling a "genuine" long running oneĬONSUMER_GROUP=>'CG_EXEC_TIME_LIMIT_DEFAULT',ĬOMMENT=>'This is the consumer group that has no limits (default)'ĬOMMENT=>'Kill statement after exceeding total execution time' this group will be the one we switch back to after running the long statement this group will be the one we switch into before running a possiblyĬONSUMER_GROUP=>'CG_EXEC_TIME_LIMIT_CANCEL',ĬOMMENT=>'This is the consumer group that has limited execution time per statement' Here is a sample script to do thatĭbms_resource_manager.create_pending_area() ĭbms_resource_manager.create_consumer_group(ĬONSUMER_GROUP=>'CG_EXEC_SHORT_LIMIT_CANCEL',ĬOMMENT=>'This is the consumer group that has smallest execution time per statement' You could look at using the resource manager to kill off sessions when they exceed a certain threshold. 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. ![]() Most of the time, the database will catch this and stop the query right there. Alternatively, the app may send an explicit cancel request to the database. 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. Some applications simply abandon the connection, in which case the database simply doesn't know you've "gone away". ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |