Open topic with navigation
SQL system properties are key/value settings that are visible to every process (TE or SM) and client connection in the database. They are stored persistently in the
SYSTEM.PROPERTIES table (see PROPERTIES System Table Description) and are modifiable by the
SET SYSTEM PROPERTY command (see SET).
Note: Only a user that has been granted the system
ADMINISTRATOR role can modify system property values.
This is set to false by default and typically will not need to be modified. If set to true, it will default to an older cost model mechanism for the SQL query optimizer.
Previously, the NuoDB cost model synthetically added 1000 rows to all small tables for the purposes of computing access costs. As more optimizations have been added this synthetic addition has been found to complicate optimization in certain cases (for instance, joins betweens large and small tables and subquery optimization). We have therefore changed the cost model such that an additional 10 rows are added to all tables, rather than 1000 rows to only small tables. This changes selectivity estimates slightly, which may mean slightly different plans. If you find a query performs worse because the plan has changed, you can recover the previous behavior by setting
COMPAT_CARDINALITY_CLAMP to true.
For NuoDB internal testing only.
The maximum number of open result sets per connection. The default is 1000.
The maximum number of open SQL statements per connection. The default is 1000.
A new materialization strategy is used to avoid repeated computations of the same subquery multiple times. A subquery will materialize its results in an in-memory table to be reused by the outer query. The
MAX_MATERIALIZED_QUERY_SIZE property specifies the maximum number of bytes that a materialized table can use. If the data to be stored is greater than this limit, it will not be cached and the query will be executed every time the query results are needed. The default is
67108864 bytes (64 MB).
MAX_MATERIALIZED_QUERY_SIZE is used as a limit for each query that could be materialized. If your query has two separate
IN SELECT clauses, your total memory consumption can be up to
2*MAX_MATERIALIZED_QUERY_SIZE. If this property is set to
0, no subqueries are materialized.
The maximum number of statements stored in the slow query log. The default is 10.
The maximum number of precompiled statements to be cached in memory. The default is 500. See LOCALSTATEMENTS System Table Description for more information. Setting
MAX_STMT_CACHE_SIZE to 0 will empty the current cache and disable statement caching.
The length of time in seconds required for a SQL statement to be eligible for reporting in the slow query log (See QUERYSTATS System Table Description). This is the duration of the SQL statement from start to finish. Crrently executing long queries are not stored in the slow query log. The default is 10.
For NuoDB internal testing only.
Used to turn on (
true) or off (
false) the use of statistics by the optimizer. The default is
true. The gathering of statistics happens regardless of this property setting. This property just determines whether or not those statistics are used. NuoDB does not recommend changing the default value.
false (default), then for a given update operation, such as
SELECT..FOR UPDATE or
DELETE, in a
READ COMMITTED or
WRITE COMMITTED transaction, if it encounters an uncommitted insert operation then that insert operation is going to be blocked. If this property is set to
true, then the insert operation will not be blocked.
The percentage of change in the number of rows to a table that will cause pre-compiled statements using the table to be re-compiled. The default is 0.10 (10%). Compiled statements using a table having more than 10% of its rows changed will have its cache statement invalidated. This will force a re-compilation the next time the statement is used.
The maximum number of deterministic user defined functions (UDF) that will be cached in memory. The cache stores any type of UDF, regardless of how it is implemented (Java or SQL). A deterministic UDF is one that is run with the same arguments. When the cache is full, the oldest entry gets removed, so if that combination of arguments is used again, the UDF code will be executed to compute the result. The default is 50.
DB_TRACE, DB_TRACE_TABLE, DB_TRACE_PATTERN, DB_TRACE_MIN_TIME, DB_TRACE_PROCEDURES
These system properties are used to define SQL tracing at the
GLOBAL level. These system properties cannot be modified by the
SET command. See Using the SQL Trace Facility for more information on setting these system properties.