Re: improve cardinality estimation

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