SV: very active session, no SQL

From: Jesper Haure Norrevang <jhn.aida_at_cbs.dk>
Date: Wed, 05 Jan 2005 07:56:53 +0100
Message-id: <000001c4f2f3$be80e6f0$4a28e282@AIDA.local>

Mladen,

I have been and I am still very confused about V$SESSION.STATUS. What I am seeing in one of my Oracle Portal databases is:
- STATUS in V$SESSION is ACTIVE

The dedicated server process does not use any CPU.
I cannot find the SQL in V$SQL
V$SESSION_WAIT says: WAITED SHORT TIME
V$SESSION_WAIT.SECONDS_IN_WAIT keeps increasing (My interpretation is that it means, that the wait state did not change during the last SECONDS_IN_WAIT seconds - typically hours).
ALTER SYSTEM KILL SESSION gives ORA-31 Session marked for kill

What I think is the situation is, that the process is hanging (i.e. waiting for something that will never happen). Unfortunately the thing we are waiting for has not been instrumented (yet). Sessionstate dumps did not make me cleverer.

My two cents is: Try to look at CPU consumption both in OS and V$SESSTAT. Have a very close look on the wait interface. Are you waiting for something? (V$SESSION_WAIT.STATE =3D WAITING). Have you been waiting for something, but are not waiting right now (WAITED SHORT TIME, WAITED KNOW TIME, or WAITED UNKNOWN TIME).

If time disappear you might be waiting for something,=20 that is not instrumented in the wait interface.

Hope this helps you to get a little less confused.

Regards
Jesper Haure Norrevang

-----Oprindelig meddelelse-----
Fra: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] P=E5 vegne af Mladen Gogala
Sendt: 5. januar 2005 05:54
Til: oracle-l_at_freelists.org
Emne: very active session, no SQL

I am extremely confused, not for the first time in my life. I am=20 sanitizing data in one
of my databases, for demo purposes. Unfortunately, the demo data in=20 question consists
of BLOB files of up to 32MB in size, so I had to do a little trickery.=20 The script below works like a charm, and itakes approximately 1 hour to=20 complete. What confuses the heck out of me is the fact that the session=20 is active, and I don't see any SQL in v$session: SQL> ed
Wrote file afiedt.buf

1 select u.username,u.osuser,u.program,u.status, s.sql_text 2 from v$session u,v$sql s

  3  where u.sid=3D27 and
  4        u.sql_address=3Ds.address and
  5*       u.sql_hash_value=3Ds.hash_value

SQL> / no rows selected

SQL>
Now, when I am not looking for SQL, the session is OK, and active: 1 select u.username,u.osuser,u.program,u.status 2 from v$session u
3* where u.sid=3D27
SQL> /

USERNAME                       OSUSER
------------------------------ ------------------------------
PROGRAM                                          STATUS
------------------------------------------------ --------
FCC                            mgogala
perl_at_medo.noip.com (TNS V1-V3)                   ACTIVE

1* select event from v$session_wait where sid=3D27 SQL> / EVENT

SQL*Net more data from client

So, I have an active session, which has one of those "idle" waits (joke = will
be apparent to anyone who takes the look at the script below) without=20 executing
any SQL? How is it possible? Here is the script:

#!/usr/bin/perl -w
use strict;
use DBI;
use Getopt::Long;

my $user   =3D "fcc";
my $passwd =3D "***";
my $db     =3D "sales_demo";

my ( $rid,$file, $buffer, $ext );
my @rowids;

# Parse command line options
my $stat =3D GetOptions(

    "u|username=3Ds" =3D> \$user,
    "p|password=3Ds" =3D> \$passwd,
    "f|file=3Ds"     =3D> \$file,
    "e|ext=3Ds"      =3D> \$ext,
    "h|help|?"     =3D> \&usage

);
if ( !defined($user) || !defined($passwd) || !defined($file) || !$stat ) = {

usage();
}

$ext =3D~ tr/[a-z]/[A-Z]/;

my $dbh =3D db_connect( $user, $passwd, $db );

# File is read as a whole
open( FL, "<", $file ) or die "Cannot open $file for read:$!\n"; $/ =3D undef;
$buffer =3D <FL>;
# Get the rowids of the rows with the right documents my $SEL =3D "select rowid from invoice_doc

where upper(doc_filename) like \'%$ext\'";

my $UPD =3D "update invoice_doc

           set doc_file=3D:FL
           where rowid=3D:RID";

my $sth =3D $dbh->prepare($SEL);
$sth->execute();
while ( my @row =3D $sth->fetchrow_array() ) {

($rid) =3D @row;
push @rowids, $rid;
}

my $upd =3D $dbh->prepare($UPD);
$upd->bind_param( ":FL", $buffer, DBI::SQL_BLOB ); $upd->bind_param( ":RID", $rid );
foreach $rid (@rowids) {

$upd->execute();
$dbh->commit();
}

END {
$dbh->disconnect() if defined($dbh); }

sub db_connect {

my ( $username, $passwd, $db ) =3D ( @_, $ENV{"TWO_TASK"} ); my $dbh =3D DBI->connect( "dbi:Oracle:$db", $username, $passwd );

    $dbh->{AutoCommit}    =3D 0;
    $dbh->{RaiseError}    =3D 1;
    $dbh->{ora_check_sql} =3D 0;
    $dbh->{RowCacheSize}  =3D 16;

return ($dbh);
}

sub usage {

print q(update_file -> updates blob field in the database

      USAGE:update_file -u=3Duser -p=3Dpasswd -f=3Dfile -e=3Dext
      OPTIONS: -u oracle username
               -p password for the above
               -f file to read the BLOB data from
               -e extension to replace         =20

);

exit(0);
}

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 05 2005 - 00:55:27 CST