SQL System Properties

SQL system properties are key/value settings that are visible to every Transaction Engine (TE) or Storage Manager (SM) process 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).

Only a user that has been granted the system ADMINISTRATOR role can modify system property values.

AUTOCOMMIT_DDL

The default is false. When set to true, all DDL statements automatically commit the user transaction before and after the statement being executed.

This provides similar behavior with how Oracle handles DDL within transactions.

COMPAT_CARDINALITY_CLAMP

This is set to false by default and typically does not need modified. If set to true, it defaults 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 between 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.

DEFAULT_CONNECTION_MEMORY_LIMIT

This is an integer multiplier of the --mem (database option) value that is used to set the memory limit for all blocking SQL engine operations - hash-grouping, sorting, sorting via priority queue for limit, distinct (via hash-grouping), union, listagg, table functions, and stored procedure returning result set that accumulate data - in main memory.

By default there is no value set for DEFAULT_CONNECTION_MEMORY_LIMIT and it is backwards compatible, that is, no checking is performed whether any of the afore mentioned operations exceed a memory limit. When specifying a value for value set for DEFAULT_CONNECTION_MEMORY_LIMIT, it must be between 0.000000 and 1.000000.

When specifying a value for DEFAULT_CONNECTION_MEMORY_LIMIT, also consider the value set for the memory-limit-bytes connection property.

DEFAULT_LOCK_WAIT_TIMEOUT

The default lock wait timeout (in seconds) for all new connections. The default value is 0, which means lock wait timeout is disabled.

Changing the DEFAULT_LOCK_WAIT_TIMEOUT will not change the runtime parameter LOCK_WAIT_TIMEOUT for established connections. For more information, see SET and Connection Properties.

DEFAULT_VIEW_SECURITY

This property is used to set the database view security behavior to either the INVOKER or DEFINER model. The default is INVOKER. All views (existing and new) will follow the security model set by this property to determine view access rights.

Using the INVOKER option
The user invoking the view must also be explicitly granted all the privileges necessary to the view’s underlying tables and functions to authorize read access to the view.

Using the DEFINER option
The user invoking the view only requires a single GRANT SELECT on VIEW privilege to authorize read access to the view.
When using this model, the following applies:
1. The creator of the view is required to have access privileges to the underlying objects referenced by the view (e.g. tables, functions). The creator of the view is the owner of the view.
2. The view owner may GRANT SELECT ON VIEW to other users/roles. The user/role may then access the view without any additional privileges on the objects within the view.
3. The owner may also specify WITH GRANT OPTION to permit those users to also grant access to other users/roles. For example GRANT SELECT ON VIEW v1 TO user1 WITH GRANT OPTION. user1 may now grant view access to other users/roles using the GRANT SELECT ON VIEW command. No additional privileges on the objects within the view are needed by user1.
4. Access to the view may be revoked by the owner or a user holding the WITH GRANT OPTION privilege.

When using the DEFINER option, the view owner’s privileges are verified at runtime to validate that the owner still has the privileges needed to run the view. If the view owner no longer has the required privileges to run the view, then the view execution is halted and an error is reported.

DISKSPILL_MEMORY_THRESHOLD_MB

This property is used to define a memory threshold that limits the memory consumption of any SQL operation. If this threshold is crossed, spill to disk functionality is triggered if it is available and supported by the operation. The default value is 64MB, increasing this value will make spill to disk less likely, but can increase the memory usage of the TE.

The QUERYBUFFERSTATS table lists compatible SQL operations that have consumed the most memory and may be useful for tuning this value for a particular workload. For more information, see QUERYBUFFERSTATS System Table Description.

IDLE_CONNECTION_TIMEOUT

The maximum time (in seconds) which indicates how long idle connections are left open. By default, IDLE_CONNECTION_TIMEOUT is set to 0, meaning that this feature is disabled. To specify a timeout value for this connection, use any value greater than 0.

This is a global setting for timing out idle connections and may be overridden, on a per connection basis, by using the idle-timeout connection property. For more information on using the idle-timeout property, see Connection Properties.

