Virtuoso Diagnostics for Query Optimization
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 | 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. |
| 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. |
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.
- To export statistics:
string_to_file ('stat.dv', serialize(stat_export()), -2); or exp_stat_to_file ('stat.dv'); which does not have 10MB file size limit
- 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 run the queries exhibiting the problem before exporting the stats. The queries drive statistics gathering.