6 Replies Latest reply: Mar 15, 2012 9:14 AM by 809801 RSS

    CANCEL_SQL vs KILL_SESSION

    809801
      I'm not a DBA, But I might've messed something up and I want to investigate why the resource manager didn't work the way I expected it to.

      We have an enterprise batch process that runs from , let's say 11:00 AM to 12:30 AM. I had run a query on production (not intentionally) around 11:00 AM.
      (I'm really not sure if I ran it before the batch process started or during the batch process)

      My id was in one of the groups(SUPPORT_GRP) of the resource plan, with a SWITCH_TIME of 1 which tells me that the resource manger should have killed my query after one minute. However, this did not happen..My query kept running for around 30 mins and delayed the batch process (which is a BIG NO-NO).

      My Q: Is there any reason why the res. mgr. wouldn't have cancelled my query?

      Would we be better off using KILL_SESSION as opposed to CANCEL_SQL?


      SUPPORT_GRP     CANCEL_SQL             1     0     0     10


      Thank you!

      Edited by: jess_saunders on Mar 14, 2012 5:57 AM