At 9:34 -0300 17/10/01, [EMAIL PROTECTED] wrote:
>After some executions of this CGI, the listener on Oracle server machine
>goes down.
>For a while, other clients that was connected to the Oracle server can still
>access it, althought it is not possible to stabilish new connections to
>Oracle.
This is because the listener is only responsible for spawning new
server processes. The existing server processes are unaffected
because they are already established.
>After some more time, the dabase stops definitivelly and needs to be
>restarted.
This is what you need to check on. My guess is that you have exceeded
either 'processes' or 'sessions' init.ora parameter. You say CGI, but
if you are actually using mod_perl, it is astonishingly easy eaven
for an experienced programmer to leave hundreds of defunct servers -
which take a suprising amount of time to clear from Oracle's internal
session table. Oracle rarely crashes. If you think that it is,
connect with svrmgrl _before_ running your tests, and stay connected
after the 'crash'. Query v$session and v$session_wait and look at the
number of sessions established and active then compare that with the
value column of v$parameter where name = 'processes'.
Also look at the logs in $ORACLE_BASE/admin/<SID>/[ubc]dump and at
the listener log in $TNS_ADMIN/log/listener.log. They will provide
clear indications of why/whether the listener/Oracle crashed.
>$v_sql1 = 'select count(numero) ' . " ... ";
>$v_count = $v_dbh->selectrow_array($v_sql1, {RaiseError => 1});
>
>$v_dbh->{LongReadLen} = 128 * 1024;
>$v_dbh->{LongTruncOk} = 1;
>$v_sth = $v_dbh->prepare($v_sql2);
>$v_sth->execute();
>while (... $v_sth->fetchrow_array)
> ...
>$v_sth->finish;
>$v_dbh->disconnect;
There is nothing obviously wrong here, excepting that the LongReadLen
is a bit ambitious. Oracle's implementation and performance on Long
Raw data is lamentable, but not normally unstable. This is almost
definitely _not_ the problem though, since it would not make the
listener crash.
>I'd like to know if somebody knows about pitfalls and problematic situations
>related to DBI, Oracle server 7.3.4, or in the stablishment of various quick
>and simultaneos connections to Oracle (as occurs with the typical use of
>this CGI), which can be result in a listener crash.
>The only error detected is just AFTER the listener crash, obviously a TNS
>error.
>Any clues?
If you want to make a better and more responsive CGI, look at
mod_perl in which one connection can handle multiple sequential
requests. Carefully written you might see an increase in performance
of 40x (what we found in testing) and a reduction in machine
resources used by Oracle.
Regards
Paul Miller
--
-
Carib Data Limited
<mailto:[EMAIL PROTECTED]>
<http://www.caribdata.co.uk>
