达梦数据库DM8开启sql日志

如果sqllog.ini文件存在，且相关配置无错误（sqllog.ini文件默认存在），且数据库SVR_LOG_NAME参数的值与sqllog.ini中模块名相同（默认为SLOG_ALL） ，将数据库SVR_LOG参数设置为1打开SQL日志，此时记录SQL日志属性以sqllog.ini中配置的为准。
如果sqllog.ini文件不存在或者 sqllog.ini 配置错误，SVR_LOG参数设置为1打开SQL日志，SQL日志属性以dm.ini里面配置的相关参数为准

1打开SQL日志记录功能

1.1第一种方法
1.1.1查看数据库相关参数

SQL> SELECT para_name,para_value,sess_value,file_value FROM V$DM_INI where para_name in (‘SVR_LOG’,‘SVR_LOG_NAME’);

#SVR_LOG=0表示未打开SQL日志

1.1.2配置sqllog.ini

注意：只有把 INI 参数 SVR_LOG 置为 1， 且 SVR_LOG_NAME 为 SLOG_ALL 时，sqllog.ini 中名称为 SLOG_ALL 的配置块才会生效。 若 SVR_LOG 为 1，但不存在sqllog.ini 或 sqllog.ini 配置错误，则仍旧使用 dm.ini 中的相关参数。

1.1.3设置SVR_LOG参数为1，打开SQL日志

SQL> SP_SET_PARA_VALUE(1,‘SVR_LOG’,1);
DMSQL executed successfully
used time: 13.123(ms). Execute id is 205.

1.1.4使配置生效

在线修改sqllog.ini文件后，可以调用过程SP_REFRESH_SVR_LOG_CONFIG() 对后面新的会话生效。

1.2 第二种方法

除了配置sqllog.ini 以外，还可以配置dm.ini中相关数据库参数。但是需要注意的是，如果sqllog.ini配置文件存在且配置正确，那么SQL日志以sqllog.ini中的配置来记录符合条件的SQL信息。

1.2.1 设置相关数据库参数

#记录SQL的类型
SF_SET_SYSTEM_PARA_VALUE(‘SQL_TRACE_MASK’,‘7:22:25:28’,0,1);
#异步记录SQL日志
SF_SET_SYSTEM_PARA_VALUE(‘SVR_LOG_ASYNC_FLUSH’,1,0,1);
#记录执行时间超过20ms的SQL语句
SF_SET_SYSTEM_PARA_VALUE(‘SVR_LOG_MIN_EXEC_TIME’,20,0,1);
#总共记录多少个日志文件
SF_SET_SYSTEM_PARA_VALUE(‘SVR_LOG_FILE_NUM’,20,0,1);

SF_SET_SYSTEM_PARA_VALUE用与修改系统整型、double、varchar的静态配置参数或动态配置参数。

有4个参数：
第一个参数为ini 参数的参数名
第二个参数为要设置的新值
第三个参数为是否立即生效。为 0 表示当前 session 修改的参数立即生效，为 1 表示当前 session 不生效，后续再生效，默认为 0。
第四个参数为scope，取值为 0、1、2 。0表示修改内存中的参数值，1表示修改内存和INI文件中参数值，0和1都只能修改动态的配置参数。2表示修改INI文件中参数，此时可用来修改静态配置参数和动态配置参数。

1.2.2 设置SVR_LOG参数为1，打开SQL日志

SP_SET_PARA_VALUE(1,‘SVR_LOG’,1);
2验证日志生成情况
2.1 执行sql语句
SQL> create table tt as select * from v$datafile;
executed successfully
used time: 20.260(ms). Execute id is 217.
SQL> select * from tt;

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

1 0 0 /home/dmdba/dmdbms/data/DAMENG/SYSTEM.DBF SYSTEM.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 05:06:38
38408 2752 2221 1616 184
35 16384 184 35 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

2 1 0 /home/dmdba/dmdbms/data/DAMENG/ROLL.DBF ROLL.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 05:06:38
38408 8192 7025 1456 20
1137 16384 20 1137 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

