常用巡检SQL

小虾米vivian

已于 2022-11-25 12:43:04 修改

阅读量476

点赞数 1

分类专栏：国产达梦文章标签：数据库

于 2022-11-25 12:41:49 首次发布

本文链接：https://blog.csdn.net/Penrosee/article/details/127503900

版权

国产达梦专栏收录该内容

35 篇文章 5 订阅

订阅专栏

本文主要是记录巡检常用的SQL，为方便查找做如下总结：

1. 查看归档文件路径信息：

select * from V$ARCH_FILE limit 10;

2.ASM磁盘组信息

select * from V$ASMGROUP;

3.查看ASM磁盘信息

select * from V$ASMDISK;

4.查看ASM文件

select * from V$ASMFILE;

5.查看历史报错信息

select
        top 50 b.USER_NAME,
        b.APPNAME         ,
        b.CLNT_IP         ,
        b.CLNT_TYPE       ,
        a.*
from
        V$RUNTIME_ERR_HISTORY a
left join V$SESSION_HISTORY b
on
        a.SESS_ID=b.SESS_ID

6.查看数据库配置信息

SELECT *
FROM
        V$DM_INI
WHERE
        PARA_NAME IN ( 'INSTANCE_NAME','MAX_OS_MEMORY','MEMORY_POOL','BUFFER','BUFFER_POOLS','MAX_BUFFER'
        ,'RECYCLE','RECYCLE_POOLS','HJ_BUF_GLOBAL_SIZE','HJ_BUF_SIZE','DICT_BUF_SIZE','TEMP_SIZE','VM_POOL_SIZE','SESS_POOL_SIZE','CACHE_POOL_SIZE','WORKER_THREADS','TASK_THREADS'
        ,'MAX_SESSION_STATEMENT','OPTIMIZER_MODE','VIEW_PULLUP_FLAG','COMPATIBLE_MODE','SVR_LOG','MAX_SESSIONS','USE_PLN_POOL','ENABLE_MONITOR','OLAP_FLAG','ARCH_INI')

7.查看归档配置信息

SELECT *,(SELECT SUM(LEN)/(1024*1024)*100 FROM V$ARCH_FILE)/arch_space_limit||'%' used_per FROM V$DM_ARCH_INI;

8.归档日志产生频率信息

备注：根据归档产生的时间，计算当日每小时产生归档的频率

SELECT
        LEFT(FIRST_TIME, 10) DAY                            ,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '00', 1, 0)) H00,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '01', 1, 0)) H01,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '02', 1, 0)) H02,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '03', 1, 0)) H03,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '04', 1, 0)) H04,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '05', 1, 0)) H05,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '06', 1, 0)) H06,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '07', 1, 0)) H07,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '08', 1, 0)) H08,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '09', 1, 0)) H09,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '10', 1, 0)) H10,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '11', 1, 0)) H11,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '12', 1, 0)) H12,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '13', 1, 0)) H13,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '14', 1, 0)) H14,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '15', 1, 0)) H15,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '16', 1, 0)) H16,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '17', 1, 0)) H17,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '18', 1, 0)) H18,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '19', 1, 0)) H19,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '20', 1, 0)) H20,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '21', 1, 0)) H21,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '22', 1, 0)) H22,
        SUM(DECODE(SUBSTR(FIRST_TIME, 12, 2), '23', 1, 0)) H23,
        COUNT(*) TOTAL
FROM
        V$ARCHIVED_LOG
WHERE   FIRST_TIME>=(SYSDATE-10)
GROUP BY
        LEFT(FIRST_TIME, 10)
ORDER BY
        LEFT(FIRST_TIME, 10) DESC;

9.超期未被删除的归档

select * from V$ARCH_FILE where CREATE_TIME<(SYSDATE-30);

10.授权信息

SELECT * FROM V$LICENSE;

11.数据库信息

SELECT *
,page/1024||'K' page_size
,SF_GET_EXTENT_SIZE() EXTENT_SIZE
,SF_GET_CASE_SENSITIVE_FLAG() CASE_SENSITIVE
,SF_GET_UNICODE_FLAG() UNICODE_FLAG
FROM V$DATABASE;

11.实例信息

SELECT * FROM V$INSTANCE;

12.表空间及表空间使用率信息

select c.*,d.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;

13.数据文件信息

select * from V$DATAFILE;

14.重做日志文件信息

SELECT
        A.FILE_ID    ,
        A.PATH       ,
        A.CLIENT_PATH,
        A.RLOG_SIZE  ,
        B.FREE_SPACE ,
        B.TOTAL_SPACE,
        B.CUR_FILE
from
        (
                select * from V$RLOGFILE  
        )
        A,
        (
                select * from V$RLOG  
        )
        B;

15.用户信息

