Thanks for all the suggestions. I should have subscribed to the mailing list before I post the question since I might have missed some suggestions if you didn't reply to me directly. That's fixed now. The performance problem is actually worse with the real app which I have ported from Linux mysql to Solaris oracle. The worst of the statements is executing a commit after three or so insert/update statement so I've created a couple of insert only test scripts for comparison(That's when the five times slowdown was noticed). They both do the same thing but one's an anonymous block in pl/sql and the other's using perl DBI/DBD::oracle hence through OCI. Using sqlloader is not feasible here since I'm just trying to isolate the performance problem with the real app and not really trying to do bulk load alone. At this point I'm more interested to see if anyone have used dbd/dbi with oracle 9i and do they have any performance problem similar to what I'm seeing. And if available what's the solution.
I have attached the two scripts if you are interested and thanks again for all your help. excuse the ignorance Ron but what is a 10046 trace?? Thanks again, Tim > -----Original Message----- > From: Reidy, Ron [mailto:[EMAIL PROTECTED] > Sent: Friday, April 16, 2004 7:47 AM > To: Scott T. Hildreth > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: performance problem with dbd oracle > > > And you can perform direct inserts. > > ----------------- > Ron Reidy > Senior DBA > Array BioPharma, Inc. > > > -----Original Message----- > From: Scott T. Hildreth [mailto:[EMAIL PROTECTED] > Sent: Friday, April 16, 2004 8:37 AM > To: Reidy, Ron > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: performance problem with dbd oracle > > > On Fri, 2004-04-16 at 09:24, Reidy, Ron wrote: > > Tim, > > > > What performance problems are you having exactly? Did you run > a 10046 trace? Are you passing the {ora_check_sql => 0} in your > prepare() call? > > > > Also, I would suggest you look into SQL*Loader. Nothing you > ever write in Perl will give you the insert speed you will get > with this utility. > > ...when array processing is available, it will be pretty close in > speed. > > > > ----------------- > > Ron Reidy > > Senior DBA > > Array BioPharma, Inc. > > > > > > -----Original Message----- > > From: Tim To [mailto:[EMAIL PROTECTED] > > Sent: Thursday, April 15, 2004 11:53 AM > > 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. > > > > 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. >
insert_test_post.pl
Description: Binary data
insert_test_post.sql
Description: Binary data
