达梦DBA日常运维

1.生成AWR数据库运行报告。

1、启用AWR包：
call  SP_INIT_AWR_SYS(1);
2、查询AWR快照：
SELECT  *  FROM  SYS.WRM$_SNAPSHOT;
3、设置快照间隔，如果不设置快照间隔，手动执行快照后SYS.WRM$_SNAPSHOT视图中没有记录：
CALL  DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(50);
--该间隔为50分钟生成一次
4、在两个时间点分别手动创建快照，或者等待系统自动生成：
10:00时创建第一快照：
call  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
30分钟后再创建一个，10:30，
call  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
5、查询AWR快照：
SELECT  *  FROM  SYS.WRM$_SNAPSHOT;
6、创建AWR报告，SYS.AWR_REPORT_HTML(快照ID1,快照ID2,'AWR报告存放路径','AWR报告名称.HTLM');：
SYS.AWR_REPORT_HTML(1,2,'C:\','AWR1.HTML');
7、关闭AWR包：
call  SP_INIT_AWR_SYS(0);
--由于AWR报告主要用于分析一定周期内的数据库运行情况，在使用完后建议选择关闭；

2.查看当前数据库环境下慢于5秒的SQL；

select * from (
SELECT sess_id,sql_text,datediff(ss,last_send_time,sysdate) ss,
       SF_GET_SESSION_SQL(SESS_ID) fullsql
 FROM V$SESSIONS WHERE STATE='ACTIVE' and sess_id <> sessid())
 where ss>=1;
--ss>=1表示1秒及以上的慢SQL，可以替换成其他时间；

3.当出现死锁的情况，可以通过执行一下SQL；

 select O.OBJECT_NAME ,S.SESS_ID,S.SQL_TEXT,L.ADDR,L.TRX_ID,L.LTYPE,L.LMODE,L.BLOCKED,L.TABLE_ID,L.ROW_IDX
 FROM V$LOCK L,DBA_OBJECTS O,V$SESSIONS S
 WHERE L.TABLE_ID=O.OBJECT_ID AND L.TRX_ID=S.TRX_ID AND L.BLOCKED=1;
--清除阻塞SQL；
 SP_CLOSE_SESSION(上述查到的SESS_ID);

查看数据库状态

select * from  v$instance;

查看慢sql

SELECT * FROM (
SELECT SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) SS,
       SF_GET_SESSION_SQL(SESS_ID) FULLSQL
 FROM V$SESSIONS WHERE STATE='ACTIVE')
 WHERE SS>=100;

查看session对应的用户名

select user_name from v$Sessions where SESS_ID='139558996491000';

查看会话登陆情况，当前执行sql

select * from v$sessions;

清除当前活跃会话

select  'SP_CLOSE_SESSION('''||SESS_ID||''');' from v$Sessions where state='ACTIVE';

查询行锁情况

select * from v$lock where blocked=1;

查询行锁情况

SELECT 
s.sess_id sid,
s.sess_seq,
l.lmode 锁模式,
s.user_name 登录用户,
s.osname 机器名,
s.clnt_host 终端用户名,
o.object_name 被锁对象名,
s.create_time 登录数据库时间
FROM sys.v$lock l, sys.all_objects o, sys.v$sessions s
WHERE l.table_id = o.object_id
AND l.trx_id = s.trx_id
ORDER BY l.trx_id, s.sess_seq

批量结束等待会话

SELECT 'SP_CLOSE_SESSION('''||SESS_ID||''');' FROM V$SESSIONS WHERE TRX_ID IN (SELECT WAIT_FOR_ID FROM V$TRXWAIT);

查看当前活跃会话执行sql

select * from v$sessions where state ='ACTIVE';

批量结束锁

SELECT 'SP_CLOSE_SESSION('''||SESS_ID||''');' FROM V$SESSIONS WHERE TRX_ID IN (SELECT TRX_ID FROM V$LOCK WHERE BLOCKED=1);

查看等待事物对应session

select * from v$sessions where trx_id in (select wait_for_id from v$trxwait);

查看等待事物

select * from v$trxwait;

查看数据库死锁

select * from v$deadlock_history;

查看行锁数量

select O.OBJECT_NAME ,S.SESS_ID,S.SQL_TEXT,L.ADDR,L.TRX_ID,L.LTYPE,L.LMODE,L.BLOCKED,L.TABLE_ID,L.ROW_IDX
 FROM V$LOCK L,DBA_OBJECTS O,V$SESSIONS S
 WHERE L.TABLE_ID=O.OBJECT_ID AND L.TRX_ID=S.TRX_ID AND L.BLOCKED=1;

查看会话ID对应的username

select user_name from v$sessions where sess_id=139547382082088;

查看当前事物量

select count(*) from v$trx;

结束会话

SP_CLOSE_SESSION('139546978331000');

批量杀持有锁会话

select 'SP_CLOSE_SESSION(`'||S.SESS_ID ||'`)'
 FROM V$LOCK L,DBA_OBJECTS O,V$SESSIONS S
 WHERE L.TABLE_ID=O.OBJECT_ID AND L.TRX_ID=S.TRX_ID AND L.BLOCKED=1;

查看慢sql

select * from (
SELECT sess_id,sql_text,datediff(ss,last_send_time,sysdate) ss,
       SF_GET_SESSION_SQL(SESS_ID) fullsql
 FROM V$SESSIONS WHERE STATE='ACTIVE' and sess_id <> sessid())
 where ss>=100;

查看行锁阻塞时间

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,row_idx 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 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 from res order by ss desc;

查看数据库的数据量

select name,total_size from v$database;

查看表空间使用率

select
        total.tablespace_name         ,
        round(total.MB, 2)           as Total_MB,
        round(total.MB - free.MB, 2) as Used_MB ,
        (round((total.MB-free.MB)/total.MB, 2))*100 ||'%'"USED_RATE" ,
        100-((round((total.MB-free.MB)/total.MB, 2))*100)||'%' "FREE_RATE"
from
        (
                select
                        tablespace_name,
                        sum(bytes) / 1024 / 1024 as MB
                from
                        dba_free_space
                group by
                        tablespace_name
        )
        free,
        (
                select
                        tablespace_name,
                        sum(bytes) / 1024 / 1024 as MB
                from
                        dba_data_files
                group by
                        tablespace_name
        )
        total
where
        free.tablespace_name = total.tablespace_name;

表空间扩容

alter tablespace  BASE_DATA add datafile '/dmdata/DAMENG/base_data14.dbf' size 77727 autoextend on maxsize 33554431

查看主库日志号

select file_lsn,cur_lsn from v$rlog;