【干货攻略】达梦数据库DM8的SQL性能优化思路浅谈系列（二）

SF_SET_SESSION_PARA_VALUE(‘MONITOR_SQL_EXEC’,1);

Select
'sP_close_session('
||sess_id
||');'                                          ,
datediff(ss, last_recv_time, sysdate) ss        ,
cast(sf_get_session_sql(sess_id) as varchar) sql,
*
from
v$sessions
where
state='ACTIVE'
order by
last_send_time;

--创建性能跟踪表
--DROP TABLE SQL_LOG;
CREATE TABLE SQL_LOG
(
GID         VARCHAR2(100),  --GUID
"USER_NAME" VARCHAR2(32) ,  --当前用户
"START_TIME" TIMESTAMP(0),  --SQL开始执行时间
"END_TIME" TIMESTAMP(0)  ,  --SQL执行结束时间
"SQL_TEXT"   VARCHAR2(4000),--SQL执行语句
"ROW_COUNT"  NUMBER(15) ,   --记录数
"LOG_TYPE"  VARCHAR2(20) ,  --完成是SUCCESSFUL,报错是FAILED
"SESSION_ID" NUMBER(38, 0) , --会话ID
"LOG_DETAIL" VARCHAR2(2000), --错误信息
"INT_STACK" VARCHAR2(4000),  ----堆栈信息 
"SQL_TIMES"    NUMBER(10, 2) --记录SQL执行时间
);
CREATE INDEX IND_SQL_LOG_SQLTEXT ON SQL_LOG(SQL_TEXT);
/
--创建跟踪过程
CREATE OR REPLACE PROCEDURE SQL_INFO("I_SQLSTART" IN VARCHAR2(32000))
AS
--说明：跟踪存储过程中某些慢SQL,跟踪大于0.1的SQL,根据场景可以改写
V_ROWCOUNT     INT;
V_TIME         DATE;             --定义时间
V_ERRMSG      VARCHAR2(5000);   --获取异常记录
V_SQLTEXT      VARCHAR2(32767);  --获取传递的记录
V_SQLTEXT_INS  VARCHAR2(9000);   --SQL记录
V_CURTIME      NUMBER(20,2);     --开始时间
V_USETIME      NUMBER(20,2);     --使用时间
V_SESSION      NUMBER(30);       --SID记录
V_INT_STACK   VARCHAR2(4000);
BEGIN
V_TIME:=SYSDATE;
V_CURTIME:=DBMS_UTILITY.GET_TIME;
V_SQLTEXT:=TRIM(I_SQLSTART);
WHILE INSTR(V_SQLTEXT,'  ')>0 LOOP
V_SQLTEXT:=REPLACE(V_SQLTEXT,'  ',' ');
END LOOP;
--执行主体EXECUTEIMMEDIATE
EXECUTE IMMEDIATE V_SQLTEXT;
V_ROWCOUNT:=SQL%ROWCOUNT;
V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURTIME)/100;
--这里调整多少秒的记录写入到SQL_LOG日志表中,这个可以嵌套到性能表里
IF V_USETIME>=0.1 THEN
V_SQLTEXT_INS:=TRIM(SUBSTRB(V_SQLTEXT,1,4000));--达梦单列VARCHAR2可以存储9000以上，如果SQL够长，我们可以改大些
V_SESSION:=SESSID;--会话SID
V_INT_STACK:=DBMS_UTILITY.FORMAT_CALL_STACK;
INSERT INTO SQL_LOG 
(GID,USER_NAME,START_TIME,END_TIME,SQL_TEXT,ROW_COUNT,LOG_TYPE,SESSION_ID,INT_STACK,SQL_TIMES)
VALUES (GUID(),USER,V_TIME,SYSDATE,V_SQLTEXT_INS,V_ROWCOUNT,'SUCCESSFUL',V_SESSION,V_INT_STACK,V_USETIME);
END IF;
EXCEPTION
--如果SQL中执行异常，也会写SQL_LOG中，方便我们分析错误语句
WHEN OTHERS THEN
ROLLBACK;
V_ERRMSG:=SQLERRM(SQLCODE);
V_INT_STACK:=DBMS_UTILITY.FORMAT_CALL_STACK;
V_SQLTEXT_INS:=TRIM(SUBSTRB(I_SQLSTART,1,4000));--达梦VARCHAR2可以存储9000以上，如果SQL够长，我们可以改大些
V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURTIME)/100;
INSERT INTO SQL_LOG 
(GID,USER_NAME,START_TIME,END_TIME,SQL_TEXT,LOG_TYPE,SESSION_ID,LOG_DETAIL,INT_STACK,SQL_TIMES)
VALUES (GUID(),USER,V_TIME,SYSDATE,V_SQLTEXT_INS,'FAILED',V_SESSION,V_ERRMSG,V_INT_STACK,V_USETIME);
COMMIT;
RAISE;  
END SQL_INFO;