How do I run a database heavy query over jdbc without timing out (Oracle)? The Next CEO of...
Mathematica command that allows it to read my intentions
Is a distribution that is normal, but highly skewed, considered Gaussian?
Traveling with my 5 year old daughter (as the father) without the mother from Germany to Mexico
What happens if you break a law in another country outside of that country?
What day is it again?
How can I replace x-axis labels with pre-determined symbols?
Can you teleport closer to a creature you are Frightened of?
Can Sri Krishna be called 'a person'?
Does int main() need a declaration on C++?
Find the majority element, which appears more than half the time
Small nick on power cord from an electric alarm clock, and copper wiring exposed but intact
How exploitable/balanced is this homebrew spell: Spell Permanency?
Raspberry pi 3 B with Ubuntu 18.04 server arm64: what pi version
Could you use a laser beam as a modulated carrier wave for radio signal?
Compensation for working overtime on Saturdays
How to implement Comparable so it is consistent with identity-equality
Does Germany produce more waste than the US?
How to coordinate airplane tickets?
Is it reasonable to ask other researchers to send me their previous grant applications?
Is it a bad idea to plug the other end of ESD strap to wall ground?
Calculate the Mean mean of two numbers
Why does freezing point matter when picking cooler ice packs?
Finitely generated matrix groups whose eigenvalues are all algebraic
Strange use of "whether ... than ..." in official text
How do I run a database heavy query over jdbc without timing out (Oracle)?
The Next CEO of Stack OverflowDisable explicit commits in JDBC, detect them in SQL, or put the database in a readonly statesql*plus 11.2 client connection to oracle timing out on LinuxQueries hang when accessing Oracle via ODBC and PythonHow to separate out individual sql statements from Oracle SQL script for execution from java codeRun stored procedures from specific package/with specific tag at lower priorityHow do I connect to oracle apex server through JDBC ?JDBC pooling Oracle driver Socket read timed outsql taking < 1 sec to run in sql developer, 37 on the application sideHow should I store a large number of dynamically generated heterogeneous tables?Oracle APEX 5.1 connect to remote db using JDBC and query data
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♦ 1 min 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♦ 1 min 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♦ 1 min 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♦ 1 min 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