达梦8常用性能优化相关SQL

一、内存性能相关

1.1 查看数据库当前运行内存大小

select
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,
(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE
from  dual;

1.2 数据库系统运行过程中，大内存sql

select SQL_TEXT,MEM_USED_BY_K,FINISH_TIME,N_RUNS
from V$SYSTEM_LARGE_MEM_SQLS
order by mem_used_by_k desc limit 5;

1.3 正在执行的SQL使用内存大小

SELECT SESSID, MAX_MEM_USED,SQL_TXT
FROM V$SQL_STAT
order by MAX_MEM_USED DESC limit 5;

1.4 共享内存池大小(以M为单位)

select sum(total_size)/1024/1024 as mem_pool from v$mem_pool;

1.5 系统缓冲区大小(以M为单位)

select sum(n_pages * page_size)/1024/1024 as BUFFER_SIZE from v$bufferpool;

1.6 监控内存池

select name, 
       is_shared, 
       is_overflow, 
       org_size/1024.0/1024.0, 
       TOTAL_size/1024.0/1024.0, 
       RESERVED_SIZE/1024.0/1024.0,
       DATA_SIZE/1024.0/1024.0, 
       EXTEND_SIZE, 
       TARGET_SIZE, 
       N_EXTEND_NORMAL , 
       N_EXTEND_EXCLUSIVE 
from v$mem_pool 
order by TOTAL_size desc limit 5;

org_size/1024.0/1024.0 --内存池初始大小
TOTAL_size/1024.0/1024.0 --内存池总大小(包括扩展的) 
RESERVED_SIZE/1024.0/1024.0 --当前已分配大小(包括扩展的) 
DATA_SIZE/1024.0/1024.0 --实际有效字节

关注如下：
N_EXTEND_EXCLUSIVE 如果长期大于 0，说明长期从池外扩展，可能存在内存泄露，需要重点关注。
若 RESERVED_SIZE 比 org_size 小，说明内存池非常空闲，可以减小对应的初始内存，避免浪费。
若 TOTAL_size 比 TARGET_SIZE 大，说明内存池不够，经常向池外申请，需要把对应参数调大。

二、阻塞相关

2.1 查看数据库阻塞情况

SELECT SYSDATE STATTIME,DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) SS,
	'被阻塞的信息'   WT,S1.SESS_ID WT_SESS_ID,S1.SQL_TEXT WT_SQL_TEXT,S1.STATE WT_STATE,S1.TRX_ID WT_TRX_ID,
	S1.USER_NAME WT_USER_NAME,S1.CLNT_IP WT_CLNT_IP,S1.APPNAME WT_APPNAME,S1.LAST_SEND_TIME WT_LAST_SEND_TIME,
	'引起阻塞的信息' FM,S2.SESS_ID FM_SESS_ID,S2.SQL_TEXT FM_SQL_TEXT,S2.STATE FM_STATE,S2.TRX_ID FM_TRX_ID,
	S2.USER_NAME FM_USER_NAME,S2.CLNT_IP FM_CLNT_IP,S2.APPNAME FM_APPNAME,S2.LAST_SEND_TIME FM_LAST_SEND_TIME
 FROM V$SESSIONS S1,V$SESSIONS S2,V$TRXWAIT W
 WHERE S1.TRX_ID=W.ID
   AND S2.TRX_ID=W.WAIT_FOR_ID;

三、会话相关

查看当前活动会话时，若当前活动会话连接数量太大，则说明数据库当前可能存在以下异常情况： 当前业务繁忙，业务量太大；当前系统中存在慢 SQL；应用的重连机制存在缺陷。

查看当前非活动会话时，若当前非活动会话连接数量太大，说明数据库可能存在以下情况： 系统当前处于会话空闲期；连接池会话上线设置过高；应用释放连接机制存在异常。

3.1 查看当前活动会话信息

select * from V$SESSIONS where STATE='ACTIVE';
select count(*) from v$sessions where state='ACTIVE';
select count(*) from v$sessions where state='IDLE';
select count(*),substr(clnt_ip,8,20),state from v$sessions group by substr(clnt_ip,8,20),state

3.2 会话使用内存总量排序

SELECT
A.CREATOR ,
B.SQL_TEXT ,
SUM(A.TOTAL_SIZE)/1024/1024||'MB' TOTAL_SIZE, --当前总量(包括扩展)
SUM(A.DATA_SIZE) /1024/1024||'MB' DATA_SIZE --实际使用量
FROM
V$MEM_POOL A,
V$SESSIONS B
WHERE
A.CREATOR = B.THRD_ID
GROUP BY
A.CREATOR,
B.SQL_TEXT
ORDER BY
TOTAL_SIZE DESC;

3.3 当前使用内存过多的SQL

SELECT MAX_MEM_USED/1024||'MB' as MAX_MEM_USED, SQL_TXT 
FROM V$SQL_STAT 
order by MAX_MEM_USED DESC;

3.4 查看当前活跃会话的session信息

Select
'sP_close_session('
||sess_id
||');' ,
datediff(ss, last_recv_time, sysdate) ss ,
cast(sf_get_session_sql(sess_id) as varchar) sql,
*
from
v$sessions
where
state='ACTIVE'
order by
last_send_time

四、慢SQL

4.1 查看系统启动以来执行时间最长的10条SQL

SELECT TOP 10 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT
FROM V$SQL_HISTORY
ORDER BY TIME_USED DESC; 

4.2 查看慢SQL

SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;

4.3 查找出活动会话中执行时间大于 1S 的 SQL

