Oracle data transfer rate determined by rows instead of data size
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?
Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL Developer. I ran some tests and found that as the row number increases, the time to transfer increases in a linear fashion (100K rows in 90 seconds, 1M rows in 903 seconds). I then ran another test where the row count remained the same (500K) but the amount of data in each row increased with each trial. For this test, the time was almost flat (~480s), even though the amount of data increased from 5MB to 45MB over 10 trials.
The issue does not seem to be a throughput (VPN tunnel) problem since data speed increased with data/row.
So, my question is, what in Oracle would be causing this consistent 1000 rows/sec value, regardless of the amount of data in each row?
We don't manage the Oracle db, but I'm hoping to go back to the dbas with some ideas to speed up our month end loads.
Test 1: Increase Row Count, Maintain Data/Row
Test 2: Maintain Row Count, Increase Data Per Row
oracle ssis oracle-11g
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
add a comment |
Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?
Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL Developer. I ran some tests and found that as the row number increases, the time to transfer increases in a linear fashion (100K rows in 90 seconds, 1M rows in 903 seconds). I then ran another test where the row count remained the same (500K) but the amount of data in each row increased with each trial. For this test, the time was almost flat (~480s), even though the amount of data increased from 5MB to 45MB over 10 trials.
The issue does not seem to be a throughput (VPN tunnel) problem since data speed increased with data/row.
So, my question is, what in Oracle would be causing this consistent 1000 rows/sec value, regardless of the amount of data in each row?
We don't manage the Oracle db, but I'm hoping to go back to the dbas with some ideas to speed up our month end loads.
Test 1: Increase Row Count, Maintain Data/Row
Test 2: Maintain Row Count, Increase Data Per Row
oracle ssis oracle-11g
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
More likely limited by the fetch size, which is a client side setting. For example: xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance
– Balazs Papp
May 4 '18 at 16:49
You mention SSIS. Row-by-row, slow-by-slow. A couple of years ago I had a situation where a "developer" created a load job with SSIS. Took 4+ hours to load abt a million rows. Trace showed row-by-row fetch/transmit/insert - from MSSQL to Oracle. I spent 15 minutes writing a PL/SQL proc to INSERT INTO .. SELECT FROM .. and it runs the same million row load in less than 5 min.
– EdStevens
May 4 '18 at 18:02
How did you "increase row size" in your 2nd test? How did you run your tests?CREATE TABLEstatement for the target table might shine some light.
– Michael Kutz
May 4 '18 at 23:45
Balazs Papp, I took the suggestion from the link that you sent. It vastly improved run times in test, so I had the dba's update the registry setting for FetchSize and the runs on the weekend (on a slower dev box) were less than half of the times in prod. Thank you! If you put your suggestion in an answer, then I'll mark it as the answer.
– neuralgroove
May 7 '18 at 12:17
add a comment |
Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?
Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL Developer. I ran some tests and found that as the row number increases, the time to transfer increases in a linear fashion (100K rows in 90 seconds, 1M rows in 903 seconds). I then ran another test where the row count remained the same (500K) but the amount of data in each row increased with each trial. For this test, the time was almost flat (~480s), even though the amount of data increased from 5MB to 45MB over 10 trials.
The issue does not seem to be a throughput (VPN tunnel) problem since data speed increased with data/row.
So, my question is, what in Oracle would be causing this consistent 1000 rows/sec value, regardless of the amount of data in each row?
We don't manage the Oracle db, but I'm hoping to go back to the dbas with some ideas to speed up our month end loads.
Test 1: Increase Row Count, Maintain Data/Row
Test 2: Maintain Row Count, Increase Data Per Row
oracle ssis oracle-11g
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?
Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL Developer. I ran some tests and found that as the row number increases, the time to transfer increases in a linear fashion (100K rows in 90 seconds, 1M rows in 903 seconds). I then ran another test where the row count remained the same (500K) but the amount of data in each row increased with each trial. For this test, the time was almost flat (~480s), even though the amount of data increased from 5MB to 45MB over 10 trials.
The issue does not seem to be a throughput (VPN tunnel) problem since data speed increased with data/row.
So, my question is, what in Oracle would be causing this consistent 1000 rows/sec value, regardless of the amount of data in each row?
We don't manage the Oracle db, but I'm hoping to go back to the dbas with some ideas to speed up our month end loads.
Test 1: Increase Row Count, Maintain Data/Row
Test 2: Maintain Row Count, Increase Data Per Row
oracle ssis oracle-11g
oracle ssis oracle-11g
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
asked May 4 '18 at 16:35
neuralgrooveneuralgroove
1034
1034
More likely limited by the fetch size, which is a client side setting. For example: xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance
– Balazs Papp
May 4 '18 at 16:49
You mention SSIS. Row-by-row, slow-by-slow. A couple of years ago I had a situation where a "developer" created a load job with SSIS. Took 4+ hours to load abt a million rows. Trace showed row-by-row fetch/transmit/insert - from MSSQL to Oracle. I spent 15 minutes writing a PL/SQL proc to INSERT INTO .. SELECT FROM .. and it runs the same million row load in less than 5 min.
– EdStevens
May 4 '18 at 18:02
How did you "increase row size" in your 2nd test? How did you run your tests?CREATE TABLEstatement for the target table might shine some light.
– Michael Kutz
May 4 '18 at 23:45
Balazs Papp, I took the suggestion from the link that you sent. It vastly improved run times in test, so I had the dba's update the registry setting for FetchSize and the runs on the weekend (on a slower dev box) were less than half of the times in prod. Thank you! If you put your suggestion in an answer, then I'll mark it as the answer.
– neuralgroove
May 7 '18 at 12:17
add a comment |
More likely limited by the fetch size, which is a client side setting. For example: xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance
– Balazs Papp
May 4 '18 at 16:49
You mention SSIS. Row-by-row, slow-by-slow. A couple of years ago I had a situation where a "developer" created a load job with SSIS. Took 4+ hours to load abt a million rows. Trace showed row-by-row fetch/transmit/insert - from MSSQL to Oracle. I spent 15 minutes writing a PL/SQL proc to INSERT INTO .. SELECT FROM .. and it runs the same million row load in less than 5 min.
– EdStevens
May 4 '18 at 18:02
How did you "increase row size" in your 2nd test? How did you run your tests?CREATE TABLEstatement for the target table might shine some light.
– Michael Kutz
May 4 '18 at 23:45
Balazs Papp, I took the suggestion from the link that you sent. It vastly improved run times in test, so I had the dba's update the registry setting for FetchSize and the runs on the weekend (on a slower dev box) were less than half of the times in prod. Thank you! If you put your suggestion in an answer, then I'll mark it as the answer.
– neuralgroove
May 7 '18 at 12:17
More likely limited by the fetch size, which is a client side setting. For example: xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance
– Balazs Papp
May 4 '18 at 16:49
More likely limited by the fetch size, which is a client side setting. For example: xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance
– Balazs Papp
May 4 '18 at 16:49
You mention SSIS. Row-by-row, slow-by-slow. A couple of years ago I had a situation where a "developer" created a load job with SSIS. Took 4+ hours to load abt a million rows. Trace showed row-by-row fetch/transmit/insert - from MSSQL to Oracle. I spent 15 minutes writing a PL/SQL proc to INSERT INTO .. SELECT FROM .. and it runs the same million row load in less than 5 min.
– EdStevens
May 4 '18 at 18:02
You mention SSIS. Row-by-row, slow-by-slow. A couple of years ago I had a situation where a "developer" created a load job with SSIS. Took 4+ hours to load abt a million rows. Trace showed row-by-row fetch/transmit/insert - from MSSQL to Oracle. I spent 15 minutes writing a PL/SQL proc to INSERT INTO .. SELECT FROM .. and it runs the same million row load in less than 5 min.
– EdStevens
May 4 '18 at 18:02
How did you "increase row size" in your 2nd test? How did you run your tests?
CREATE TABLE statement for the target table might shine some light.– Michael Kutz
May 4 '18 at 23:45
How did you "increase row size" in your 2nd test? How did you run your tests?
CREATE TABLE statement for the target table might shine some light.– Michael Kutz
May 4 '18 at 23:45
Balazs Papp, I took the suggestion from the link that you sent. It vastly improved run times in test, so I had the dba's update the registry setting for FetchSize and the runs on the weekend (on a slower dev box) were less than half of the times in prod. Thank you! If you put your suggestion in an answer, then I'll mark it as the answer.
– neuralgroove
May 7 '18 at 12:17
Balazs Papp, I took the suggestion from the link that you sent. It vastly improved run times in test, so I had the dba's update the registry setting for FetchSize and the runs on the weekend (on a slower dev box) were less than half of the times in prod. Thank you! If you put your suggestion in an answer, then I'll mark it as the answer.
– neuralgroove
May 7 '18 at 12:17
add a comment |
2 Answers
2
active
oldest
votes
More likely limited by the fetch size, which is a client side setting.
For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/
Quoting some content of the blog post:
Batch size controls how many rows SSIS retrieves with each “batch”.
After retrieving all the rows in a batch, SSIS signals to Oracle that
it is ready for another batch. Of course, network latency is
introduced during this signaling process. Thus, decreasing the number
of total batches also decreases the number of round-trips SSIS has to
make. The default batch size is only 100; increasing this to a
reasonable number should boost performance.
And:
https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/
If you are experiencing the same or similar issue, then you may wish
to experiment with different settings for the FetchSize parameter on
the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User
ID=User;FetchSize=100000;. The default setting is FetchSize=100. This
means that the Oracle OLE DB provider will request and fetch 100 rows
of data. SQL Server will then read 100 rows of data. Once all 100 rows
have been read, the Oracle provider will request another 100 rows of
data. This will continue untill all rows are read.
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
add a comment |
Use Attunity Connector for Oracle This will definitely boost performance.
https://docs.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-2017
If feasible, hit query on single partiton at a time which will give immense performance enhancement.
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
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%2f205862%2foracle-data-transfer-rate-determined-by-rows-instead-of-data-size%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
More likely limited by the fetch size, which is a client side setting.
For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/
Quoting some content of the blog post:
Batch size controls how many rows SSIS retrieves with each “batch”.
After retrieving all the rows in a batch, SSIS signals to Oracle that
it is ready for another batch. Of course, network latency is
introduced during this signaling process. Thus, decreasing the number
of total batches also decreases the number of round-trips SSIS has to
make. The default batch size is only 100; increasing this to a
reasonable number should boost performance.
And:
https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/
If you are experiencing the same or similar issue, then you may wish
to experiment with different settings for the FetchSize parameter on
the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User
ID=User;FetchSize=100000;. The default setting is FetchSize=100. This
means that the Oracle OLE DB provider will request and fetch 100 rows
of data. SQL Server will then read 100 rows of data. Once all 100 rows
have been read, the Oracle provider will request another 100 rows of
data. This will continue untill all rows are read.
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
add a comment |
More likely limited by the fetch size, which is a client side setting.
For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/
Quoting some content of the blog post:
Batch size controls how many rows SSIS retrieves with each “batch”.
After retrieving all the rows in a batch, SSIS signals to Oracle that
it is ready for another batch. Of course, network latency is
introduced during this signaling process. Thus, decreasing the number
of total batches also decreases the number of round-trips SSIS has to
make. The default batch size is only 100; increasing this to a
reasonable number should boost performance.
And:
https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/
If you are experiencing the same or similar issue, then you may wish
to experiment with different settings for the FetchSize parameter on
the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User
ID=User;FetchSize=100000;. The default setting is FetchSize=100. This
means that the Oracle OLE DB provider will request and fetch 100 rows
of data. SQL Server will then read 100 rows of data. Once all 100 rows
have been read, the Oracle provider will request another 100 rows of
data. This will continue untill all rows are read.
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
add a comment |
More likely limited by the fetch size, which is a client side setting.
For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/
Quoting some content of the blog post:
Batch size controls how many rows SSIS retrieves with each “batch”.
After retrieving all the rows in a batch, SSIS signals to Oracle that
it is ready for another batch. Of course, network latency is
introduced during this signaling process. Thus, decreasing the number
of total batches also decreases the number of round-trips SSIS has to
make. The default batch size is only 100; increasing this to a
reasonable number should boost performance.
And:
https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/
If you are experiencing the same or similar issue, then you may wish
to experiment with different settings for the FetchSize parameter on
the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User
ID=User;FetchSize=100000;. The default setting is FetchSize=100. This
means that the Oracle OLE DB provider will request and fetch 100 rows
of data. SQL Server will then read 100 rows of data. Once all 100 rows
have been read, the Oracle provider will request another 100 rows of
data. This will continue untill all rows are read.
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
More likely limited by the fetch size, which is a client side setting.
For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/
Quoting some content of the blog post:
Batch size controls how many rows SSIS retrieves with each “batch”.
After retrieving all the rows in a batch, SSIS signals to Oracle that
it is ready for another batch. Of course, network latency is
introduced during this signaling process. Thus, decreasing the number
of total batches also decreases the number of round-trips SSIS has to
make. The default batch size is only 100; increasing this to a
reasonable number should boost performance.
And:
https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/
If you are experiencing the same or similar issue, then you may wish
to experiment with different settings for the FetchSize parameter on
the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User
ID=User;FetchSize=100000;. The default setting is FetchSize=100. This
means that the Oracle OLE DB provider will request and fetch 100 rows
of data. SQL Server will then read 100 rows of data. Once all 100 rows
have been read, the Oracle provider will request another 100 rows of
data. This will continue untill all rows are read.
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
answered May 7 '18 at 17:12
Balazs PappBalazs Papp
27.1k2931
27.1k2931
add a comment |
add a comment |
Use Attunity Connector for Oracle This will definitely boost performance.
https://docs.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-2017
If feasible, hit query on single partiton at a time which will give immense performance enhancement.
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Use Attunity Connector for Oracle This will definitely boost performance.
https://docs.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-2017
If feasible, hit query on single partiton at a time which will give immense performance enhancement.
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Use Attunity Connector for Oracle This will definitely boost performance.
https://docs.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-2017
If feasible, hit query on single partiton at a time which will give immense performance enhancement.
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Use Attunity Connector for Oracle This will definitely boost performance.
https://docs.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-2017
If feasible, hit query on single partiton at a time which will give immense performance enhancement.
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
answered 5 mins ago
Pankaj TodkarPankaj Todkar
1
1
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Pankaj Todkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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%2f205862%2foracle-data-transfer-rate-determined-by-rows-instead-of-data-size%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
More likely limited by the fetch size, which is a client side setting. For example: xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance
– Balazs Papp
May 4 '18 at 16:49
You mention SSIS. Row-by-row, slow-by-slow. A couple of years ago I had a situation where a "developer" created a load job with SSIS. Took 4+ hours to load abt a million rows. Trace showed row-by-row fetch/transmit/insert - from MSSQL to Oracle. I spent 15 minutes writing a PL/SQL proc to INSERT INTO .. SELECT FROM .. and it runs the same million row load in less than 5 min.
– EdStevens
May 4 '18 at 18:02
How did you "increase row size" in your 2nd test? How did you run your tests?
CREATE TABLEstatement for the target table might shine some light.– Michael Kutz
May 4 '18 at 23:45
Balazs Papp, I took the suggestion from the link that you sent. It vastly improved run times in test, so I had the dba's update the registry setting for FetchSize and the runs on the weekend (on a slower dev box) were less than half of the times in prod. Thank you! If you put your suggestion in an answer, then I'll mark it as the answer.
– neuralgroove
May 7 '18 at 12:17