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

Sponsored Content

Top Forums UNIX for Advanced & Expert Users Perl-to-Oracle performance: DBI-pack visa 'sqlplus' usage Post 302371440 by adderek on Saturday 14th of November 2009 06:25:49 PM

11-14-2009

Registered User

Dude,

You are concerned about Oracle DB connection performance.... and it shows that you lack some basic understanding of it. I'm not a god-of-Oracle but I will try to explain some things here so that you would understand why your approach is wrong.

When you are connecting to Oracle - you are establishing a session. This would consume some memory (let say that it is 0.5 MiB) on the server. It require some time as well. Let say that it require 0.5 s. Everything you do in Oracle is done in transaction (DDL, DML, ...) and the transaction is ended by any commit (including DDL).
Whenever you make a single insert into the DB - it require some time. Let say that it is 0.0000001 s.

Now imagine that you are using sqlplus and DBI. In both cases you wish to insert a single row of data. If one of them (DBI and sqlplus) would insert data in 0.0000002 s instead of 0.0000001 s then.... which one is faster? Do you remember that the session establishment took 0.5 s?

It is possible that you would like to compare the data insertion performance. In that case I can see the following options:
1. Inserts using sql*loader in direct mode (note that additional constraints should be taken into consideration)
2. Inserts using sql*loader in indirect mode or some other thing using bulk loading (jdbc or something else)
3. Inserts using non-bulk loading (sql*loader with commit after every row or something else like repeating dumb insert n-times from perl)
4. Inserts using 1 session and 1 transaction for every row inserted into the DB

The performance is best for 1. and worst for 4.
If you wish to load a lot of data into the DB then I suggest bulk loading (JDBC might be an option) or... if you already have files like .csv then you might use sql*loader (in indirect or direct mode).

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?

Just a general hint: Thy to avoid using shell + sqlplus if you are dealing with a large number of data and complex logic.

adderek

View Public Profile for adderek

Find all posts by adderek

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 PHP

oci_set_client_info

OCI_SET_CLIENT_INFO(3)													    OCI_SET_CLIENT_INFO(3)

oci_set_client_info - Sets the client information

SYNOPSIS

       bool oci_set_client_info (resource  $connection, string	$client_info)

DESCRIPTION

	Sets the client information for Oracle tracing.

	The  client  information  is registered with the database when the next 'roundtrip' from PHP to the database occurs, typically when an SQL
       statement is executed.

	The client information can subsequently be queried from database administration views such as V$SESSION.

	The value may be retained across persistent connections.

PARAMETERS

	      o $connection
		-An Oracle connection identifier, returned by oci_connect(3), oci_pconnect(3), or oci_new_connect(3).

	      o $client_info
		- User chosen string up to 64 bytes long.

RETURN VALUES

	Returns TRUE on success or FALSE on failure.

EXAMPLES

       Example #1

	      Setting the client information

	      <?php

	      $c = oci_connect('hr', 'welcome', 'localhost/XE');

	      // Record the client information
	      oci_set_client_info($c, 'My Application Version 2');

	      // Code that causes a roundtrip, for example a query:
	      $s = oci_parse($c, 'select * from dual');
	      oci_execute($s);
	      oci_fetch_all($s, $res);

	      sleep(30);

	      ?>

	      // While the script is running, the administrator can see the client
	      // information:

	      sqlplus system/welcome
	      SQL> select client_info from v$session;

NOTES

       Note

	      Oracle version requirement

	      This function is available when PHP is linked with Oracle Database libraries from version 10 g onwards.

       Tip

	      Performance

	      With older versions of OCI8 or the Oracle Database, the client information can be set using the Oracle  DBMS_APPLICATION_INFO  pack-
	      age. This is less efficient than using oci_set_client_info(3).

       Caution

	      Roundtrip Gotcha

	      Some  but  not  all  OCI8  functions  cause roundtrips. Roundtrips to the database may not occur with queries when result caching is
	      enabled.

SEE ALSO

       oci_set_module_name(3), oci_set_action(3), oci_set_client_identifier(3).

PHP Documentation Group 												    OCI_SET_CLIENT_INFO(3)