|
|
 |
 |
 |
 |
Need help : degrading performance in record updates
We are facing some problem in our batch application. This java based application is reading a file with 25 lakh records and updating in Oracle DB. The batch is commited after each 300 records. We are taking a time stamp after each 10000 records. What we have observed is, initial it was taking some 3-4 seconds to
update 10000 records.
The time required is increasing progressively.
After 12 Lakh records, now it is taking almost 10+ mins to update
10000 records.
I have failed to pin down a problem, for such continuously degrading
performance.
This is important for us because, we have such 16 files to upload. And
this upload is just data preparation for the analysis that we need to
perform on one bug that is observed on production.
I think, Re-do logsize of Oracle should not be a problem since we are
committing every 300 records.
On May 11, 8:39 am, Akshay @gmail.com wrote:
> We are facing some problem in our batch application. > This java based application is reading a file with 25 lakh records and > updating in Oracle DB. > The batch is commited after each 300 records. > We are taking a time stamp after each 10000 records. > What we have observed is, initial it was taking some 3-4 seconds to
> update 10000 records.
> The time required is increasing progressively.
> After 12 Lakh records, now it is taking almost 10+ mins to update
> 10000 records.
> I have failed to pin down a problem, for such continuously degrading
> performance.
> This is important for us because, we have such 16 files to upload. And
> this upload is just data preparation for the analysis that we need to
> perform on one bug that is observed on production.
> I think, Re-do logsize of Oracle should not be a problem since we are
> committing every 300 records.
Any thoughts, as to why this problem of degrading performance could be? And solutions if any? Thanks in advance :-)
-----------------------------------------------Reply-----------------------------------------------
On 11.05.2007 05:41, Akshay @gmail.com wrote:
> On May 11, 8:39 am, Akshay @gmail.com wrote: >> We are facing some problem in our batch application. >> This java based application is reading a file with 25 lakh records and >> updating in Oracle DB. >> The batch is commited after each 300 records. >> We are taking a time stamp after each 10000 records. >> What we have observed is, initial it was taking some 3-4 seconds to
>> update 10000 records.
>> The time required is increasing progressively.
>> After 12 Lakh records, now it is taking almost 10+ mins to update
>> 10000 records.
>> I have failed to pin down a problem, for such continuously degrading
>> performance.
>> This is important for us because, we have such 16 files to upload. And
>> this upload is just data preparation for the analysis that we need to
>> perform on one bug that is observed on production.
>> I think, Re-do logsize of Oracle should not be a problem since we are
>> committing every 300 records.
> Any thoughts, as to why this problem of degrading performance could
> be? And solutions if any?
If you provide Oracle version and DDL - maybe. robert
-----------------------------------------------Reply-----------------------------------------------
Akshay @gmail.com schreef: > We are facing some problem in our batch application.
> This java based application is reading a file with 25 lakh records and
> updating in Oracle DB.
Error 1: not using the tools provided - use SQL*Loader! What a lakh record anyway? > The batch is commited after each 300 records.
Error 2: committing is slow! > We are taking a time stamp after each 10000 records.
So what? > What we have observed is, initial it was taking some 3-4 seconds to
> update 10000 records.
Say what? What platform do you run on - a pentium I class machine with 640kB memory? > The time required is increasing progressively. > After 12 Lakh records, now it is taking almost 10+ mins to update > 10000 records. > I have failed to pin down a problem, for such continuously degrading
> performance.
You have not done anything but trial and error. And post this. > This is important for us because, we have such 16 files to upload. And > this upload is just data preparation for the analysis that we need to > perform on one bug that is observed on production. > I think, Re-do logsize of Oracle should not be a problem since we are
> committing every 300 records.
-- Regards, Frank van Bortel Top-posting is one way to shut me up...
-----------------------------------------------Reply-----------------------------------------------
On May 10, 8:39 pm, Akshay @gmail.com wrote:
> We are facing some problem in our batch application. > This java based application is reading a file with 25 lakh records and > updating in Oracle DB. > The batch is commited after each 300 records. > We are taking a time stamp after each 10000 records. > What we have observed is, initial it was taking some 3-4 seconds to
> update 10000 records.
> The time required is increasing progressively.
> After 12 Lakh records, now it is taking almost 10+ mins to update
> 10000 records.
> I have failed to pin down a problem, for such continuously degrading
> performance.
> This is important for us because, we have such 16 files to upload. And
> this upload is just data preparation for the analysis that we need to
> perform on one bug that is observed on production.
> I think, Re-do logsize of Oracle should not be a problem since we are
> committing every 300 records.
Are you committing in a loop? jg
--
@home.com is bogus.
http://www.readingonline.org/articles/art_index.asp?HREF=/articles/r_...
-----------------------------------------------Reply-----------------------------------------------
On May 10, 11:39 pm, Akshay @gmail.com wrote:
> We are facing some problem in our batch application. > This java based application is reading a file with 25 lakh records and > updating in Oracle DB. > The batch is commited after each 300 records. > We are taking a time stamp after each 10000 records. > What we have observed is, initial it was taking some 3-4 seconds to
> update 10000 records.
> The time required is increasing progressively.
> After 12 Lakh records, now it is taking almost 10+ mins to update
> 10000 records.
> I have failed to pin down a problem, for such continuously degrading
> performance.
> This is important for us because, we have such 16 files to upload. And
> this upload is just data preparation for the analysis that we need to
> perform on one bug that is observed on production.
> I think, Re-do logsize of Oracle should not be a problem since we are
> committing every 300 records.
10046 trace and profile it. You won't need to guess. Borrow or by this book: http://www.bookpool.com/sm/059600527X. It tells you how to not guess. Use bulk processing where possible.... -----------------------------------------------Reply-----------------------------------------------
> What a lakh record anyway?
http://en.wikipedia.org/wiki/Lakh -----------------------------------------------Reply-----------------------------------------------
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 EscVector schreef:
Yeah - I'm aware of the fact I can look it up, it just pisses me off I have to. This is an international forum, with English as the language to communicate. In spite the fact, English is not the mother tongue of many visitors -including me- it would be proof of dignity and respect to try an abide by rules of the language. Lakh is *not* an english word. - -- Regards, Frank van Bortel Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFGRZHcLw8L4IAs830RAllkAJ9Dto/WgyEJUBne1BggJWaALh8TqwCfSGfm
zcnqmXEWQYEkzwZDc0zqFNQ=
=6eye
-----END PGP SIGNATURE-----
-----------------------------------------------Reply-----------------------------------------------
On May 12, 6:07 am, Frank van Bortel <frank.van.bor @gmail.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > EscVector schreef:
> >> What a lakh record anyway?
> >http://en.wikipedia.org/wiki/Lakh
> Yeah - I'm aware of the fact I can look it up, it just pisses
> me off I have to.
> This is an international forum, with English as the language
> to communicate.
> In spite the fact, English is not the mother tongue of many
> visitors -including me- it would be proof of dignity and respect
> to try an abide by rules of the language.
> Lakh is *not* an english word.
> - --
> Regards,
> Frank van Bortel
> Top-posting is one way to shut me up...
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
> iD8DBQFGRZHcLw8L4IAs830RAllkAJ9Dto/WgyEJUBne1BggJWaALh8TqwCfSGfm
> zcnqmXEWQYEkzwZDc0zqFNQ=
> =6eye
> -----END PGP SIGNATURE-----
Well, you did ask... :) Sorry! My question would be do they mean 100k*25.
-----------------------------------------------Reply-----------------------------------------------
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 EscVector schreef:
> Well, you did ask... :) Sorry!
Just being grumpy - sorry. > My question would be do they mean 100k*25.
That would be my understanding, now that I know they meant 2,500,000 records. Which is nothing, really, and loads on my 5 year old PC in about 5 minutes. - --
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFGRwAiLw8L4IAs830RAv/SAJ9HxBh4C3rG6CgMrfsozxtwCY2ERACdGedl
bruZtHlDhNatQAF6TH8/LiI=
=PRGb
-----END PGP SIGNATURE-----
-----------------------------------------------Reply-----------------------------------------------
On May 13, 5:10 pm, Frank van Bortel <frank.van.bor @gmail.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > EscVector schreef:
> > Well, you did ask... :) Sorry!
> Just being grumpy - sorry.
> > My question would be do they mean 100k*25.
> That would be my understanding, now that I know they meant
> 2,500,000 records. Which is nothing, really, and loads
> on my 5 year old PC in about 5 minutes.
> - --
> Regards,
> Frank van Bortel
> Top-posting is one way to shut me up...
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
> iD8DBQFGRwAiLw8L4IAs830RAv/SAJ9HxBh4C3rG6CgMrfsozxtwCY2ERACdGedl
> bruZtHlDhNatQAF6TH8/LiI=
> =PRGb
> -----END PGP SIGNATURE-----
Hello All, first of all my apologies for the "Lakh" thing.
I need to use the batch program as I need to process the text file
data to upload in the db.
Frank, I am not a oracle person but a Java programmer.
On the program side I have tried a couple of things to my
understanding. Rechecked for any memory leaks possible, thoroughly.
Tightened the code related to file reading.
As for "Error02 : Commit slow"... I did not get your point.... do you
say that, commits should be more frequent?
jg, yes, I am committing in a loop.
yes, 100k*25 is the records per file.
I connecting through a Dual Core 2.99GHz 1GB machine to another Dual
Core 2.99GHz 1GB machine, both running WinXP Professional. And
Oracle9i is the database. I just dont understand what could be the
bottleneck :-(.
Thank you in advance for any help/directions.
:-)
-----------------------------------------------Reply-----------------------------------------------
On May 11, 4:39 am, Akshay @gmail.com wrote: > I think, Re-do logsize of Oracle should not be a problem since we are
> committing every 300 records.
That will create more redo, not less. However, it is probably not directly relevant to your problem. -----------------------------------------------Reply-----------------------------------------------
On May 14, 1:49 pm, Akshay @gmail.com wrote:
> On May 13, 5:10 pm, Frank van Bortel <frank.van.bor @gmail.com> > wrote: > > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> > EscVector schreef:
> > > Well, you did ask... :) Sorry!
> > Just being grumpy - sorry.
> > > My question would be do they mean 100k*25.
> > That would be my understanding, now that I know they meant
> > 2,500,000 records. Which is nothing, really, and loads
> > on my 5 year old PC in about 5 minutes.
> > - --
> > Regards,
> > Frank van Bortel
> > Top-posting is one way to shut me up...
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.4.1 (MingW32)
> > iD8DBQFGRwAiLw8L4IAs830RAv/SAJ9HxBh4C3rG6CgMrfsozxtwCY2ERACdGedl
> > bruZtHlDhNatQAF6TH8/LiI=
> > =PRGb
> > -----END PGP SIGNATURE-----
> Hello All,
> first of all my apologies for the "Lakh" thing.
> I need to use the batch program as I need to process the text file
> data to upload in the db.
> Frank, I am not a oracle person but a Java programmer.
> On the program side I have tried a couple of things to my
> understanding. Rechecked for any memory leaks possible, thoroughly.
> Tightened the code related to file reading.
> As for "Error02 : Commit slow"... I did not get your point.... do you
> say that, commits should be more frequent?
> jg, yes, I am committing in a loop.
> yes, 100k*25 is the records per file.
> I connecting through a Dual Core 2.99GHz 1GB machine to another Dual
> Core 2.99GHz 1GB machine, both running WinXP Professional. And
> Oracle9i is the database. I just dont understand what could be the
> bottleneck :-(.
> Thank you in advance for any help/directions.
> :-)
Some comments/suggestions/ideas: 1. Use sqlload if you can. When it comes to bulk loads, this should be
your first option. Even if it's not a very structured input file, you
can, in most cases, still do some logical record conversions. Of
course, you'll need to check the doc or ask a colleague who knows the
tool.
2. If you really can't use sqlload (why not?) and need to stick to the
java program, then make sure you're using bind variables. I hope
you're not dynamically building your insert statement (including the
actual values) for each and every record. This is a sure way to kill
your performance.
3. If #2 above is not the cause, then do send us more info. statspack,
tkprof/trace, the works.
HTH.
Romeo
-----------------------------------------------Reply-----------------------------------------------
On 14 mei, 07:49, Akshay @gmail.com wrote:
> On May 13, 5:10 pm, Frank van Bortel <frank.van.bor @gmail.com> > wrote: > > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> > EscVector schreef:
> > > Well, you did ask... :) Sorry!
> > Just being grumpy - sorry.
> > > My question would be do they mean 100k*25.
> > That would be my understanding, now that I know they meant
> > 2,500,000 records. Which is nothing, really, and loads
> > on my 5 year old PC in about 5 minutes.
> > - --
> > Regards,
> > Frank van Bortel
> > Top-posting is one way to shut me up...
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.4.1 (MingW32)
> > iD8DBQFGRwAiLw8L4IAs830RAv/SAJ9HxBh4C3rG6CgMrfsozxtwCY2ERACdGedl
> > bruZtHlDhNatQAF6TH8/LiI=
> > =PRGb
> > -----END PGP SIGNATURE-----
> Hello All,
> first of all my apologies for the "Lakh" thing.
> I need to use the batch program as I need to process the text file
> data to upload in the db.
> Frank, I am not a oracle person but a Java programmer.
> On the program side I have tried a couple of things to my
> understanding. Rechecked for any memory leaks possible, thoroughly.
> Tightened the code related to file reading.
> As for "Error02 : Commit slow"... I did not get your point.... do you
> say that, commits should be more frequent?
No - LESS frequent, as commits are slow. > jg, yes, I am committing in a loop.
Standard receipe to get ORA-01555: Snapshot too old. > yes, 100k*25 is the records per file.
> I connecting through a Dual Core 2.99GHz 1GB machine to another Dual
> Core 2.99GHz 1GB machine, both running WinXP Professional. And
> Oracle9i is the database. I just dont understand what could be the
> bottleneck :-(.
> Thank you in advance for any help/directions.
> :-)
As commecnted by several others, too: why not use the tool provided by Oracle for this type of actions: SQL*Loader?!? Else, show the code.
-----------------------------------------------Reply----------------------------------------------- |
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc