达梦数据库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’);

达梦数据库DM8开启sql日志

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

1.1.2配置sqllog.ini

达梦数据库DM8开启sql日志

注意:只有把 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 d a t a f i l e ; i n s e r t i n t o t t a s s e l e c t ∗ f r o m v datafile; insert into tt as select * from v datafile;insertintottasselect∗fromvdatafile;

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 , T A B O B J . I N F O 3 , T A B O B J . S C H I D , S C H O B J . N A M E S C H N A M E , T A B O B J . C R T D A T E , I N F O 8 , T A B O B J . I N F O 2 ∗ ( P A G E / 1024 ) / 1024 , T A B O B J . I N F O 1 f r o m ( s e l e c t T A B O B J I N N E R . N A M E , T A B O B J I N N E R . I D , T A B O B J I N N E R . S U B T Y P E , TAB_OBJ.INFO3, TAB_OBJ.SCHID, SCH_OBJ.NAME SCHNAME, TAB_OBJ.CRTDATE, INFO8, TAB_OBJ.INFO2*(PAGE/1024)/1024, TAB_OBJ.INFO1 from (select TAB_OBJ_INNER.NAME, TAB_OBJ_INNER.ID, TAB_OBJ_INNER.SUBTYPE ,TABO​BJ.INFO3,TABO​BJ.SCHID,SCHO​BJ.NAMESCHNAME,TABO​BJ.CRTDATE,INFO8,TABO​BJ.INFO2∗(PAGE/1024)/1024,TABO​BJ.INFO1from(selectTABO​BJI​NNER.NAME,TABO​BJI​NNER.ID,TABO​BJI​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 ‘% A U X ′ a n d T A B O B J I N N E R . N A M E n o t l i k e ′ AUX' and TAB_OBJ_INNER.NAME not like '% AUX′andTABO​BJI​NNER.NAMEnotlike′_AUX’ and TAB_OBJ_INNER.NAME not like '% A L O G ′ a n d T A B O B J I N N E R . S U B T Y P E ALOG' and TAB_OBJ_INNER.SUBTYPE ALOG′andTABO​BJI​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日志进行分析。

上一篇:java内部类


下一篇:内部类的访问特点。