Re: Long Parse Time for a big Statement

If Lothar can query v$sql the way you've suggested then looking at typecheck memory is worth doing; though it does need to be after an attempt to execute the query since EXPLAIN PLAN behaves differently from the parsing for execution so a positive check for TCHK won't necessarily prove anything. The other detail to watch out for is whether it will be necessary to query x$glob for the correct columns as the statement might not be revealed in v$sql if it fails parsing.

Regards
Jonathan Lewis

On Tue, 18 Jan 2022 at 14:25, Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:

> Some workarounds proposed in this thread target the query optimization
> phase.
>
>
>
> But I think the processing might not even come that far; the problem might
> appear during parsing instead, because ORA-04031 is raised for the function qcopCreateLog
> , which, according to Frits Hoogland, is a parsing function. This is
> something that can be definitely verified with 10053 trace.
>
>
>
> If the query indeed fails during the parsing phase, I would suggest
> checking if the columns are fully referenced with the schema name. (Some
> query generators do that.) The check doesn’t take much time, and if it
> really turns out to be the problem, it’s fairly easy to fix.
>
>
>
> Why do I suggest that?
>
>
>
> There was a problem in an earlier release when columns in certain types of
> queries were referenced by schema name, see
> https://nenadnoveljic.com/blog/memory-leak-parsing/ .
>
>
>
> Your query has similar characteristics as the one used in the article
> above: complex case expression, lots of brackets, long query text.
>
>
>
> The only difference is, that in my case, the excessive allocations were in
> PGA, so I got ORA-04030 instead of ORA-04031. I cannot reproduce the issue
> in 19c, but maybe you’re hitting a variant of “my” problem.
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
> __
>

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