Martin Gainty wrote:
SQLLoader multithreading will effect stream building on the client
system to be done in parallel with stream loading on the server system.
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm
How does perl support multithreading?
It is suppose to be thread safe but you would have to compile and run
perl in 'thread' mode and then of course design your application to be
'threaded'
I know of some designers who do use threading with DBD::Oracle I myself
have never tried. We actually I have yet to find the need to.
I checked the DBD::Oracle and OCI can be enabled in OCI_THREADED mode
if the original DBD::Oracle install was done in Thread mode. In other
words you have to compile
your DBD as threaded or not.
||
cheers
John
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede
unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig.
Diese Nachricht dient lediglich dem Austausch von Informationen und
entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten
Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den
Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
> Date: Mon, 3 May 2010 07:32:16 -0400
> From: sco...@pythian.com
> To: paragka...@gmail.com
> CC: j...@jeffseger.com; dbi-users@perl.org
> Subject: Re: Insert Query with place holders Vs SQLLDR
>
> Parag Kalra wrote:
> > Ok I increased the ora_array_chunk_size size as shown below (I hope
> > its correct)
> >
> > # Connecting to database
> > my $dbh = DBI->connect("dbi:Oracle:$dbname", $uname, $passwd,{
> > PrintError => 0,
> > RaiseError => 0,
> > ora_array_chunk_size => 1000000
> > } ) or die "Could not connect to the database\n";
> >
> > Also removed the section to drop and create table from all the 3
> > scripts. Here are the new results with same set of records:
> >
> > sqlldr conv - 27 seconds
> > DBD::Oracle - 37 seconds
> > sqlldr direct - 02 seconds
> >
> > The bottom line is sqlldr if used in direct mode is incomparable but
> > DBD::Oracle is almost as efficient as sqlldr used in conventional mode
> >
>
> I agree with that. I can see that the extra time perl is using most
> likely has to do with getting the tuples back to perl. This is good
> stuff as it shows that DBD::Oracle is comparable
> or at least close in processing time to SQLLDR. Or course with Perl you
> get a complete language so your potential for processing is much
greater.
>
> I do have a question about sqlldr does it keep a record of the 'failed'
> inserts or does it just report at the end. In DBD::Oracle we get a set
> of Tuples that we can recheck for errors is there
> comparable in SQLLDR??
>
> Cheers
> John
>
>
> > Cheers,
> > Parag
> >
> >
> >
> > On Sun, May 2, 2010 at 5:27 PM, Jeffrey Seger <j...@jeffseger.com
> > <mailto:j...@jeffseger.com>> wrote:
> >
> > I think you have to keep the time reading records and setting up the
> > arrays in the comparison, as SQLLDR has to read and parse too. I do
> > agree that this could be sped up by changing the chunk size though.
> >
> > I'd also take out dropping and re-creating the table, or at the very
> > least, time that operation separately. If you are calling TRUNCATE in
> > your SQLLDR script, then call TRUNCATE in your Perl script as well.
> >
> >
> >
> >
> > On Sun, May 2, 2010 at 7:58 PM, John Scoles <sco...@pythian.com
> > <mailto:sco...@pythian.com>> wrote:
> > > That seems about right a millon records in less than a min is
> > very fast.
> > >
> > > This could be spead up a bit by setting 'ora_array_chunk_size'
> > to a larger
> > > value than the default 1000.
> > >
> > > If you can run it again with 10000 and 100000 or even 1000000 to
> > see what we
> > > get.
> > >
> > > I couldn't tell from your code but you are only testing the time
> > it takes to
> > > do the insert and not the time to set up the arrays??
> > >
> > > cheers
> > > John SColes
> > >
> > > On Sun, May 2, 2010 at 7:44 PM, Parag Kalra
> > <paragka...@gmail.com <mailto:paragka...@gmail.com>> wrote:
> > >
> > >> Ok here is the thing.
> > >>
> > >> This afternoon I coded few scripts and created a test setup to
> > validate the
> > >> benchmarking results of the various tools and following are the
> > results:
> > >>
> > >> Environment Settings
> > >> Operating System: Windoze XP SP3 (sorry couldn't test it Nix
> > as it is a
> > >> prod box)
> > >> Database: Oracle 10g 10.2.0.1.0
> > >> Perl: 5.10.1
> > >> DBD-Oracle: 1.21
> > >> Total records used for testing: 1048576
> > >>
> > >> Time of insertion taken by sqlldr in conventional mode -
> > 35
> > >> seconds
> > >> Time of insertion taken by sqldr in direct mode -
> > >> 06 seconds
> > >> Time of insertion taken by DBD::Oracle using the array
> > interface - 46
> > >> seconds
> > >>
> > >> PFA the Perl script used for testing. Let me if any of you
> > interested in
> > >> viewing the sqlldr control scripts.
> > >>
> > >> Cheers,
> > >> Parag
> > >>
> > >>
> > >>
> > >> On Sun, May 2, 2010 at 12:27 PM, Jeffrey Seger
> > <j...@jeffseger.com <mailto:j...@jeffseger.com>> wrote:
> > >>
> > >>> My experience is that DBD::Oracle using the array interface is
> > >>> comparable performance-wise to SQLLDR in conventional path mode.
> > >>> However, SQLLDR can also be called in direct path mode, which
> > is even
> > >>> faster, but you should really know what you are doing before using
> > >>> that. It has implications beyond the performance aspect that
> > you need
> > >>> to be aware of.
> > >>>
> > >>> If you want/need/understand using direct path, then SQLLDR is
> > the way
> > >>> to go. If you want better integration with the rest of your
> > program
> > >>> then DBD::Oracle is the way to go.
> > >>>
> > >>> On Sun, May 2, 2010 at 4:20 AM, Parag Kalra
> > <paragka...@gmail.com <mailto:paragka...@gmail.com>> wrote:
> > >>> > Hi All,
> > >>> >
> > >>> > I want to know which one of these algorithm would be the
> > most optimized
> > >>> > solution to insert large number of records and Why:
> > >>> >
> > >>> > 1. Preparing an Insert query once and executing it with
> > place holders
> > >>> for
> > >>> > the entire set of records
> > >>> > 2. Executing Oracle's sqlldr command and uploading the same
> > set of
> > >>> records
> > >>> >
> > >>> > Cheers,
> > >>> > Parag
> > >>> >
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> "Champions do not become champions when they win the event,
> > but in the
> > >>> hours, weeks, months and years they spend preparing for it. The
> > >>> victorious performance itself is merely the demonstration of their
> > >>> championship character." -T. Alan Armstrong
> > >>>
> > >>> "The Ow that can be expressed is not the true Ow." - Ao Tzu
> > >>>
> > >>
> > >>
> > >
> > > --
> > > Miss MySQL Conference 2010? No problem.
> > > Access Pythian speaker session videos at http://bit.ly/mysql2010
> > >
> > >
> >
> >
> >
> > --
> > "Champions do not become champions when they win the event, but in the
> > hours, weeks, months and years they spend preparing for it. The
> > victorious performance itself is merely the demonstration of their
> > championship character." -T. Alan Armstrong
> >
> > "The Ow that can be expressed is not the true Ow." - Ao Tzu
> >
> >
>
------------------------------------------------------------------------
Hotmail has tools for the New Busy. Search, chat and e-mail from your
inbox. Learn more.
<http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1>
