Python code to cancel a running Oracle SQL Query
I have the following python code which runs multiple SQL Queries in Oracle database and combines them into one dataframe.
The queries exist in a txt file and every row is a separate SQL query. The loop runs sequentially the queries. I want to cancel any SQL queries that run for more than 10 secs so as not to create an overhead in the database.
The following code doesnt actually me give the results that i want. More specifically this bit of the code really help me on my issue:
if (time.time() - start) > 10:
connection.cancel()
Full python code is the following. Probably it is an oracle function that can be called so as to cancel the query.
import pandas as pd
import cx_Oracle
import time
ip = 'XX.XX.XX.XX'
port = XXXX
svc = 'XXXXXX'
dsn_tns = cx_Oracle.makedsn(ip, port, service_name = svc)
connection = cx_Oracle.connect(user='XXXXXX'
, password='XXXXXX'
, dsn=dsn_tns
, encoding = "UTF-8"
, nencoding = "UTF-8"
)
filepath = 'C:/XXXXX'
appended_data =
with open(filepath + 'sql_queries.txt') as fp:
line = fp.readline()
while line:
start = time.time()
df = pd.read_sql(line, con=connection)
if (time.time() - start) > 10:
connection.cancel()
print("Cancel")
appended_data.append(df)
df_combined = pd.concat(appended_data, axis=0)
line = fp.readline()
print(time.time() - start)
fp.close()
python sql oracle kill cancellation
add a comment |
I have the following python code which runs multiple SQL Queries in Oracle database and combines them into one dataframe.
The queries exist in a txt file and every row is a separate SQL query. The loop runs sequentially the queries. I want to cancel any SQL queries that run for more than 10 secs so as not to create an overhead in the database.
The following code doesnt actually me give the results that i want. More specifically this bit of the code really help me on my issue:
if (time.time() - start) > 10:
connection.cancel()
Full python code is the following. Probably it is an oracle function that can be called so as to cancel the query.
import pandas as pd
import cx_Oracle
import time
ip = 'XX.XX.XX.XX'
port = XXXX
svc = 'XXXXXX'
dsn_tns = cx_Oracle.makedsn(ip, port, service_name = svc)
connection = cx_Oracle.connect(user='XXXXXX'
, password='XXXXXX'
, dsn=dsn_tns
, encoding = "UTF-8"
, nencoding = "UTF-8"
)
filepath = 'C:/XXXXX'
appended_data =
with open(filepath + 'sql_queries.txt') as fp:
line = fp.readline()
while line:
start = time.time()
df = pd.read_sql(line, con=connection)
if (time.time() - start) > 10:
connection.cancel()
print("Cancel")
appended_data.append(df)
df_combined = pd.concat(appended_data, axis=0)
line = fp.readline()
print(time.time() - start)
fp.close()
python sql oracle kill cancellation
As far as I see, connection.cancel() is the only viable option
– HellHammer
Nov 14 '18 at 8:55
@HellHammer I have used connection.cancel() but the query is first sent to the database and then the statement connection.cancel() runs. In other words the queries always run no matter what. I want the code to capture the execution time and then kill the query
– Demis
Nov 14 '18 at 8:59
You need the query to execute async for any timer on the execution time to work. Cancelling the client side issue of the query does not mean the DB will actually cancel - you would have to go specifically kill the spid in the DB.
– Andrew
Nov 14 '18 at 9:25
add a comment |
I have the following python code which runs multiple SQL Queries in Oracle database and combines them into one dataframe.
The queries exist in a txt file and every row is a separate SQL query. The loop runs sequentially the queries. I want to cancel any SQL queries that run for more than 10 secs so as not to create an overhead in the database.
The following code doesnt actually me give the results that i want. More specifically this bit of the code really help me on my issue:
if (time.time() - start) > 10:
connection.cancel()
Full python code is the following. Probably it is an oracle function that can be called so as to cancel the query.
import pandas as pd
import cx_Oracle
import time
ip = 'XX.XX.XX.XX'
port = XXXX
svc = 'XXXXXX'
dsn_tns = cx_Oracle.makedsn(ip, port, service_name = svc)
connection = cx_Oracle.connect(user='XXXXXX'
, password='XXXXXX'
, dsn=dsn_tns
, encoding = "UTF-8"
, nencoding = "UTF-8"
)
filepath = 'C:/XXXXX'
appended_data =
with open(filepath + 'sql_queries.txt') as fp:
line = fp.readline()
while line:
start = time.time()
df = pd.read_sql(line, con=connection)
if (time.time() - start) > 10:
connection.cancel()
print("Cancel")
appended_data.append(df)
df_combined = pd.concat(appended_data, axis=0)
line = fp.readline()
print(time.time() - start)
fp.close()
python sql oracle kill cancellation
I have the following python code which runs multiple SQL Queries in Oracle database and combines them into one dataframe.
The queries exist in a txt file and every row is a separate SQL query. The loop runs sequentially the queries. I want to cancel any SQL queries that run for more than 10 secs so as not to create an overhead in the database.
The following code doesnt actually me give the results that i want. More specifically this bit of the code really help me on my issue:
if (time.time() - start) > 10:
connection.cancel()
Full python code is the following. Probably it is an oracle function that can be called so as to cancel the query.
import pandas as pd
import cx_Oracle
import time
ip = 'XX.XX.XX.XX'
port = XXXX
svc = 'XXXXXX'
dsn_tns = cx_Oracle.makedsn(ip, port, service_name = svc)
connection = cx_Oracle.connect(user='XXXXXX'
, password='XXXXXX'
, dsn=dsn_tns
, encoding = "UTF-8"
, nencoding = "UTF-8"
)
filepath = 'C:/XXXXX'
appended_data =
with open(filepath + 'sql_queries.txt') as fp:
line = fp.readline()
while line:
start = time.time()
df = pd.read_sql(line, con=connection)
if (time.time() - start) > 10:
connection.cancel()
print("Cancel")
appended_data.append(df)
df_combined = pd.concat(appended_data, axis=0)
line = fp.readline()
print(time.time() - start)
fp.close()
python sql oracle kill cancellation
python sql oracle kill cancellation
asked Nov 14 '18 at 8:48
DemisDemis
5710
5710
As far as I see, connection.cancel() is the only viable option
– HellHammer
Nov 14 '18 at 8:55
@HellHammer I have used connection.cancel() but the query is first sent to the database and then the statement connection.cancel() runs. In other words the queries always run no matter what. I want the code to capture the execution time and then kill the query
– Demis
Nov 14 '18 at 8:59
You need the query to execute async for any timer on the execution time to work. Cancelling the client side issue of the query does not mean the DB will actually cancel - you would have to go specifically kill the spid in the DB.
– Andrew
Nov 14 '18 at 9:25
add a comment |
As far as I see, connection.cancel() is the only viable option
– HellHammer
Nov 14 '18 at 8:55
@HellHammer I have used connection.cancel() but the query is first sent to the database and then the statement connection.cancel() runs. In other words the queries always run no matter what. I want the code to capture the execution time and then kill the query
– Demis
Nov 14 '18 at 8:59
You need the query to execute async for any timer on the execution time to work. Cancelling the client side issue of the query does not mean the DB will actually cancel - you would have to go specifically kill the spid in the DB.
– Andrew
Nov 14 '18 at 9:25
As far as I see, connection.cancel() is the only viable option
– HellHammer
Nov 14 '18 at 8:55
As far as I see, connection.cancel() is the only viable option
– HellHammer
Nov 14 '18 at 8:55
@HellHammer I have used connection.cancel() but the query is first sent to the database and then the statement connection.cancel() runs. In other words the queries always run no matter what. I want the code to capture the execution time and then kill the query
– Demis
Nov 14 '18 at 8:59
@HellHammer I have used connection.cancel() but the query is first sent to the database and then the statement connection.cancel() runs. In other words the queries always run no matter what. I want the code to capture the execution time and then kill the query
– Demis
Nov 14 '18 at 8:59
You need the query to execute async for any timer on the execution time to work. Cancelling the client side issue of the query does not mean the DB will actually cancel - you would have to go specifically kill the spid in the DB.
– Andrew
Nov 14 '18 at 9:25
You need the query to execute async for any timer on the execution time to work. Cancelling the client side issue of the query does not mean the DB will actually cancel - you would have to go specifically kill the spid in the DB.
– Andrew
Nov 14 '18 at 9:25
add a comment |
2 Answers
2
active
oldest
votes
A better approach would be to spend some time tuning the queries to make them as efficient as necessary. As @Andrew points out we can't easily kill a database query from outside the database - or even from another session inside the database (it requires DBA level privileges).
Indeed, most DBAs would rather you ran a query for 20 seconds rather than attempt to kill every query which runs more than 10. Apart from anything else, having a process which polls you query to see how long it's been running for is itself a waste of database resources.
I suggest you discuss this with your DBA. You may find you're worrying about nothing.
Thank you for the answer. The queries are tuned. It is just the nature of the task that makes it difficult. Indeed it is very difficult to kill a query outside the database. Maybe it is best to look at a different approach
– Demis
Nov 14 '18 at 10:20
You could look at using the resource manager to kill off sessions when they exceed a certain threshold --> asktom.oracle.com/pls/asktom/…
– Ted at ORCL.Pro
Nov 16 '18 at 11:29
add a comment |
Look at cx_Oracle 7's Connection.callTimeout setting. You'll need to be using Oracle client libraries 18+. (These will connect to Oracle DB 11.2+). The doc for the equivalent node-oracledb parameter explains the fine print behind the Oracle behavior and round trips.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53296152%2fpython-code-to-cancel-a-running-oracle-sql-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
A better approach would be to spend some time tuning the queries to make them as efficient as necessary. As @Andrew points out we can't easily kill a database query from outside the database - or even from another session inside the database (it requires DBA level privileges).
Indeed, most DBAs would rather you ran a query for 20 seconds rather than attempt to kill every query which runs more than 10. Apart from anything else, having a process which polls you query to see how long it's been running for is itself a waste of database resources.
I suggest you discuss this with your DBA. You may find you're worrying about nothing.
Thank you for the answer. The queries are tuned. It is just the nature of the task that makes it difficult. Indeed it is very difficult to kill a query outside the database. Maybe it is best to look at a different approach
– Demis
Nov 14 '18 at 10:20
You could look at using the resource manager to kill off sessions when they exceed a certain threshold --> asktom.oracle.com/pls/asktom/…
– Ted at ORCL.Pro
Nov 16 '18 at 11:29
add a comment |
A better approach would be to spend some time tuning the queries to make them as efficient as necessary. As @Andrew points out we can't easily kill a database query from outside the database - or even from another session inside the database (it requires DBA level privileges).
Indeed, most DBAs would rather you ran a query for 20 seconds rather than attempt to kill every query which runs more than 10. Apart from anything else, having a process which polls you query to see how long it's been running for is itself a waste of database resources.
I suggest you discuss this with your DBA. You may find you're worrying about nothing.
Thank you for the answer. The queries are tuned. It is just the nature of the task that makes it difficult. Indeed it is very difficult to kill a query outside the database. Maybe it is best to look at a different approach
– Demis
Nov 14 '18 at 10:20
You could look at using the resource manager to kill off sessions when they exceed a certain threshold --> asktom.oracle.com/pls/asktom/…
– Ted at ORCL.Pro
Nov 16 '18 at 11:29
add a comment |
A better approach would be to spend some time tuning the queries to make them as efficient as necessary. As @Andrew points out we can't easily kill a database query from outside the database - or even from another session inside the database (it requires DBA level privileges).
Indeed, most DBAs would rather you ran a query for 20 seconds rather than attempt to kill every query which runs more than 10. Apart from anything else, having a process which polls you query to see how long it's been running for is itself a waste of database resources.
I suggest you discuss this with your DBA. You may find you're worrying about nothing.
A better approach would be to spend some time tuning the queries to make them as efficient as necessary. As @Andrew points out we can't easily kill a database query from outside the database - or even from another session inside the database (it requires DBA level privileges).
Indeed, most DBAs would rather you ran a query for 20 seconds rather than attempt to kill every query which runs more than 10. Apart from anything else, having a process which polls you query to see how long it's been running for is itself a waste of database resources.
I suggest you discuss this with your DBA. You may find you're worrying about nothing.
answered Nov 14 '18 at 9:39
APCAPC
120k15118230
120k15118230
Thank you for the answer. The queries are tuned. It is just the nature of the task that makes it difficult. Indeed it is very difficult to kill a query outside the database. Maybe it is best to look at a different approach
– Demis
Nov 14 '18 at 10:20
You could look at using the resource manager to kill off sessions when they exceed a certain threshold --> asktom.oracle.com/pls/asktom/…
– Ted at ORCL.Pro
Nov 16 '18 at 11:29
add a comment |
Thank you for the answer. The queries are tuned. It is just the nature of the task that makes it difficult. Indeed it is very difficult to kill a query outside the database. Maybe it is best to look at a different approach
– Demis
Nov 14 '18 at 10:20
You could look at using the resource manager to kill off sessions when they exceed a certain threshold --> asktom.oracle.com/pls/asktom/…
– Ted at ORCL.Pro
Nov 16 '18 at 11:29
Thank you for the answer. The queries are tuned. It is just the nature of the task that makes it difficult. Indeed it is very difficult to kill a query outside the database. Maybe it is best to look at a different approach
– Demis
Nov 14 '18 at 10:20
Thank you for the answer. The queries are tuned. It is just the nature of the task that makes it difficult. Indeed it is very difficult to kill a query outside the database. Maybe it is best to look at a different approach
– Demis
Nov 14 '18 at 10:20
You could look at using the resource manager to kill off sessions when they exceed a certain threshold --> asktom.oracle.com/pls/asktom/…
– Ted at ORCL.Pro
Nov 16 '18 at 11:29
You could look at using the resource manager to kill off sessions when they exceed a certain threshold --> asktom.oracle.com/pls/asktom/…
– Ted at ORCL.Pro
Nov 16 '18 at 11:29
add a comment |
Look at cx_Oracle 7's Connection.callTimeout setting. You'll need to be using Oracle client libraries 18+. (These will connect to Oracle DB 11.2+). The doc for the equivalent node-oracledb parameter explains the fine print behind the Oracle behavior and round trips.
add a comment |
Look at cx_Oracle 7's Connection.callTimeout setting. You'll need to be using Oracle client libraries 18+. (These will connect to Oracle DB 11.2+). The doc for the equivalent node-oracledb parameter explains the fine print behind the Oracle behavior and round trips.
add a comment |
Look at cx_Oracle 7's Connection.callTimeout setting. You'll need to be using Oracle client libraries 18+. (These will connect to Oracle DB 11.2+). The doc for the equivalent node-oracledb parameter explains the fine print behind the Oracle behavior and round trips.
Look at cx_Oracle 7's Connection.callTimeout setting. You'll need to be using Oracle client libraries 18+. (These will connect to Oracle DB 11.2+). The doc for the equivalent node-oracledb parameter explains the fine print behind the Oracle behavior and round trips.
answered Nov 16 '18 at 3:02
Christopher JonesChristopher Jones
2,0961715
2,0961715
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53296152%2fpython-code-to-cancel-a-running-oracle-sql-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
As far as I see, connection.cancel() is the only viable option
– HellHammer
Nov 14 '18 at 8:55
@HellHammer I have used connection.cancel() but the query is first sent to the database and then the statement connection.cancel() runs. In other words the queries always run no matter what. I want the code to capture the execution time and then kill the query
– Demis
Nov 14 '18 at 8:59
You need the query to execute async for any timer on the execution time to work. Cancelling the client side issue of the query does not mean the DB will actually cancel - you would have to go specifically kill the spid in the DB.
– Andrew
Nov 14 '18 at 9:25