Re: improve cardinality estimation

Sounds like a good solution.
As DanTow (http://www.singingsql.com/index.html) once wrote: There is usually an execution plan that is a good compromise in all cases.

Am 28.10.2020 um 16:27 schrieb Laurentiu Oprea:
>  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
> <mailto: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
> <mailto: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
>> <mailto: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
>> <mailto: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