3 3 0 /home/dmdba/dmdbms/data/DAMENG/TEMP.DBF TEMP.DBF
2021-02-13 19:45:21 1
2 2021-02-13 22:35:22
2021-02-13 19:45:21
38408 1664 1656 16 0
0 16384 0 0 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

4 4 0 /home/dmdba/dmdbms/data/DAMENG/MAIN.DBF MAIN.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 05:06:38
38408 8192 8175 96 6
8 16384 6 8 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

5 5 0 /home/dmdba/dmdbms/data/ITPUX.DBF /home/dmdba/dmdbms/data/ITPUX.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 06:24:51
38408 131072 131047 144 9
0 16384 9 0 0 0 0 NULL

used time: 2.138(ms). Execute id is 218.
SQL>
[dmdba@dmm DB_DAMENG_FULL_20210213_182944_094931]$ disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 12.137(ms)
disql V8
SQL> select * from tt;

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

1 0 0 /home/dmdba/dmdbms/data/DAMENG/SYSTEM.DBF SYSTEM.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 05:06:38
38408 2752 2221 1616 184
35 16384 184 35 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

2 1 0 /home/dmdba/dmdbms/data/DAMENG/ROLL.DBF ROLL.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 05:06:38
38408 8192 7025 1456 20
1137 16384 20 1137 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

3 3 0 /home/dmdba/dmdbms/data/DAMENG/TEMP.DBF TEMP.DBF
2021-02-13 19:45:21 1
2 2021-02-13 22:35:22
2021-02-13 19:45:21
38408 1664 1656 16 0
0 16384 0 0 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

4 4 0 /home/dmdba/dmdbms/data/DAMENG/MAIN.DBF MAIN.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 05:06:38
38408 8192 8175 96 6
8 16384 6 8 1 33554431 0 NULL

LINEID GROUP_ID ID PATH CLIENT_PATH

---

       CREATE_TIME                                                                                          STATUS$    
       ---------------------------------------------------------------------------------------------------- -----------
       RW_STATUS   LAST_CKPT_TIME                                                                                      
       ----------- ----------------------------------------------------------------------------------------------------
       MODIFY_TIME                                                                                         
       ----------------------------------------------------------------------------------------------------
       MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ          
       -------------------- -------------------- -------------------- -------------------- --------------------
       PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
       -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------

5 5 0 /home/dmdba/dmdbms/data/ITPUX.DBF /home/dmdba/dmdbms/data/ITPUX.DBF
2021-02-13 05:06:38 1
2 2021-02-13 22:35:22
2021-02-13 06:24:51
38408 131072 131047 144 9
0 16384 9 0 0 0 0 NULL

used time: 1.847(ms). Execute id is 304.
SQL> insert into tt as select * from v$datafile;insertintottasselect\ast fromv$datafile;

insert into tt as select * from v$datafile;
*
line 1, column 18, nearby [select] has error[-2007]:
Syntax error.
used time: 1.348(ms). Execute id is 0.
SQL> select * from TABLE_1;
select * from TABLE_1;
[-2106]:Error in line: 1
Invalid table or view name [TABLE_1].
used time: 1.749(ms). Execute id is 0.
SQL> select * from ITPUX.TABLE_1;

LINEID ---------- --------- –
1 9.000000 2 3.000000 3 18.000000 4 19.000000 5 14.000000 6 13.000000 7 12.000000 8 11.000000

                                8 rows got

used time: 7.335(ms). Execute id is 305.

2.2 验证日志

