9.28.3.Query Optimization Diagnostics

Queries involving a large number of possible plans may run out of memory during optimization. There are a number of settings that influence query optimization memory utilization.

These are set in the virtuoso.ini file or can be altered on a running system with __dbf_set function:


INI section INI parameter name __dbf_set function name __dbf_set function description
Parameters MaxMemPoolSize sqlo_max_mp_size Controlls the size limit in bytes for transient memory consumption. Increasing this may help. The given value should be over 10M, increasing this over 100M is seldom useful but can be tried.
Parameters MaxOptimizeLayouts sqlo_max_layouts Decreasing will reduce the number of plans tried, hence save memory. Reasonable values are 0 for no limit or somewhere in excess of 500 for a limit.
Parameters StopCompilerWhenXOverRunTime sqlo_compiler_exceeds_run_factor Setting to 1 will stop optimization once the best plan is expected to take less time and the amount of time spent optimizing so far.
Flag enable_joins_only enable_joins_only When set, will cause the optimizer to only consider next plan candidates that are connected by a join to the existing partial plan. In other words, no cartesian products will be considered. This may save some space and time.

When reporting issues with query optimization it will be useful to include statistics from the database in order to facilitate reproducing the effect. The function stat_export() produces a statistics summary that can be read back into another database with the stat_import() function.

To export statistics:

string_to_file('stat.dv', serialize(stat_export()), -2);

To load exported statistics into a database:

stat_import (deserialize (file_to_string ('stat.dv')));

When exporting statistics as part of bug reporting, make sure to first run the queries exhibiting the problem then only export the stats. The queries drive statistics gathering.