通过下面的语句查询优化建议
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. |
