Re: improve cardinality estimation

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
>>
>>
>>

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