达梦数据库巡检

巡检命令语句

-- 查询数据库实例信息(实例名,实例模式)
SELECT INSTANCE_NAME,MODE$ FROM V$INSTANCE;
-- 活动会话数
SELECT COUNT(1) FROM V$SESSIONS WHERE STATE='ACTIVE' UNION ALL
SELECT COUNT(1) FROM V$SESSIONS;
-- 事务等待
SELECT * FROM V$TRXWAIT;
-- 查询所有用户；
SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS;
-- 查询系统中所有用户的数据库对象权限信息
-- 非系统用户权限信息
SELECT * 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
)
WHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR') )
ORDER BY GRANTEE,PRIVILEGE_TYPE,PRIVILEGE;
--作业调度信息
SELECT * FROM SYSJOB.SYSJOBSCHEDULES;
--作业运行历史信息
SELECT * FROM SYSJOB.SYSSTEPHISTORIES2 A WHERE (SELECT COUNT(*) FROM SYSJOB.SYSSTEPHISTORIES2 B WHERE B.NAME = A.NAME AND B.EXEC_ID >= A.EXEC_ID) <= 1 ORDER BY A.START_TIME DESC,A.NAME;
--资源限制信息
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;
-- 死锁记录
SELECT * FROM V$DEADLOCK_HISTORY;
-- 错误日志记录
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;
-- DBLINK
SELECT * FROM DBA_DB_LINKS;
 
SELECT SF_GET_PAGE_SIZE ();
SELECT SF_GET_EXTENT_SIZE ();
SELECT SF_GET_CASE_SENSITIVE_FLAG();
SELECT SF_GET_UNICODE_FLAG ();
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='LENGTH_IN_CHAR' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='BLANK_PAD_MODE' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='MEMORY_POOL' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='HUGE_BUFFER' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='BUFFER' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='WORKER_THREADS' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='TASK_THREADS' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='IO_THR_GROUPS' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='CHECK_DB_IS_ACTIVE' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='ENABLE_SPACELIMIT_CHECK' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='ENABLE_MONITOR' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='FAST_COMMIT' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='USE_PLN_POOL' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='RS_CAN_CACHE' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='SVR_LOG' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='SVR_LOG_ASYNC_FLUSH' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='SVR_LOG_FILE_NUM' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='HA_INST_CHECK_IP' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='HA_INST_CHECK_PORT';
--修改FAST_COMMIT;
--SP_SET_PARA_VALUE (1,'FAST_COMMIT',0); 
--SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='FAST_COMMIT';
--OLTP参数查询
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='BUFFER_POOLS' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='FAST_POOL_PAGES' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='FAST_ROLL_PAGES' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='MULTI_PAGE_GET_NUM' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='SESS_PLN_NUM' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='CLT_CONST_TO_PARAM' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='CKPT_RLOG_SIZE' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='CKPT_DIRTY_PAGES' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='CKPT_INTERVAL' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='CKPT_FLUSH_RATE' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='CKPT_FLUSH_PAGES' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='FORCE_FLUSH_PAGES' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='DIRECT_IO' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='BDTA_SIZE' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='OLAP_FLAG' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='FAST_RELEASE_SLOCK' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='NOWAIT_WHEN_UNIQUE_CONFLICT' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='UNDO_EXTENT_NUM' UNION ALL
SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='UNDO_RETENTION';
-- 数据库最慢的20条 SQL
SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
-- 数据库近20条慢 SQL
SELECT TOP 20 SQL_TEXT,EXEC_TIME,FINISH_TIME,N_RUNS FROM V$LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;
-- 数据库高内存的20 SQL
SELECT TOP 20 SQL_TEXT  FROM  V$SYSTEM_LARGE_MEM_SQLS ORDER BY MEM_USED_BY_K DESC;
-- 数据库高MTAB的20 SQL
SELECT TOP 20 SQL_TEXT FROM V$MTAB_USED_HISTORY;
-- 数据库排序页最多的SQL
SELECT TOP 20 SQL_TEXT FROM V$SORT_HISTORY;
-- 数据库HASH连接最多的SQL
SELECT TOP 20 SQL_TEXT FROM V$HASH_MERGE_USED_HISTORY;
--查询最近的sql执行记录
select * from v$sql_history;
--查询某个用户下所有的表
select * from user_tables;  --查询当前用户下所有的表
select * from all_tables where owner=’TEST’;  --dba用户查询某个模式下的所有表
--查询某个用户下所有表字段
select * from all_tab_cols where owner=’TEST’;
--查看表注释
select * from ALL_TAB_COMMENTS where ower=’TEST’;
--查看字段注释
select * from ALL_COL_COMMENTS where ower=’TEST’;
--查询数据库版本
select * from v$version;
--查询授权信息
select * from v$license;
--查询服务器信息
select * from V$SYSTEMINFO;
--查询会话连接信息
select * from v$sessions;
select count(*),state from v$sessions group by state;
select count(*),clnt_ip from v$sessions group by clnt_ip;
--查看数据库服务器配置参数
select * from v$dm_ini;

CPU占用率

top  

内存使用率

free -m   top  MEM

操作系统及版本

cat /proc/version或lsb_release -a

内存

cat /proc/meminfo

cpu

cat /proc/cpuinfo或lscpu

磁盘空间

df –h

总结主要包含如下内容：
数据库正常运行状况：正常运行
性能良好，没有死锁产生
备份文件状况：不存在备份文件
代理作业状况：没有设置定时任务
服务器硬件状况：存储设备正常，cpu/内存正常，网络正常。
查看数据库进程：

ps –ef | grep dmdbms
ps –ef | grep dmserver

查看数据库进程所占用文件句柄数：

lsof -p 45423 | wc –l

查看数据库进程所占用文件句柄数：

netstat -antp|grep dmserver|grep ESTABLISHED|wc -l

LINUX常用性能监控命令
使用top命令查看cpu使用率
使用iostat命令查看磁盘I/O使用情况
使用dstat工具查看磁盘I/O使用情况
使用free命令查看内存使用情况
使用nmon工具监控系统一段时间的整体情况
使用perf top命令查看系统热点情况