Any change made to the IDLE_CONNECTION_TIMEOUT value affects all active connections that don’t override the global setting Any time a client connection is terminated for being idle for too long, a message will be logged under the net category.

If IDLE_CONNECTION_TIMEOUT is set using a value greater than 0, ensure that the testWhileIdle DataSource property s enabled (set to true). If the testWhileIdle property is not enabled, and the connection is terminated because of IDLE_CONNECTION_TIMEOUT, the DataSource does not automatically replace the terminated connection in the connection pool. For information on using DataSource properties, see Configuring a NuoDB DataSource.

INDEX_ENCODING

For NuoDB internal testing only.

MAX_CONNECTION_OPEN_RESULTSETS

The maximum number of open result sets per connection. The default is 1000.

MAX_CONNECTION_OPEN_STATEMENT

The maximum number of open SQL statements per connection. The default is 1000.

MAX_MATERIALIZED_QUERY_SIZE

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.

MAX_QUERY_COUNT

The maximum number of statements stored in the slow query log. The default is 10.

MAX_STMT_CACHE_SIZE

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 empties the current cache and disables statement caching.

MIN_QUERY_TIME

The minimum length of time (in seconds) it takes a SQL query to execute (from start to finish) before it is reported in the slow query log (see QUERYSTATS System Table Description). The default is 10. If the sql-statement-metrics logging category is specified, or the debug logging level is set, the following information is also written to the log:

2018-04-20T09:48:12.293+0200 [3465] [6,2690]:SQL metrics for statement:
<original SQL statement>

==> COMPILETIME: 380, EXECTIME: 601468, RUNTIME: 601848, TIMESTAMP:
1524210491691483, NUMPARAM: 0, PARAMS: , USER: CLOUD, SCHEMA: USER, CONNID: 6,
EXECID: 0, TRANSID: 2690, EXECUTIONCOUNT: 3, CLIENTINFO: nuosql, CLIENTHOST:
127.0.0.1, CLIENTPID: 3469, INDEXHITS: 0, INDEXFETCHES: 0, EXHAUSTIVEFETCHES: 0,
INDEXBATCHFETCHES: 0, EXHAUSTIVEBATCHFETCHES: 0, RECORDSFETCHED: 0,
RECORDSRETURNED: 1, INSERTS: 0, UPDATES: 0, DELETIONS: 0, REPLACES: 0,
RECORDSSORTED: 0, UPDATECOUNT: 0, EVICTED: 0, LOCKED: 0, REJECTEDINDEXHITS: 0,
USEDMEMORYBYTES: 0

For information on logging levels supported, see Using Log Files.

If the sql-statement-explain-plans logging category is specified, or the debug logging level is set, the following information is also written to the log:

                  2018-04-20T09:48:12.293+0200 [3465] [6,2690]:EXPLAIN ANALYZE:
                    <the EXPLAIN plan of the statement>

MIN_QUERY_WARN_TIME

The minimum length of time (in seconds) it takes a SQL query to execute (from start to finish) before the entry in the log controlled by the MIN_QUERY_TIME property is logged with the warn logging level (rather than the debug logging level). The default is 315,360,000.

OPT_MAX_SCAN_REOPTIMIZE

For NuoDB internal testing only.

OPT_STATS_INDEX

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. We recommend not changing the default value.

ROLLBACK_DEADLOCK_VICTIM

This property is used to automatically roll back the transaction chosen as the victim when a deadlock takes place. By default, this is set to true.

SQL_DISKSPILL

This property can be used to disable spill to disk on all TEs in the database. To disable spill to disk functionality on all TEs in the database, set this property to false. By default, this property is set to true (enabled).

SQL_STATEMENT_LOG_MIN_TIME

Any SQL statement which runs for longer than the minimum time specified in seconds is logged, if either of the following two log categories are set:

sql-statement-metrics - Displays general performance metrics per statement
sql-statement-explain-plans - Displays SQL query plan used at runtime

TABLE_CHG_TOLERANCE

The percentage of change in the number of rows to a table that cause precompiled 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.

UDF_CACHE_SIZE

The maximum number of deterministic User-Defined Functions (UDFs) 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 is removed. If that combination of arguments is used again, the UDF code is executed to compute the result. The default is 50.