select * from DBA_USERS;

16.用户权限信息

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;

17.作业信息

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;

18.作业调度信息

SELECT * FROM SYSJOB.SYSJOBSCHEDULES;

19.作业运行历史信息

SELECT *
FROM SYSJOB.SYSSTEPHISTORIES2 A
WHERE (SELECT COUNT(*)
          FROM SYSJOB.SYSSTEPHISTORIES2 B
         WHERE B.NAME = A.NAME
           AND B.EXEC_ID >= A.EXEC_ID) <= 10 and errcode <>0
ORDER BY A.START_TIME DESC,A.NAME;

20.DBLINK信息

select * from DBA_DB_LINKS;

21.序列信息

select * from DBA_SEQUENCES;

22.资源限制

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;

23.死锁记录

select * from V$DEADLOCK_HISTORY;

24.阻塞

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;

25.错误日志记录

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 SYSSTEPHISTORIES2.ERRCODE!=0)
WHERE RN<=10;

26.系统信息

select * from V$SYSTEMINFO;

27.字典缓存

select * from V$DB_CACHE;

28.数据缓冲池

select * from V$BUFFERPOOL;

29.内存池

select * from V$MEM_POOL;

30.系统统计信息

select * from V$SYSSTAT where classid in (11,5) order by classid desc;

31.会话统计

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;

32.对象统计

select tablespace_name,'TABLE_OF_TS' OBJTYPE,count(*) COUNTS from all_tables group by tablespace_name union all
select * from ( select owner,object_type,count(*) from all_objects 
where owner not in ('SYS','SYSTEM','SYSAUDITOR','SYSSSO','CTISYS') group by object_type,owner
order by 1,2);

33.表行数统计

begin
for rec in (select owner,table_name from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')) loop
call SP_TAB_STAT_INIT(rec.owner,rec.table_name);
end loop;
end;
/
select owner,table_name,tablespace_name,status,num_rows from all_tables where owner not in ('SYS','SYSAUDITOR','SYSSSO','CTISYS')
order by owner,num_rows desc;

34.最慢的20条SQL

SELECT SESS_ID	,SQL_ID,	SQL_TEXT,	EXEC_TIME,	FINISH_TIME	,N_RUNS,	SEQNO,	TRX_ID,	SESS_SEQ
FROM (SELECT SESS_ID	,SQL_ID,	SQL_TEXT,	EXEC_TIME,	FINISH_TIME	,N_RUNS,	SEQNO,	TRX_ID,	SESS_SEQ,
ROW_NUMBER() OVER (PARTITION BY sql_id ORDER BY exec_time DESC) RN 
FROM  V$LONG_EXEC_SQLS) WHERE RN=1 order by finish_time desc,exec_time desc;

35.高内存的20条SQL

SELECT TOP 20 SESS_ID,SQL_ID	,SQL_TEXT	,MEM_USED_BY_K	,FINISH_TIME	,N_RUNS,	SEQNO	,TRX_ID,	SESS_SEQ
FROM (SELECT SESS_ID,SQL_ID	,SQL_TEXT	,MEM_USED_BY_K	,FINISH_TIME	,N_RUNS	,SEQNO	,TRX_ID,	SESS_SEQ,
ROW_NUMBER() OVER (PARTITION BY sql_id ORDER BY mem_used_by_k DESC) RN 
FROM  V$LARGE_MEM_SQLS) WHERE RN=1 order by mem_used_by_k DESC;

36.20条长耗时等待事件

SELECT top 20 * FROM V$SYSTEM_EVENT ORDER BY TOTAL_WAITS DESC;

37.50条大存储用户表和索引信息

SELECT 'USER_SPACE' USER_SPACE,USERNAME,'' TABLE_NAME,'' INDEX_NAME,(USER_USED_SPACE(USERNAME)*(PAGE/1024))/1024 USED_MB FROM ALL_USERS
UNION SELECT a.* FROM
(SELECT TOP 50 'TABLE_SPACE' TABLE_SPACE,OWNER,TABLE_NAME,'' INDEX_NAME,(TABLE_USED_SPACE(OWNER,TABLE_NAME)*(PAGE/1024))/1024 USED_MB FROM ALL_TABLES ORDER BY 5 DESC) a
UNION SELECT b.* FROM
(SELECT TOP 50 'INDEX_SPACE' INDEX_SPACE,A.OWNER,A.TABLE_NAME,A.INDEX_NAME,(INDEX_USED_SPACE(B.ID)*(PAGE/1024))/1024 USED_MB FROM ALL_INDEXES A,SYSOBJECTS B WHERE A.INDEX_NAME=B.NAME AND B.SUBTYPE$='INDEX'
ORDER BY 5 DESC) b

更多内容，请访问达梦社区地址：https:eco.dameng.com