How do I run a database heavy query over jdbc without timing out (Oracle)?
For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows to the client. When I try to run one of the long running queries with default jdbc parameters, I inevitably get: "IO Error: Socket read timed out" after ~30 minutes even though the database is still processing the query (verified in V$SESSION and V$SQLAREA). I am using the newest ojdbc8.jar direct from Oracle and running Oracle Enterprise 12.1.0.2.0.
EDIT:
The output of these queries is being stored in a separate database. The nature of our environment is such that we have an arbitrary number of queries that could be created/run so using stored procedures would be cumbersome.
oracle oracle-12c jdbc pentaho
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows to the client. When I try to run one of the long running queries with default jdbc parameters, I inevitably get: "IO Error: Socket read timed out" after ~30 minutes even though the database is still processing the query (verified in V$SESSION and V$SQLAREA). I am using the newest ojdbc8.jar direct from Oracle and running Oracle Enterprise 12.1.0.2.0.
EDIT:
The output of these queries is being stored in a separate database. The nature of our environment is such that we have an arbitrary number of queries that could be created/run so using stored procedures would be cumbersome.
oracle oracle-12c jdbc pentaho
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Make sure that the firewall or any other device involved in between the database server and client is not killing your connection.
– JSapkota
Feb 8 '18 at 10:23
There is no firewall between the two servers in question.
– Andrew Hangsleben
Feb 8 '18 at 21:02
add a comment |
For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows to the client. When I try to run one of the long running queries with default jdbc parameters, I inevitably get: "IO Error: Socket read timed out" after ~30 minutes even though the database is still processing the query (verified in V$SESSION and V$SQLAREA). I am using the newest ojdbc8.jar direct from Oracle and running Oracle Enterprise 12.1.0.2.0.
EDIT:
The output of these queries is being stored in a separate database. The nature of our environment is such that we have an arbitrary number of queries that could be created/run so using stored procedures would be cumbersome.
oracle oracle-12c jdbc pentaho
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows to the client. When I try to run one of the long running queries with default jdbc parameters, I inevitably get: "IO Error: Socket read timed out" after ~30 minutes even though the database is still processing the query (verified in V$SESSION and V$SQLAREA). I am using the newest ojdbc8.jar direct from Oracle and running Oracle Enterprise 12.1.0.2.0.
EDIT:
The output of these queries is being stored in a separate database. The nature of our environment is such that we have an arbitrary number of queries that could be created/run so using stored procedures would be cumbersome.
oracle oracle-12c jdbc pentaho
oracle oracle-12c jdbc pentaho
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
edited Feb 8 '18 at 21:01
Andrew Hangsleben
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
asked Feb 7 '18 at 21:12
Andrew HangslebenAndrew Hangsleben
263
263
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Make sure that the firewall or any other device involved in between the database server and client is not killing your connection.
– JSapkota
Feb 8 '18 at 10:23
There is no firewall between the two servers in question.
– Andrew Hangsleben
Feb 8 '18 at 21:02
add a comment |
Make sure that the firewall or any other device involved in between the database server and client is not killing your connection.
– JSapkota
Feb 8 '18 at 10:23
There is no firewall between the two servers in question.
– Andrew Hangsleben
Feb 8 '18 at 21:02
Make sure that the firewall or any other device involved in between the database server and client is not killing your connection.
– JSapkota
Feb 8 '18 at 10:23
Make sure that the firewall or any other device involved in between the database server and client is not killing your connection.
– JSapkota
Feb 8 '18 at 10:23
There is no firewall between the two servers in question.
– Andrew Hangsleben
Feb 8 '18 at 21:02
There is no firewall between the two servers in question.
– Andrew Hangsleben
Feb 8 '18 at 21:02
add a comment |
1 Answer
1
active
oldest
votes
You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
– Andrew Hangsleben
Feb 8 '18 at 21:04
There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
– Gandolf989
Feb 9 '18 at 13:33
add a comment |
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f197337%2fhow-do-i-run-a-database-heavy-query-over-jdbc-without-timing-out-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
– Andrew Hangsleben
Feb 8 '18 at 21:04
There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
– Gandolf989
Feb 9 '18 at 13:33
add a comment |
You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
– Andrew Hangsleben
Feb 8 '18 at 21:04
There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
– Gandolf989
Feb 9 '18 at 13:33
add a comment |
You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
You can use PL/SQL and schedule jobs to populate tables or schedule jobs to update materialized views. Then once the data has been updated you can query as much as you want, since the heavy lifting will be done. You should also try tuning the queries. If you have an index with the filter columns and any other columns that you need, then you can avoid reading the index then the table. If you start indexes with the low cardinality columns going towards high cardinality columns, you can reduce the amount of rows looked at. Look at materialized views and including rowid so that you can try to do a fast refresh.
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
answered Feb 8 '18 at 16:30
Gandolf989Gandolf989
1,236512
1,236512
I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
– Andrew Hangsleben
Feb 8 '18 at 21:04
There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
– Gandolf989
Feb 9 '18 at 13:33
add a comment |
I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
– Andrew Hangsleben
Feb 8 '18 at 21:04
There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
– Gandolf989
Feb 9 '18 at 13:33
I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
– Andrew Hangsleben
Feb 8 '18 at 21:04
I realize I was missing a bit of information about the problem. Our environment needs to allow for any query to be created and run. Using stored procedures for this would be unduly cumbersome and require extra space for storing the output before pushing to the destination server. I've updated the question to reflect this.
– Andrew Hangsleben
Feb 8 '18 at 21:04
There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
– Gandolf989
Feb 9 '18 at 13:33
There are two aspects to what you want. How do you keep JDBC from timing out when waiting for a long query and how to tune the queries that take the most resources/time. If you post specific queries and explain plans, than people can help you to tune those queries. If you post your JDBC configuration, than people can help you to tune that. Even though individual queries may vary, you can create materialized views that are nearly current and take out much of the work for getting the data. You just need to think about how to do that and make them generic enough for all of your queries.
– Gandolf989
Feb 9 '18 at 13:33
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f197337%2fhow-do-i-run-a-database-heavy-query-over-jdbc-without-timing-out-oracle%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
Make sure that the firewall or any other device involved in between the database server and client is not killing your connection.
– JSapkota
Feb 8 '18 at 10:23
There is no firewall between the two servers in question.
– Andrew Hangsleben
Feb 8 '18 at 21:02