Overview|V4.3.0| docs|Distributed Database

Overview

Last Updated：2024-05-29 07:17:41 Updated

This topic lists the system variables of OceanBase Database in detail based on their intended use.

Auto-increment column variables

Variable	Description
auto_increment_increment	The auto-increment step size. It is used only for MySQL client logon.
auto_increment_offset	The start value of an auto-increment column.
last_insert_id	The last inserted auto-increment value in the current session.
identity	The `identity` variable is synonymous with the `last_insert_id` variable and can be queried by using `select @@identity`.
sql_auto_is_null	Specifies whether to obtain the auto-increment column value of the last inserted row.
auto_increment_cache_size	The number of cached auto-increment values.
div_precision_increment	The number of digits by which to increase the scale of the result of a division operation based on the scale of the dividend. This variable is for compatibility with MySQL.

Transaction variables

Variable	Description
autocommit	Specifies whether to automatically commit transactions.
tx_isolation	The isolation level of transactions.
ob_proxy_set_trx_executed	Specifies whether to send `START TRANSACTION Syntax` to OBServer through OceanBase Database Proxy (ODP).
ob_early_lock_release	Specifies whether to enable the early lock release (ELR) feature for transactions.
ob_trx_idle_timeout	The idle timeout period of a transaction in microseconds. A timeout occurs when the execution interval between two statements in a transaction exceeds the specified timeout period.
transaction_isolation	The isolation level of transactions.
transaction_read_only	Specifies whether to enable just read-only transactions.
ob_trx_lock_timeout	The lock wait timeout period in microseconds for transactions.

Character set and string variables

Variable	Description
character_set_client	The character set in which the statements are sent by the client.
character_set_connection	The character set to which the received statements are converted.
character_set_database	The character set of the default database.
character_set_results	The character set to which the server converts the result sets or error messages before sending them to the client.
character_set_server	The character set of the server.
character_set_system	The character set of the system.
collation_connection	The character set and collation for the connection.
collation_database	The default character set and collation of the database.
collation_server	The default character set and collation for the server.
character_set_filesystem	The character set of the file system.
sql_warnings	Specifies whether to generate an information string for single-row INSERT statements when warnings occur.
init_connect	The character string that the server executes for each connected client. The string consists of one or more SQL statements which are separated by semicolons (;).
nls_sort	The string value sorting rule. Note At present, this variable does not take effect.
nls_comp	The string value comparison rule. Note At present, this variable does not take effect.
nls_characterset	Allows you to view the default character set of the data types such as CHAR, VARCHAR2, and CLOB in the database. Note At present, this variable does not take effect.
nls_nchar_characterset	The default character set of the database, which is used for data types such as NCHAR, NVARCHAR2, and NCLOB. Note At present, this variable does not take effect.

Maximum value variables

Variable	Description
max_allowed_packet	The maximum size in bytes of a network packet.
max_user_connections	The maximum number of connections that a single user can set up with the OBServer node. When the value is set to `0`, the number of connections is not limited.
max_sp_recursion_depth	The maximum recursion level of a specified stored procedure.
parallel_servers_target	The queuing conditions for large queries on each server.
group_concat_max_len	The maximum length in bytes of the result of the `GROUP_CONCAT()` function.
sql_select_limit	The maximum number of rows to be returned for a SELECT query.
max_connections	The maximum number of connections for a tenant.

Time zone and date variables

Variable	Description
time_zone	The session time zone of the current tenant. The value can be an offset, such as `'+08:00'`, or a geographical location, such as `Asia/Shanghai`.
timestamp	The timestamp in seconds. This variable affects the result of `SELECT NOW(6)`.
system_time_zone	The system time zone of the server.
error_on_overlap_time	Specifies whether to report an error in the case of an ambiguous overlap timestamp.
nls_language	The default language of prompt messages, date, month, and the default values of `nls_sort` and `nls_date_language`.
nls_territory	The current territory, which is used in combination with `nls_language`.
nls_date_language	The default date language.
nls_calendar	The calendar system used by the database.

Version information variables

Variable	Description
version_comment	The version information of the OBServer node.
version	The version number of OBServer.
ob_last_schema_version	The latest schema version used in the session.
ob_proxy_global_variables_version	The latest version information of global variables returned by the OBServer node to ODP.

Log variables

Variable	Description
binlog_row_image	Specifies whether to record full column logs.
ob_enable_show_trace	Specifies whether to enable trace logs.
ob_trace_info	Trace information to be transparently transferred, which can be stored in REDO logs.
ob_log_level	The level of session logs. If this variable is not specified, the system log level is used.
tracefile_identifier	The custom identifier that becomes part of the log so that a row iteration trace log can be identified with ease.

