DM教程FG074-达梦数据库查询计划分析

本文档详细介绍DM数据库查询计划分析的方法和技巧，包括查询计划概念、查询计划组成、查询计划生成、查询计划分析工具、查询计划分析方法、查询计划优化策略、实施方案等内容，风哥教程参考DM官方文档《DM8执行计划》手册，适合DBA人员进行DM数据库查询性能的分析和优化。

Part01-基础概念与理论知识

1.1 DM数据库查询计划概念

DM数据库查询计划是数据库优化器生成的执行SQL语句的详细步骤，包括表的访问方式、连接顺序、连接方式、索引使用等信息。查询计划是SQL语句执行的蓝图，直接影响SQL语句的执行效率。

查询计划的重要性：

性能分析：通过查询计划可以分析SQL语句的执行效率
性能优化：通过分析查询计划可以找出SQL语句的性能瓶颈
索引优化：通过查询计划可以分析索引的使用情况
执行计划调优：通过修改查询计划可以提高SQL语句的执行效率

1.2 DM数据库查询计划组成

DM数据库查询计划的组成部分：

操作符：查询计划中的基本执行单元，如表扫描、索引扫描、连接操作等
访问路径：访问表数据的方式，如全表扫描、索引扫描等
连接方式：多表连接的方式，如嵌套循环连接、哈希连接、排序合并连接等
连接顺序：多表连接的顺序
执行顺序：查询计划中操作的执行顺序
成本估算：查询计划的执行成本估算

1.3 DM数据库查询计划生成

DM数据库查询计划生成过程：

语法分析：解析SQL语句，生成语法树
语义分析：检查SQL语句的语义正确性，生成逻辑执行计划
优化：根据统计信息和优化规则，生成多个候选执行计划
选择：根据成本估算，选择最优的执行计划
执行：执行选定的执行计划

风哥提示：查询计划是SQL语句执行的核心，了解查询计划的生成过程和组成部分对于SQL优化至关重要。

Part02-生产环境规划与建议

2.1 DM数据库查询计划分析工具

DM数据库查询计划分析工具：

# 查询计划分析工具
#
# 1. EXPLAIN命令
##
# 语法
EXPLAIN [EXTENDED] SQL语句;
##
# 示例
SQL> EXPLAIN SELECT * FROM fgedu.t_user WHERE id > 1000;
#
# 2. DBMS_XPLAN包
##
# 语法
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id));
##
# 示例
SQL> SELECT sql_id FROM v$sql WHERE sql_text LIKE ‘%SELECT * FROM fgedu.t_user%’; 风哥提示：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘1234567890’));
#
# 3. 图形化工具
##
# DMSQL工具
– 执行SQL语句后，点击”执行计划”标签查看查询计划
##
# DM管理工具
– 执行SQL语句后，点击”执行计划”标签查看查询计划
#
# 4. 系统视图
##
# V
$SQL_PLAN
– 存储SQL语句的执行计划
##
# V
$SQL
– 存储SQL语句的执行信息
##
# V
$SQL_STATISTICS
– 存储SQL语句的执行统计信息

2.2 DM数据库查询计划分析方法

DM数据库查询计划分析方法：

查询计划分析步骤：

获取查询计划：使用EXPLAIN命令或其他工具获取SQL语句的查询计划
分析访问路径：分析表的访问方式，如全表扫描、索引扫描等
分析连接方式：分析多表连接的方式，如嵌套循环连接、哈希连接等
分析连接顺序：分析多表连接的顺序
分析成本估算：分析查询计划的成本估算
识别性能瓶颈：找出查询计划中的性能瓶颈
提出优化建议：根据分析结果提出优化建议

2.3 DM数据库查询计划优化策略

DM数据库查询计划优化策略：

学习交流加群风哥微信: itpux-com

优化SQL语句：重写SQL语句，使用更高效的查询方式
创建索引：为查询条件创建合适的索引
更新统计信息：定期更新表的统计信息，确保优化器生成准确的查询计划
调整参数：调整数据库参数，如优化器模式、内存分配等
使用提示：使用SQL提示引导优化器生成更优的查询计划
分区表：对于大表，使用分区表提高查询效率
物化视图：对于复杂查询，使用物化视图提高查询效率
并行查询：对于大型查询，使用并行查询提高执行效率

Part03-生产环境项目实施方案

3.1 DM数据库查询计划实施方案

3.1.1 查询计划分析实施

