Explain plan query size limit?

ScottL

As part of a predictive model we have a query that is 4,344 lines long. When we try to run an explain plan we get the message: “ALERT! SQL/Command truncated due to size”.

I couldn't find any documentation as to the size limit for an explain plan - does anyone know? Clearly the query needs to be rewritten but it would be nice to know the size limit.

thanks

Jim_Knicely

Hi,

You can increase the size of the MaxParsedQuerySizeMB config param. The default is 512 MB. Although, note that this param should be changed under support guidance...

dbadmin=> \x
Expanded display is on.
dbadmin=> select parameter_name, current_value, description, change_under_support_guidance from configuration_parameters where parameter_name = 'MaxParsedQuerySizeMB';
-[ RECORD 1 ]-----------------+--------------------------------------------------------------------------------------------------------------------------------------
parameter_name                | MaxParsedQuerySizeMB
current_value                 | 512
description                   | Maximum amount of memory allowed for parsing a single request; Increasing this value may help with 'Request size too big' errors (MB)
change_under_support_guidance | t

ScottL

Thanks, Jim!