The system variables of OceanBase Database can be categorized into global variables and session-level variables. You can set system variables to ensure that the behaviors of OceanBase Database meet your business requirements.
Variable categories
A global variable is used to implement a global modification. Different users of the same database tenant share the settings of global variables. Modifications to global variables remain effective after you exit the session. In addition, modifications to a global variable do not take effect on the currently open session and take effect only after a new session is established.
A session-level variable is used to implement a session modification. When a client is connected to the database, the database copies global variables to automatically generate session-level variables. Modifications to session-level variables apply to the current session only.
Comparison with system parameters
| Comparison item | System parameter | System variable |
|---|---|---|
| Applicable scope | Affects a cluster, zone, server, or tenant. | Affects a tenant globally or at the session level. |
| Effective mode |
|
|
| Modification method |
|
Modifications can only be performed by using SQL statements. For example, obclient> SET ob_query_timeout = 20000000; obclient> SET GLOBAL ob_query_timeout = 20000000;obclient> ALTER SESSION SET ob_query_timeout = 20000000;obclient> ALTER SYSTEM SET ob_query_timeout = 20000000; |
| Persistence | System parameters are persisted into internal tables and configuration files and can be queried from the /home/admin/oceanbase/etc/observer.config.bin and /home/admin/oceanbase/etc/observer.config.bin.history files. |
Only variables at the global level are persisted, while those at the session level are not. |
| Lifecycle | Long. A system parameter remains effective for the entire duration of a process. | Short. A system variable takes effect only after the tenant schema is created. |
| Query method | You can query a system parameter by using the SHOW PARAMETERS statement. For example,obclient> SHOW PARAMETERS LIKE 'schema_history_expire_time'; |
You can query a variable by using the SHOW [GLOBAL] VARIABLES statement. For example, obclient> SHOW VARIABLES LIKE 'ob_query_timeout';obclient> SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout'; obclient> SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';obclient> SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout'; |
References
For information about system variables, see System variable summary.
For information about how to view and set a system variable, see Set variables.