SQL Profile优化：分钟级查询到秒级的实战解析
120 浏览量
更新于2024-09-04
收藏 83KB PDF 举报
本文主要介绍了一个使用SQL Profile对慢查询进行优化的实际案例，涉及社保系统中的个人医疗费用明细查询。
在SQL优化过程中，SQL Profile是一种有效的方法，它可以帮助数据库识别并改进执行计划，以提高查询性能。在这个案例中，一个社保系统的自助查询系统中存在一个查询个人医疗费用明细的SQL语句，执行时间过长，耗时超过一分钟。该查询语句如下：
```sql
select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017';
```
此查询是针对名为v_zzzd_ylbx_ylfymxcx的视图进行的，视图的创建语句如下：
```sql
create or replace view v_zzzd_ylbx_ylfymxcx as
select a.indi_id aac001, a.idcard aac002, '' aof008, a.center_id aab301,
a.name aac003, a.hospital_id akf008, d.hospital_name akf009, a.serial_no akf010,
f.biz_name akf011,
nvl(round(sum(b.real_pay), 2), 0) akf012,
nvl(round(sum(case when b.fund_id='003' then b.real_pay else 0 end), 2), 0) akf013, 0 akf014, 0 akf015, 0 akf016,
nvl(round(sum(case when b.fund_id='001' then b.real_pay else 0 end), 2), 0) ak093,
nvl(round(sum(b.real_pay), 2), 0) - nvl(round(sum(case when b.fund_id in ('999', '003') and b.policy_item_code in (
'S00', 'S01', 'C001', 'C004', 'C006') then b.real_pay else 0 end), 2), 0) ak092,
nvl(round(sum(case when b.fund_id in ('999', '003') and b.policy_item_code in ('S00', 'S01', 'C001', 'C004', 'C006') then
b.real_pay else 0 end), 2), 0) ak094,
nvl(round(sum(case when b.fund_id in ('003', '999') then b.real_pay else 0 end), 2), 0) ak09
```
这个视图的定义包含多个聚合函数、条件判断以及空值处理，可能涉及复杂的表连接和计算，这可能是导致查询效率低下的原因。在SQL优化中，我们需要考虑以下几个方面：
1. 执行计划分析：首先，通过执行`EXPLAIN PLAN`或使用数据库自带的性能分析工具，如Oracle的`DBMS_XPLAN`，查看查询的执行计划，了解哪些操作（如全表扫描、索引查找、连接等）可能导致性能问题。
2. 索引优化：检查查询语句中的字段是否已经建立合适的索引，特别是`WHERE`子句中的条件字段。在这个例子中，`aac002`字段可能是优化的关键，因为它是查询的唯一条件。如果该字段没有索引，可以考虑创建。
3. 视图重写：如果视图的复杂性是问题所在，可以尝试对视图进行重写，减少计算逻辑，或者直接在查询中处理部分计算，以简化执行计划。
4. SQL Profile的创建与应用：在Oracle数据库中，SQL Profile是一种自动调整机制，可以根据执行计划的实际运行情况提供改进建议。可以使用`DBMS_SQLTUNE`包创建SQL Profile，然后应用到有问题的SQL语句上，以改进其执行计划。
5. 数据分区：如果底层表的数据量非常大，考虑是否可以对表进行分区，尤其是基于查询条件的字段，如时间、地区等，可以显著提高查询效率。
6. 并行查询：对于大型查询，启用并行查询（Parallel Query）可以将工作负载分散到多个处理器，加速处理速度。
7. 统计信息更新：确保数据库中的统计信息是最新的，以便优化器能做出正确的选择。过时的统计信息可能导致优化器选择不理想的执行计划。
8. SQL语句重构：在某些情况下，可能需要完全重构SQL语句，比如使用子查询、临时表或存储过程来提高性能。
在实际操作中，应结合具体数据库的特性和环境，通过测试不同的优化策略，找到最适合的解决方案。在优化过程中，需要不断监控查询性能，确保优化措施有效且不会带来其他负面影响。同时，也要考虑到系统的整体稳定性和资源消耗。
使用使用SQL Profile进行进行SQL优化案例优化案例
一个社保系统的自助查询系统查询个人医疗费用明细的查询语句要用一分多钟还没查询出来，语句如下：
select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'
从上面的语句可知是从视图 v_zzzd_ylbx_ylfymxcx中查询数据。v_zzzd_ylbx_ylfymxcx视图的创建语句如下：
create or replace view v_zzzd_ylbx_ylfymxcx as
select a.indi_id aac001，a.idcard aac002，'' aof008，a.center_id aab301，
a.name aac003，a.hospital_id akf008，d.hospital_name akf009，a.serial_no akf010，
f.biz_name akf011，
nvl(round(sum(b.real_pay)，2)，0) akf012，
nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end)，2)，0) akf013， 0 akf014，0 akf015，0 akf016，
nvl(round(sum(case when b.fund_id = '001' then b.real_pay else 0 end)，2)，0) ak093，
nvl(round(sum(b.real_pay)，2)，0) – nvl(round(sum(case when b.fund_id in ('999'，'003') and b.policy_item_code in (
'S00'，'S01'，'C001'，'C004''C006') then b.real_pay else 0 end)，2)，0) ak092，
nvl(round(sum(case when b.fund_id in ('999'，'003') and b.policy_item_code in ( 'S00'，'S01'，'C001'，'C004''C006') then
b.real_pay else 0 end)，2)，0) ak094，
nvl(round(sum(case when b.fund_id in('003'， '999') then b.real_pay else 0 end)，2)，0) ak095，
a.fin_date akf017，to_char(nvl(a.in_days，0)) akf018，
nvl(round(sum(case when b.fund_id = '003' then b.real_pay else 0 end)，2)，0) akf019，
nvl(round(sum(case when b.fund_id in( '001'，'201'，'301' ) then b.real_pay else 0 end)，2)，0) akf020
from bs_insured h，mt_biz_fin a ，mt_pay_record_fin b，bs_disease c，bs_hospital d ，bs_hosp_level e ，bs_biztype
f，bs_corp g
where h.indi_id=a.indi_id
and a.hospital_id = b.hospital_id
and a.serial_no = b.serial_no
and a.biz_type = f.biz_type
and a.center_id = f.center_id
and a.center_id=c.center_id
and a.fin_disease=c.icd
and a.hospital_id = d.hospital_id
and d.hosp_level=e.hosp_level
and a.biz_type in ('10'，'11'，'12'，'13'，'16'，'17')
and a.valid_flag = 1
and b.valid_flag = 1
and a.pers_type in ('1'，'2')
and a.corp_id = g.corp_id
group by a.indi_id ，a.idcard ，a.center_id，a.name ，a.hospital_id ，d.hospital_name，a.serial_no ，
f.biz_name，a.fin_date，a.in_days;
生成SQL Profile有两种方式：自动和手动方式，这里使用自动方式来生成SQL Profile.
下面创建一个SQL自动调整优化任务：
SQL> declare
2 my_task_name varchar2(30);
3 my_sqltext clob;
4 begin
5 my_sqltext :='select * from v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
6 my_task_name :=dbms_sqltune.create_tuning_task(
7 sql_text => my_sqltext，
8 user_name => 'INSUR_CHANGDE'，
9 scope=>'COMPREHENSIVE'，
10 time_limit=>60，
11 task_name => 'my_sql_tuning_task_2014080803'，
12 description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080803');
3 end;
4 /
PL/SQL procedure successfully completed.通过下面的语句查询优化建议
SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080803') from dual;
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : my_sql_tuning_task_2014080803
Tuning Task Owner : INSUR_CHANGDE
Scope : COMPREHENSIVE
下载后可阅读完整内容，剩余7页未读，立即下载
2012-12-29 上传
2008-12-09 上传
2009-03-24 上传
2023-04-28 上传
2023-10-20 上传
2023-04-22 上传
2023-06-09 上传
2023-04-07 上传
2023-04-04 上传
weixin_38590790
- 粉丝: 4
- 资源: 940
我的内容管理
展开
最新资源
- 探索数据转换实验平台在设备装置中的应用
- 使用git-log-to-tikz.py将Git日志转换为TIKZ图形
- 小栗子源码2.9.3版本发布
- 使用Tinder-Hack-Client实现Tinder API交互
- Android Studio新模板：个性化Material Design导航抽屉
- React API分页模块：数据获取与页面管理
- C语言实现顺序表的动态分配方法
- 光催化分解水产氢固溶体催化剂制备技术揭秘
- VS2013环境下tinyxml库的32位与64位编译指南
- 网易云歌词情感分析系统实现与架构
- React应用展示GitHub用户详细信息及项目分析
- LayUI2.1.6帮助文档API功能详解
- 全栈开发实现的chatgpt应用可打包小程序/H5/App
- C++实现顺序表的动态内存分配技术
- Java制作水果格斗游戏：策略与随机性的结合
- 基于若依框架的后台管理系统开发实例解析
