• Topic
  • Discussion
  • ODS.VirtQueryOptDiagnostic(Last) -- DAVWikiAdmin? , 2017-06-13 06:07:24 Edit WebDAV System Administrator 2017-06-13 06:07:24

    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 Sort in ascending order 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.

    • 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.

    Related