Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re: Re: performance problem with oralce dbd

by tito (Novice)
on Apr 15, 2004 at 18:12 UTC ( [id://345484]=note: print w/replies, xml ) Need Help??


in reply to Re: performance problem with oralce dbd
in thread performance problem with oracle dbd

#! /use/bin/perl -w #
# $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;

Replies are listed 'Best First'.
Re^3: performance problem with oralce dbd
by adrianh (Chancellor) on Apr 15, 2004 at 18:37 UTC

    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.

Re: Re: Re: performance problem with oralce dbd
by tilly (Archbishop) on Apr 16, 2004 at 05:48 UTC
    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
      Isn't the example one prepare and many executes? Does Oracle perform multiple hard parses anyway?

      /J

Re: Re: Re: performance problem with oralce dbd
by heathm (Initiate) on Apr 16, 2004 at 21:42 UTC

    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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://345484]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (3)
As of 2024-03-29 14:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found