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@xxxxxxxxx> 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@xxxxxxxxxx> wrote:
My apologies if that was already mentioned. (This thread is rather long).Jared Still
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@xxxxxxxxx> 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@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,etc) ?
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,
Thanks for your help.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
--
//www.freelists.org/webpage/oracle-l
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