[dmdba@dmm dm]$ cd log/
[dmdba@dmm log]$ ls
dmsql_DMSERVER_20210213_224109.log
[dmdba@dmm log]$ vi dmsql_DMSERVER_20210213_224109.log
2021-02-13 22:41:09.814 (EP[0] sess:0x7f3924011ab8 thrd:3268 user:SYSDBA trxid:38411 stmt:0x7f39240342b0 appname:manager.exe ip:::ffff:192.168.3.2) [SEL] /Manager/select tab_obj_out., comment_obj.comment$ from
(select /+OPTIMIZER_OR_NBEXP(2)*/ TAB_OBJ.NAME, TAB_OBJ.ID, TAB_OBJ.SUBTYPE$,TA{B}_{O}BJ.INFO3,TA{B}_{O}BJ.SCHID,SC{H}_{O}BJ.NAMESCHNAME,TA{B}_{O}BJ.CRTDATE,INFO8,TA{B}_{O}BJ.INFO2\ast (PAGE/1024)/1024,TA{B}_{O}BJ.INFO1from(selectTA{B}_{O}B{J}_{I}NNER.NAME,TA{B}_{O}B{J}_{I}NNER.ID,TA{B}_{O}B{J}_{I}NNER.SUBTYPE$, TAB_OBJ_INNER.INFO1, TAB_OBJ_INNER.INFO2, TAB_OBJ_INNER.INFO3, TAB_OBJ_INNER.INFO8, TAB_OBJ_INNER.SCHID, TAB_OBJ_INNER.CRTDATE from SYS.SYSOBJECTS TAB_OBJ_INNER , SYS.SYSOBJECTS SCH_OBJ_INNER, SYS.SYSOBJECTS USER_OBJ_INNER where TAB_OBJ_INNER.type$ = ‘SCHOBJ’ and TAB_OBJ_INNER.INFO3&0x100000!=0x100000 and TAB_OBJ_INNER.INFO3&0x200000!=0x200000 and TAB_OBJ_INNER.INFO3 & 0x003F not in (0x0A, 0x20) and (TAB_OBJ_INNER.INFO3 & 0x100000000) = 0 and TAB_OBJ_INNER.NAME not like ‘CTI % _’ and TAB_OBJ_INNER.NAME not like ‘%$AU{X}^{\prime }andTA{B}_{O}B{J}_{I}NNER.NAMEnotlik{e}^{\prime }$_AUX’ and TAB_OBJ_INNER.NAME not like '%$ALO{G}^{\prime }andTA{B}_{O}B{J}_{I}NNER.SUBTYPE$ = ‘UTAB’ and (TAB_OBJ_INNER.PID=-1 or TAB_OBJ_INNER.PID=0) and TAB_OBJ_INNER.INFO3 & 0x003F != 13 and TAB_OBJ_INNER.SCHID = 150995944 and USER_OBJ_INNER.SUBTYPE$ = ‘USER’ and SCH_OBJ_INNER.ID = TAB_OBJ_INNER.SCHID and SCH_OBJ_INNER.PID = USER_OBJ_INNER.ID and SF_CHECK_PRIV_OPT(UID(), CURRENT_USERTYPE(), TAB_OBJ_INNER.ID, USER_OBJ_INNER.ID, USER_OBJ_INNER.INFO1, TAB_OBJ_INNER.ID) = 1) TAB_OBJ, (select ID, NAME from SYS.SYSOBJECTS where TYPE$=‘SCH’ and ID = 150995944) SCH_OBJ where TAB_OBJ.SCHID=SCH_OBJ.ID ) TAB_OBJ_OUT LEFT JOIN SYS.SYSTABLECOMMENTS COMMENT_OBJ ON TAB_OBJ_OUT.NAME = COMMENT_OBJ.TVNAME AND TAB_OBJ_OUT.SCHNAME = COMMENT_OBJ.SCHNAME order by TAB_OBJ_OUT.NAME; EXECTIME: 3(ms).
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~

3总结
经过测试，SQL日志优先使用sqllog.ini中的配置，可以在线修改sqllog.ini文件后，通过执行SP_REFRESH_SVR_LOG_CONFIG() 立即对后面新的会话生效。

在测试时，发现sqllog.ini配置的执行时间MIN_EXEC_TIME条件与disql中返回的执行已用时间有时候存在大概1ms内的误差，对于功能上来说可以忽略这点影响。

对于系统上线之前或者用于短时间的测试的情况，可以执行SP_SET_PARA_VALUE(1,‘SVR_LOG’,1);打开SQL日志，默认会捕获全部语句的全部记录，再通过相关工具导入SQL日志进行分析。