如果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
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
,TABOBJ.INFO3,TABOBJ.SCHID,SCHOBJ.NAMESCHNAME,TABOBJ.CRTDATE,INFO8,TABOBJ.INFO2∗(PAGE/1024)/1024,TABOBJ.INFO1from(selectTABOBJINNER.NAME,TABOBJINNER.ID,TABOBJINNER.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′andTABOBJINNER.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′andTABOBJINNER.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日志进行分析。