A Definitive Answer? Perl and Cursors

joec_ has asked for the wisdom of the Perl Monks concerning the following question:

I have a PLSQL procedure (into a 10G oracle database) which takes 8-9 seconds on both my development and production linux boxes. They are both using perl 5.8.7. Development has DBI 1.607 and DBD::Oracle 1.17 / production has DBI 1.40 and DBD::Oracle 1.19. They have a 10.2.0 oracle client installed. The procedure should return 218 rows which isnt a lot.

However, on my Windows machine (with Active Perl 5.10, DBI 1.604, DBD::Oracle 1.21), the procedure returns its results in under a second!

Is there some problem with cursor handling in Linux?

Im at the end of my tether with this...

Anyone have a definitive answer / suggestions?

Thanks

Joe

-----

Eschew obfuscation, espouse elucidation!

Comment on A Definitive Answer? Perl and Cursors

Replies are listed 'Best First'.
Re: A Definitive Answer? Perl and Cursors by perrin (Chancellor) on Mar 23, 2009 at 11:34 UTC
No, there isn't a problem with cursors. I recommend you ask on the dbi-users mailing list where you should be bale to find people who have done this before.	[reply]
Re^2: A Definitive Answer? Perl and Cursors by joec_ (Scribe) on Mar 23, 2009 at 12:03 UTC
Hi, how do i post on dbi users? All i can seem to do is search the lists... thanks. ----- Eschew obfuscation, espouse elucidation!	[reply]
Re^3: A Definitive Answer? Perl and Cursors by mje (Curate) on Mar 23, 2009 at 14:44 UTC
See http://lists.cpan.org/showlist.cgi?name=dbi-users	[reply]
Re^3: A Definitive Answer? Perl and Cursors by perrin (Chancellor) on Mar 23, 2009 at 14:04 UTC
You probably have to join the list before you can post.	[reply]
Re: A Definitive Answer? Perl and Cursors by Bloodnok (Vicar) on Mar 23, 2009 at 11:41 UTC
From your all too brief overview, it strikes me that there are more variables at play i.e. other than cursor handling under Linux, here... OS perl version CPAN module versions Have you tried one of the Devel modules e.g. Devel::Prof in an attempt to identify/discover bottlenecks etc. ? A user level that continues to overstate my experience :-))	[reply]
Re: A Definitive Answer? Perl and Cursors by derby (Abbot) on Mar 23, 2009 at 11:46 UTC
The cursor library is the last place I would expect a bottleneck. Since you say it's quick on your newer stack, I would first suspect a network issue ... either something solvable like a DNS resolution timeout or something semi-insolvable, like the client forcing everything through a slow VPN. Some timing numbers -- either via Benchmark or DBI_TRACE should help you pinpoint the issues. -derby	[reply]
Re: A Definitive Answer? Perl and Cursors by locked_user sundialsvc4 (Abbot) on Mar 23, 2009 at 13:46 UTC
Since the work is being done by a PL/SQL stored procedure, which (of course) returns its results instantly on the Windows box, you know that the bottleneck cannot be either the PL/SQL nor the size of the database. Since the delays you are experiencing are “eight or nine seconds,” the cause simply has to be physical in nature ... that is to say, networking, or quite possibly a timeout. As others have said, you need to trace it, look at server-logs and so forth. There isn't a “programming error” in this world that's going to cause this kind of a delay. So, don't stare at your code, or at Perl. The problem isn't there.
Re: A Definitive Answer? Perl and Cursors by clueless newbie (Curate) on Mar 23, 2009 at 14:06 UTC
Mastering Perl mentions that DBI makes use of an environmental variable DBI_PROFILE. Perhaps if you use it on both boxes you can determine if DBI is the problem that you suspect it is.	[reply]