JPD has asked for the wisdom of the Perl Monks concerning the following question:

The subroutine pasted below is part of a Perl DBI script that connects to an Oracle 9i database. This subroutine runs many times in a short time period. My DBA tells me that Oracle is parsing the subroutine's query each time the subroutine runs. Is there something I can do to encourage Oracle to save and re-use the execution plan for this query? 
sub secIsNew {
 
  # Given a PACE security ID, return 1 if that security is newly held,
  # 0 if that security is not newly held.

  use strict 'vars';
  my($secId) = $_[0];
  my($sth); # Statement handle
  my($count); # Count returned by SQL select statements

  my($sql) = "select count(*) 
                  from analytics.an_security 
                  where security_id = :1 and is_new = 'Y'"; 
  $sth = $dbh->prepare($sql);
  $sth->bind_param(1, $secId);
  $sth->execute();
  ($count) = $sth->fetchrow_array;

  return $count;
}

[download]

Replies are listed 'Best First'.
Re: Question About Proper Use of Bind Variables in Perl DBI Scripts
by jZed (Prior) on Mar 24, 2005 at 17:43 UTC
    Yes, prepare the query once outside the subroutine, then let the subroutine execute the already prepared query multiple times.
[reply]
      Or you may want to examine using prepare_cached (take note of the warnings, however)
      --------------
      "But what of all those sweet words you spoke in private?"
      "Oh that's just what we call pillow talk, baby, that's all."
[reply]
      JZed: Your suggestion was very easy to implement and it worked perfectly. Thanks! JPD
[reply]
Re: Question About Proper Use of Bind Variables in Perl DBI Scripts
by ikegami (Patriarch) on Mar 24, 2005 at 18:00 UTC
    Doesn't Oracle cache the execution plan server-side? It's something I once heard.
[reply]
      Okay, here is how it works:

      If you issue a completely new query, Oracle has to do what is called a hard parse: It reads the SQL string, checks permissions for the accessed objects, statistics about table data size and distribution, existence of indices, and generates the execution plan. This is expensive. For simple queries, this takes more time than the execution itself. In a web application (or any other OLTP scenario) you do not want this to happen more than necessary.

      The resulting execution plan is cached in Oracle's server memory. If you issue the same query again, it will only incur a soft parse. It will just find the already existing execution plan and reuse it. This is what happens in the OP's program, and it is much better than a hard parse.

      The first priority is to reduce hard parses to soft parses. To do this, the SQL statement must be identical, so you have to use bind variables.

      Now, you can take it a little further, by preparing the statement only once, and executing it many times. In this case, you will have only one parse (hopefully a soft parse) and subsequent executions run without any parsing (no parse). Obviously, that is the best case. Using prepare_cached can help you with that.

[reply]

        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)

        Read more... (576 Bytes)
[reply]
[d/l]
[select]
      It does, but I think it only keeps a certain number cached. If a particular query is run enough, it will be cached, but this is very dependent on use of the database. If there is a lot of activity, you need to run your query many times before it will be cached in the DB.

      The advice above allow you to reuse the prepare on the Perl side without counting on Oracle to do it.

[reply]

Back to Seekers of Perl Wisdom