Re: Wildly inconsistent performance

In article <680b1826.0111271104.64c77f7c_at_posting.google.com>, kkubek_at_yahoo.com says...
>
>We have a package that does a series of lookups, and returns a single
>result.
>
>I have a script that makes 10 calls to the package with different
>input. I use timex to time the 10 calls. Sometimes, they return in
>less than 2 seconds (total time for 10 calls). Other times, it takes
>over a minute for the exact same test data. It tends to have two
>speeds (slow and fast), and it gets stuck in one speed or the other
>for extended periods. For example, it will take 2 seconds for each of
>20 runs, then 1 minute for each of the next 30 runs, then back to 2
>seconds.
>
>I've run bstat/estat, and nothing looks amiss .... 99% hit ratios, no
>latch contention, no RBS contention, no waits, no nothing. The
>database is fairly low activity (typically 20-50 connections), and I
>don't see a pattern of something else running at the time of the
>slowdown.
>
>I've also monitored it a little at the OS level (not my specialty),
>and don't see any disk, memory or swap issues either.
>
>I'm running the tests directly from the server, so network/connection
>issues are eliminated.
>
>Any other ideas ??
>
>Database : 8.1.6.3.0
>OS : Solaris 5.8
>Veritas Cluster Manager 1.3

sql_trace, timed_statistics and tkprof...

see
http://asktom.oracle.com/~tkyte/tkprof.html

for a redirect to the documetation. It'll show you if the problem exists in the database or outside the database (as it times things in the database only). if in the database, it'll show you "what" is getting stuck. we can start from there.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp