Are you using Oracle in Shared Server mode (MTS)?
We had this problem with Oracle 8.1.5 and 8.1.6 in MTS mode. When we
switched to Dedicated Server mode, it went away. BTW, this is a
'know' Oracle bug and it's suppose to be fixed in 8.1.7, but we
haven't tested it yet.
-Jim
Tim Bunce muttered:
> Some people have reported problems with DBI 1.15 + DBD::Oracle 1.06.
> Please try using DBI 1.14 and see if that helps.
> Please _do_ let me know if that helps.
>
> Tim.
>
> On Thu, Apr 19, 2001 at 02:09:52AM -0500, Hugh Riley wrote:
> > First of all, I am new to perl, so be gentle with me. Any assistance would
> > be greatly appreciated
> >
> > I'm having a problem with a perl script that uses DBI (1.15) with the
> > Oracle DBD (1.06) that seems to be tied to the number of fields I want
> > returned and the number of rows that are returned. Essentially, the script
> > is hanging on the fetch of the data. The database is on another server in
> > the same subnet.
> >
> > In the SELECT, there's a ROWNUM limit. If I remove it, same problem. If I
> > drop it down to 100, then no problems. Before I put the RowCacheSize
> > parameter in the connect string, the script would hang at the execute
> > portion (after the "dbd_st_execute SELECT (out0, lob0)..." bit in the
> > dbitrace.log)
> >
> > If I decrease the number of fields, I can increase the number of rows I get
> > back. However, the number of rows I can retrieve with the current number of
> > fields seems to be slowly decreasing. The first time I actually figured out
> > how many rows I could get back, it was at 517. Now, it's somewhere around 475.
> >
> > I've tried modifying the way that I retrieve the data (fetch with
> > bind_colums vs. fields with fetchrow_array vs. etc.), the RowCacheSize and
> > others, but nothing seems to do anything.
> >
> > Again, thanks for any assistance any of you can provide.
> >
> > Here's a test script I created so I can work on the problem while not doing
> > everything the main script does:
> >
>--------------------------------------------------------------------------------------
> > #!/usr/bin/perl -w
> >
> > BEGIN {
> > $ENV{ORACLE_HOME} = '/usr/local/OraHome';
> > $ENV{TWO_TASK} = 'SID';
> > }
> > use DBI;
> > use DBD::Oracle;
> >
> > my $sid="SID";
> > my $user="user";
> > my $pass="pass";
> >
> > DBI->trace( 4, 'dbitrace.log' );
> >
> > my $dbh = DBI->connect("DBI:Oracle:$sid",$user,$pass, {RaiseError => 1,
> > RowCacheSize => -1});
> >
> > my $strSQL = 'SELECT field1, field2, field3, field4, field5, field6,
> > field7, field8, field9, field10, field11, field12, field13, field14,
> > field15, field16, field17 FROM table_name WHERE rownum <= 1000 AND
> > otherfield IS NULL ORDER BY field5';
> >
> > my $sth = $dbh->prepare ($strSQL);
> >
> > $sth->execute();
> >
> > my( $field1, $field2, $field3, $field4, $field5, $field6, $field7, $field8,
> > $field9, $field10, $field11, $field12, $field13, $field14, $field15,
> > $field16, $field17 );
> >
> > $sth->bind_columns( \($field1, $field2, $field3, $field4, $field5, $field6,
> > $field7, $field8, $field9, $field10, $field11, $field12, $field13,
> > $field14, $field15, $field16, $field17) );
> >
> > while ($sth->fetch) {
> > print "$field1\n";
> > }
> >
> > $sth->finish;
> >
> > $dbh->disconnect();
> >
>--------------------------------------------------------------------------------------
> >
> > and here's the result from the dbitrace.log:
> >
> >
> >
>--------------------------------------------------------------------------------------
> >
> > DBI 1.15-nothread dispatch trace level set to 4
> > -> DBI->connect(DBI:Oracle:SID, user, ****, HASH(0x80f86e0))
> > -> DBI->install_driver(Oracle) for perl=5.006 pid=26311 ruid=560 euid=560
> > install_driver: DBD::Oracle loaded (version 1.06)
> > New DBI::dr (for DBD::Oracle::dr, parent=, id=)
> > dbih_setup_handle(DBI::dr=HASH(0x816e0fc)=>DBI::dr=HASH(0x81bd828),
> > DBD::Oracle::dr, 0, Null!)
> > dbih_make_com(Null!, DBD::Oracle::dr, 88)
> > <- install_driver= DBI::dr=HASH(0x816e0fc)
> > -> connect for DBD::Oracle::dr (DBI::dr=HASH(0x816e0fc)~0x81bd828
> > 'SID' 'user' **** HASH(0x81bc578))
> > New DBI::db (for DBD::Oracle::db, parent=DBI::dr=HASH(0x81bd828), id=)
> > dbih_setup_handle(DBI::db=HASH(0x81bc818)=>DBI::db=HASH(0x81bd81c),
> > DBD::Oracle::db, 816de50, Null!)
> > dbih_make_com(DBI::dr=HASH(0x81bd828), DBD::Oracle::db, 112)
> > <- connect= DBI::db=HASH(0x81bc818) at DBI.pm line 412.
> > -> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
> > 'RaiseError' 1)
> > STORE DBI::db=HASH(0x81bd81c) 'RaiseError' => 1
> > <- STORE= 1 at DBI.pm line 437.
> > -> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
> > 'PrintError' 1)
> > STORE DBI::db=HASH(0x81bd81c) 'PrintError' => 1
> > <- STORE= 1 at DBI.pm line 437.
> > -> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
> > 'AutoCommit' 1)
> > <- STORE= 1 at DBI.pm line 437.
> > -> STORE for DBD::Oracle::db (DBI::db=HASH(0x81bd81c)~INNER
> > 'RowCacheSize' -1)
> > <- STORE= 1 at DBI.pm line 441.
> > <- connect= DBI::db=HASH(0x81bc818)
> > -> prepare for DBD::Oracle::db (DBI::db=HASH(0x81bc818)~0x81bd81c
> > 'SELECT field1, field2, field3, field4,
> > field5, field6, field7, field8, field9, field10, field11, field12, field13,
> > field14, field15, field16, field17
> > FROM table_name WHERE rownum <= 1000 AND otherfield IS NULL ORDER BY field5')
> > New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x81bd81c), id=)
> > dbih_setup_handle(DBI::st=HASH(0x81bc83c)=>DBI::st=HASH(0x81bc590),
> > DBD::Oracle::st, 81bd804, Null!)
> > dbih_make_com(DBI::db=HASH(0x81bd81c), DBD::Oracle::st, 204)
> > dbd_st_prepare'd sql SELECT
> > dbd_describe SELECT (EXPLICIT, lb 80)...
> > fbh 1: 'FIELD1' NULLable, otype 1-> 5, dbsize 65/66, p65.s0
> > fbh 2: 'FIELD2' NULLable, otype 1-> 5, dbsize 7/8, p7.s0
> > fbh 3: 'FIELD3' NULLable, otype 1-> 5, dbsize 50/51, p50.s0
> > fbh 4: 'FIELD4' NULLable, otype 1-> 5, dbsize 50/51, p50.s0
> > fbh 5: 'FIELD5' NULLable, otype 2-> 5, dbsize 22/134, p7.s0
> > fbh 6: 'FIELD6' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 7: 'FIELD7' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 8: 'FIELD8' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 9: 'FIELD9' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 10: 'FIELD10' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 11: 'FIELD11' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 12: 'FIELD12' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 13: 'FIELD13' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 14: 'FIELD14' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 15: 'FIELD15' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 16: 'FIELD16' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > fbh 17: 'FIELD17' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
> > dbd_describe'd 17 columns (row bytes: 206 max, 188 est avg, cache: -1)
> > <- prepare= DBI::st=HASH(0x81bc83c) at ortest.pl line 20.
> > -> execute for DBD::Oracle::st (DBI::st=HASH(0x81bc83c)~0x81bc590)
> > dbd_st_execute SELECT (out0, lob0)...
> > dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
> > <- execute= '0E0' at ortest.pl line 22.
> > -> bind_columns in DBD::_::st for DBD::Oracle::st
> > (DBI::st=HASH(0x81bc83c)~0x81bc590 SCALAR(0x81baef0) SC
> > ALAR(0x81baf08) SCALAR(0x81baf20) SCALAR(0x81baf38) SCALAR(0x81baf50)
> > SCALAR(0x81baf68) SCALAR(0x81baf80) SCA
> > LAR(0x81baf98) SCALAR(0x81bafb0) SCALAR(0x81bafc8) SCALAR(0x81bafe0)
> > SCALAR(0x81bc464) SCALAR(0x81bc47c) SCAL
> > AR(0x81bc494) SCALAR(0x81bc4ac) SCALAR(0x81bc4c4) SCALAR(0x81bc4dc))
> > dbih_setup_fbav for 17 fields => 0x81f327c
> > dbih_sth_bind_col 1 => SCALAR(0x81baef0)
> > dbih_sth_bind_col 2 => SCALAR(0x81baf08)
> > dbih_sth_bind_col 3 => SCALAR(0x81baf20)
> > dbih_sth_bind_col 4 => SCALAR(0x81baf38)
> > dbih_sth_bind_col 5 => SCALAR(0x81baf50)
> > dbih_sth_bind_col 6 => SCALAR(0x81baf68)
> > dbih_sth_bind_col 7 => SCALAR(0x81baf80)
> > dbih_sth_bind_col 8 => SCALAR(0x81baf98)
> > dbih_sth_bind_col 9 => SCALAR(0x81bafb0)
> > dbih_sth_bind_col 10 => SCALAR(0x81bafc8)
> > dbih_sth_bind_col 11 => SCALAR(0x81bafe0)
> > dbih_sth_bind_col 12 => SCALAR(0x81bc464)
> > dbih_sth_bind_col 13 => SCALAR(0x81bc47c)
> > dbih_sth_bind_col 14 => SCALAR(0x81bc494)
> > dbih_sth_bind_col 15 => SCALAR(0x81bc4ac)
> > dbih_sth_bind_col 16 => SCALAR(0x81bc4c4)
> > dbih_sth_bind_col 17 => SCALAR(0x81bc4dc)
> > <- bind_columns= 1 at ortest.pl line 26.
> > -> fetch for DBD::Oracle::st (DBI::st=HASH(0x81bc83c)~0x81bc590)
> > dbd_st_fetch 17 fields...
> >
> > ---------------------------------------------------------
--
<[EMAIL PROTECTED]> (Replace Z's with E's to reply)
If NT is your answer, you don't understand the question.
-Anonymous
