达梦统计信息收集 及获取真实执行计划
1. 统计信息对执行计划的影响
达梦数据库是基于代价的优化器，达梦数据库统计信息不准，会影响到执行计划的估算，导致SQL解析到错误的执行计划
|
DROP TABLE IF EXISTS TEST; CREATE TABLE TEST (EID INT PRIMARY KEY IDENTITY(1,1),ENAME VARCHAR(20),AGE INT,E_DATE DATE,SAL NUMBER(8,2),PHONE VARCHAR(20),DEPTNO INT); --创建存储过程 CREATE OR REPLACE PROCEDURE P_TEST AS DECLARE I INT; BEGIN FOR I IN 1..10000 LOOP INSERT INTO TEST (ENAME,AGE,E_DATE,SAL,PHONE,DEPTNO) SELECT DBMS_RANDOM.STRING('2',TRUNC(DBMS_RANDOM.VALUE(4,6))), TRUNC(DBMS_RANDOM.VALUE(18,60)), ADD_DAYS(SYSDATE(),DBMS_RANDOM.VALUE(-10000,-10)), TRUNC(DBMS_RANDOM.VALUE(1000,50000),2), TRUNC(DBMS_RANDOM.VALUE(13111111111,17999999999)), TRUNC(DBMS_RANDOM.VALUE(1,10)) FROM DUAL; END LOOP; IF MOD(I,500)=0 THEN COMMIT; END IF; END; --调用存储过程 CALL P_TEST; --查看test表基数，根据需要选择过滤性大的字段建索引 select count(distinct ename) name,count(DISTINCT age) age ,count(distinct e_date) edate,count(DISTINCT sal) sal ,count(distinct phone)phone,count(distinct deptno)deptno from test; CREATE INDEX IDX_TEST_ENAME ON TEST (ENAME); CREATE INDEX IDX_TEST_HDATE ON TEST (E_DATE); CREATE INDEX IDX_TEST_SAL ON TEST (SAL); --查看表是否统计信息 SELECT TABLE_NAME,NUM_ROWS,LAST_ANALYZED,TEMPORARY FROM USER_TABLES WHERE TABLE_NAME='TEST';
主要看NUM_ROWS字段，如果没有收集通常是空，第二个LAST_ANALYZED 代表收集的时间，如果没有收集这个字段也是空，第三个TEMPORARY字段附带说明，N表示不是临时表，Y表示是临时表，通常如果TEMPORARY=Y 是不用收集的 |
2. 客户端查看预估执行计划
通过客户端看到：当sal<5000和sal<10000时，客户端估算结果集都是500，当条件ENAME='ATCA'时，估算结果集是250
|
SELECT * FROM TEST WHERE SAL <5000; SELECT * FROM TEST WHERE SAL <10000; SELECT * FROM TEST WHERE ENAME='ATCA' |
3. 获取真实执行计划
使用EXPLAIN语句可以查看sql的执行计划，但EXPLAIN只生成执行计划，并不会真正执行SQL语句，因此产生的执行计划有可能不准，下面可以通过几种方法获取真实执行计划，可以看到执行计划估算数据量和实际数据量，相差不大一般没有影响，如果比较大就要收集了，否则会影响性能
|
（1）执行set autotrace trace获取真实计划 --在服务器disql登录，查看上面真实执行计划 ALTER SESSION SET 'MONITOR_SQL_EXEC'=1; set autotrace trace; SELECT * FROM TEST WHERE SAL <5000; --实际877，估算500 SELECT * FROM TEST WHERE SAL <10000; --实际1811，估算500 SELECT * FROM TEST WHERE ENAME='ATCA' --实际1，估算250 （2）v$cachepln中获取执行计划 v$cachepln中保存了SQL缓冲区中的执行计划信息，在ini参数USE_PLN_POOL !=0时才统计。根据v$cachepln中的cache_item可以获取实际执行计划信息 select * from v$cachepln where upper(sqlstr) like '%SELECT * FROM TEST WHERE ENAME%' 在 trace 目录中生成对应 trc 文件,查看文件里面的执行计划 alter session set events 'immediate trace name plndump level 139718993205344,dump_file ''/dmdata/testcahe.log'''; （3）通过ET系统函数获取执行计划（主要看执行计划哪一步的执行最久） --可通过配置参数中的ENABLE_MONITOR=1、MONITOR_TIME=1和MONITOR_SQL_EXEC=1开启两个参数均为动态参数，可直接调用系统函数进行修改 SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1); SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1); --仅对当前会话有效 sf_set_session_para_value('MONITOR_SQL_EXEC',1); 下面视图可以看每个步骤的具体描述 select * from V$SQL_NODE_NAME where NAME='BLKUP2'; |
h4. 收集统计信息
|
--收集指定用户默认模式下统计信息 select 'DBMS_STATS.GATHER_SCHEMA_STATS('''||username||''',100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' from all_users; --注意：比如下面只是收集SYSDBA模式下面的统计信息，如果SYSDBA有多个模式，其他模式的统计信息是无法收集的 DBMS_STATS.GATHER_SCHEMA_STATS('SYSDBA',100,TRUE,'FOR ALL COLUMNS SIZE AUTO'); --查看当前用户下有多少模式名 SELECT SCH_OBJ.NAME SCHEMA_NAME,USER_OBJ.NAME USER_NAME FROM( SELECT NAME, ID, PID, CRTDATE FROM SYS.SYSOBJECTS WHERE TYPE$='SCH')SCH_OBJ, (SELECT NAME, ID FROM SYS.SYSOBJECTS WHERE TYPE$='UR' AND SUBTYPE$='USER' ) USER_OBJ WHERE SCH_OBJ.PID=USER_OBJ.ID AND USER_OBJ.NAME='SYSDBA' ORDER BY SCH_OBJ.NAME; --收集TEST表统计信息 DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO'); |
5. 收集统计信息之后看执行计划
收集统计信息之后，客户端执行计划估值值和实际值很准确了，但服务器真实执行计划还是以前老的执行计划，这是因为以前执行了这个sql，缓存了以前的执行计划。
|
--客户端执行计划 |
|
--服务器服务器trace真实执行计划 SELECT * FROM TEST WHERE SAL <5000
|
6. 清空缓存
|
--找到对应SQL 对应的 cache_item 值 select cache_item,sqlstr,'call sp_clear_plan_cache('||cache_item||');' from v$cachepln where sqlstr like '%表名%'; --根据cache_item 值，清空对应sql的缓存 call sp_clear_plan_cache(cache_item)；---不加 pln 就是清理所有 sql 缓存 --清空缓存之后，真实执行计划已经正常了 |
