select * from v$dm_ini

-- 其中 para_type 类型的值有：

READ ONLY  只读，不能修改

SYS        系统级别，可以修改全局生效

IN FILE    只能在配置文件中修改

SESSION    会话级别

只有session 和 sys 类型可以在线更改， 其他类型更改需要重启

#修改系统级别的参数

alter system set 'MEMORY_LEAK_CHECK'=1 ;

或者

SP_SET_PARA_VALUE(1,'MEMORY_LEAK_CHECK',1);

#修改会话级别的参数

alter session set 'MONITOR_SQL_EXEC'=1 ;

或者

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);  

-- 动态打开会话级参数 MONITOR_SQL_EXEC，该设置只对本会话有效，其余会话无影响

-- 查询会话信息

select  A.SESS_ID,A.SQL_TEXT,A.STATE,A.N_USED_STMT,A.CURR_SCH,

A.USER_NAME,A.TRX_ID,A.CREATE_TIME,A.CLNT_TYPE,A.TIME_ZONE,A.OSNAME,A.CONN_TYPE, B.PROTOCOL_TYPE,B.IP_ADDR FROM  SYS.V$SESSIONS A,SYS.V$CONNECT B where  A.Sess_id= B.SADDR ORDER BY SF_GET_EP_SEQNO(A.rowid),A.Sess_id;

-- 或者

select  SF_GET_SESSION_SQL(ASESS_ID), -- 获取完整 sql

A.SQL_TEXT,A.STATE,A.N_USED_STMT,A.CURR_SCH,

A.USER_NAME,A.TRX_ID,A.CREATE_TIME,A.CLNT_TYPE,A.TIME_ZONE,A.OSNAME,A.CONN_TYPE, B.PROTOCOL_TYPE,B.IP_ADDR FROM  SYS.V$SESSIONS A,SYS.V$CONNECT B where  A.Sess_id= B.SADDR ORDER BY SF_GET_EP_SEQNO(A.rowid),A.Sess_id;

-- 会话中执行超过指定时间的语句

-- 查询执行效率慢的 sql

SELECT * FROM (

SELECT

sess_id,

sql_text,

datediff (ss, last_recv_time, SYSDATE) Y_EXETIME,

SF_GET_SESSION_SQL (SESS_ID) fullsql,

clnt_ip

FROM V$SESSIONS WHERE STATE = 'ACTIVE')

WHERE

Y_EXETIME >= 2;   -- 执行时间超 2s，可以自定义该时间

-- 查询当前所有会话数

select count(*) from v$sessions;

-- 查看当前数据库中活动会话

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

-- 查看当前非活动会话

select count(*) from v$sessions where state='IDLE';

-- 结束会话

sp_close_session(sess_id);

