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@xxxxxxxxxxxx>
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
__