# 查询计划分析实施
#
# 1. 分析SQL语句
##
# 示例1：简单查询
SQL> EXPLAIN SELECT * FROM fgedu.t_user WHERE id = 1000;
##
# 示例2：复杂查询
SQL> EXPLAIN SELECT u.id, u.name, o.amount
FROM fgedu.t_user u
JOIN fgedu.t_order o ON u.id = o.user_id
WHERE u.id > 1000 AND o.amount > 1000;
#
# 2. 分析查询计划
##
# 示例1：简单查询的查询计划
# 执行EXPLAIN命令
SQL> EXPLAIN SELECT * FROM fgedu.t_user WHERE id = 1000;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST
———- ———- ————- ———- ———- ———-
1 1 TABLE ACCESS T_USER 1 2
2 INDEX SCAN PK_T_USER 1 1
##
# 示例2：复杂查询的查询计划
# 执行EXPLAIN命令
SQL> EXPLAIN SELECT u.id, u.name, o.amount
FROM fgedu.t_user u
JOIN fgedu.t_order o ON u.id = o.user_id
WHERE u.id > 1000 AND o.amount > 1000;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST 学习交流加群风哥QQ113257174
———- ———- ————- ———- ———- ———-
1 1 NESTED LOOP 100 100
2 TABLE ACCESS T_USER 100 50
3 INDEX SCAN PK_T_USER 100 25
4 TABLE ACCESS T_ORDER 1 1
5 INDEX SCAN IDX_T_ORDER_USER_ID 1 0.5
#
# 3. 优化查询计划
##
# 示例1：添加索引
SQL> CREATE INDEX idx_t_order_amount ON fgedu.t_order(amount);
##
# 示例2：使用SQL提示
SQL> EXPLAIN SELECT /*+ USE_HASH(u, o) */ u.id, u.name, o.amount
FROM fgedu.t_user u
JOIN fgedu.t_order o ON u.id = o.user_id
WHERE u.id > 1000 AND o.amount > 1000;

3.1.2 查询计划监控实施

# 查询计划监控实施
#
# 1. 监控工具
##
# 系统视图
# 查看SQL语句的执行计划
SQL> SELECT * FROM v$sql_plan WHERE sql_id = ‘1234567890’;
# 查看SQL语句的执行信息
SQL> SELECT * FROM v$sql WHERE sql_id = ‘1234567890’;
# 查看SQL语句的执行统计信息
SQL> SELECT * FROM v$sql_statistics WHERE sql_id = ‘1234567890’;
#
# 2. 监控脚本
##
# 查询计划监控脚本
#!/bin/bash
# query_plan_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
echo “===== 执行计划信息 =====”
sqlplus -s SYSDBA/SYSDBA < 10
ORDER BY elapsed_time DESC
LIMIT 10;
EOF 更多视频教程www.fgedu.net.cn
echo “===== 执行计划详情 =====”
sqlplus -s SYSDBA/SYSDBA < 10
ORDER BY elapsed_time DESC
LIMIT 5
);
EOF

3.2 DM数据库查询计划参数调优

DM数据库查询计划参数调优：

# 查询计划参数调优
#
# 关键参数
##
# OPTIMIZER_MODE
– 描述：优化器模式
– 可选值：ALL_ROWS（全表扫描优先）、FIRST_ROWS（索引扫描优先）
– 建议值：根据业务需求选择
– 调整命令：sp_set_para_value(0, ‘OPTIMIZER_MODE’, ‘ALL_ROWS’);
##
# OPTIMIZER_DYNAMIC_SAMPLING
– 描述：动态采样级别
– 可选值：0-10
– 建议值：2-4
– 调整命令：sp_set_para_value(0, ‘OPTIMIZER_DYNAMIC_SAMPLING’, 2);
##
# OPTIMIZER_INDEX_COST_ADJ
– 描述：索引成本调整因子
– 可选值：1-10000
– 建议值：100
– 调整命令：sp_set_para_value(0, ‘OPTIMIZER_INDEX_COST_ADJ’, 100);
##
# OPTIMIZER_INDEX_CACHING
– 描述：索引缓存因子
– 可选值：0-100
– 建议值：90
– 调整命令：sp_set_para_value(0, ‘OPTIMIZER_INDEX_CACHING’, 90);
##
# HASH_AREA_SIZE
– 描述：哈希连接区域大小
– 建议值：根据内存大小调整
– 调整命令：sp_set_para_value(0, ‘HASH_AREA_SIZE’, 67108864);
##
# SORT_AREA_SIZE 更多学习教程公众号风哥教程itpux_com
– 描述：排序区域大小
– 建议值：根据内存大小调整
– 调整命令：sp_set_para_value(0, ‘SORT_AREA_SIZE’, 67108864);
#
# 参数调优示例
##
# 硬件环境：16核CPU，64GB内存
# 调整查询计划参数
SQL> sp_set_para_value(0, ‘OPTIMIZER_MODE’, ‘ALL_ROWS’);
SQL> sp_set_para_value(0, ‘OPTIMIZER_DYNAMIC_SAMPLING’, 2);
SQL> sp_set_para_value(0, ‘OPTIMIZER_INDEX_COST_ADJ’, 100);
SQL> sp_set_para_value(0, ‘OPTIMIZER_INDEX_CACHING’, 90);
SQL> sp_set_para_value(0, ‘HASH_AREA_SIZE’, 67108864);
SQL> sp_set_para_value(0, ‘SORT_AREA_SIZE’, 67108864);
# 查看查询计划参数
SQL> select para_name, para_value from v$dm_ini where para_name in (
‘OPTIMIZER_MODE’, ‘OPTIMIZER_DYNAMIC_SAMPLING’, ‘OPTIMIZER_INDEX_COST_ADJ’,
‘OPTIMIZER_INDEX_CACHING’, ‘HASH_AREA_SIZE’, ‘SORT_AREA_SIZE’
);
# 输出
行号 PARA_NAME PARA_VALUE
———- ———————— ———-
1 OPTIMIZER_MODE ALL_ROWS
2 OPTIMIZER_DYNAMIC_SAMPLING 2
3 OPTIMIZER_INDEX_COST_ADJ 100
4 OPTIMIZER_INDEX_CACHING 90
5 HASH_AREA_SIZE 67108864
6 SORT_AREA_SIZE 67108864