SELECT

        DS.SESS_ID "被阻塞的会话ID",

        DS.SQL_TEXT "被阻塞的SQL",

        DS.TRX_ID "被阻塞的事务ID",

        (CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",

        DS.CREATE_TIME "开始阻塞时间",

        SS.SESS_ID "占用锁的会话ID",

        SS.SQL_TEXT "占用锁的SQL",

        SS.CLNT_IP "占用锁的IP",

        L.TID "占用锁的事务ID"

FROM

        V$LOCK L

LEFT JOIN V$SESSIONS DS

ON

        DS.TRX_ID = L.TRX_ID

LEFT JOIN V$SESSIONS SS

ON

        SS.TRX_ID = L.TID

WHERE

        L.BLOCKED = 1

上述 SQL 语句中，“占用锁的会话 ID ”表示该会话占用这个对象的锁，且事务一直没有提交。导致“被阻塞的会话 ID ”无法对该对象上锁。可以参考以下解决方式：

方式一：杀掉占用锁的会话，释放锁。

SP_CLOSE_SESSION ( 占用锁的会话 ID );

方式二：杀掉被阻塞的会话。

SP_CLOSE_SESSION ( 被阻塞的会话 ID );

-- 或者下面的阻塞语句

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, TID 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;

select

        dh.trx_id ,

        sh.sess_id,

        wm_concat(top_sql_text)

from

        V$DEADLOCK_HISTORY dh,

        V$SQL_HISTORY sh

where

        dh.trx_id =sh.trx_id

    and dh.sess_id=sh.sess_id

group by

        dh.trx_id, sh.sess_id;

SELECT b.object_name, c.sess_id, a.*

FROM v$lock a, dba_objects b, v$sessions c

 WHERE a.table_id = b.object_id AND ltype = 'OBJECT' AND a.trx_id = c.trx_id;

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. BUFFER_SIZE：系统缓冲区大小，以 M 为单位。推荐值：系统缓冲区大小为可用物理内存的 60%～80%。有效值范围（8~1048576）

    2. MEM_POOL：共享内存池大小，以 M 为单位。共享内存池是由 DM 管理的内存。有效值范围：32 位平台为（642000），64 位平台为（6467108864）

    3. TOTAL_SIZE：BUFFER_SIZE 和 MEM_POOL 的总和。

内存不足常见原因有如下两种情况：

    1. memory_target 设置为 0，导致会话使用的内存未释放，可以考虑修改 memory_target 参数。

    2. 会话执行的 sql 消耗大量的内存，可以根据以下 sql 找到最占用内存的 sql，再进行 sql 优化。

SELECT "SESSID", MAX_MEM_USED||'KB',SQL_TXT FROM V$SQL_STAT 

order by MAX_MEM_USED DESC;

select  name ,stat_val/1024.0/1024.0 from  v$sysstat where CLASSID = 11 ;

SELECT

        A.CREATOR                          ,

        B.SQL_TEXT                         ,

        SUM(A.TOTAL_SIZE)/1024.0/1024.0 TOTAL_M,    -- 当前总量 (包括扩展)

        SUM(A.DATA_SIZE) /1024.0/1024.0 DATA_SIZE_M  -- 实际使用量

FROM

        V$MEM_POOL A,

        V$SESSIONS B

WHERE

        A.CREATOR = B.THRD_ID

GROUP BY

        A.CREATOR,

        B.SQL_TEXT

ORDER BY

        TOTAL_M DESC;

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;

在启动前查询上述语句，记录初始值。然后在内存增长的时候，再查询上述语句。

（2）打开 MEMORY_LEAK_CHECK

alter system set 'MEMORY_LEAK_CHECK'=1 ;

（3）查询 V$MEM_REGINFO 视图，关注 REFNUM 字段，若该字段值很大，则说明存在内存堆积的情况。

select * from V$MEM_REGINFO ORDER BY REFNUM DESC;

SELECT Upper(F.TABLESPACE_NAME)         "表空间名",

       D.TOT_GROOTTE_MB                 "表空间大小(M)",

       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')

       || '%'                           "使用比",

       F.TOTAL_BYTES                    "空闲空间(M)",

       F.MAX_BYTES                      "最大块(M)"

FROM   (SELECT TABLESPACE_NAME,

               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,

               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES

        FROM   SYS.DBA_FREE_SPACE

        GROUP  BY TABLESPACE_NAME) F,

       (SELECT DD.TABLESPACE_NAME,

               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB

        FROM   SYS.DBA_DATA_FILES DD

        GROUP  BY DD.TABLESPACE_NAME) D

WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER  BY 2 desc;

-- 临时表空间经常过大，说明内存设置过小或存在大量中间结果集存放，需要视情况进行优化。

-- 为了不影响磁盘空间的使用，可以通过 ini 参数 TEMP_SIZE 配置大小、TEMP_SPACE_LIMIT 设置上限、存储过程 SP_TRUNC_TS_FILE 来收缩 TEMP 表空间文件（生产环境请谨慎使用）。

SELECT TS.NAME, DF.PATH FROM V$TABLESPACE AS TS, V$DATAFILE AS DF WHERE TS.ID = DF.GROUP_ID;

select I.TABLE_OWNER,I.TABLE_NAME,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS FROM USER_INDEXES I,USER_OBJECTS O WHERE O.OBJECT_NAME=I.INDEX_NAME AND O.STATUS='INVALID';

select THREAD_ID,TRX_ID,WAIT_CLASS,WAIT_OBJECT,WAIT_START,WAIT_TIME, SPACE_ID,FILE_ID,PAGE_NO FROM V$WAIT_HISTORY;

SELECT employee_id,

                  employee_name,

                  job_title,

                  manager_id,

                  department_id,

                  LEVEL

             FROM dmhr.emp

       START WITH employee_id = 100

       CONNECT BY PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

100	Steven, King	President	NULL	90	1

101	Neena, Kochhar	Administration Vice President	100	90	2

108	Nancy, Greenberg	Finance Manager	101	100	3

109	Daniel, Faviet	Accountant	108	100	4

110	John, Chen	Accountant	108	100	4

111	Ismael, Sciarra	Accountant	108	100	4

112	Jose Manuel, Urman	Accountant	108	100	4

113	Luis, Popp	Accountant	108	100	4

可通过查看系统视图，及时发现活动会话中的慢 SQL，将其在计划缓存中的执行计划打印到 trc 文件中，核查是否与在管理工具中查询到的执行计划一致，偏差较大则清理缓存中执行计划。

具体方法如下：

通过 PLNDUMP 来看对应缓存中的 SQL 执行计划

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

select * from (

select timestampdiff(second,s.last_recv_time,sysdate) t ,s.* 

from v$sessions s  where state='ACTIVE')

where t > 1

    找到对应慢 SQL 对应的 cache_item 值。

select * from v$cachepln where upper(sqlstr) like '%SQL%'

    在 trace 目录中生成对应 trc 文件

alter session set events 'immediate trace name plndump ,level cache_item'

    对比管理工具的执行计划和 .trc 文件中的执行计划。

    清理内存中执行计划缓存。

call sp_clear_plan_cache();

call sp_clear_plan_cache(pln号)；--- 不加 pln 就是清理所有 sql 缓存。

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);  

