Mike Dameron wrote:
>
> We have several scripts using Apache::ASP and DBI, which return
> information from an Oracle database. The problem is that if I run a
> report that taken a long time (not sure how long but over 30 minutes)
> and the connection either times out or I hit the stop button the oracle
> process which gets spawned does not die. I can see in the process list
> that the oracle process is still tied to a httpd process and continues
> to eat up cpu cycles.
>
Why would you run a report from a web site that would take
30+ minutes to finish ? Typical web connection timeouts
are a few minutes or less ?
I think the real solution is to make your reports fast enough
to run from a web site, like run the real report every night,
and cache its results in the database for later that you can get
them from the browser instantly.
A problem is that any report that long has the potential
to read lock your tables, so that if any updates need to
happen they are locked out. You web site seems to then
shut down.
One solution is to kill any queries that take too long,
so that your web site is less at risk to these kind of
untuned database killing queries. I do this with the
the below Oracle specific code, run in a cron job.
Note that I use a class that wraps around DBI, so you
will have to change some things to make it work for you.
-- Joshua
_________________________________________________________________
Joshua Chamas Chamas Enterprises Inc.
NODEWORKS >> free web link monitoring Huntington Beach, CA USA
http://www.nodeworks.com 1-714-625-4051
$self->Debug("cleaning up locks");
my $data = $self->DoSQL(<<SQL, 'Fetch');
-- (
select s.sid,s.serial#
from sys.v_\$lock l, sys.v_\$session s
where l.ctime > $self->{stale_time}
and l.block != 0
and (
l.lmode = 3 or
l.lmode = 5 or
l.lmode = 4 or
l.lmode = 6
)
and l.sid = s.sid
and s.status != 'KILLED'
order by l.ctime desc
-- )
SQL
;
return unless $data;
my($sid, $serial) = @{$data};
if($SessionKills{"$sid,$serial"}++ >= 3) {
$self->Critical("killing session $sid,$serial, give up");
}
my $rv = $self->DoSQL("alter system kill session '$sid,$serial'") || 0;
$self->Log("killing stale session $sid: $rv\n");
$self->Commit();
