Performance implication of many open "statement handles"

First, I am not a good C++ programmer, so my examples will be in Perl, but the core questins will be about C++.
Anyways, my company is developing applications to be deployed on Oracle.

I perl, I would connect to Oracle like this: my $dbh = DBI->connect($dsn, $user, $password, {AutoCommit=>0}) or die "Error...$DBI::errstr\n";;

I will then instantiate a statement handle, and call the "prepare" method on it:
 $sth1 = $dbh->prepare("insert into METERDETAILRECORD

(id,meterlog_id,operation_date,hour,min,resolution,quantity,resource_id)

                 values (mdr_seq.nextval,?,$tradeint,?,?,24,191,?)");

Notice the 4 bind variables prepared.I loop thru a certain list, and execute the prepared insert statement handle, feeding it some variables that change with each iteration.

foreach $i (@list)
  {
$sth1->execute($maximportid,$hour,$minute,$resource)  or die "Can't execute SQL statement: $DBI::errstr\n"; }
(and eventually, close the statement and database handle near the end of the
script)

So far, so good. Works great, TKPROF tells me the query gets parsed once, and executed lotsa times.

Now, the question:
Can similar operations: Prepare once, execute many times, be done in C++/OCI? (Sorry if that's a stoopid question) What are the consequences, in terms of resources used on the Oracle server, of having lots of "prepared, and open" handles in the application for a long
(hours/days) time? Is it only entries in the shared_pool, and get groomed
out if not referenced for a long time?
Should the prepared handles be closed shortly after use, and re-prepared(triggering a soft parse in the server), or is it OK, to not close them until the application closes?

Thanks for reading so far, got a little long, Foz Received on Fri May 09 2003 - 17:48:48 CDT