I am performing a load test for my application. All my SQL queries which are fired from my application, individually look better and perform fast.

The same SQL are becoming bottleneck when they are part of a load where response time of this queries varies from 1 second to 100 seconds. There is no high usage of the CPU or memory in the database machine and there is no network latency as well.

I am wondering what else is causing this queries to perform slow under load conditions. During the load itself, sometimes I get the response time below a second, the same query gets response time of 100 seconds in another executions.

Are there any locks or waits happening during a synchronous access of the tables? Most of these queries are SELECT queries only which should not be the cause for locks or waits.

The application environment is Oracle 11g, JBoss 5.1. The elapsed times of the queries are calculated at JDBC level.

I/O looks quiet better, though there are few high physical read ratios. If the I/O is a bottleneck then it should happen for all the time, right? I get slow response times once in a while. If there are locks/waits happening, is there a way to find them out during the test? may be with the help of a perf stats or tool?

  • 1
    Are you aware of Statspack, AWR reports or similar? tkprof?
    – Philᵀᴹ
    Oct 30, 2013 at 0:35
  • If some indexes or tables are getting flushed out of cache under load, you'll get (perhaps drastic) differences in performance from one execution to the other. And I/O spikes. See if you can run your tests with a larger cache. If that "fixes" the perf. inconsistencies, then either you need to live with a larger cache, or tune your queries and schema.
    – Mat
    Oct 30, 2013 at 5:22
  • I am not much aware of statspack or AWR reports. How do I know whether indexes or tables flushed out from my cache? I have more than 100 Gigs of data sapred across different tablespaces in a SAN disk. Oct 30, 2013 at 18:59
  • Does anybody know how to run "Who is Active v11.11"? Oct 30, 2013 at 19:01

1 Answer 1


I know it's late but there is a lot that could be going on.

-Make sure your statistics look good:

select owner,table_name,sample_size,num_rows,last_analyzed
from dba_tab_statistics.

num_rows and last_analyzed should match if you use default settings.

select owner,table_name,index_name,sample_size,num_rows,last_analyzed
from dba_ind_statistics.

Again sample_size and num_rows should match.

  • Verify if you use CURSOR_SHARING=EXACT or FORCE (if SIMILAR, change it to FORCE),(will require sysdba privilege):

    SQL>show parameter cursor; SQL>alter system set cursor_sharing=FORCE; Note that ideally you want to match the parameters you have on your production system. You may want to change these to test them as a cause of your problem however.

  • Verify that your SGA is sized properly:

    select * from v$sga_target_advice You would get something like this:


     10240             .25    131401926              1.2592          1.4876E+10
     15360            .375    117178544              1.1229          1.1815E+10
     20480              .5    112472201              1.0778          1.0648E+10
     25600            .625    109873799              1.0529          1.0250E+10
     30720             .75    107077125              1.0261          9638685159
     35840            .875    105929237              1.0151          9404059270
     40960               1    104353499                   1          9058914623
     46080           1.125    103706507               .9938          8926654470
     51200            1.25    102788197                .985          8724640673
     56320           1.375    102391653               .9812          8646734008
     61440             1.5    101807274               .9756          8515379746
     66560           1.625    101275077               .9705          8394896181
     71680            1.75     97987941                .939          7696453864
     76800           1.875     97967070               .9388          7696453864
     81920               2     97967070               .9388          7696453864

Where SGA_SIZE_FACTOR is 1, is my current SGA_SIZE. You can look at column ESTD_DB_TIME_FACTOR and ESTD_PHYSICAL_READS to see how much benefit I get if I increase it to the sizes mentioned in SGA_SIZE. In my case, not much benefit which means I'm good.

  • Compare your query when executed with literal values vs bind variables. You may want to see if you have a lot of hard parses for a given query. Check v$sql and v$sql_plan (have a look at column child_number for a given sql_id.

  • Do you have very skewed data on some of the columns in your where clause, joins and group by? If so you may be experiencing issues related to skewed data if bind variables are used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.