Directory variables

Variable	Description
datadir	The local disk directory for data storage.
plugin_dir	The directory for storing plug-in DLL files.
secure_file_priv	The directory that can be accessed for file import or export.

Debugging variables

Variable	Description
debug_sync	The synchronization point during debugging, which is compatible with MySQL.
ob_global_debug_sync	The synchronization point during debugging. Unlike `debug_sync`, this variable takes effect on all sessions.

Query variables

Variable	Description
query_cache_size	The size of memory in bytes allocated for caching query results, which also indicates memory not in use. Note At present, this variable does not take effect.
query_cache_type	The query cache type. Note At present, this variable does not take effect.
ob_query_timeout	The query timeout period in microseconds.
is_result_accurate	Indicates whether the query result is correct when a query carries a `topk` hint.
net_buffer_length	The SQL query size acceptable to the OBServer node.

Timeout period variables

Variable	Description
connect_timeout	The connection timeout period in seconds.
default_password_lifetime	The permitted password lifetime.
net_read_timeout	The number of seconds to wait for more data of the connection before aborting the read.
net_write_timeout	The number of seconds to wait for a block to be written to a connection before aborting the write.
ob_trx_timeout	The transaction timeout period in microseconds.
ob_pl_block_timeout	The timeout period in microseconds for PL execution.
interactive_timeout	The number of seconds that the server waits for an interactive idle connection to become active before closing it.
wait_timeout	The number of seconds that the server waits for a non-interactive idle connection to become active before closing it.

Plan cache variables

Variable	Description
ob_enable_plan_cache	Specifies whether to enable the plan cache.
ob_plan_cache_percentage	Specifies the percentage of tenant memory that can be occupied by the plan cache.
ob_plan_cache_evict_high_percentage	Specifies the percentage of the absolute value of the maximum memory usage to trigger plan cache eviction.
ob_plan_cache_evict_low_percentage	Specifies the percentage of the absolute value of the maximum memory usage to stop plan cache eviction.

Recycle bin variables

Variable	Description
recyclebin	Specifies whether to enable the recycle bin.

Memory variables

Variable	Description
ob_sql_work_area_percentage	The maximum percentage of tenant memory for SQL execution.
ob_sql_audit_percentage	The maximum percentage of the SQL audit memory to the total memory of the current tenant.
ob_reserved_meta_memory_percentage	The percentage of reserved memory of a tenant. The reserved memory mainly stores metadata-related structure information.
ob_temp_tablespace_size_percentage	The temporary tablespace size threshold in percentage for a tenant disk.

FOREIGN KEY constraint variables

Variable	Description
foreign_key_checks	Specifies whether to enable FOREIGN KEY constraint check for DML or DDL statements.

Allowlist variables

Variable	Description
ob_tcp_invited_nodes	The IP address allowlist of a tenant. The value can contain percent signs (%), underscores (_), and IP addresses. Multiple IP addresses are separated with commas (,). IP list matching, mask matching, and fuzzy matching are supported.

Throttling variables

Variable	Description
sql_throttle_current_priority	The throttling priority. Only requests of sessions whose `sql_throttle_current_priority` value is less than the `sql_throttle_priority` value are throttled.
sql_throttle_priority	The throttling priority.
sql_throttle_rt	The response time (RT) in microseconds.
sql_throttle_network	The request queuing time. If the request queuing time is greater than or equal to the specified value, the query is not executed.

Audit variables

Variable	Description
ob_enable_sql_audit	Specifies whether to enable SQL audit for the current tenant.

Optimizer variables

Variable	Description
optimizer_use_sql_plan_baselines	Specifies whether the optimizer uses SQL plan baselines.
optimizer_capture_sql_plan_baselines	Specifies whether new plans can be automatically captured to the plan baseline.
parallel_degree_policy	The strategy based on which the optimizer selects the degree of parallelism (DOP).
parallel_degree_limit	The maximum DOP that the optimizer can select when the Auto DOP strategy is used.
parallel_min_scan_time_threshold	The minimum evaluation period for the parallel scan of base tables.
optimizer_dynamic_sampling	The level of dynamic sampling.
optimizer_features_enable	The version of the OceanBase Database optimizer that you want to enable.

Data format variables

Note

At present, variables described in the following table do not take effect.