3.3 DM数据库查询计划缓存

DM数据库查询计划缓存：

from DB视频:www.itpux.com
# 查询计划缓存
#
# 1. 缓存原理
– DM数据库会缓存SQL语句的执行计划
– 当执行相同的SQL语句时，直接使用缓存的执行计划
– 缓存可以提高SQL语句的执行效率
#
# 2. 缓存管理
##
# 查看缓存
SQL> SELECT * FROM v$sql_plan_cache;
##
# 清除缓存
SQL> DBMS_SHARED_POOL.PURGE(‘sql_id’, ‘C’);
##
# 清除所有缓存
SQL> DBMS_SHARED_POOL.PURGE_ALL();
#
# 3. 缓存优化
##
# 绑定变量
# 优化前：硬解析
SQL> EXECUTE IMMEDIATE ‘SELECT * FROM fgedu.t_user WHERE id = ‘ || id;
# 优化后：软解析
SQL> EXECUTE IMMEDIATE ‘SELECT * FROM fgedu.t_user WHERE id = :id’ USING id;
##
# 使用SQL提示
SQL> SELECT /*+ CACHE */ * FROM fgedu.t_user WHERE id > 1000;
##
# 调整缓存参数
###
# SHARED_POOL_SIZE
– 描述：共享池大小
– 建议值：根据内存大小调整
– 调整命令：sp_set_para_value(1, ‘SHARED_POOL_SIZE’, 1073741824);
###
# LIBRARY_CACHE_SIZE
– 描述：库缓存大小
– 建议值：根据内存大小调整
– 调整命令：sp_set_para_value(1, ‘LIBRARY_CACHE_SIZE’, 536870912);

Part04-生产案例与实战讲解

4.1 DM数据库查询计划分析案例

以下是一个查询计划分析的案例：

#
# 查询计划分析案例
##
# 场景描述
SQL语句执行缓慢，需要分析查询计划找出性能瓶颈
##
# 分析步骤
# 1. 执行SQL语句
SQL> SELECT * FROM fgedu.t_user WHERE name = ‘张三’;
# 2. 获取查询计划
SQL> EXPLAIN SELECT * FROM fgedu.t_user WHERE name = ‘张三’;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST
———- ———- ————- ———- ———- ———-
1 1 TABLE ACCESS T_USER 1000 100
# 3. 分析查询计划
– 操作：TABLE ACCESS（全表扫描）
– 表：T_USER
– 估计行数：1000
– 成本：100
# 4. 分析表结构
SQL> SELECT * FROM all_indexes WHERE table_name=’T_USER’;
# 输出
行号 OWNER INDEX_NAME TABLE_NAME UNIQUENESS
———- ——– ——————– ———— ———-
1 FGEDU PK_T_USER T_USER UNIQUE
# 5. 优化方案
– 创建索引：为name字段创建索引
# 6. 实施优化
SQL> CREATE INDEX idx_t_user_name ON fgedu.t_user(name);
# 7. 验证优化效果
SQL> EXPLAIN SELECT * FROM fgedu.t_user WHERE name = ‘张三’;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST
———- ———- ————- ————– ———- ———-
1 1 TABLE ACCESS T_USER 1 2
2 INDEX SCAN IDX_T_USER_NAME 1 1
# 8. 执行SQL语句
SQL> SELECT * FROM fgedu.t_user WHERE name = ‘张三’;
# 执行时间：0.01秒（优化前：1.2秒）

