达梦数据库主要监测指标
1. 数据库信息
|
select * from ( SELECT a.*, page/1024 ||'K' page_size, SF_GET_EXTENT_SIZE() ||'K' EXTENT_SIZE , decode(SF_GET_CASE_SENSITIVE_FLAG(), 1, '是', 0, '否') CASE_SENSITIVE, decode(SF_GET_UNICODE_FLAG(), 1, 'UTF8', 0, 'GB18030') UNICODE_FLAG, decode(SF_GET_LENGTH_IN_CHAR(), 1, '是', 0, '否') LENGTH_IN_CHAR FROM V$DATABASE a ); |
2. 归档配置信息
|
select * from ( SELECT A.*, ( SELECT SUM(LEN)/(1024*1024)*100 FROM V$ARCH_FILE ) /arch_space_limit ||'%' used_per FROM V$DM_ARCH_INI A ); |
3. 授权信息
|
SELECT * FROM V$LICENSE; |
4. 实例信息
|
SELECT * FROM V$INSTANCE; |
5. 表空间信息
|
select * from ( select c.*,d.used_per||'%' used_per from V$TABLESPACE c join( SELECT a.id,100-(sum(b.free_size)*100/sum(b.total_size)) used_per FROM V$TABLESPACE a,V$DATAFILE b where a.id=b.GROUP_ID group by a.id) d on c.id=d.id order by c.id); |
6. 数据文件信息
|
select * from V$DATAFILE; |
7. 重做日志信息
|
select * from ( SELECT A.FILE_ID , A.PATH , A.CLIENT_PATH, A.RLOG_SIZE , B.FREE_SPACE , B.TOTAL_SPACE, B.CUR_FILE from ( select a1.* from V$RLOGFILE a1 where true ) A, ( select a2.* from V$RLOG a2 where true ) B); |
8. 用户信息
|
select * from DBA_USERS; |
9. 用户权限信息
|
select * from ( SELECT a.* FROM ( SELECT GRANTEE , GRANTED_ROLE PRIVILEGE , 'ROLE_PRIVS' PRIVILEGE_TYPE, CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION FROM DBA_ROLE_PRIVS
UNION
SELECT GRANTEE , PRIVILEGE , 'SYS_PRIVS' PRIVILEGE_TYPE, ADMIN_OPTION FROM DBA_SYS_PRIVS
UNION
SELECT GRANTEE, PRIVILEGE ||' ON ' ||OWNER ||'.' ||TABLE_NAME PRIVILEGE , 'TABLE_PRIVS' PRIVILEGE_TYPE, GRANTABLE FROM DBA_TAB_PRIVS ) a WHERE GRANTEE IN ( SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS', 'SYSDBA', 'SYSSSO', 'SYSAUDITOR') ) ORDER BY GRANTEE , PRIVILEGE_TYPE, PRIVILEGE ); |
10. 用户资源限制信息
|
select * from ( select b.name, a.SESS_PER_USER, a.CONN_IDLE_TIME, a.FAILED_NUM, a.LIFE_TIME, a.REUSE_TIME, a.REUSE_MAX, a.LOCK_TIME, a.GRACE_TIME, a.LOCKED_STATUS, a.LASTEST_LOCKED, a.PWD_POLICY, a.RN_FLAG, a.ALLOW_ADDR, a.NOT_ALLOW_ADDR, a.ALLOW_DT, a.NOT_ALLOW_DT, a.LAST_LOGIN_DTID, a.LAST_LOGIN_IP, a.FAILED_ATTEMPS from SYSUSERS a,SYS.SYSOBJECTS b where a.id=b.id); |
11. 作业信息
|
select * from ( SELECT A.ID , A.NAME , A."ENABLE" , A.USERNAME , A.CREATETIME, A.MODIFYTIME, A.DESCRIBE , B.LAST_DATE ||' ' ||B.LAST_SEC LAST_TIME, B.NEXT_DATE ||' ' ||B.NEXT_SEC NEXT_TIME, B.WHAT FROM SYSJOB.SYSJOBS A, SYSJOB.USER_JOBS B WHERE A.ID=B.JOB ); |
12. 作业的运行历史信息
|
SELECT * FROM ( SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO FROM (SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY EXEC_ID DESC) RN FROM SYSJOB.SYSSTEPHISTORIES2) WHERE RN<=10);
|
13. 死锁记录查询
|
select * from V$DEADLOCK_HISTORY; |
14. 堵塞查询
|
select * from ( WITH TRX_TAB AS (SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0), TRX_SESS AS ( SELECT L.TRX_ID WT_TRXID, L.ROW_IDX BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID) WT_TABLE, S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS, S1.USER_NAME WT_USER_NAME,S2.USER_NAME BLK_USER_NAME,S1.SQL_TEXT,S1.CLNT_IP,DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2 WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID) SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1); |
15. 内存池查询
|
select * from V$MEM_POOL; |
16. 数据缓冲池查询
|
select * from V$BUFFERPOOL; |
17. 数据字典缓存查询
|
select * from V$DB_CACHE; |
18. 统计信息查询
|
select * from V$SYSSTAT where classid in (11,5) order by classid desc; |
19. 会话信息查询
|
select * from ( SELECT STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME,COUNT(*) COUNTS FROM V$SESSIONS GROUP BY STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME ORDER BY STATE); |
20. 最慢的20条sql查询
|
SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC; |
21. 最消耗内存的20条sql查询
|
select * from V$SYSTEM_LARGE_MEM_SQLS order by mem_used_by_k desc; |
22. 最消耗IO的sql查询
|
select sess_id,top_sql_text,time_used,N_LOGIC_READ,N_PHY_READ from V$SQL_HISTORY order by N_PHY_READ desc; |
23. ASM磁盘组信息
|
select * from V$ASMGROUP; |
24. ASM磁盘信息
|
select * from V$ASMDISK; |
25. ASM文件信息
|
select * from V$ASMFILE; |
26. DSC节点信息查询
|
select * from v$DSC_EP_INFO; |
27. DCR配置信息查询
|
select * from V$DCR_INFO; |
28. 主备延迟查询（在备机上查询）
|
select DATEDIFF(MS,LAST_CMT_TIME,APPLY_CMT_TIME) from V$RAPPLY_STAT; |
