Skip to Main Content
  • Questions

  • Limit SQL execution for specific SQL IDs

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Narendra.

Asked: July 09, 2025 - 12:41 pm UTC

Last updated: July 10, 2025 - 10:32 am UTC

Version: 19.25

Viewed 100+ times

You Asked

Hello Connor/Chris,

Hope you are doing well.
Not sure if I am missing something obvious but is there a way to limit execution of specific SQL ID to a certain amount of elapsed time/CPU time? I looked at Resource Manager but it only allows to affect based on user/service/action. Also looked at SQL Quarantine but that appears to be binary i.e. sql either runs or does not.

The context behind my question is a real-world situation where a specific SQL ID occasionally runs for a long time, purely due to data skew. While a permanent solution is being developed and verified, the ask is to put in place a workaround that should automatically cancel long-running executions of this specific SQL ID but allow other executions to continue, without any manual intervention.

Is this possible? If yes, how? If not then is there a different way to approach this requirement?

Thanks in advance

and Chris said...

Does the long-running version have a different plan? If so, you can use SQL Quarantine to stop the statement from running with that specific plan:

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-1D48BAE6-D085-497D-8B7C-28B304E05090

If not, can you set the module/action for the transactions with this query and configure Resource Manager to cancel it based on these values?

Rating

  (3 ratings)

Comments

Re: Limit SQL execution for specific SQL IDs

Narendra, July 09, 2025 - 4:20 pm UTC

Thank you Chris for your response.
Unfortunately it is a packaged application so no option of using module/action without a change. Also my case is about SQL ID using same execution plan but having different response times due to skew in underlying data.
My intention is to find a way to limit/affect only long-running executions but allow other executions of a specific SQL ID (with specific execution plan) without making changes to application code, if there exists one (something like what sql patch/sql profile/sql plan baselines do)

Thanks in advance
Chris Saxon
July 09, 2025 - 4:35 pm UTC

How slow does the query get compared to other queries where it's expected and OK for them to run for a long time? i.e. can you set a blanket query limit for the app using Resource Manager without impacting reporting or other long-running queries?

If not, I'm not aware of other out-of-the-box methods. You could roll your own by having a job that checks v$session for sessions that have been running this SQL id for some time and run alter system cancel SQL on them.

To Narendra

J. Laurindo Chiappa, July 09, 2025 - 6:37 pm UTC

Hi - just adding, we can CANCEL (ie, kill) a long-running SQL ** OR ** we can raise the resources given to the long-running strand SQL: regarding CANCEL you've already all the info, and about the other option, please read about Automatic resource group changing in https://oracle-base.com/articles/12c/resource-manager-enhancements-12cr1#runaway-query-management, https://oracle-base.com/articles/misc/articles-misc#resource-manager , https://oracle-base.com/articles/19c/sql-quarantine-19c#automatic-sql-quarantine , OR you could (under some conditions) simply to Change manually the resource group for the query that you identified as unusual/"wrong" via DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS , read about it in the Docs....
Chris Saxon
July 10, 2025 - 10:32 am UTC

Good suggestion

Narendra, July 10, 2025 - 8:36 am UTC

How slow does the query get compared to other queries where it's expected and OK for them to run for a long time? i.e. can you set a blanket query limit for the app using Resource Manager without impacting reporting or other long-running queries?

If we are able to set a blanket query limit for the app using Resource Manager (and it will have to be very high to begin with to ensure no disruption), is there a way I can limit execution time for this specific SQL ID? For this specific SQL ID, the expectation is that any execution that completes within 5 seconds should be allowed bu anything over 5 seconds should be cancelled, without affecting any other SQL IDs
Chris Saxon
July 10, 2025 - 10:30 am UTC

Not that I'm aware of - as you've noted, Resource Manager isn't that granular.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.