Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Fork a new process for inserting a record in DB (Oracle)

by juvenile_monk (Initiate)
on Nov 07, 2013 at 13:26 UTC ( [id://1061562]=perlquestion: print w/replies, xml ) Need Help??

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

Hello Perl Monks, I have a task to fill in huge amounts of Data in my Database. Almost about 100 Million records as quickly as possible. To achieve this, I'm using Parallel::ForkManager, which I had always used without any trouble. When I use the same module for inserting records, I get the following error.

ORA-24550: signal received: Unhandled exception: Code=c0000005 Flags=0

Please find my code structure below
use DBI; use DBD::Oracle; use Parallel::ForkManager; my $pm = Parallel::ForkManager->new(MAX_PROCESSES); my $pid = $pm->start and next; my $check = &doInsert($x,$y,$z); $pm->finish; # Terminates the child process sub doInsert { ...... ..... INSERT INTO TABLE ......... }
I have searched in various blog posts, and found one of the remedies, which actually does not work, but it will just disable the error message which shows up on the console... which is setting these flags off in the Oracle 11.2 Client "sqlnet.ora" file DIAG_ADR_ENABLED=OFF DIAG_SIGHANDLER_ENABLED=FALSE DIAG_DDE_ENABLED=FALSE Is there any solution to the above problem. Rewards to the person who helps me out with this issue.... :)

Replies are listed 'Best First'.
Re: Fork a new process for inserting a record in DB (Oracle)
by talexb (Chancellor) on Nov 07, 2013 at 13:52 UTC

    When I had to do this over ten years ago, I use the Ingres' bcp utility. Does Oracle have a similar tool that you could use instead of writing a Perl script?

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

        I'm trying to populate fresh data into the DB. So, I don't have any source from where I can simply dump it using sqlldr.
Re: Fork a new process for inserting a record in DB (Oracle)
by sundialsvc4 (Abbot) on Nov 07, 2013 at 14:30 UTC

    I cordially advise that you are going about this the wrong way ... that “parallel tasks/threads” will not make things go faster, but substantially slower.   You should be using the bulk-loading tool that Oracle provides for this purpose, since it is optimized to do exactly this.

    If you find that for whatever reason you do need to load large amounts of data into an SQL database (of any sort) by conventional means, there are several things that I suggest you consider:

    1. Parallelism is probably not your friend, probably will not help, because it just makes more work for the SQL engine at the other end of the wire.
    2. You must do the work within a transaction, and you should carefully choose that transaction’s “isolation level.”   (This sort of work needs to push other concurrent work aside as much as possible, through an aggressive choice of isolation level, which is why it is often done in the wee hours...)   Post a few thousand records, say, then commit, then open up another one.   (This will allow the database to do “lazy writing.”)   prepare the statement-handle, with placeholders, and use it repeatedly.
    3. Consider how the process can be made restartable.   Once you have successfully committed, record where you are in the input-file so that you could potentially restart at that checkpoint if the program should unexpec...   ;-)

    But, once again, there are “bulk loader” programs that are specifically designed to do this.

      I'd look into what these two guys said above...
      To avoid changing your code too much, just do all your data preparation as you currently do, but rather than executing the resutling INSERT SQL through the handle, append it to a file(s) or print it to STDOUT (and redirect it to a file) then push the resulting SQL files to your DB's buck loader.

      Hope that gives you some ideas...

        "then push the resulting SQL files to your DB's buck loader."

        A minor point, bulk loaders don't take sql files as input. They load text formatted files, for example CSV.

        Thanx monx... This will definitely help me to tackle my situation.
Re: Fork a new process for inserting a record in DB (Oracle)
by mje (Curate) on Nov 07, 2013 at 14:20 UTC

    You are in a world of pain and I doubt you will insert one row at a time using Parallel::ForkManager any quicker than inserting all the rows in one process. You should probably look for some sort of Oracle bulk insert program of look at execute_for_fetch/execute_array in DBI (both are which are natively implemented in DBD::Oracle).

    If you want to persue P::FM then you at least need to know that a) as soon as the first child exits, your DBI handle will be destroyed b) when child processes exit they often cause a SIGCHLD to be sent to the parent and Oracle client does things by default with SIGCHLD. You can solve (a) by setting AutoInactiveDestroy on your DBI handle. (b) is trickier as you have already found in your search.

Re: Fork a new process for inserting a record in DB (Oracle)
by RichardK (Parson) on Nov 07, 2013 at 14:12 UTC

    Does each fork create it's own database connection ?

    How do you insert the records? one at a time or in batches? If you're looking for speed it's usually best to batch your inserts into one transaction. So turn autocommit off and handle them yourself.

      Yes. I'm opening a DB connection for each thread and closing the connection. When I run the script... it just runs fine for first few seconds.... and then throws the above error...

        Wow. That's a completely brutal way of doing it. Think of how much work is involved in setting up a database connection, doing one insert, and then tearing down that connection.

        A better way might be to split the data into 20 chunks, and then pass each of the chunks to a separate process.

        Alex / talexb / Toronto

        Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Re: Fork a new process for inserting a record in DB (Oracle)
by karlgoethebier (Abbot) on Nov 08, 2013 at 07:15 UTC

    Perhaps this might be also interesting: SQL*Loader FAQ

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1061562]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (6)
As of 2024-03-19 11:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found