If the parameters are the same (I'm assuming that's what's meant by exact same query
), it shouldn't be parameter sniffing (users get a bad plan for the wrong parameter(s)), but rather users are getting different plans for the same parameter(s). It could be because of settings like quoted_identifier
and arithabort
, which you can compare in sys.dm_exec_sessions
for the fast user and the slow user, or it could be because they have different default schemas and objects are referenced without the schema prefix. Parameter sniffing may still be involved (hence why one of them has a bad plan).
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.
Best Answer
Queries can become slow in such cases:
key_buffer_size
and buffer should be refreshed prior to perform the query(join|sort)_buffer_size
and on-disk temporary tables are createdAny of the mentioned above can cause sporadical slowdown. List is not exhaustive, sure.