-- 动态打开会话级参数 MONITOR_SQL_EXEC，该设置只对本会话有效，其余会话无影响

-- 查询语句

select * from tablename

-- 可以在消息栏看到：

[执行语句1]:

select * from tablename

执行成功, 执行耗时84毫秒. 执行号:97812

-- 点击执行号可以看到操作符的执行时间

DLCK	1	0.74%	5	0	2	0	0

PRJT2	3	2.22%	4	2	4	0	0

TOPN2	11	8.15%	3	3	4	0	0

NSET2	50	37.04%	2	1	3	0	0

CSCN2	70	51.85%	1	4	2	0	0

如上图所示，该 sql 语句共涉及 5 个操作符，按照执行的先后顺序分别是 CSCN2、NSET2、TOPN2、PRJT2、DLCK。

其中，耗时最长的操作符是 CSCN2，耗时 70 微秒，占总执行时间的 51.85%；耗时排第二位的操作符是 NSET2，耗时 50 微秒，占总执行时间的 37.04%。

根据上述的分析，我们就需要针对这 2 个操作符进行针对性的优化。

达梦的数据通过 B 树维护的。可对指定索引进行空间整理，重组数据，达到优化该表的目的。具体操作如下，以 test 为例介绍：

--- 首先查看 TEST 表的索引名

select name from sysobjects where pid=(select id from sysobjects where name='TEST' and type$='SCHOBJ')and subtype$='INDEX';

-- 结果

INDEX33568488

INDEX33568489

INDEX108643191852292

--- 重组 TEST 表的索引

SP_REORGANIZE_INDEX('SYSDBA','INDEX33568488');

-- 开启结果集缓存策略，可大大提升 sql 执行时间，达梦数据库如何开启结果集缓存

修改 dm.ini 参数；

vim /dmdata/DAMENG/dm.ini

## 设置参数值，开启结果集缓存

RS_CAN_CACHE=1

BUILD_FORWARD_RS=1

RS_CACHE_TABLES=HGTEST.YBCK_CBEC_ELIST_ITEM,HGTEST.YBCK_CBEC_ELIST

当 RS_CAN_CACHE 为 1 时，还可以通过设置 INI 参数 RS_CACHE_TABLES 和 RS_CACHE_MIN_TIME 对缓存的结果集进行限制和过滤。RS_CACHE_TABLES 指定可以缓存结果集的基表清单，只有查询涉及的所有基表全部在参数指定范围内，此查询才会缓存结果集。RS_CACHE_MIN_TIME 则指定了缓存结果集的查询语句执行时间的下限，只有实际执行时间不少于指定值的查询结果集才会缓存。

