This paper mainly introduces the classification, viewing and modification of parameters in Dameng database.
The main contents are shown in the figure:
In Dameng database, its performance can also be maintained through a series of parameters. In the maintenance process, it is very important to master the maintenance of parameters.
Parameter type
In Dameng database, you can view the type of parameters through the type field in the v$parameter data dictionary.
SQL> select distinct type from v$parameter; Line number TYPE ---------- --------- 1 READ ONLY 2 SYS 3 IN FILE 4 SESSION
- Manually modify the read only type. Such parameters cannot be modified during database operation.
- IN FILE: static parameter, which can be modified by alter system set command.
- SYS/SESSION: dynamic parameters, which can be modified by alter system or alter session command. Of course, alter session set can only modify dynamic session level parameters.
View parameters
You can view parameters in two ways
- Method 1: view by viewing the data dictionary
In DM7, there are many data dictionaries in Damon database. You can find the required data dictionary through sysobjects, which is similar to the DICT data dictionary in Oracle. For example, find the data dictionary related to ini. However, in the latest version of DM8-20210618-XXXX of DM8, sysobjects basically records data dictionaries and other object information starting with non-v $.
DM7: SQL> select name from sysobjects where name like '%INI%' limit 5; Line number NAME ---------- -------------------------- 1 SYSUSERINI$ 2 V$DM_INI 3 V$DM_ARCH_INI 4 V$DM_MAL_INI 5 V$DM_REP_RPS_INST_NAME_INI DM8: disql V8 SQL> select name from sysobjects where name like '%INI%' limit 5; Line number NAME ---------- ---------------------- 1 SYSUSERINI$ 2 SYSUSERINI 3 SYSINDEXSYSUSERINI$ 4 SP_TABLE_COL_STAT_INIT 5 SP_HP_COL_STAT_INIT After creating a table object, you can see this data dictionary again: SQL> create table newv8 (id number); Operation executed Elapsed time: 5.058(millisecond). Execution number:701. SQL> select name from sysobjects where name like 'NEW%'; Line number NAME ---------- ----- 1 NEWV8
Using v$dm_ini view the value of the parameter
View data dictionary structure: desc v$dm_ini Line number NAME TYPE$ NULLABLE ---------- ----------- ------------ -------- 1 PARA_NAME VARCHAR(128) Y 2 PARA_VALUE VARCHAR(256) Y 3 MIN_VALUE VARCHAR(256) Y 4 MAX_VALUE VARCHAR(256) Y 5 MPP_CHK CHAR(1) Y 6 SESS_VALUE VARCHAR(256) Y 7 FILE_VALUE VARCHAR(256) Y 8 DESCRIPTION VARCHAR(256) Y 9 PARA_TYPE VARCHAR(200) Y View the information of the first five static parameters: name, current value in memory, session level value, dm.ini Value in file, parameter type select para_name,para_value,sess_value,file_value,para_type from v$dm_ini where para_type='IN FILE' limit 5; Line number PARA_NAME PARA_VALUE SESS_VALUE FILE_VALUE PARA_TYPE ---------- ------------------ ---------- ---------- ---------- --------- 1 MAX_OS_MEMORY 95 95 95 IN FILE 2 MEMORY_POOL 91 91 91 IN FILE 3 MEMORY_TARGET 0 0 0 IN FILE 4 MEMORY_EXTENT_SIZE 1 1 1 IN FILE 5 MEMORY_MAGIC_CHECK 2 2 2 IN FILE
Use v$parameter to view the value of the parameter
Data dictionary structure: desc v$parameter; Line number NAME TYPE$ NULLABLE ---------- ----------- ------------- -------- 1 ID INTEGER Y 2 NAME VARCHAR(80) Y 3 TYPE VARCHAR(200) Y 4 VALUE VARCHAR(4000) Y 5 SYS_VALUE VARCHAR(4000) Y 6 FILE_VALUE VARCHAR(4000) Y 7 DESCRIPTION VARCHAR(255) Y View the parameter name, type, current value and global value of the system in the file dm.ini Values in: SQL> select name,type,value,sys_value,file_value from v$parameter where type='SYS' limit 5; Line number NAME TYPE VALUE SYS_VALUE FILE_VALUE ---------- -------------------- ---- ----- --------- ---------- 1 CTL_BAK_NUM SYS 10 10 10 2 BAK_POLICY SYS 0 0 0 3 MEMORY_LEAK_CHECK SYS 0 0 0 4 BUFFER_FAST_RELEASE SYS 1 1 1 5 SORT_BUF_GLOBAL_SIZE SYS 1000 1000 1000
- Method 2: view by function
SF_GET_PARA_VALUE (scope int, ini_param_name varchar(256)) function Gets the value of a non floating point and string type parameter SF_GET_PARA_DOUBLE_VALUE ( scope int, ini_param_name varchar(256)) function Gets the value of a parameter of floating point type SF_GET_PARA_STRING_VALUE ( scope int, ini_param_name varchar(256)) function Gets the value of a parameter of type string SF_GET_SESSION_PARA_VALUE (paraname varchar(8187)) function Gets the value of a parameter of session level integer type SF_GET_SESSION_PARA_DOUBLE_VALUE(paraname varchar(8187)) function Gets the value of a parameter of a session level floating point type The parameters of the above function, scope There are two values, 2 for memory and 1 for file dm.ini Example: View in memory memory_pool What is the value of the parameter SQL> SELECT sf_get_para_value(2,'MEMORY_POOL'); Line number SF_GET_PARA_VALUE(2,'MEMORY_POOL') ---------- ---------------------------------- 1 91
modify parameters
The modification methods vary according to the type of parameter.
-
read only type parameter:
Modification is not allowed during database operation. You can only modify DM INI file. -
in file static parameter and dynamic sys/session parameter can be modified in two ways:
The first mode: command line mode
You can modify the following statements: ALTER SYSTEM SET '<Parameter name>' =<Parameter value> [DEFERRED] [MEMORY|BOTH|SPFILE]; memory: It takes effect immediately at the memory level, and the restart database service fails. It is only applicable to dynamic parameters, i.e sys，session Type. spfile: Write the value of the parameter to dm.ini The file needs to be restarted to take effect and permanently retained. Applies only to static parameters, i.e in file Type. both: Write values to memory and dm.ini The file takes effect immediately after reaching the current level, and the restart does not become invalid. It is only applicable to dynamic parameters. deferred: After adding, the parameter value will be delayed to take effect, and the current session Not valid, only valid for newly created sessions; The default is effective immediately. It is effective for both the current session and the newly created session. It is only applicable to dynamic parameters.
Example 1: modify the in file static parameter and view it in memory and file level
select name,value from v$parameter where type='IN FILE' limit 5; Line number NAME VALUE ---------- ------------------ ----- 1 MAX_OS_MEMORY 95 2 MEMORY_POOL 91 3 MEMORY_TARGET 0 4 MEMORY_EXTENT_SIZE 1 5 MEMORY_MAGIC_CHECK 2 alter system set 'MAX_OS_MEMORY'=80 spfile; DMSQL Process completed successfully write to dm.ini Values in file: select sf_get_para_value(1,'MAX_OS_MEMORY'); Line number SF_GET_PARA_VALUE(1,'MAX_OS_MEMORY') ---------- ------------------------------------ 1 80 Elapsed time: 12.005(millisecond). Execution number:44. Current value in memory: select sf_get_para_value(2,'MAX_OS_MEMORY'); Line number SF_GET_PARA_VALUE(2,'MAX_OS_MEMORY') ---------- ------------------------------------ 1 95 adopt v$parameter View the current system value and the value written to the file: SQL> select name,value,type,sys_value,file_value from v$parameter where name='MAX_OS_MEMORY'; Line number NAME VALUE TYPE SYS_VALUE FILE_VALUE ---------- ------------- ----- ----- --------- ---------- 1 MAX_OS_MEMORY 95 IN FILE 95 80 Restart the database service at this time: disql China customs warehouse: shutdown immediate; Startup service: [dmdba@enmoedu ~]$ /etc/init.d/DmServiceDMSERVER start Starting DmServiceDMSERVER: [ OK ] disql View the value of the parameter in. The value of the current system takes effect as the latest value: select name,value,type,sys_value,file_value from v$parameter where name='MAX_OS_MEMORY'; Line number NAME VALUE TYPE SYS_VALUE FILE_VALUE ---------- ------------- ----- ------- --------- ---------- 1 MAX_OS_MEMORY 80 IN FILE 80 80
Example 2: delayed validation. After deferred is added, it will take effect in a new session
session I: select name,value,type from v$parameter where type='SESSION' limit 5; Line number NAME VALUE TYPE ---------- -------------- ------ ------- 1 SORT_BUF_SIZE 45 SESSION 2 SORT_BLK_SIZE 1 SESSION 3 SORT_FLAG 0 SESSION 4 HAGR_HASH_SIZE 100000 SESSION 5 HJ_BUF_SIZE 50 SESSION alter system set 'SORT_BUF_SIZE'=50 deferred memory; DMSQL Process completed successfully SQL> select name,value,type from v$parameter where name='SORT_BUF_SIZE'; Line number NAME VALUE TYPE ---------- ------------- ----- ------- 1 SORT_BUF_SIZE 45 SESSION Newly created session II: select name,value,type from v$parameter where name='SORT_BUF_SIZE'; Line number NAME VALUE TYPE ---------- ------------- ----- ------- 1 SORT_BUF_SIZE 50 SESSION
The second method: function method
set up dm.ini Parameter values of non floating point and string types in the file Syntax: SP_SET_PARA_VALUE (scope int, ini_param_name varchar(256) ,value bigint) set up dm.ini Parameter value of floating point type in parameter Syntax: SP_SET_PARA_DOUBLE_VALUE (scope int, ini_param_name varchar(256),value double) set up dm.ini String type parameter value in file Syntax: SP_SET_PARA_STRING_VALUE (scope int, ini_param_name varchar(256) ,value varchar(8187)) In the above functions scope The values are 1 and 2. 1 indicates dm.ini The file and memory parameters are modified without restarting the server; 2 means only modify dm.ini File, which takes effect after the server is restarted. Set session level INI Value of parameter Syntax: SF_SET_SESSION_PARA_VALUE (paraname varchar(8187),value bigint) Reset session level INI Parameter value, so that the parameter value is consistent with the system level. Syntax: SP_RESET_SESSION_PARA_VALUE (paraname varchar(8187)) Modify system integer double,varchar Static configuration parameters or dynamic configuration parameters. Syntax: SF_SET_SYSTEM_PARA_VALUE (paraname varchar(256), value bigint/double/varchar(256),deferred int, scope int) deferred: Effective immediately. A value of 0 indicates the current session The modified parameter takes effect immediately. A value of 1 indicates the current value session It will not take effect. It will take effect later. The default value is 0. scope: The values are 0, 1 and 2. 0 means to modify parameter values in memory, 1 means to modify memory and INI Parameter values in the file, 0 and 1, can only modify dynamic configuration parameters. 2 indicates modification INI Parameters in the file, which can be used to modify static configuration parameters and dynamic configuration parameters
Example: modify the static parameter buffer to 800
select name,value,type from v$parameter where name like '%BUFFER%'; Line number NAME VALUE TYPE ---------- ------------------- ----- ------- 1 HUGE_BUFFER 8 IN FILE 2 BUFFER 760 IN FILE 3 BUFFER_POOLS 19 IN FILE 4 BUFFER_FAST_RELEASE 1 SYS 5 MAX_BUFFER 1520 IN FILE The modified value is 800 dm.ini File: call sp_set_para_value(2,'BUFFER',800); DMSQL Process completed successfully In file dm.ini View in: select sf_get_para_value(1,'buffer'); Line number SF_GET_PARA_VALUE(1,'buffer') ---------- ----------------------------- 1 800 To view the current value in memory: select sf_get_para_value(2,'buffer'); Line number SF_GET_PARA_VALUE(2,'buffer') ---------- ----------------------------- 1 760
Database restart:
Customs clearance: SQL> shutdown abort;
Warehouse startup:/etc/init.d/DmServiceDMSERVER start
Starting DmServiceDMSERVER: [ OK ]
Check the value in memory again for effect:
select sf_get_para_value(2,'buffer');
Line number SF_GET_PARA_VALUE(2,'buffer')
---------- -----------------------------
1 800
summary
This paper mainly introduces the types of parameters in Damon database and how to modify different types of parameters. When it comes to modification, it can be modified through command line and function.