SV: very active session, no SQL
- From: Jesper Haure Norrevang <jhn.aida@xxxxxx>
- To: mgogala@xxxxxxxxxxxxxxxxxxxx
- Date: Wed, 05 Jan 2005 07:56:53 +0100
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] P=E5
vegne af Mladen Gogala
Sendt: 5. januar 2005 05:54
Til: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx (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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
