Re: improve cardinality estimation

 I definitely would feel very nervous to use sql translation, clearly have no intention to use it but it is good to know about it ( I didn't knew about it before so thanks) .

At this point I managed to save the situation using a couple of baselines (the same path is good "enough" for all kinds of situations (bind values) and its duration for this particular section is1 minute compared with 1 hour so this makes people happy). Even more with some luck, beginning of next year I`ll have the code updated similar to what Jonathan suggested. Thanks all for your input. It was very useful and helpful.

În mie., 28 oct. 2020 la 16:37, Jared Still <jkstill_at_gmail.com> a scris:

> and you should feel uneasy about sql translation
>
> sql translation could be used for all kinds of nefarious things.
>
> On Wed, Oct 28, 2020 at 01:29 Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
>> My apologies if that was already mentioned. (This thread is rather long).
>> sysdate <= sysdate + 1 always holds true. I do not think the optimizer is
>> capable of analysis at that level which goes beyond a straight logical
>> calculus transformation.
>> You could use some constant "where sysdate between trunc(col_from_date)
>> and trunc(nvl(col_to_date,to_date('12312999','MMDDYYYY')))".
>>
>> This approach has his disadvantages too . It can lead to underestimation
>> as it extend the time window unrealistically. It might hold in your case.
>> That's a matter of testing.
>> Your rewrite is cleaner and I believe that is the preferred solution.
>> Instead of changing the code there is always the option of the sql
>> translation, although I would feel a bit uneasy about it.
>> http://kerryosborne.oracle-guy.com/2013/07/13/sql-translation-framework/
>>
>> Regards
>>
>>
>> Lothar
>>
>> Am 28.10.2020 um 08:39 schrieb Laurentiu Oprea:
>>
>> Overall, I start to suspect that the only solution is code re-write . But
>> not sure if this is a legitimate question: if there is an equivalent code
>> shape should oracle rewrite the query behind the scenes with that shape? Or
>> in this situation the "sysdate" scares him being non-deterministic, even if
>> the code with the different shape is not influenced by non-deterministic
>> character of sysdate?
>>
>> În mie., 28 oct. 2020 la 07:08, Laurentiu Oprea <
>> laurentiu.oprea06_at_gmail.com> a scris:
>>
>>> Dynamic sampling 11 was the nightmare until not so long. Due to a bug
>>> the automatic determined value was 11. It was generating a parsing time of
>>> over 5 minutes to parallel queries and when downgrade was happening due to
>>> various reasons I could see even 30 minutes of parsing (there were even
>>> extreme cases of 2-3 hours).
>>>
>>> Thanks for suggestion, although indeed solves the cardinality issues in
>>> my case the side effect is crazy parse time for some other more important
>>> queries.
>>>
>>> În mie., 28 oct. 2020 la 04:12, Mladen Gogala <gogala.mladen_at_gmail.com>
>>> a scris:
>>>
>>>> Is this an OLTP instance? If it is not, you may try with
>>>> OPTIMIZER_DYNAMIC_SAMPLING=11. If that is an OLTP instance, using
>>>> dynamic sampling will increase parsing time and lower the performance,
>>>> albeit not drastically.
>>>>
>>>> On 10/27/20 11:31 AM, Laurentiu Oprea wrote:
>>>> > Hello,
>>>> >
>>>> > I found a query with a very bad execution plan due to low
>>>> > cardinality evaluation of below where clause
>>>> >
>>>> > where
>>>> > sysdate between trunc(col_from_date) and
>>>> > trunc(nvl(col_to_date,sysdate+1))
>>>> >
>>>> > Is there any way I can improve the estimate (like extended stats,
>>>> etc) ?
>>>> >
>>>> > Thanks for your help.
>>>>
>>>> --
>>>> Mladen Gogala
>>>> Database Consultant
>>>> Tel: (347) 321-1217
>>>>
>>>> --
>>>> http://www.freelists.org/webpage/oracle-l
>>>>
>>>>
>>>>
>> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Principal Consultant at Pythian
> Oracle ACE Alumni
> Pythian Blog http://www.pythian.com/blog/author/still/
> Github: https://github.com/jkstill
>
>
>

--
http://www.freelists.org/webpage/oracle-l