VirtQueryOptDiagnostic Virtuoso Diagnostics for Query Optimization 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. <tgroup><thead /><tbody> <row /> <row><entry> Parameters </entry><entry> MaxMemPoolSize </entry><entry> 200000000 </entry><entry> sqlo_max_mp_size </entry><entry> The <emphasis>sqlo_max_mp_size</emphasis> is a size limit in bytes for transient memory consumption. Increasing this may help. The <emphasis>sqlo_max_mp_size </emphasis> should be over 10M; increasing this over 100M is seldom useful but can be tried. </entry></row> <row><entry> Parameters </entry><entry> MaxOptimizeLayouts </entry><entry> 1000 </entry><entry> sqlo_max_layouts </entry><entry> Decreasing <emphasis>sqlo_max_layouts</emphasis> will reduce the number of plans tried, hence save memory. Reasonable values for <emphasis>sqlo_max_layouts</emphasis> are 0 for no limit, or somewhere in excess of 500. </entry></row> <row><entry> Parameters </entry><entry> StopCompilerWhenXOverRunTime </entry><entry> 0 </entry><entry> sqlo_compiler_exceeds_run_factor </entry><entry> Setting <emphasis>sqlo_compiler_exceeds_run_factor</emphasis> to 1 will stop opttimization once the best plan is expected to take less time than the amount of time spent optimizing so far. </entry></row> <row><entry> Flags </entry><entry> enable_joins_only </entry><entry> 1 </entry><entry> enable_joins_only </entry><entry> Setting <emphasis>enable_joins_only</emphasis> 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 <ulink url="http://dbpedia.org/resource/Cartesian_product">Cartesian products</ulink> will be considered. This may save some space and time. <emphasis>Note:</emphasis> This option is now the default with <emphasis>3215</emphasis>+ builds and there are no known cases when it should be set to 0.</entry></row> </tbody></tgroup></table> <para>When reporting issues with query optimization, it will be useful to include statistics from the database in order to facilitate reproducing the effect. The <emphasis><ulink url="http://docs.openlinksw.com/virtuoso/fn_stat_export.html">stat_export()</ulink></emphasis> function produces a statistics summary that can be read back into another database with the <emphasis><ulink url="http://docs.openlinksw.com/virtuoso/fn_stat_import.html">stat_import()</ulink></emphasis> function.</para> <itemizedlist mark="bullet" spacing="compact"><listitem>To export statistics: <programlisting>string_to_file ('stat.dv', serialize(stat_export()), -2); or exp_stat_to_file ('stat.dv'); which does not have 10MB file size limit </programlisting> </listitem> <listitem>To load exported statistics into a database: <programlisting>stat_import (deserialize (file_to_string ('stat.dv'))); </programlisting> 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.</listitem> </itemizedlist><para> </para> <bridgehead class="http://www.w3.org/1999/xhtml:h2">Related</bridgehead> <itemizedlist mark="bullet" spacing="compact"><listitem><ulink url="VirtTipsAndTricksGuide">Virtuoso Tips and Tricks Collection</ulink> </listitem> <listitem><ulink url="http://docs.openlinksw.com/virtuoso/sqloptimizer.html">Virtuoso SQL Optimization</ulink> </listitem> </itemizedlist></section></docbook>