My queries are slow under load

loadoracle-11g-r2performance

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?

Best Answer

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:

    SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

     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.

Related Question