Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> SV: very active session, no SQL

SV: very active session, no SQL

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


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:

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.

Jesper Haure Norrevang

-----Oprindelig meddelelse-----
Fra: =
[] P=E5 vegne af Mladen Gogala
Sendt: 5. januar 2005 05:54
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

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 (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 ) = {


$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);
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) {


    $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


Mladen Gogala
Oracle DBA


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

Original text of this message