In looking at your posted code: 1. Set AutoCommit => 0. You are committing after each insert (or update, or delete) statement. Commits are very expensive. If you would trace your session (using event 10046), you would see exactly why it is so expensive.
Other comments below: -- Ron Reidy Sr. DBA Array BioPharma, Inc. -----Original Message----- From: Tim To [mailto:[EMAIL PROTECTED] Sent: Fri 4/16/2004 4:27 PM To: Lincoln A. Baxter; Janet Goldstein Cc: Dbi-Users Subject: RE: performance problem with dbd oracle Thanks for the suggestion. I do realize that bind varibles are important to performance and the hashing algorithm that Oracle uses to identify sql statement in the library cache. Unfortunately a portion of the sql statements are generated by a third party perl app and if we try to take over we have other problems :( Also I thought bind variables shouldn't be that important when it comes to insert statement since there's no execution plan invovled?? I could be wrong here). You are wrong here. Bind variables a very inportant. Anyways, the comparison plsql code is also not using any bind variables so if the bottleneck is bind variable related (i.e. within Oracle) it should affect the pl/sql code the same way - shouldn't it?? Tim > -----Original Message----- > From: Lincoln A. Baxter [mailto:[EMAIL PROTECTED] > Sent: Friday, April 16, 2004 3:01 PM > To: Janet Goldstein > Cc: Dbi-Users > Subject: RE: performance problem with dbd oracle > > > On Fri, 2004-04-16 at 09:10, Jones Robert Civ TTMS Keesler wrote: > > Code? Also, using sqlloader, the native program for > inserting data, > > has been cited as being faster than using DBI for straight insertions. > > If you are not using bind variables (placeholders for your data), then > it will be quite slow, since every statement would then have to be > parsed by oracle. > > Thats why you were asked for your code. > > Lincoln > > > > > -----Original Message----- > > From: Tim To [mailto:[EMAIL PROTECTED] > > Sent: Thursday, April 15, 2004 12:53 PM > > To: [EMAIL PROTECTED] > > Subject: performance problem with dbd oracle > > > > > > Hi, > > How can I improve performance when using oracle dbd? We have an urgent > > performance problem where inserting data into an Oracle 9i > database using > > DBI/DBD oracle is at least five times slower than stright sql > using sqlplus. > > Any suggestion is appreciated. > > > > DBD::oracle 1.15 > > DBI 1.4 > > PERL 5.8.0 > > running on solaris 9 > > Oracle 9.2.0.1 > > > > Thanks, > > Tim To > > > This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