4.2 DM数据库查询计划优化案例

以下是一个查询计划优化的案例：

#
# 查询计划优化案例
##
# 场景描述
复杂查询执行缓慢，需要优化查询计划
##
# 优化步骤
# 1. 执行SQL语句
SQL> SELECT u.id, u.name, o.amount, o.order_date
FROM fgedu.t_user u
JOIN fgedu.t_order o ON u.id = o.user_id
WHERE u.id > 1000 AND o.amount > 1000
ORDER BY o.order_date DESC;
# 2. 获取查询计划
SQL> EXPLAIN SELECT u.id, u.name, o.amount, o.order_date
FROM fgedu.t_user u
JOIN fgedu.t_order o ON u.id = o.user_id
WHERE u.id > 1000 AND o.amount > 1000
ORDER BY o.order_date DESC;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST
———- ———- ————- ———- ———- ———-
1 1 SORT 100 200
2 NESTED LOOP 100 150
3 TABLE ACCESS T_USER 100 50
4 INDEX SCAN PK_T_USER 100 25
5 TABLE ACCESS T_ORDER 1 1
6 INDEX SCAN IDX_T_ORDER_USER_ID 1 0.5
# 3. 分析查询计划
– 操作：SORT（排序）
– 成本：200
– 排序是性能瓶颈
# 4. 优化方案
– 创建复合索引：为order_date字段创建索引
– 使用SQL提示：使用哈希连接
# 5. 实施优化
SQL> CREATE INDEX idx_t_order_amount_date ON fgedu.t_order(amount, order_date);
# 6. 验证优化效果
SQL> EXPLAIN SELECT /*+ USE_HASH(u, o) */ u.id, u.name, o.amount, o.order_date
FROM fgedu.t_user u
JOIN fgedu.t_order o ON u.id = o.user_id
WHERE u.id > 1000 AND o.amount > 1000
ORDER BY o.order_date DESC;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST
———- ———- ————- ————– ———- ———-
1 1 HASH JOIN 100 100
2 TABLE ACCESS T_USER 100 50
3 INDEX SCAN PK_T_USER 100 25
4 TABLE ACCESS T_ORDER 100 50
5 INDEX SCAN IDX_T_ORDER_AMOUNT_DATE 100 25
# 7. 执行SQL语句
SQL> SELECT /*+ USE_HASH(u, o) */ u.id, u.name, o.amount, o.order_date
FROM fgedu.t_user u
JOIN fgedu.t_order o ON u.id = o.user_id
WHERE u.id > 1000 AND o.amount > 1000
ORDER BY o.order_date DESC;
# 执行时间：0.05秒（优化前：1.5秒）

4.3 DM数据库查询计划性能调优

以下是一个查询计划性能调优的案例：

#
# 查询计划性能调优案例
##
# 场景描述
查询计划性能较差，需要调优
##
# 调优步骤
# 1. 分析当前查询计划
SQL> EXPLAIN SELECT * FROM fgedu.t_user WHERE id > 1000000;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST
———- ———- ————- ———- ———- ———-
1 1 TABLE ACCESS T_USER 900000 1000
# 2. 分析表结构
SQL> SELECT * FROM all_indexes WHERE table_name=’T_USER’;
# 输出
行号 OWNER INDEX_NAME TABLE_NAME UNIQUENESS
———- ——– ——————– ———— ———-
1 FGEDU PK_T_USER T_USER UNIQUE
# 3. 分析统计信息
SQL> SELECT * FROM dba_tab_statistics WHERE table_name=’T_USER’;
# 输出
行号 OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
———- ——– ———— ———- —————
1 FGEDU T_USER 1000000 2025-01-01
# 4. 优化方案
– 更新统计信息
– 调整查询计划参数
– 创建分区表
# 5. 实施优化
# 更新统计信息
SQL> ANALYZE TABLE fgedu.t_user COMPUTE STATISTICS;
# 调整查询计划参数
SQL> sp_set_para_value(0, ‘OPTIMIZER_MODE’, ‘ALL_ROWS’);
# 创建分区表
SQL> CREATE TABLE fgedu.t_user_partitioned (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
age NUMBER
) PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (100000),
PARTITION p2 VALUES LESS THAN (200000),
PARTITION p3 VALUES LESS THAN (300000),
PARTITION p4 VALUES LESS THAN (400000),
PARTITION p5 VALUES LESS THAN (500000),
PARTITION p6 VALUES LESS THAN (600000),
PARTITION p7 VALUES LESS THAN (700000),
PARTITION p8 VALUES LESS THAN (800000),
PARTITION p9 VALUES LESS THAN (900000),
PARTITION p10 VALUES LESS THAN (MAXVALUE)
);
# 插入数据
SQL> INSERT INTO fgedu.t_user_partitioned SELECT * FROM fgedu.t_user;
# 6. 验证调优效果
SQL> EXPLAIN SELECT * FROM fgedu.t_user_partitioned WHERE id > 1000000;
# 输出
行号 PLAN_ID OPERATION NAME EST_ROWS COST
———- ———- ————- ——————– ———- ———-
1 1 PARTITION RANGE SINGLE 100000 100
2 TABLE ACCESS T_USER_PARTITIONED 100000 100
# 7. 执行SQL语句
SQL> SELECT * FROM fgedu.t_user_partitioned WHERE id > 1000000;
# 执行时间：0.1秒（优化前：1.8秒）

