The oracle optimizer should not take long for an uncached query that's only using one table. Especially if the tables have been analyzed. There are only three possible plans for the query given -- a full table read, using an index that's based on security_id, or using an index based on is_new (okay, technically, you might have more than two indexes that meet those criteria, so there might be a couple more options, but should still be negligible.)

You should only need to worry about the time to generate the execution plan when you're dealing with complex table joins, or subqueries, or the like. This query given should be very quick, even if you don't have an index on security_id, and haven't analyzed the table. (well, the actual execution won't be very quick necessarily, but the planning should be quick).

You do, however, as Thilosophy said, make sure the statements are identical. Unless it's changed since 8i (when I took Oracle's SQL Tuning class), this means case sensitive, identical whitespace, etc. Which they are, as well as I can tell. Which would mean that the database isn't caching your plan -- which given that it uses LRU, could be a sign of a bigger problem with the database.

I would suggest having the DBA check V$SQL and V$SQL_PLAN to see what's currently being saved. Unfortunately, all of my oracle books are at work, and this isn't the sort of thing I have memorized. I know there are ways to check how full the SGA and Shared Pool are ... STATSPACK might give you some information, and of course, the error logs.

Building on what Thilosophy said, I'd also suggest the following code adjustment (note -- I've changed the Oracle bind placeholders to the ones that DBI uses... I'm not sure if DBD::Oracle handles the :1 syntax, or not, but I know ? works)

use strict;
my $sth;

sub secIsNew {
 
  # Given a PACE security ID, return 1 if that security is newly held,
  # 0 if that security is not newly held.

  $sth ||= $dbh->prepare <<EOF;
SELECT COUNT(*) FROM analytics.an_security
WHERE security_id = ? AND is_new = 'Y'
EOF

  $sth->execute(@_);
  my ($count) = $sth->fetchrow_array();
  return $count;
}
[download]