通过下面的语句查询优化建议
SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080806') from dual; GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : my_sql_tuning_task_2014080804 Tuning Task Owner : INSUR_CHANGDE Scope : COMPREHENSIVE Time Limit(seconds) : 600 Completion Status : COMPLETED Started at : 08/08/2014 20:03:46 Completed at : 08/08/2014 20:04:27 Number of SQL Profile Findings : 1 ------------------------------------------------------------------------------- Schema Name: INSUR_CHANGDE SQL ID : 0rpt6bzp60cjm SQL Text : select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017' ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- |
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 28.75%)
------------------------------------------
- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2014080804', replace => TRUE); ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3514293130 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 251 | 53965 | 36 (6)| 00:00:01 | | 1 | HASH GROUP BY | | 251 | 53965 | 36 (6)| 00:00:01 | | 2 | NESTED LOOPS | | 251 | 53965 | 35 (3)| 00:00:01 | | 3 | NESTED LOOPS | | 252 | 52920 | 34 (3)| 00:00:01 | | 4 | NESTED LOOPS | | 252 | 51408 | 33 (4)| 00:00:01 | |* 5 | HASH JOIN | | 251 | 46686 | 32 (4)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 28 | 4704 | 28 (0)| 00:00:01 | | 8 | NESTED LOOPS | | 28 | 3472 | 22 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 79 | 9638 | 21 (0)| 00:00:01 | | 10 | INLIST ITERATOR | | | | | | | 11 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 79 | 6952 | 6 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | IDX$$_429C0001 | 27 | | 1 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | |* 15 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | 1 (0)| 00:00:01 | | 17 | TABLE ACCESS FULL | BS_BIZTYPE | 96 | 1728 | 3 (0)| 00:00:01 | |* 18 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 | |* 19 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 | |* 20 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID") 6 - filter("B"."VALID_FLAG"='1') 12 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2')) 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') 14 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID") 15 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL") 16 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO") 18 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD") 19 - access("H"."INDI_ID"="A"."INDI_ID") 20 - access("A"."CORP_ID"="G"."CORP_ID") 2- Using SQL Profile -------------------- Plan hash value: 484693682 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 251 | 53965 | 25 (4)| 00:00:01 | | 1 | HASH GROUP BY | | 251 | 53965 | 25 (4)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 251 | 53965 | 24 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 28 | 4788 | 19 (6)| 00:00:01 | | 5 | NESTED LOOPS | | 28 | 4284 | 18 (6)| 00:00:01 | | 6 | NESTED LOOPS | | 28 | 4116 | 17 (6)| 00:00:01 | | 7 | NESTED LOOPS | | 28 | 4060 | 16 (7)| 00:00:01 | | 8 | NESTED LOOPS | | 28 | 3108 | 10 (0)| 00:00:01 | |* 9 | HASH JOIN | | 28 | 2968 | 9 (0)| 00:00:01 | | 10 | TABLE ACCESS FULL | BS_BIZTYPE | 96 | 1728 | 3 (0)| 00:00:01 | | 11 | INLIST ITERATOR | | | | | | | 12 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 79 | 6952 | 6 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | IDX$$_429C0001 | 27 | | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | |* 17 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 | |* 20 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("B"."VALID_FLAG"='1') 9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID") 13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND ("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2')) 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') 14 - access("A"."CORP_ID"="G"."CORP_ID") 16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID") 17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL") 18 - access("H"."INDI_ID"="A"."INDI_ID") 19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD") 20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO") ------------------------------------------------------------------------------- |