参数详情可以查看下方表格’参数说明‘

验证 dm.ini 修改是否正确；

select * from v$dm_ini where PARA_NAME in ('RS_CAN_CACHE','BUILD_FORWARD_RS','RS_CACHE_TABLES');

重启数据库生效。

验证结果集缓存发现第一次查询慢 第二次查询就会快

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

示例：

sp_set_para_value(1,'ENABLE_MONITOR',1);

sf_set_session_para_value('MONITOR_SQL_EXEC',1);

需要优化的sql

select * from tablename;

通过消息可以查询到执行号，我这里是97828

调用DBMS_SQLTUNE函数传入执行号：

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=>97828) from dual;

下面是执行打印的结果：

SQL Monitoring Report

SQL Text

------------------------------

select * from zcjy_tdnz.ht_land

Global Information

------------------------------

 Status                                 :  DONE (ALL ROWS)

 Session                                :  SYSDBA (140599657854928:1260)

 SQL ID                                 :  13090

 SQL Execution ID                       :  97828

 Execution Started                      :  2024-04-01 20:43:09

 Duration                               :  0.155809s

 Program                                :  manager.exe

Global Stats

=========================================================

| Affected |  Bytes   | Bytes |  Physical  |  Logical   |

|   Rows   | Allocate | Free  | Read(page) | Read(page) |

=========================================================

|      100 |        0 |     0 |        100 |         20 |

=========================================================

SQL Plan

------------------------------

1   #NSET2: [529, 285219, 5845] 

2     #PRJT2: [529, 285219, 5845]; exp_num(161), is_atom(FALSE) 

3       #CSCN2: [529, 285219, 5845]; INDEX33580258(HT_LAND)

-- 创建一个表结构与 table2 一模一样的表，只复制结构不复制数据；

create table table1 as select * from table2 where 1=2;

-- 创建一个表结构与 table2 一模一样的表，复制结构同时也复制数据；

create table table1 as select * from table2 ;

-- 创建一个表结构与 table2 一模一样的表，复制结构同时也复制数据，但是指定新表的列名

create table table1(新列名1,新列名2) as select 原列名1,原列名2 from table2;

-- 修改表字段类型

ALTER TABLE 表名 ADD COLUMN 字段名称 字段类型

-- 在打开监控开关（ENABLE_MONITOR=1、MONITOR_TIME=1）后

#显示最近 1000 条执行时间较长的 SQL 语句

select * from V$SYSTEM_LONG_EXEC_SQLS

#显示服务器启动以来执行时间最长的 20 条 SQL 语句

select * from V$LONG_EXEC_SQLS

-- 查看完整的 sql 语句

sf_get_session_sql(sess_id)

-- 执行命令查询执行的函数

select cache_item,sqlstr from v$cachepln where sqlstr like '%functionname%';

-- 生成执行计划的文件 在 /data/DAMENG/trace/ 目录下

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME PLNDUMP,LEVEL 140015356688432';

-- 打开文件后看见有另一个 cache_item  在重复执行命如：

sub_method[1] :: PLN[140163083606064][PTEST1]

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME PLNDUMP,LEVEL 140163083606064';

-- 当需要 in 一个字符串集合

select * from a where name in ('a,b,c,d')

-- 下面语句可以根据某个字符做匹配返回一个结果集

(select regexp_substr('jcd12,jcd13,jcd14','[^,]+',1,level,'i') as tempcol from dual connect by level <= LENGTH(TRANSLATE('jcd12,jcd13,jcd14',','||'jcd12,jcd13,jcd14',','))+1) 

-- 上面查询的语句就可以写成

select * from a where name in (select regexp_substr('a,b,c,d','[^,]+',1,level,'i') as tempcol from dual connect by level <= LENGTH(TRANSLATE('a,b,c,d',','||'a,b,c,d',','))+1)

select 

