Perl-to-Oracle performance: DBI-pack visa 'sqlplus' usage Post: 302371877

Sponsored Content

Top Forums UNIX for Advanced & Expert Users Perl-to-Oracle performance: DBI-pack visa 'sqlplus' usage Post 302371877 by alex_5161 on Monday 16th of November 2009 01:07:00 PM

11-16-2009

Registered User

Very nice!
I appreciate your answers, guys, but aren't you avoiding to answer instead?!
'Loader'?!
'Shell'?!
I did not ask to compare any possibility to load (?! - why that, I did not ask about it) data to Oracle!!
Also I did not ask about different version and different configuration of Oracle used!!
(Let's avoid considering the diff-OS, Windows, Linux, .., different machines, dais of the week, weather, magnetic storms and alliances' from out of space, as well as mood of developer, sysadmin and DBA!)

I am talking about the 2 specified way of communicating (!!!) with Oracle from (!!!) Perl ! (Do not consider different version of Perl; the DBI pack is already available!)
That simply means: comparing the same amount of operations by processing it with the Perl package DBI and by processing the SAME set of operations by using the 'sqlplus' through Perl-pipe as I have shown!!

The Oracle session is going to be opened in any way! Everything processed in Oracle going to be processed the same way!
You would like to say it is most expensive part - fine !! - I have heard you!! But that part does not depend on the way of establishing it from a Perl code!

Would you like to say that the pipe (open pp,'-|',..) and 'executed' (.. = qx |..|) done by a new shell session? If so, I could see your point. I am not sure how Perl performs such task. I would guess it is done by system calls inside of own session.

I see that the 'pipe' and 'executed should not be compared, as the 'pipe' could be left opened and used as needed, but the 'qx|...|' must be completed right away. So, it is not the stuff that could be used in all cases by the same way.

But the DBI and 'pipe' could be compared as both could stay opened as much as decided and be used for the same stuff.

The ACCTIONS to Oracle I am talking about is repeatable iteration to retrieve and update some record(s) under some list of specifications - more specific, having some input file. Every file record required some analyzing of the Oracle related (to the file record) data and, maybe, update or insert.
The amount of the file record pretty significant: hundreds thousands - couple millions records.

Therefore, besides the Oracle processing (sure it has to be most sufficient, but it is different story) the way to request and change data from/in Oracle still important.

Under the 'performance', honorable gurus, I mean the amount of time that the operations should take.

So, I guess now it is little bit more clear that all these questions aren't relevant:

Quote:

Could you, please, specify :
- What do you understand as "the performance"?
- What kind of data you would like to load?
- What Oracle version you are using?
- How many data you have? (ex. 20 000 files each 1kB or 1 file of size 1TB)
- How often the data are supposed to be loaded?
- Is the loader running on the same machine as the Oracle DB is?

alex_5161

View Public Profile for alex_5161

Find all posts by alex_5161

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

PERL DBI module install

We ran into an issue trying to install DBI and DB2 modules for perl for AIX from the link http://www-306.ibm.com/software/data/db2/perl/ We tried to install the DBI module using bash# perl -MCPAN -e 'install DBI' command. However we ended up with the following error. Stop. ...

2. Programming

perl dbi to oracle getting disconnect_all for oracle dbi help

hi i am trying to connect to an oracle database using dbi and i get this :: Driver has not implemented the disconnect_all method. at /opt/perl/lib/site_perl/5.8.0/sun4-solaris/DBI.pm line 575 END failed--call queue aborted. for all i know, the script was working earlier, but has...

3. Shell Programming and Scripting

Installing Perl DBI and DBD

Hi, i have some queries on installing the Perl DBI and the DBD Oracle. I know that i have to install the DBI first. I have the source files in a folder in my home directory.The commands to install arecd /home/DBI Perl Makefile.PL make make installI would like to know, after executing these...

4. Shell Programming and Scripting

connect to MySQL from Perl using DBI

Hi, I want to connect perl with the mysql to select and so on but the connection don't work code #!/usr/bin/perl BEGIN { # PERL MODULES WE WILL BE USING use DBI; $dbh = DBI->connect('DBI:mysql:C:\Program Files\MySQL\MySQL Server 5.0\data\db1','','pass') or die $DBI::errstr;} #...

5. UNIX for Advanced & Expert Users

Perl's DBI Module on OS X - uninstallable?

i've been struggling with installing the Perl DBI & DBD modules all weekend, and I'm getting close, but no cigar as of yet. When I run the perl script db.pl I get the following mismatch error: Mon Apr 19 09:43:29 EDT 2010 /Library/Perl/DBD-mysql-4.011 -> peterv@MBP17.local<515>$: db.pl | tee...

6. Shell Programming and Scripting

perl: help with DBI

Hi there, I have a bit of code similar to below (which ive actually got from perldoc, but mine is similar enough) $sth = $dbh->prepare(q{ SELECT region, sales FROM sales_by_region }); $sth->execute; my ($region, $sales); # Bind Perl variables to columns: $rv =...

7. Shell Programming and Scripting

Perl DBI error

Hi All, I installed DBI module in a non INC location and using it in my script via "use lib". But it throw the below error at the "use DBI" step. Please help Usage: DBI::_install_method(dbi_class, meth_name, file, attribs=Nullsv) at /xx/xxx/xxxxx/xxxxx/oracle/lib/DBI.pm/oracle/lib/DBI.pm line...

8. Shell Programming and Scripting

Perl Oracle DBI through Apache problem

Experts, I've been struggling with making a Perl Oracle DBI script to work through my Apache webserver. Mysql DBI scripts work fine, but I'm having issue's with Oracle. The oracle script works on command line, but I'm getting an "Internal Server Error" with apache Sourcing the oracle...

LEARN ABOUT DEBIAN

sql::reservedwords::oracle

SQL::ReservedWords::Oracle(3pm) 			User Contributed Perl Documentation			   SQL::ReservedWords::Oracle(3pm)

NAME

       SQL::ReservedWords::Oracle - Reserved SQL words by Oracle

SYNOPSIS

	  if ( SQL::ReservedWords::Oracle->is_reserved( $word ) ) {
	      print "$word is a reserved Oracle word!";
	  }

DESCRIPTION

       Determine if words are reserved by Oracle Database.

METHODS

       is_reserved( $word )
	   Returns a boolean indicating if $word is reserved by either Oracle7, Oracle8i, Oracle9i or Oracle10g.

       is_reserved_by_oracle7( $word )
	   Returns a boolean indicating if $word is reserved by Oracle7.

       is_reserved_by_oracle8( $word )
	   Returns a boolean indicating if $word is reserved by Oracle8i.

       is_reserved_by_oracle9( $word )
	   Returns a boolean indicating if $word is reserved by Oracle9i.

       is_reserved_by_oracle10( $word )
	   Returns a boolean indicating if $word is reserved by Oracle10g.

       reserved_by( $word )
	   Returns a list with Oracle versions that reserves $word.

       words
	   Returns a list with all reserved words.

EXPORTS

       Nothing by default. Following subroutines can be exported:

       is_reserved
       is_reserved_by_oracle7
       is_reserved_by_oracle8
       is_reserved_by_oracle9
       is_reserved_by_oracle10
       reserved_by
       words

SEE ALSO

       SQL::ReservedWords

       <http://www.oracle.com/technology/documentation/>

AUTHOR

       Christian Hansen "chansen@cpan.org"

COPYRIGHT

       This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.8.8							    2008-03-28					   SQL::ReservedWords::Oracle(3pm)