DM达梦数据库的常用性能诊断语句

 

-- 查询活跃会话
SELECT *
FROM V$SESSIONS
WHERE STATE='ACTIVE';


--查询已执行超过 2 秒的活动 SQL。
SELECT* FROM (
SELECT SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_RECV_TIME,SYSDATE) Y_EXETIME,
        SF_GET_SESSION_SQL(SESS_ID) FULLSQL,CLNT_IP
FROM V$SESSIONS WHERE STATE='ACTIVE')
WHERE Y_EXETIME>=2;


-- 慢SQL
SELECT ls.Sess_id, ls.sql_id, ls.* FROM V$LONG_EXEC_SQLS ls WHERE ls.sql_text LIKE '%xxxx%' ORDER BY ls.finish_time desc;


-- 查询阻塞。
WITH LOCKS AS
         (SELECT O.NAME, L.*, S.SESS_ID, S.SQL_TEXT, S.CLNT_IP,S.LAST_SEND_TIME
            FROM V$LOCK L, SYSOBJECTS O, V$SESSIONS S
           WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID),
      LOCK_TR AS
          (SELECT TRX_ID WT_TRXID, TID BLK_TRXID
            FROM LOCKS
           WHERE BLOCKED = 1),
      RES AS
          (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,
                 T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,
                 SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,
                 DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,
                 T1.SQL_TEXT WT_SQL, T2.SQL_TEXT BLK_SQL
            FROM LOCK_TR S, LOCKS T1, LOCKS T2
           WHERE     T1.LTYPE = 'OBJECT'
                 AND T1.TABLE_ID <> 0
                 AND T2.LTYPE = 'OBJECT'
                 AND T2.TABLE_ID <> 0
                 AND S.WT_TRXID = T1.TRX_ID
                 AND S.BLK_TRXID = T2.TRX_ID)
SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID, BLK_SQL
FROM RES;


-- 查询内存中的执行计划
SELECT * FROM V$SQL_STAT;
SELECT * FROM V$SQL_PLAN sp WHERE sp.sql_id = 8325105;
SELECT * FROM V$SQL_PLAN_NODE sp WHERE sp.sql_id = 8325105;        -- DEPTH: 节点层次;    CARD: 行数
SELECT * FROM V$SQL_SUBPLAN sp WHERE sp.sql_id = 8325105;
SELECT * FROM V$SQL_PLAN_DCTREF sp WHERE sp.sql_id = 8325105;

-- 清空指定SQL的执行计划
select * from v$cachepln where sqlstr LIKE '%table_tkk%';
SP_CLEAR_PLAN_CACHE(281071332652960);

SELECT * FROM V$SQL_STAT_HISTORY;
SELECT * FROM v$SQL_HISTORY sh ORDER BY sh.start_time;


SELECT ls.Sess_id, ls.sql_id, ls.* FROM V$LONG_EXEC_SQLS ls WHERE ls.sql_text LIKE '%xxxx%' ORDER BY ls.finish_time desc;
SELECT sh.EXEC_ID, sh.* FROM v$SQL_HISTORY sh WHERE  sh.sess_id = 281064270463272 AND sh.sql_id = 8325105 ;

-- 查询内存中的执行计划
SELECT     N.NAME, TIME_USED, N_ENTER, n.*, h.*
FROM     V$SQL_NODE_NAME N, V$SQL_NODE_HISTORY H
WHERE N.TYPE$ = H.TYPE$
     AND EXEC_ID = 628528422;

/*
     ET参数：EXEC_ID
    
     ET结果说明：
         OP: 操作符
         TIME(us): 时间开销，单位为微秒
         PERCENT: 执行时间占总时间百分比
         RANK: 执行时间耗时排序
         SEQ: 执行计划节点号
         N_ENTER: 进入次数
*/
CALL ET(628530596);   -- EXEC_ID； 



SELECT * FROM  V$PARAMETER p WHERE p.Name LIKE '%MONITOR_SQL_EXEC%';
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC', 1);


-- 查询大表
SELECT t.num_rows, t.sample_size, t.last_analyzed, t.*
FROM dba_tables t
WHERE t.num_rows >0
ORDER BY t.num_rows desc;

-- 查询字段
SELECT *
FROM user_tab_columns t
WHERE t.table_name = 'tableName'
ORDER BY t.column_id;


--查询索引
SELECT i.num_rows, i.sample_size, i.last_analyzed, i.*
FROM dba_indexes i
WHERE i.table_name = 'tableName';

SELECT * FROM dba_ind_columns c
WHERE c.table_name like 'TKK2%'
ORDER BY c.table_name, c.index_name, c.COLUMN_POSITION;

select para_name,para_value from v$dm_ini where para_name ='CLOB_LIKE_MAX_LEN';



-- 更新统计信息。
DBMS_STATS.TABLE_STATS_SHOW('schemaName','tableName');
DBMS_STATS.GATHER_TABLE_STATS('schemaName','tableName',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_INDEX_STATS('schemaName','tableName');


--AWR 日志诊断
--启用系统包和 AWR 包：
CALL SP_INIT_AWR_SYS(1);
CALL SP_CREATE_SYSTEM_PACKAGES(1);

--查询 AWR 快照：
SELECT * FROM SYS.WRM$_SNAPSHOT;

--设置快照间隔，如果不设置快照间隔，手动执行快照后 SYS.WRM$_SNAPSHOT 视图中没有记录：
CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(50);

--在两个时间点分别手动创建快照，或者等待系统自动生成。
--10:00时创建第一快照：
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

--30分钟后再创建一个，10:30：
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

--查询 AWR 快照：
SELECT * FROM SYS.WRM$_SNAPSHOT;

--创建AWR报告，SYS.AWR_REPORT_HTML(快照ID1,快照ID2,'AWR报告存放路径','AWR报告名称.HTLM')：
SYS.AWR_REPORT_HTML(1,2,'C:\','AWR1.HTML');