You seem to be misunderstanding how grep works. I recommend re-reading the documentation.

You actually want something like this:

if ( grep $_ == $xpid, @procs ) {
   print "Pid is in use : $xpid\n";
}
[download]

Also, fetchrow_arrayref returns an array _reference_ (as in the name). So you should be writing:

$procs = $dbh->selectall_arrayref("select spid from v\$process");
if ( grep $_ == $xpid, @$procs ) {
   print "Pid is in use : $xpid\n";
}
[download]

The potential downside with grep is that it always searches the whole list - which will impact performance if the list is large. Something like this might be more efficient (or build a hash as robiticus suggests):

$procs = $dbh->selectall_arrayref("select spid from v\$process");
my $found;
foreach (@$procs) {
  if ($_ == $xpid) {
    $found = 1;
    last;
  }
}
if ($found) {
   print "Pid is in use : $xpid\n";
}
[download]

Oh, and one last tip. If you use single quotes around your SQL then you don't need to escape the '$'.

$procs = $dbh->selectall_arrayref('select spid from v$process');
[download]