Hi all,

We have a web service using: 

Redhat Linux AS3
Perl 5.8.5
Apache 1.3.10
mod_perl
mod_soap
SOAP::Lite
<<our code>>
DBI
Apache::DBI
DBD::Oracle
OCI

[download]

Which talks to a database server over the local network (during testing), which is: 

Redhat Linux AS3
Oracle Enterprise Database Server 9i

[download]

We have been doing performance testing, in which we send many SOAP requests to the web server (many per second), and measure the time it takes to handle each transaction. The average amount of time is less than 500ms. Unfortunately, every once in a while, we have a transaction that takes up to 20 seconds, or longer.

Using Apache::DProf, it looks like a lot of the real (wall clock) time is happening during: 

 DBI::connect_cached x 121    7.58s = (0.02 + 7.56)s
   DBD::Oracle::db::ping x 121   7.56s = (0.00 + 7.56)s
     DBD::_mem::common::DESTROY x 121     0.00s
     DBI::common::DESTROY x 242     0.00s
     DBI::common::FETCH x 121    0.00s
     DBI::db::prepare x 121   7.56s = (0.00 + 7.56)s
       DBD::Oracle::db::prepare x 121     7.56s = (0.00 + 7.56)s
         DBD::Oracle::st::_prepare x 121     7.56s

[download]

But the same view, of user+system time, shows almost no time there.

On the database side, a SQLNet trace shows that it is just waiting (during these long transactions) for the client side to finish the request.

All of the times within the database itself are sub-second.

Has anyone else experienced anything like this? Any idea what would cause about one transaction in a thousand to take upwards of 40x the average processing time?

Thanks for any ideas!

In reply to Apache::DBI + DBD::Oracle performance problem by gofaster

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.