Part05-风哥经验总结与分享

5.1 DM数据库查询计划最佳实践

基于多年DM数据库运维经验，总结以下查询计划最佳实践：

定期分析查询计划：定期分析SQL语句的查询计划，找出性能瓶颈
使用合适的索引：为查询条件创建合适的索引
更新统计信息：定期更新表的统计信息，确保优化器生成准确的查询计划
调整查询计划参数：根据业务需求调整查询计划参数
使用SQL提示：在必要时使用SQL提示引导优化器生成更优的查询计划
优化SQL语句：重写SQL语句，使用更高效的查询方式
使用分区表：对于大表，使用分区表提高查询效率
使用物化视图：对于复杂查询，使用物化视图提高查询效率
监控查询计划：监控SQL语句的执行计划，及时发现问题
缓存查询计划：合理使用查询计划缓存，提高SQL语句的执行效率

生产环境建议：查询计划是SQL语句执行的核心，需要定期分析和优化，确保SQL语句的执行效率。

5.2 DM数据库常见查询计划问题

DM数据库常见查询计划问题及解决方案：

#
# 问题1：全表扫描
#
# 原因分析
– 没有为查询条件创建索引
– 索引失效
– 统计信息不准确
#
# 解决方案
– 创建合适的索引
– 修复索引失效问题
– 更新统计信息
#
# 问题2：排序操作成本高
#
# 原因分析
– 没有为排序字段创建索引
– 排序数据量过大
– 排序区域大小不足
#
# 解决方案
– 为排序字段创建索引
– 优化排序操作
– 调整SORT_AREA_SIZE参数
#
# 问题3：连接操作效率低
#
# 原因分析
– 连接方式选择不当
– 连接顺序不合理
– 没有为连接条件创建索引
#
# 解决方案
– 使用合适的连接方式
– 调整连接顺序
– 为连接条件创建索引
#
# 问题4：查询计划缓存失效
#
# 原因分析
– SQL语句不一致
– 绑定变量使用不当
– 缓存大小不足
#
# 解决方案
– 使用绑定变量
– 保持SQL语句一致性
– 调整缓存参数
#
# 问题5：统计信息不准确
#
# 原因分析
– 统计信息过期
– 表数据变化较大
– 统计信息收集不完整
#
# 解决方案
– 定期更新统计信息
– 在表数据变化较大时更新统计信息
– 使用DBMS_STATS包收集统计信息

5.3 DM数据库查询计划优化建议

DM数据库查询计划优化建议：

了解查询计划：熟悉DM数据库查询计划的组成和生成过程
定期分析查询计划：定期分析SQL语句的查询计划，找出性能瓶颈
使用合适的索引：为查询条件创建合适的索引
更新统计信息：定期更新表的统计信息，确保优化器生成准确的查询计划
调整查询计划参数：根据业务需求调整查询计划参数
使用SQL提示：在必要时使用SQL提示引导优化器生成更优的查询计划
优化SQL语句：重写SQL语句，使用更高效的查询方式
使用分区表：对于大表，使用分区表提高查询效率
使用物化视图：对于复杂查询，使用物化视图提高查询效率
监控查询计划：监控SQL语句的执行计划，及时发现问题
缓存查询计划：合理使用查询计划缓存，提高SQL语句的执行效率
学习优化技术：不断学习新的查询计划优化技术，提高优化能力

风哥提示：查询计划是SQL语句执行的核心，DBA人员必须掌握查询计划的分析和优化方法，根据实际的业务需求和系统环境进行合理配置和优化，提高SQL语句的执行效率。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html