Re: Long Parse Time for a big Statement
- From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
- To: Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
- Date: Tue, 18 Jan 2022 15:24:49 +0000
On Tue, 18 Jan 2022 at 14:43, Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
wrote:
Jonathan,
Would it make sense to also include die typecheck memory (TCHK) when
measuring the total shared memory consumed by an SQL?
For example, in Lothar’s case it was the TCHK allocation that failed.
In some cases, like “create table as select”, the TCHK shared memory can
be significantly larger than the SQLA part:
https://nenadnoveljic.com/blog/large-tchk-allocations-shared-pool/
Best regards,
Nenad
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
Other related posts:
