-- 在数据库内部可以使用SQL查看动态视图v$dm_ini来查询参数配置情况
-- 譬如：查看compatible_mode参数。在DM中，这个参数控制着DM对其他关系型数据库（Oracle，MySQL）等的兼容性
SELECT PARA_NAME, PARA_VALUE, PARA_TYPE FROM v$dm_ini WHERE para_name LIKE 'COMPA%';


-- DM数据库的INI参数，都可以通过如下方式查看，在DM达梦数据库中，有3个系统函数可以查看参数配置情况。
-- 分别是SF_GET_PARA_VALUE、SF_GET_PARA_DOUBLE_VALUE和SF_GET_PARA_STRING_VALUE三个函数，分别对应参数值为数值类型、浮点类型、字符串类型的参数时使用。


SF_GET_PARA_VALUE(scope int, paraname varchar(256))
SF_GET_PARA_DOUBLE_VALUE(scope int, paraname varchar(8187))
SF_GET_PARA_STRING_VALUE(scope int, paraname varchar(8187))
-- 其中，SCOPE参数为1表示获取INI文件中配置参数的值，为2表示获取内存中配置参数的值。

-- 示例：查看数据库实例最大连接数，在文件和内存中的配置。达梦数据库安装默认连接数为100，实际项目中可能不够用，如下是修改最大连接数的方法

SELECT  SF_GET_PARA_VALUE(2,'MAX_SESSIONS'); -- 先查一下当前最大连接数，看看结果是不是默认的100


-- DM的INI参数都可以通过调用系统过程SP_SET_PARA_VALUE/SP_SET_PARA_DOUBLE_VALUE/SP_SET_PARA_STRING_VALUE的方法来进行修改或设置。
-- SP_SET_PARA_VALUE的作用就是设置 dm.ini 文件中非浮点和字符串类型的参数值

-- 第一个参数：scope：取值为 1、2 。1 表示 dm.ini 文件和内存参数都修改，不需要重启服务器；2 表示只修改 dm.ini 文件，服务器重启后生效。
-- 第二个参数：具体要设置的dm.ini中的参数名
-- 第三个参数：参数值


-- 举例说明：将 dm.ini 文件中 HFS_CACHE_SIZE 参数值设置为 320
SP_SET_PARA_VALUE (1,'HFS_CACHE_SIZE',320);

-- （索引组织表）每个表只有一个聚集索引：二叉树结构只能按照一个索引键来排序

------------------------------------------------------------------------------------------------------------------------
-- DDL （如果仅创建业务字段，达梦内部会自动生成ROWID来做为隐式主键）
CREATE TABLE users(
    id BIGINT,
    C1 INT,
    C2 int,
    C3 VARCHAR(20),
    C4 VARCHAR(20)
);

-- 查询此表上的索引：可以看到达梦默认会创建一个随机名称的聚集索引。索引类型是CLUSTER
SELECT * FROM DBA_INDEXES idx 
    WHERE  idx.owner='QHDATA_TEST' AND  idx.table_name='users';

-- 用户手动在该表添加聚集索引（以id为索引键）后，该默认聚集索引则会自动删除。这个用户创建聚集索引是可以删除的，如果删除后原先的聚集索引会自动恢复。
CREATE CLUSTER INDEX idx_users_id  on QHDATA_TEST."users"(id);

-- 如果直接在该表上基于其他表创建聚集索引，则直接覆盖前面的。
CREATE CLUSTER INDEX idx_users_index  on QHDATA_TEST."users"(id);

-- 查询此索引的DDL语句
SELECT dbms_metadata.get_ddl(OBJECT_TYPE => 'INDEX',NAME=>upper('idx_users_id'),SCHNAME => 'QHDATA_TEST');

