Search code examples
sqloracle11gquery-performance

Same query, same data, same execution plan BUT different response time (ORACLE 11g)

i'm going crazy with a (simple) query on a view.

The query is build dinamically at front-end and the where clause have a condition like:

 AND(   value1 = '*'
        OR value1 = 'Y'
        OR value1 is null)

This block is built depending on 3 check box, so you can have, when only 1 box checked, the condition

 AND( value1 = '*')

The query have nice risponse time (1sec) for EVERY combination of value, except for the condition 

AND( value1 = '*')

and for the condition 

AND( value1 = 'Y')

that have 30 min response time.

Trying to execute the query using TOAD I noticed that the first attempt the response time is great even for those cases, but the second time i execute the same query with the same data, the response time is 30min.

The column value1 is quite hard to evaluate (depends on 3 function that have some other queries), but is really strange for me that the query work fine for exampe with the condition AND (value1 ='*' or value1 ='Y).

I yust want a way to tell oracle optimizer "Ehy, don't do anything whit that column and use it for last", is possible?

Thanks to all.

Solution

  • Try to add hint to the query:

    /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */

    For more information google Cardinality feedback