Dameng 8 parameter management

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.

Keywords: Database

Added by dtyson2000 on Wed, 12 Jan 2022 21:10:22 +0200