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 the __dbf_set() function.
| INI section | INI param name | default value | __dbf_set flag_name | __dbf_set flag_name description |
|---|---|---|---|---|
| Parameters | MaxMemPoolSize | 200000000 | sqlo_max_mp_size | The sqlo_max_mp_size is a size limit in bytes for transient memory consumption. Increasing this may help. The sqlo_max_mp_size should be over 10M; increasing this over 100M is seldom useful but can be tried. |
| Parameters | MaxOptimizeLayouts | 1000 | sqlo_max_layouts | Decreasing sqlo_max_layouts will reduce the number of plans tried, hence save memory. Reasonable values for sqlo_max_layouts are 0 for no limit, or somewhere in excess of 500. |
| Parameters | StopCompilerWhenXOverRunTime | 0 | sqlo_compiler_exceeds_run_factor | Setting sqlo_compiler_exceeds_run_factor to 1 will stop opttimization once the best plan is expected to take less time than the amount of time spent optimizing so far. |
| Flags | enable_joins_only | 1 | enable_joins_only | Setting enable_joins_only 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. Note: This option is now the default with 3215+ builds and there are no known cases when it should be set to 0. |
When reporting issues with query optimization, it will be useful to include statistics from the database in order to facilitate reproducing the effect.
The stat_export() function produces a statistics summary that can be read back into another database with the stat_import() function.
string_to_file ('stat.dv', serialize(stat_export()), -2); or exp_stat_to_file ('stat.dv'); which does not have 10MB file size limit
stat_import (deserialize (file_to_string ('stat.dv')));