No answering the question
Martin, April 11, 2017 - 4:51 pm UTC
I ask about how to prevent long query and the answer is about how to stop long query.
April 12, 2017 - 5:02 am UTC
Well...you had plenty of ambiguity :-)
"Is there a way to prevent long running query to be executed on the database. Like setting a timeout of 1 minute for the query before killing it. "
You plan to kill it after 1minute without running it ? .... uh huh.
And of course ... I'm sure that once Chris gave you a link to Resource Manager, you went and read up on the feature, yes ?
Perhaps during the reading you would have seen the MAX_EST_EXEC_TIME directive which can be used to stop a query based on the *estimated* execution time.
Of course you would have seen that yes ?
Preventing V Starting
Stephen, April 11, 2017 - 6:26 pm UTC
The Oracle DB, currently does not have a way to say that this particular query will take x amount of time to run.
It can only tell us how long it is running or has run.
Therefore, Oracle can stop a running query, but not prevent it from starting.
Here is a simple example. You decide to go to the store you have 30 minutes.
You know the average time to get there and back, where the item you want is located, and an average time to checkout(estimates). Your estimate is less than 30 minutes.
Now you actually go to the store. On this particular occasion, you have to stop for every stoplight. The store is crowded, so parking is difficult, and the checkout lines are long. (actual). Your total time turns into 45 minutes.
The next time you go to the store, all the lights are green, the store is almost empty, your actual total time take less than 30 minutes.
Just like this example, there are some things that Oracle cannot predict during the estimation phase. Maybe everything will align just right and the query will return in the time I have allotted. Then again, maybe not, but that is not something that could be determined before deciding to run the query or in this example go to the store.
Stephen Miller
Logic Question
Jeff, January 25, 2021 - 7:36 pm UTC
Seems like a logic question, with a bit of derision thrown in with the answers. Also the provided links don't seem to actually address the question.
Summarizing....
J. Laurindo Chiappa, January 27, 2021 - 2:51 pm UTC
January 27, 2021 - 4:51 pm UTC
You got it