-- 索引DDL语句
CREATE OR REPLACE CLUSTER  INDEX "QHDATA_TEST"."idx_users_id" ON "QHDATA_TEST"."users"("id" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
------------------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------------------
-- 创建基于主键的聚集索引：PRIMARY KEY主键定义的作用是表示数据唯一，CLUSTER表示基于这个主键列的聚集索引
CREATE  TABLE  STUDENT1( 
    STUNO     INT           CLUSTER PRIMARY KEY, 
    STUNAME   VARCHAR(15)   NOT NULL, 
    TEANO     INT, 
    CLASSID   INT
);

-- 默认情况下，达梦的dm.ini中PK_WITH_CLUSTER参数为1，即建表时指定的主键会自动转化为聚集主键
-- 主键语句中不带CLUSTER关键字，这与上面是同义的。  
CREATE  TABLE  STUDENT2( 
    STUNO     INT           PRIMARY KEY, 
    STUNAME   VARCHAR(15)   NOT NULL, 
    TEANO     INT, 
    CLASSID   INT
);


-- 查看这个索引（基于主键列的索引组织表B+TREE数据结构）
SELECT * from DBA_INDEXES idx WHERE  idx.owner='QHDATA_TEST' AND  idx.table_name='STUDENT2';

-- 查看索引定义（NAME基于上面查询得来）
SELECT dbms_metadata.get_ddl(OBJECT_TYPE => 'INDEX',NAME=>upper('INDEX33625894'),SCHNAME => 'QHDATA_TEST');

-- 索引定义语句： CLUSTER UNIQUE  INDEX ， 唯一的聚集索引
CREATE OR REPLACE CLUSTER UNIQUE  INDEX "QHDATA_TEST"."INDEX33625893" ON "QHDATA_TEST"."STUDENT1"("STUNO" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;

CREATE OR REPLACE CLUSTER UNIQUE  INDEX "SYSDBA"."INDEX33625920" ON "SYSDBA"."STUDENT1"("STUNO" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE OR REPLACE CLUSTER UNIQUE  INDEX "SYSDBA"."INDEX33625921" ON "SYSDBA"."STUDENT2"("STUNO" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
------------------------------------------------------------------------------------------------------------------------


-- CLUSTER PRIMARY KEY          主键约束，指明指定列作为基表的聚集索引（也 叫聚簇索引）和主关键字；
-- PRIMARY KEY                  主键约束，指明指定列作为基表的主关键字（当PK_WITH_CLUSTER 为0时，即建表时指定的主键不会自动转化为聚集主键；若为 1，则主键自动变为聚集主键。）；
-- 这两个等价，都是 CLUSTER UNIQUE  INDEX

-- CLUSTER KEY                  指定列为聚集索引键，但是这个列是非唯一的字段；
CREATE OR REPLACE CLUSTER  INDEX "SYSDBA"."INDEX33625905" ON "QHDATA_TEST"."STUDENT4"("id" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;

-- NOT CLUSTER PRIMARY KEY      主键约束，指明指定列作为基表的非聚集索引主关键字。这个会创建两个索引：
-- 基于这个列的唯一索引 和  基于ROWID的聚集索引
CREATE OR REPLACE UNIQUE  INDEX "SYSDBA"."INDEX33625915" ON "SYSDBA"."STUDENT5"("id" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE OR REPLACE CLUSTER INDEX "SYSDBA"."INDEX33625914" ON "SYSDBA"."STUDENT5"( STORAGE(ON "MAIN", CLUSTERBTR) ;

-- CLUSTER UNIQUE KEY       指定列为聚集索引键，并且是唯一的；

CREATE TABLE "qhdata_warehouse"."dw_addr_admin_area_code"  (
    "rid" INT NOT NULL,
    "update_time" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP() NOT NULL,
    "start_time" DATE,
    "end_time" DATE,
    "version_num" INT NOT NULL,
    "lng_lat_GCJ02" VARCHAR(100),
    "lng_lat_GCJ02_md5" VARCHAR(64) NOT NULL,
    "UA0003" VARCHAR(100),
    "UA0004" VARCHAR(100),
    "UA0005" VARCHAR(100),
    "UA0006" VARCHAR(100),
    "UA0007" VARCHAR(100),
    "UA0008" VARCHAR(100),
    "code_version" VARCHAR(100),
    "addr_code" VARCHAR(600),
    "check_md5" VARCHAR(64),
    "deal_ind" INT,
    UNIQUE("lng_lat_GCJ02_md5")
) STORAGE(ON "MAIN", CLUSTERBTR);

-- 聚集索引
CREATE OR REPLACE CLUSTER  INDEX "qhdata_warehouse"."INDEX_RID_111" ON "qhdata_warehouse"."dw_addr_admin_area_code"("rid" ASC) STORAGE(ON "MAIN", CLUSTERBTR);

-- 唯一索引
CREATE OR REPLACE UNIQUE  INDEX "qhdata_warehouse"."INDEX177922827890000" ON "qhdata_warehouse"."dw_addr_admin_area_code"("rid" ASC) STORAGE(ON "MAIN", CLUSTERBTR);


-- 基于UA0008的索引
CREATE OR REPLACE  INDEX "qhdata_warehouse"."INDEX177921343911900" ON "qhdata_warehouse"."dw_addr_admin_area_code"("UA0008" ASC) STORAGE(ON "MAIN", CLUSTERBTR);

-- 基于UA0005的索引
CREATE OR REPLACE  INDEX "qhdata_warehouse"."INDEX177921813174100" ON "qhdata_warehouse"."dw_addr_admin_area_code"("UA0005" ASC) STORAGE(ON "MAIN", CLUSTERBTR);

-- 联合索引
CREATE OR REPLACE  INDEX "qhdata_warehouse"."mutli_index" ON "qhdata_warehouse"."dw_addr_admin_area_code"("UA0008" ASC,"UA0005" ASC) STORAGE(ON "MAIN", CLUSTERBTR);


-- 主要通过explain和explain for来查看SQL语句的执行计划
explain  select * from qhdata_warehouse.dw_addr_admin_area_code where UA0005 = '440300000000' and  UA0008 = '440311006001';
explain for select * from qhdata_warehouse.dw_addr_admin_area_code where UA0005 = '440300000000' and  UA0008 = '440311006001';

SELECT NUM_ROWS,LAST_ANALYZED,TEMPORARY  FROM USER_TABLES WHERE TABLE_NAME='TEST_TAB1';

SELECT ONWER, TABLE_NAME , NUM_ROWS,LAST_ANALYZED,TEMPORARY  FROM ALL_TABLES WHERE TABLE_NAME='bak_20240904180937_ud_cwx';

SELECT ONWER, TABLE_NAME , NUM_ROWS,LAST_ANALYZED,TEMPORARY  FROM USER_TABLES WHERE TABLE_NAME='bak_20240904180937_ud_cwx';

-- 1、收集数据库的统计信息，对库上所有模式下的所有用户表以及表上的所有索引生成统计信息
CALL SP_DB_STAT_INIT ();

-- 2、清空库的统计信息
CALL SP_DB_STAT_DEINIT ();

-- 3、对某张表或某个索引生成统计信息 CALL SP_TAB_STAT_INIT ('模式名', '表名或索引名');
对表 SYSOBECTS 生成统计信息 CALL SP_TAB_STAT_INIT ('SYS', 'SYSOBJECTS');

-- 4、清空某张表的统计信息 CALL SP_TAB_STAT_DEINIT ('模式名', '表名');
清空表 SYSOBECTS 的统计信息 CALL SP_TAB_STAT_DEINIT ('SYS', 'SYSOBJECTS');

-- 5、对某个表上所有的列生成统计信息 CALL SP_TAB_COL_STAT_INIT ('模式名', '表名');
对'SYSOBJECTS'表上所有的列生成统计信息 CALL SP_TAB_COL_STAT_INIT ('SYS', 'SYSOBJECTS');

-- 6、清空某个表上所有的列的统计信息 CALL SP_TAB_COL_STAT_DEINIT ('模式名', '表名');
清空'SYSOBJECTS'表上所有的列统计信息 CALL SP_TAB_COL_STAT_DEINIT ('SYS', 'SYSOBJECTS');

-- 7、对指定的列生成统计信息，不支持大字段列和虚拟列 
CALL SP_COL_STAT_INIT ('模式名', '表名', '列名');
    --对表 SYSOBJECTS 的 ID 列生成统计信息 
CALL SP_COL_STAT_INIT ('SYS', 'SYSOBJECTS', 'ID');

-- 8、清空指定列的统计信息 
CALL SP_COL_STAT_DEINIT ('模式名', '表名', '列名');
-- 清空表 SYSOBJECTS 的 ID 列统计信息 
CALL SP_COL_STAT_INIT ('SYS', 'SYSOBJECTS', 'ID');

-- 9、对表上所有的索引生成统计信息 CALL SP_TAB_INDEX_STAT_INIT ('模式名', '表名');
对 SYSOBJECTS 表上所有的索引生成统计信息 CALL SP_TAB_INDEX_STAT_INIT ('SYS', 'SYSOBJECTS');

-- 10、对指定的索引生成统计信息 CALL SP_INDEX_STAT_INIT ('模式名', '索引名'); 
-- 对指定的索引 IND 生成统计信息 CALL SP_INDEX_STAT_INIT ('SYSDBA', 'IND');

-- 11、清空指定的索引的统计信息 CALL SP_INDEX_STAT_DEINIT ('模式名', '索引名');
-- 清空指定的索引 IND 的统计信息 CALL SP_INDEX_STAT_DEINIT ('SYSDBA', 'IND');

-- 12、对某个 SQL 查询语句中涉及的所有表和过滤条件中的列(不包括大字段、ROWID)生成统计信息。可能返回的错误提示：
-- 1） 语法分析出错，sql 语句语法错误 2） 对象不支持统计信息，统计的表或者列不存在，或者不允许被统计 CALL SP_SQL_STAT_INIT ('SQL');
-- 对'SELECT * FROM SYSOBJECTS'语句涉及的所有表生成统计信息 
CALL SP_SQL_STAT_INIT ('SELECT * FROM SYSOBJECTS');

-- 13、对指定的列生成统计信息，不支持大字段列和虚拟列 
CALL SP_COL_STAT_INIT('模式名','表名','列名')
-- 对表 SYSOBJECTS 的 ID 列生成统计信息 
CALL SP_COL_STAT_INIT ('SYS', 'SYSOBJECTS', 'ID');

-- 14、清空指定的列的统计信息 
CALL SP_COL_STAT_DEINIT('模式名','表名','列名')
-- 清空表 SYSOBJECTS 的 ID 列统计信息 
CALL SP_COL_STAT_DEINIT ('SYS', 'SYSOBJECTS', 'ID');

-- 15、对某个表上所有的列，按照指定的采样率生成统计信息 
CALL SP_STAT_ON_TABLE_COLS('模式名','表名','采样率')
-- 对'SYSOBJECTS'表上所有的列生成统计信息，采样率 90 
CALL SP_STAT_ON_TABLE_COLS('SYS','SYSOBJECTS',90);

-- 16、删除一个表的多维统计信息 
CALL SP_TAB_MSTAT_DEINIT('模式名','表名')

-- 两个参数均为动态参数，可直接调用系统函数进行修改

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);

-- LONG_EXEC_SQLS_CNT 


 -- v$SYSTEM_LONG_EXEC_SQLS     显示服务器启动以来执行时间最长的 20 条 SQL 语句
 -- v$LONG_EXEC_SQLS            显示最近 1000 条执行时间较长的 SQL 语句
 -- v$SQL_HISTORY               显示执行 sql 的历史记录信息；可以方便用户经常使用的记录进行保存。    

-- 查看ET相关参数及其值
select para_name,para_value,para_type from v$dm_ini where para_name in ('ENABLE_MONITOR','MONITOR_TIME','MONITOR_SQL_EXEC')

-- 配置相关参数开启ET功能
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);    -- 开启监视
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);  -- 监视SQL执行
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);      -- 监视时间

--使用ET分析sql执行效率,语句的执行号为3105，可以通过ET(3105)的方式调用ET
SELECT D.* FROM DMTEST.EMPLOYEES E, DMTEST.DEPT_EMP D WHERE D.EMP_NO = E.EMP_NO LIMIT 10;
ET(3105);

# 开启SQL日志
SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);

-- 查询buffer相关视图
SELECT distinct name,count(*) FROM v$bufferpool GROUP BY name ORDER BY 2 DESC;

-- 查询BUFFER相关参数
SELECT * FROM V$DM_INI WHERE PARA_NAME LIKE '%BUFFER%';

-- 查询BUFFER相关
SELECT name ,type,value,sys_value,file_value,description FROM v$parameter WHERE upper(name) LIKE upper('%BUFFER%');

select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%BUFFER%');

select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%session%');


select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%vm%');

--MAX_SESSIONS:缺省值10000，静态属性，系统运行同时连接的最大数，取值范围1-15000。在安全版环境下，该参数受LICENSE的限制，取二者中较小者。当会话数达到 MAX_SESSIONS 值后，为了保证管理员可继续管理数据库，允许 SYSDBA 额外开启 10 个会话连接。

--LISTEN_IP：手动属性，与 PORT_NUM 配合使用，监听 IP 地址。若指定，则连接时必须指定与该值一样的 IP，否则创建连接失败。

--MAX_SESSION_STATEMENT：缺省值10000，动态系统级属性，单个会话上允许同时打开的语句句柄最大数，取值范围 64~20480）。

--MAX_SESSION_MEMORY：缺省值0，动态系统级属性，单个会话的运行内存上限，单位 MB，取值范围 0~262144，0 表示不限制。

SELECT name,TOTAL_SIZE,TARGET_SIZE,IS_OVERFLOW,FILE_NAME FROM v$mem_pool;
SELECT name,VALUE,DESCRIPTION FROM v$parameter WHERE name='MEMORY_POOL';
SELECT PARA_NAME,PARA_VALUE,DESCRIPTION ,PARA_TYPE FROM v$dm_ini WHERE PARA_NAME like '%MEMORY_%';

-- 内存总量查询：达梦数据库使用的内存大致等于 BUFFER + MPOOL，对应的 SQL 语句为
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;


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 , --TARGET 范围内累计扩展次数
N_EXTEND_EXCLUSIVE --超过 TARGET 累计扩展次数
from v$mem_pool order by TOTAL_size desc;


注意：
1、N_EXTEND_EXCLUSIVE 如果长期大于 0，说明长期从池外扩展，可能存在内存泄露。需要重点关注。
2、用到备份池的话,需要保持高度关注，此时系统非常危险。
3、内存池创建的线程号 creator 可以与 session 的 thrd_id 关联，查看对应的某个会话的内存使用情况。
4、如果 RESERVED_SIZE 比 org_size 小，说明内存池非常空闲，需要把对于的初始内存放小，否则浪费。
5、如果 TOTAL_size 比 TARGET_SIZE 大，说明内存池不够，经常向池外申请，需要把对于的参数调大。尽量保持每个池自持。


2.2 如何判断 BUFFER 空闲还是紧张
select name, --缓冲区名称
n_pages, --页数
free, --空闲页数目
N_DISCARD64 --淘汰的页数
from v$bufferpool
注意重点：
1、如果 free 很多说明该缓冲区很空闲，可以适当的调整降低 buffer 缓冲区参数值。
2、如果 free 项为 0，或者 N_DISCARD64 非零，表示该缓冲区经常淘汰。这种情况，就说明对应的缓冲区
参数太小，导致频繁淘汰，需要调整对应的缓冲区的参数。

SELECT para_name,para_value,MAX_VALUE,DEFAULT_VALUE,DESCRIPTION,PARA_TYPE 
    FROM v$dm_ini 
WHERE para_name LIKE '%MEMORY%';




select a.mem_all mem_size, b.mem_all buffer_size,a.MEM_ALL+b.MEM_ALL MEM_M,a.MEM_USED+b.MEM_USED USED_M
from
(select sum(TOTAL_SIZE)/1024/1024 MEM_ALL,
sum(RESERVED_SIZE)/1024/1024 MEM_USED 
from v$mem_pool)a,
(select sum(PAGE_SIZE*N_PAGES)/1024/1024 MEM_ALL,
sum(PAGE_SIZE*(N_PAGES-FREE))/1024/1024 MEM_USED 
from v$bufferpool) b


SELECT name, is_shared,CHK_LEAK,IS_OVERFLOW,sum(ORG_SIZE) t_ORG_SIZE,sum(TOTAL_SIZE) t_TOTAL_SIZE,sum(TARGET_SIZE)          t_TARGET_SIZE,count(*) cnt  FROM V$MEM_POOl
GROUP BY name, is_shared,CHK_LEAK,IS_OVERFLOW  order by 6 desc;