Re^3: Question About Proper Use of Bind Variables in Perl DBI Scripts

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]

Comment on Re^3: Question About Proper Use of Bind Variables in Perl DBI Scripts Select or Download Code

Replies are listed 'Best First'.
Re^4: Question About Proper Use of Bind Variables in Perl DBI Scripts by Thilosophy (Curate) on Mar 25, 2005 at 02:50 UTC
The oracle optimizer should not take long for an uncached query that's only using one table. It is true that a hard parse for a complex SQL statement takes more time than for a simple SQL statement. However, doing a hard parse always takes more time than not doing it, and in some cases the parsing overhead is the dominating factor for the total execution time. All I wanted to say, therefore is that hard parsing has to be avoided, and that is exactly what bind variables are for. Especially if the tables have been analyzed Actually, I should think that building an execution plan is faster if the tables have not been analyzed. In the absence of statistics (gathered during an analyze), Oracle has to skip all its clever calculations and defaults to some hard-coded heuristics. Of course, the quality of the resulting execution plan will suffer, so spending some extra time on gathering table statistics and using them is a good thing (And you can profit most from this extra cost when re-using the execution plan over and over by having bind variables) So, to sum up, not using bind variables is absolutely killing your Oracle performance, and if you run queries in a loop, you should also give prepare_cached some serious considerations.	[reply]

Replies are listed 'Best First'.

Re^4: Question About Proper Use of Bind Variables in Perl DBI Scripts
by Thilosophy (Curate) on Mar 25, 2005 at 02:50 UTC

The oracle optimizer should not take long for an uncached query that's only using one table.

It is true that a hard parse for a complex SQL statement takes more time than for a simple SQL statement.

However, doing a hard parse always takes more time than not doing it, and in some cases the parsing overhead is the dominating factor for the total execution time. All I wanted to say, therefore is that hard parsing has to be avoided, and that is exactly what bind variables are for.

Especially if the tables have been analyzed

Actually, I should think that building an execution plan is faster if the tables have not been analyzed. In the absence of statistics (gathered during an analyze), Oracle has to skip all its clever calculations and defaults to some hard-coded heuristics.

Of course, the quality of the resulting execution plan will suffer, so spending some extra time on gathering table statistics and using them is a good thing (And you can profit most from this extra cost when re-using the execution plan over and over by having bind variables)

So, to sum up, not using bind variables is absolutely killing your Oracle performance, and if you run queries in a loop, you should also give prepare_cached some serious considerations.

[reply]