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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
