Re: Long Parse Time for a big Statement

On Tue, 18 Jan 2022 at 14:43, Noveljic Nenad <nenad.noveljic_at_vontobel.com> 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

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