select * from (
select timestampdiff(second,s.last_recv_time,sysdate) t ,s.* 
from v$sessions s  where state='ACTIVE')
where t > 1

4.4 最近一个小时已经执行过的最慢语句TOP20

有的时候某条SQL执行时间很短，毫秒级，收录不到v$long_exec_sqls视图里。但执行次数多，对系统造成的影响很大。这样的SQL应该优先进行优化。下面的语句显示最近一个小时内累计执行时间最多的SQL，统计SQL执行次数，单次执行时间，累计执行时间，累计执行时间占总时间的比例。

with SQL_HISTORY
as
(
select a.*
from v$sql_history a,v$session_history b
where a.START_TIME > sysdate - 1.0/24
and a.sess_id=b.sess_id and a.sess_seq=b.sess_seq
and b.user_name='SYSDBA' 
)
select sql_id,substr(top_sql_text,1,35)  || decode(substr(top_sql_text,36,1),'','',' .....') sql_text,sec_to_time(round((sum(time_used)+0.0)/1000000,3)) sql_time_total，
round((sum(time_used)+0.0) * 100/(select sum(time_used) from SQL_HISTORY ),2) "RATIO %"，
count(*) sql_execs,sec_to_time(round((sum(time_used)+0.0)/count(*)/1000000,5)) sql_time_per_exec,(min(time_used)+0.0)/1000000 second_min，(max(time_used)+0.0)/1000000 second_max
from SQL_HISTORY
group by sql_id,top_sql_text
order by 4 desc limit 20;

4.5 当前正在执行的最慢语句TOP20

v$long_exec_sqls、v$system_long_exec_sqls还有v$sql_history都只能显示已经执行完的语句。如果某条语句一直没有执行完，则无法统计到。这时就需要下面的语句

select clnt_ip,sec_to_time(datediff(ss,last_send_time,sysdate)) elapsed,appname,user_name,RUN_STATUS,sql_id,sql_text,last_send_time
from v$sessions where state in ('ACTIVE','WAIT')
order by elapsed desc
limit 20

4.6 统计最慢的SQL的执行节点耗时

可以分析出最慢的SQL中最耗时的执行节点

with SQL_HISTORY
as
(
select a.*
from v$sql_history a,v$session_history b
where a.START_TIME > sysdate - 1.0/24
and a.sess_id=b.sess_id and a.sess_seq=b.sess_seq
and b.user_name='SYSDBA' 
)
select a.sql_id,substr(a.top_sql_text,1,35)  || decode(substr(a.top_sql_text,36,1),'','',' .....') sql_text,
a.name,node_time_used/1000000.0 node_time,
a.execs,sql_time_used/1000000.0 sql_time,b.execs,
round(node_time_used*100.0/sql_time_used,2) "ratio %"
from
(select sql_id,top_sql_text,name,sum(b.time_used) node_time_used,count(*) execs
from SQL_HISTORY a,v$sql_node_history b,v$sql_node_name c
where  a.exec_id=b.exec_id and b.type$=c.type$
group by sql_id,top_sql_text,name
) a,
(
select sql_id,top_sql_text,sum(time_used) sql_time_used,count(*) execs
from SQL_HISTORY
group by sql_id,top_sql_text
) b
where a.sql_id=b.sql_id 
order by 4 desc 
limit 20

五、操作系统相关

5.1 查询占用cpu最多的线程

ps -eLo pcpu,pmem,pid,tid,psr,wchan:14,comm|grep DM进程号 |sort

六、诊断相关SQL

6.1 找到对应SQL的内存中的执行计划

select * from v$cachepln where upper(sqlstr) like '%SQL%';
#trace文件生成在data目录下的trace目录中
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME PLNDUMP,LEVEL 9658337440';

6.2 导出数据库内全部缓存的执行计划

select  'ALTER SESSION SET EVENTS ''IMMEDIATE TRACE NAME PLNDUMP,LEVEL '||cache_item||''';'  from v$cachepln ;

6.3 清空数据库执行计划缓存

select cache_item,sqlstr from v$cachepln
where sqlstr like '%insert into test2 select%' and sqlstr not like '%cachepln%';

call sp_clear_plan_cache();
#不加 pln 就是清理所有 sql 缓存
call sp_clear_plan_cache(pln号);

6.4 如何使用 DBMS_SQLTUNE 包获取 SQL 执行信息

DBMS_SQLTUNE 包提供一系列对实时 SQL 监控的方法。当 SQL 监控功能开启后，DBMS_SQLTUNE 包可以实时监控 SQL 执行过程中的信息，包括：执行时间、执行代价、执行用户、统计信息等情况。SQL 监控功能开启的方法是将 DM.INI 参数 ENABLE_MONITOR 和 MONITOR_SQL_EXEC 均设置为 1。

SQL> sf_set_session_para_value('MONITOR_SQL_EXEC',1);
SQL> set autotrace traceonly
SQL> select * from test;
已用时间: 0.195(毫秒). 执行号:57103.
SQL> set autotrace off
SQL> set long 99999
SQL> select dbms_sqltune.report_sql_monitor(sql_exec_id=>57103);

七、统计信息相关

7.1 收集表的统计信息

dbms_stats.gather_TABLE_stats('SYSDBA','TEST',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

7.2 统计信息查看

dbms_stats.table_stats_show('SYSDBA','TEST');
dbms_stats.column_stats_show('SYSDBA','TEST','NAME');
dbms_stats.index_stats_show('SYSDBA','IND_TEST');

7.3 清除统计信息

DBMS_STATS.DELETE_TABLE_STATS('SYSDBA','TEST')；

八、HINT相关