使用SQL Profile进行SQL优化案例
作者：网络转载 发布时间：[ 2014/8/12 10:28:48 ] 推荐标签：SQL 数据库
通过下面的语句查询优化建议
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
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 08/08/2014 19:42:47
Completed at : 08/08/2014 19:43:49
Number of Index Findings : 1
Number of SQL Restructure Findings: 1
Number of Errors : 1
-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID : 0rpt6bzp60cjm
SQL Text : select * from v_zzzd_ylbx_ylfymxcx where
aac002='430703198202280017'
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以改进此语句的执行计划。
Recommendation (estimated benefit: 99.98%)
------------------------------------------
- 考虑运行可以改进物理方案设计的 Access Advisor 或者创建推荐的索引。
create index INSUR_CHANGDE.IDX$$_429C0001 on
INSUR_CHANGDE.MT_BIZ_FIN("IDCARD"，TO_NUMBER("VALID_FLAG")，"PERS_TYPE"，"BIZ_
TYPE");
这里在创建IDX$$_429C0001索引时，TO_NUMBER("VALID_FLAG")这是因为表MT_BIZ_FIN中的valid_flag是varchar2而视图定义中写成了valid_flag=1的原因
- 考虑运行可以改进物理方案设计的 Access Advisor 或者创建推荐的索引。
create index INSUR_CHANGDE.IDX$$_429C0002 on
INSUR_CHANGDE.MT_PAY_RECORD_FIN("HOSPITAL_ID"，"SERIAL_NO");
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是， 使用典型的 SQL 工作量运行 "Access Advisor"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案， 包括计算索引维护的开销和附加的空间消耗。
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
谓词 TO_NUMBER("A"."VALID_FLAG")=1 (在执行计划的行 ID 9 处使用) 包含索引列 "VALID_FLAG"
的隐式数据类型转换。此隐式数据类型转换使优化程序无法有效地使用表 "INSUR_CHANGDE"."MT_BIZ_FIN" 的索引。
这是因为表MT_BIZ_FIN中的valid_flag是varchar2而视图定义中写成了valid_flag=1的原因
Recommendation
--------------
- 将谓词重写为等价型以便利用索引。
Rationale
---------
如果谓词是不等式条件或者如果存在关于索引列的表达式或隐式数据类型转换， 则优化程序无法使用索引。
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- 当前操作因超时而中断。这是因为优化任务设置的超时时间为60秒的原因
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3562745886
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1505 | 127K (2)| 00:25:25 |
| 1 | HASH GROUP BY | | 7 | 1505 | 127K (2)| 00:25:25 |
| 2 | NESTED LOOPS | | 7 | 1505 | 127K (2)| 00:25:25 |
| 3 | NESTED LOOPS | | 7 | 1491 | 127K (2)| 00:25:25 |
| 4 | NESTED LOOPS | | 7 | 1253 | 127K (2)| 00:25:25 |
| 5 | NESTED LOOPS | | 7 | 1127 | 127K (2)| 00:25:25 |
| 6 | NESTED LOOPS | | 7 | 1085 | 127K (2)| 00:25:25 |
| 7 | NESTED LOOPS | | 14 | 1554 | 127K (2)| 00:25:25 |
| 8 | NESTED LOOPS | | 14 | 1484 | 127K (2)| 00:25:25 |
|* 9 | TABLE ACCESS FULL | MT_BIZ_FIN | 14 | 1232 | 127K (2)| 00:25:25 |
| 10 | TABLE ACCESS BY INDEX ROWID| BS_BIZTYPE | 1 | 18 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_BS_BIZTYPE | 1 | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_1 | 1 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("A"."IDCARD"='430703198202280017' AND TO_NUMBER("A"."VALID_FLAG")=1 AND
("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2') AND ("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR
"A"."BIZ_TYPE"='12' OR "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17'))
11 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
12 - access("A"."CORP_ID"="G"."CORP_ID")
13 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
14 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
15 - access("H"."INDI_ID"="A"."INDI_ID")
16 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
18 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
19 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
这是按优化建议创建两个索引后的执行计划
2- Using New Indices
--------------------
Plan hash value: 2373509962
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1505 | 14 (8)| 00:00:01 |
| 1 | HASH GROUP BY | | 7 | 1505 | 14 (8)| 00:00:01 |
| 2 | NESTED LOOPS | | 7 | 1505 | 13 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 7 | 1470 | 12 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 7 | 1428 | 11 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 7 | 1302 | 10 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 7 | 1288 | 9 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 7 | 1050 | 7 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 14 | 1484 | 4 (0)| 00:00:01 |
| 9 | INLIST ITERATOR | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 14 | 1232 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX$$_429C0001 | 14 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | BS_BIZTYPE | 1 | 18 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_BS_BIZTYPE | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("A"."IDCARD"='430703198202280017' AND "MT_BIZ_FIN".???)
filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
"A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
13 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
14 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
15 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
17 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
18 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
20 - access("H"."INDI_ID"="A"."INDI_ID")
21 - access("A"."CORP_ID"="G"."CORP_ID")
-------------------------------------------------------------------------------
因为前一次优化任务因为超时中断了所以再次进行SQL自动优化任务，并将超时时间设置为600秒
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=>600，
11 task_name => 'my_sql_tuning_task_2014080804'，
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_2014080804');
3 end;
4 /
PL/SQL procedure successfully completed.
本文内容不用于商业目的，如涉及知识产权问题，请权利人联系SPASVO小编(021-61079698-8054)，我们将立即处理，马上删除。
相关推荐
在测试数据库性能时，需要注意哪些方面的内容？测试管理工具TC数据库报错的原因有哪些？怎么解决？数据库的三大范式以及五大约束编程常用的几种时间戳转换(java .net 数据库)优化mysql数据库的几个步骤数据库并行读取和写入之Python实现深入理解数据库(DB2)缓冲池（BufferPool）国内三大云数据库测试对比预警即预防：6大常见数据库安全漏洞数据库规划、设计与管理数据库-事务的概念SQL Server修改数据库物理文件存在位置使用PHP与SQL搭建可搜索的加密数据库用Python写一个NoSQL数据库详述 SQL 中的数据库操作详述 SQL 中的数据库操作Java面试准备:数据库MySQL性能优化
sales@spasvo.com