distinct 'select a.table_name,a.n,b.n,round((b.n*1.0/a.n)*100,2)||''%'' from (select count(*) as n,'''||OBJECT_NAME||

''' as table_name from dba_tables where owner = '''||OBJECT_NAME||

''') a,(select count(*) as n,'''||OBJECT_NAME||

''' as table_name from SYSTABLECOMMENTS where schname = '''||OBJECT_NAME||

''') b where a.table_name = b.table_name union all'

from ALL_OBJECTS where OBJECT_TYPE = 'SCH'

-- 然后执行上述 SQL 结果如下：

模式名称	 表个数 有注释信息的表个数 有注释信息的表比例

table_name  n     n               round((b.n*1./a.n)*100,2)||'%'

ZCJY_ZS	    619	  4	              0.65%

-- 可以展示所有表名

select table_name from dba_tables where owner = '模式名' order by table_name 

table_name

table_a

table_b

-- 可以展示数据库下表总数，此模式下为 859 张表 

select count(*) from dba_tables where owner = '模式名' 

COUNT(*)

859

select tvname,comment$ from SYSTABLECOMMENTS where schname = '模式名' order by tvname

tvname	comment$

SJYH	手机用户

SJDX	手机短信

模式名称	表名称	是否有表名称注释	表名称注释	总字段个数	有注释信息的字段个数	有注释信息的字段比例	是否为空表	数据量

--①可查出模式名称、表名称、总字段个数、有注释信息的字段个数、有注释信息的字段比例

-- 先查出当前模式下的表和模式：例如查出 DB_TEST 模式下

select owner,table_name,

'select a.owner,a.tvname,a.n,b.n,round((b.n*1.0/a.n)*100,2)||''%'' from (select count(*) as n,'''

||owner||

''' as owner,'''||table_name||''' as tvname from all_tab_columns where owner = '''||owner||

''' and table_name = '''||table_name||''') a,(select count(*) as n,'''||owner||

''' as owner,'''||table_name||''' as tvname from SYSCOLUMNCOMMENTS where schname = '''||owner||

''' and tvname = '''||table_name||''') b where a.owner = b.owner and a.tvname = b.tvname  union all'

from dba_tables 

where 

owner = 'DB_TEST'

order by owner,table_name

--②可查出模式名称、表名称、表的数据量

-- 再查出可以查出模式名称和表名称以及表数据量的 SQL 语句

select 'select '''||owner||''' as owner,'''||table_name||''' as tvname,count(*) from "'||owner||'"."'||table_name||'" union all '

from dba_tables 

where owner = 'DB_TEST'

order by owner,table_name

--③可查出表的中文注释：

select a.table_name,b.comment$ 

from (select table_name from dba_tables where owner = 'ODS_TEST' order by table_name) a

left join (select tvname,comment$ from SYSTABLECOMMENTS where schname = 'DB_TEST') b

on a.table_name = b.tvname

select owner,table_name,column_name,data_type,data_length,nullable

 from all_tab_columns 

where owner like 'DB_TEST%'

-- 示例结果如下：

owner	table_name	column_name	data_type	data_length	nullable

DB_TEST	ODS_TEST	ACCOUNT_ID	VARCHAR	    32	        N

select * from V$SQL_STAT where sessid = '139454492043355';

-- 可以查看 sql 占用情况、io 等待时间等等

MAX_MEM_USED - 内存使用情况 （kb）

IO_WAIT_TIME - io 等待时间 （毫秒） 可能可服务器的磁盘性能有问题

EXEC_TIME    - 总执行时间（毫秒）

-- 先创建对应的表值变量

create or replace type report_01 as object(

m1 varchar(50),

s1 decimal(18,0)

);

create or replace type report_01_table as table of report_01;

-- 创建函数

CREATE OR REPLACE FUNCTION "fn_report"

(

	"v_type" varchar(50)

)

RETURN report_01_table

AUTHID DEFINER

is v_text report_01_table :=report_01_table();

begin

select report_01(

m1,

s1 ) bulk collect into v_text from (

select '合计' as m1, 1000 as s1

)dd;

return v_text;

end fn_report;

-- 调用

select * from table(fn_report('test'));