# $Id: test.pl
#
# To test insert rate of Oracle DBD.
#
#*********************************************************************
+********
use DBI;
use strict;
use diagnostics;

if (@ARGV < 3)
{
  print "\n";
  print " test.pl  <database name> <database account> <database accoun
+t password>\n";
  print "\n";
  exit;
}

my $database_name = $ARGV[0];
my $db_user_name = $ARGV[1];
my $db_password = $ARGV[2];
my $data_source = "dbi:Oracle:$database_name";

if (!defined($db_password))
{
  $db_password = "password";
}
if (!defined($db_user_name))
{
  $db_user_name = "userid";
}
if (!defined($database_name))
{
  $database_name = "app";
}

#
# Connect to the database
#
$dbh = DBI->connect($data_source,
                    $db_user_name,
                    $db_password,
                    { RaiseError => 1, AutoCommit => 1 , ora_session_m
+ode => 0}) 
                    || die "Could not connect to database '$database_n
+ame'\n";
$dbh->{RowCacheSize} = 100;

my $rc; # return code

    my $func = $dbh->prepare(q{
INSERT INTO temp ( ACCT_STATUS_TYPE, NAS_USER_NAME, NAS_IDENTIFIER, NA
+S_IP_ADDRESS,
ACCT_SESSION_ID, FRAMED_IP_ADDRESS, SERVICE_TYPE, FRAMED_PROTOCOL, MED
+IUM_TYPE, NAS_PORT_TYPE,
NAS_PORT, NAS_PORT_ID, NAS_REAL_PORT, ACCT_DELAY_TIME, ACCT_SESSION_TI
+ME, ACCT_TIME_STAMP,
RATE_LIMIT_RATE, RATE_LIMIT_BURST, POLICE_RATE, POLICE_BURST, FILTER_I
+D, FORWARD_POLICY,
HTTP_REDIRECT_PROFILE_NAME, CONTEXT_NAME, SESSION_TIMEOUT, IDLE_TIMEOU
+T, RB_DHCP_MAX_LEASES,
MULTICAST_SEND, MULTICAST_RECEIVE, MULTICAST_MAX_GROUPS, IGMP_PROFILE_
+NAME,
QOS_POLICING_POLICY_NAME, QOS_METERING_POLICY_NAME, QOS_PQ_POLICY_NAME
+, ATM_PROFILE_NAME, PPPOE_URL,
PPPOE_MOTM, PPPOE_IP_ROUTE, PPP_DNS_PRIMARY, PPP_DNS_SECONDARY, PPP_NB
+NS_PRIMARY,
PPP_NBNS_SECONDARY, ACCT_TERMINATE_CAUSE, SESSION_ERROR_MSG, ACCT_REAS
+ON, ACCT_INPUT_OCTETS_64,
ACCT_OUTPUT_OCTETS_64, ACCT_INPUT_PACKETS_64, ACCT_OUTPUT_PACKETS_64, 
+ACCT_MCAST_INPUT_OCTETS_64,
ACCT_MCAST_OUTPUT_OCTETS_64, ACCT_MCAST_INPUT_PACKETS_64,
ACCT_MCAST_OUTPUT_PACKETS_64 ) VALUES ( 
'Start', 'joe', 'ser-1', '127.0.0.1', '0B00FFFF90000010-40647961', NUL
+L, 'Outbound-User'
, NULL, 'DSL', 'Virtual', 201392128, '12/1 clips 20013', NULL, NULL, N
+ULL, 99999999
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'BASIC', NULL, NULL, 1, NU
+LL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NU
+LL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) 
    },{ora_check_sql => 0 });
  my $start_time = time;
  print "Start of insert test, time:", $start_time, "\n";
  foreach (1..1500) {
    $func->execute();
  }
  my $end_time = time;
  print "End of insert test, time:", $end_time, "\n"; 
  
  $difference = $end_time - $start_time;
  print "Time taken: ", $difference, "\n";
#
# to be absolutely sure that the export selection criteria and the pur
+ge criteria sync up we 
#  pick a time in the recent past by subtracting 6 hours (or 3600 seco
+nds)away from the max timestamp
#

print "end \n";


$dbh->disconnect;
1;
[download]

This is untested since I don't have an Oracle box to hand to play with.

Were you doing a commit after every insert with the SQL you sent to sqlplus? If not I suspect that changing AutoCommit => 0 and doing an explicit commit every few hundred inserts will speed things up considerably.

Oracle in particular does not do well with hard parses.

Convert your query to use placeholders (ie pass in ? for each parameter, and then in the execute pass in the data) and performance should improve greatly. More importantly if load matters, hard parses use a type of internal lock known as a latch, and latch contention will cause Oracle to scale very poorly.

Of course prepare_cached with placeholders will perform even better, but just switching to placeholders will make Oracle perform much better.

To understand Oracle's performance characteristics, I can highly recommend virtually anything by Tom Kyte. He has strong opinions on how to use Oracle (some of which I disagree with) but does a very good job of explaining how things work and giving you the tools to explore further.

UPDATE: As noticed by jplindstrom and etcshadow, I misread the code and the hard parses issue that I talked about is indeed a red herring. However if you are benchmarking, be aware that if the real code does not use placeholders, then hard parses will be a problem for the database, and you really want to avoid them.

What you're saying is accurate but not applicable to the case in point. For starters, the poster's code is only one query repeated exactly, thus multiple parses would be soft-parses, not hard-parses. Second, the poster does not even repeatedly parse the statement... there's just one parse and many executes.

------------
:Wq
Not an editor command: Wq
[download]

Isn't the example one prepare and many executes? Does Oracle perform multiple hard parses anyway?

/J

One thing I've noticed is the extreme amount of time that Oracle RAW data types take when DBD::Oracle thinks they are strings. You might consider binding your ACCT_SESSION_ID listed in the code if that value is a RAW using something like the following:

$sth->bind_param(0, pack('H*', '0B00FFFF90000010-40647961'), { ora_type => ORA_RAW });

This will save you lots of churning on the database side, as ORACLE will need to convert it to a raw value if you don't specify. We noticed a substantial performance difference when doing this with all of our RAW columns. Also as noted by others, { Autocommit => 0 } will be a big difference in speed.


Hope that's helpful.