in reply to Too many open files error with DBD Oracle

If you want to use threads in conjunction with DBI, it behoves you to read the pod for the appropriate DBD::*. In this particular case, this might be useful information:

ora_dbh_share

Needs at least Perl 5.8.0 compiled with ithreads. Allows to share database connections between threads. The first connect will make the connection, all following calls to connect with the same ora_dbh_share attribute will use the same database connection. The value must be a reference to a already shared scalar which is initialized to an empty string. 

  our $orashr : shared = '' ;

  $dbh = DBI->connect ($dsn, $user, $passwd, {ora_dbh_share => \$orash
+r}) ;

[download]

That said, your snippet is concealing why you are using threads.

  1. It calls start() 11 times synchronously.
  2. start() creates 25 threads and then waits for the to complete.
  3. Each thread,
    1. creates a (*new*) connection to the DB;
    2. Opens a file;
    3. Closes the file;
    4. Drops the connection to the DB.

It is hard to see why you would be failing for a lack of file handles, when you should have at most 25 open at any given time. Unless Perl on Solaris, or Solaris itself is terminally broken?

The question of whether you could actually benefit from multiple connections to Oracle is entirely obscured.

Replies are listed 'Best First'.
Re^2: Too many open files error with DBD Oracle
by JoeW (Novice) on Jun 23, 2010 at 15:39 UTC
    For info, the actual program in which this logic appears does this:

    At Start - opens a large file containing SQL statements, 1 per line.
    Creates 25 threads.
    Reads SQL file, and passes SQL statements to threads one at a time, using shared variables. At each line, waits for a free thread to run statement.

    Each thread runs each SQL statement sent to it, and logs information (response time) in a file, one for each thread. Then signals back to Start that it's ready for another statement.

    At end of file, all threads disconnect and finish, and another file is processed, as above.

    The application, which worked fine until I increased the number of input files, is a test harness to measure SQL response times.

    I think threads is the best way to do this...
[reply]
      For info, the actual program in which this logic appears does this:

      Now that would be interesting to see...

      But for this specific problem, I modified your thread code as follows:

      sub RunThread {
    my $tid = threads->tid;
    my $dbh = DBI->connect("DBD::Pg",...) or die "failed connect";
    my ($x, $y)=@_;
    open (my $fh2, ">/tmp/da-$x-$y") or die "failed in thread $! at it
+er $x thread $y " ;
    print "$tid : ", fileno( $fh2 ); sleep 1;
    close ($fh2);
    $dbh->disconnect or die "disconnect failed";
}

      [download]

      When I run that, I get the following which shows that the filenos underlaying the file handles are being reused by each new batch of threads. If you don't see similar, then it probably means that Solaris/Perl on your system is downlevel or broken. 

      [19:04:06.98] c:\test>DBjunk.pl
1 : 3
2 : 4
3 : 5
4 : 6
5 : 7
6 : 8
8 : 9
10 : 10
9 : 11
7 : 12
12 : 13
14 : 14
13 : 15
11 : 16
15 : 17
16 : 3
17 : 4
19 : 5
18 : 6
20 : 7
21 : 8
22 : 9
23 : 10
24 : 11
25 : 12
27 : 3
28 : 4
26 : 5
29 : 6
30 : 7
31 : 8
32 : 3
33 : 4
34 : 5
35 : 6
36 : 7
37 : 8
38 : 9
39 : 10
40 : 11
41 : 12

      [download]
      Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
      "Science is about questioning the status quo. Questioning authority".
      In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]
[select]
        Thanks, no I don't see that - the file numbers gradually increase. See discussion below - looks like Oracle client is leaking :(
        Thanks for the replies, a very fruitful introduction to perlmonks!
[reply]

      Maybe your open filehandles get cloned for every thread? You could instead first create your threads and then later on allocate the resources only needed in one thread.

[reply]

In Section Seekers of Perl Wisdom