Variable	Description
nls_length_semantics	The length semantics of the CHAR and VARCHAR2 data types.
nls_nchar_conv_excp	Specifies whether to report an error when data is lost during the conversion between NCHAR/NVARCHAR2 and CHAR/VARCHAR2.
nls_numeric_characters	The decimal and thousands separators used in a string during the conversion between a number and a string.
nls_currency	The local currency symbol for the `L` number format element.
nls_iso_currency	The local currency symbol for the `C` number format element.
nls_dual_currency	The local currency symbol for the `U` number format element.
nls_date_format	The format of converting a date to a string and the format of implicitly converting a string to a date.
nls_timestamp_format	The format to use when you convert a timestamp or LTZ timestamp to a string, or when you implicitly convert a string to a timestamp or LTZ timestamp.
nls_timestamp_tz_format	The format to use when you convert a timestamp or TZ timestamp to a string, or when you implicitly convert a string to a timestamp or TZ timestamp.

Feature enabling or disabling variables

Variable	Description
ob_enable_transformation	Specifies whether to enable the transformation feature of the SQL optimizer.
ob_enable_index_direct_select	Specifies whether to enable direct queries of index tables.
ob_enable_aggregation_pushdown	Specifies whether to enable aggregation pushdown.
ob_enable_transmission_checksum	Specifies whether to perform checksum on data packets between OBProxy and OBServer nodes. The setting takes effect in real time.
ob_enable_truncate_flashback	Specifies whether to enable flashback for truncated tables.
ob_enable_jit	Specifies whether to enable the just-in-time (JIT) execution engine.

Case sensitivity variables

Variable	Description
lower_case_table_names	Specifies whether object names are case-sensitive for storage comparison.

Read/Write mode variables

Variable	Description
read_only	Specifies whether the tenant is in read-only mode.
ob_read_consistency	The read consistency level.
tx_read_only	Specifies whether the transaction is read-only.
ob_max_read_stale_time	The maximum latency threshold in microseconds for weak-consistency read queries.

User password variables

Variable	Description
validate_password_check_user_name	Specifies whether the user password can be the same as the username.
validate_password_length	The minimum length of the user password.
validate_password_mixed_case_count	The minimum number of uppercase and lowercase letters that a user password must contain.
validate_password_number_count	The minimum number of digits that a user password must contain.
validate_password_policy	The password check strategy.
validate_password_special_char_count	The minimum number of special characters that a user password must contain.

Runtime filter variables

Variable	Description
runtime_filter_type	The runtime filter type at the tenant level.
runtime_filter_wait_time_ms	The maximum waiting duration for runtime filters.
runtime_filter_max_in_num	The number of distinct values (NDV) in the filter condition specified by the runtime filter of the IN type.
runtime_bloom_filter_max_size	The maximum memory usage in bytes allowed for the runtime filter of the BLOOM_FILTER type.

Other variables

Variable	Description
block_encryption_mode	The encryption algorithm used in the AES_ENCRYPT() and AES_DECRYPT() functions.
cursor_sharing	Specifies whether to parameterize an SQL statement when processing it.
explicit_defaults_for_timestamp	Specifies whether to enable non-standard behaviors for processing default values and NULL values in TIMESTAMP columns.
innodb_strict_mode	The check mode for SQL statements with specific syntax.
max_connections	The maximum allowable FD value.
ob_bnl_join_cache_size	The volume of the cached data that triggers a Batch Nest Loop Join.
ob_capability_flag	The capabilities that the OBServer node can provide. It is used to inform ODP of the features supported and features not supported by the OBServer node during capability negotiation between the OBServer node and ODP.
ob_compatibility_mode	The compatibility mode of a tenant.
ob_route_policy	The routing strategy for internal retries between ODP or the Java client and the OBServer node.
performance_schema	Declares to the client whether performance queries are supported.
resource_manager_plan	Activates a resource management plan. The CPU quota for limiting the user activities in a resource group varies based on the resource management plan.
sql_mode	The SQL mode. Actions such as Insert vary greatly with the SQL mode.
sql_safe_updates	Specifies whether to enable the safe update mode of MySQL. The mode is disabled by default.
license	The license type.
ob_proxy_partition_hit	Specifies whether to enable ODP to send the data requested by SQL statements to the OBServer node where the primary partition of the data is located.
sql_quote_show_create	Specifies whether to quote identifiers for the `SHOW CREATE TABLE` and `SHOW CREATE DATABASE` statements.
ob_org_cluster_id	The `CLUSTER_ID` of an OceanBase cluster.
server_uuid	The UUID of the OBServer node.
default_storage_engine	The default storage engine of an OBServer node.
ob_enable_rich_error_msg	Specifies whether to display information such as svr_ip, time, and trace_id when an error occurs during SQL execution.