0 Replies Latest reply: Dec 18, 2012 4:53 AM by 947409 RSS

    ODP.NET does not respect oracle resource manager?

    947409
      Hi,

      I'm sorry if this is the wrong forum for my problem. I was confuse to post my problem on asp.net or somewhere else but i think this is a good place since my asp.net oltp application make use of ODP.net to connect to the oracle db

      I'm having problem with a long sitting sql running backgroud on my database. I'm trying to get rid this problem by limiting sql execution time using resource manager. Here is my plan directive:

      dbms_resource_manager.create_plan_directive(
      PLAN=> 'LIMIT_EXEC_TIME',
      GROUP_OR_SUBPLAN=>'GROUP_WITH_LIMITED_EXEC_TIME',
      COMMENT=>'Kill statement after exceeding total execution time',
      SWITCH_GROUP=>'CANCEL_SQL',
      SWITCH_TIME_IN_CALL=>10,
      SWITCH_ESTIMATE=>false
      );

      and when i tried from sql developer to execute a long running query it looks work. My server has 12 core and i expect my sql will be terminate after execute about 2 minutes but it takes longer than 2 minutes for oracle db to cancel my sql. However, still i can see a message of 'ORA-00040: active time limit exceeded - call aborted'. I queried from DBA_RSRC_PLAN_DIRECTIVES and i see my plan was there http://imageshack.us/a/img90/3008/plandirectives.png;

      The problems is from my oltp aplication which is using asp.net, the sql never canceled. See http://imageshack.us/a/img820/2638/sqlmonitor.png. Any help is highly appreciated