使用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
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.
透過下面的語句查詢最佳化建議
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")
-------------------------------------------------------------------------------
執行下面的語句來接受SQL 概要檔案
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2014080804', replace => TRUE,force_match => TRUE); PL/SQL procedure successfully completed.
再來測試該語句
SQL> select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017';
AAC001 AAC002 AAB301 AKF008 AKF010 AKF011 AKF012 AKF013 AKF014
------- -------------------- ------- ----------- -------- ------- -------- -------- -------
44499 430703198202280017 430701 4307000305 18000304 購藥 19 19 0
44499 430703198202280017 430701 4307030186 14200513 購藥 34 34 0
44499 430703198202280017 430701 4307000070 11535710 購藥 7 7 0
44499 430703198202280017 430701 4307000211 13157523 購藥 10 10 0
44499 430703198202280017 430701 4307000178 10504509 購藥 37.2 37.2 0
44499 430703198202280017 430701 4307000025 14186783 購藥 6.5 6.5 0
44499 430703198202280017 430701 4307000211 18855092 購藥 51 51 0
44499 430703198202280017 430701 4307000025 23298689 購藥 32 32 0
44499 430703198202280017 430701 4307000305 17251025 購藥 20 20 0
44499 430703198202280017 430701 4307000211 11246538 購藥 10.5 10.5 0
44499 430703198202280017 430701 4307000011 20015343 門診 20 20 0
44499 430703198202280017 430701 4307000135 13248044 購藥 103.2 103.2 0
44499 430703198202280017 430701 4307000070 17745955 購藥 20 20 0
44499 430703198202280017 430701 4307000011 23548511 門診 94.2 94.2 0
44499 430703198202280017 430701 4307000305 18000319 購藥 16 16 0
44499 430703198202280017 430701 4307000025 20291585 購藥 374 374 0
44499 430703198202280017 430701 4307000075 11425923 購藥 11.8 11.8 0
44499 430703198202280017 430701 4307000089 23298593 購藥 170.8 170.8 0
44499 430703198202280017 430701 4307000110 11548588 購藥 28.5 28.5 0
44499 430703198202280017 430701 4307000011 18454938 門診 105.8 105.8 0
44499 430703198202280017 430701 4307000075 11757756 購藥 282.7 282.7 0
44499 430703198202280017 430701 4307000025 10545113 購藥 340.8 340.8 0
44499 430703198202280017 430701 4307000285 17325032 購藥 67.5 67.5 0
44499 430703198202280017 430701 4307000070 17341126 購藥 87 87 0
44499 430703198202280017 430701 4307000211 17655418 購藥 20 20 0
44499 430703198202280017 430701 4307000011 19042114 門診 127.2 127.2 0
44499 430703198202280017 430701 4307000211 18070864 購藥 6 6 0
44499 430703198202280017 430701 4307000011 23547574 門診 36 36 0
28 rows selected.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 1n2t3u0q0gmhz, child number 0
-------------------------------------
select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'
Plan hash value: 484693682
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| |
| 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 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / B@SEL$2
10 - SEL$F5BB74E1 / F@SEL$2
12 - SEL$F5BB74E1 / A@SEL$2
13 - SEL$F5BB74E1 / A@SEL$2
14 - SEL$F5BB74E1 / G@SEL$2
15 - SEL$F5BB74E1 / D@SEL$2
16 - SEL$F5BB74E1 / D@SEL$2
17 - SEL$F5BB74E1 / E@SEL$2
18 - SEL$F5BB74E1 / H@SEL$2
19 - SEL$F5BB74E1 / C@SEL$2
20 - SEL$F5BB74E1 / B@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('optimizer_index_cost_adj' 20)
OPT_PARAM('optimizer_index_caching' 90)
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "F"@"SEL$2")
INDEX_RS_ASC(@"SEL$F5BB74E1" "A"@"SEL$2" ("MT_BIZ_FIN"."IDCARD" "MT_BIZ_FIN"."VALID_FLAG"
"MT_BIZ_FIN"."PERS_TYPE" "MT_BIZ_FIN"."BIZ_TYPE"))
NUM_INDEX_KEYS(@"SEL$F5BB74E1" "A"@"SEL$2" "IDX$$_429C0001" 3)
INDEX(@"SEL$F5BB74E1" "G"@"SEL$2" ("BS_CORP"."CORP_ID"))
INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))
INDEX(@"SEL$F5BB74E1" "E"@"SEL$2" ("BS_HOSP_LEVEL"."HOSP_LEVEL"))
INDEX(@"SEL$F5BB74E1" "H"@"SEL$2" ("BS_INSURED"."INDI_ID"))
INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("BS_DISEASE"."CENTER_ID" "BS_DISEASE"."ICD"))
INDEX(@"SEL$F5BB74E1" "B"@"SEL$2" ("MT_PAY_RECORD_FIN"."HOSPITAL_ID"
"MT_PAY_RECORD_FIN"."SERIAL_NO"))
LEADING(@"SEL$F5BB74E1" "F"@"SEL$2" "A"@"SEL$2" "G"@"SEL$2" "D"@"SEL$2" "E"@"SEL$2" "H"@"SEL$2"
"C"@"SEL$2" "B"@"SEL$2")
USE_HASH(@"SEL$F5BB74E1" "A"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "G"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "E"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "H"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "C"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "B"@"SEL$2")
END_OUTLINE_DATA
*/
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")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."INDI_ID"[NUMBER,22], "A"."IDCARD"[VARCHAR2,25], "A"."CENTER_ID"[VARCHAR2,10],
"A"."NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20], "D"."HOSPITAL_NAME"[VARCHAR2,70],
"A"."SERIAL_NO"[VARCHAR2,16], "F"."BIZ_NAME"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7],
"A"."IN_DAYS"[NUMBER,22], SUM("B"."REAL_PAY")[22], SUM(CASE "B"."FUND_ID" WHEN '003' THEN
"B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" ELSE 0 END
)[22], SUM(CASE WHEN (("B"."FUND_ID"='999' OR "B"."FUND_ID"='003') AND
("B"."POLICY_ITEM_CODE"='S00' OR "B"."POLICY_ITEM_CODE"='S01' OR "B"."POLICY_ITEM_CODE"='C001' OR
"B"."POLICY_ITEM_CODE"='C004''C006')) THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE "B"."FUND_ID"
WHEN '003' THEN "B"."REAL_PAY" WHEN '999' THEN "B"."REAL_PAY" ELSE 0 END )[22], SUM(CASE
"B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" WHEN '201' THEN "B"."REAL_PAY" WHEN '301' THEN
"B"."REAL_PAY" ELSE 0 END )[22]
2 - "B"."POLICY_ITEM_CODE"[VARCHAR2,20], "B"."FUND_ID"[VARCHAR2,3], "B"."REAL_PAY"[NUMBER,22]
3 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
"A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
"A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7],
"D"."HOSPITAL_NAME"[VARCHAR2,70], "B".ROWID[ROWID,10]
4 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
"A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
"A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DATE"[DATE,7],
"D"."HOSPITAL_NAME"[VARCHAR2,70]
5 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
"A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
"A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
"A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70]
6 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
"A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
"A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
"A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70]
7 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
"A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
"A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
"A"."FIN_DATE"[DATE,7], "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1]
8 - "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20], "A"."HOSPITAL_ID"[VARCHAR2,20],
"A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20],
"A"."IDCARD"[VARCHAR2,25], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
"A"."FIN_DATE"[DATE,7]
9 - (#keys=2) "A"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_NAME"[VARCHAR2,20],
"A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."INDI_ID"[NUMBER,22],
"A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22],
"A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7]
10 - "F"."CENTER_ID"[VARCHAR2,10], "F"."BIZ_TYPE"[CHARACTER,2], "F"."BIZ_NAME"[VARCHAR2,20]
11 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2],
"A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."IDCARD"[VARCHAR2,25],
"A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22], "A"."FIN_DISEASE"[VARCHAR2,20],
"A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10]
12 - "A"."HOSPITAL_ID"[VARCHAR2,20], "A"."SERIAL_NO"[VARCHAR2,16], "A"."BIZ_TYPE"[VARCHAR2,2],
"A"."INDI_ID"[NUMBER,22], "A"."NAME"[VARCHAR2,20], "A"."PERS_TYPE"[VARCHAR2,3],
"A"."IDCARD"[VARCHAR2,25], "A"."CORP_ID"[NUMBER,22], "A"."IN_DAYS"[NUMBER,22],
"A"."FIN_DISEASE"[VARCHAR2,20], "A"."FIN_DATE"[DATE,7], "A"."CENTER_ID"[VARCHAR2,10]
13 - "A".ROWID[ROWID,10], "A"."IDCARD"[VARCHAR2,25], "A"."PERS_TYPE"[VARCHAR2,3],
"A"."BIZ_TYPE"[VARCHAR2,2]
15 - "D"."HOSPITAL_NAME"[VARCHAR2,70], "D"."HOSP_LEVEL"[CHARACTER,1]
16 - "D".ROWID[ROWID,10]
20 - "B".ROWID[ROWID,10]
Note
-----
- SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement
163 rows selected.
從SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement 這個資訊就是知道已經使用了SQL概要檔案
現在語句執行只要0.1毫秒
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1349468/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用SQL Profile進行SQL優化案例SQL優化
- sql最佳化：使用sql profile最佳化sql語句SQL
- sql profile使用SQL
- ORACLE SQL PROFILE使用OracleSQL
- sql profile的使用SQL
- 使用sql profile固定執行計劃SQL
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- SQL Server profile使用技巧SQLServer
- DM 傳統行業SQL最佳化案例行業SQL
- sql profileSQL
- sql_profile的使用(一)SQL
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- SQL最佳化案例-使用with as最佳化Subquery Unnesting（七）SQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- 使用coe_xfr_sql_profile固定執行計劃SQL
- 使用SQL PROFILE 給出合理的執行計劃SQL
- SQL最佳化案例-正確的使用索引（二）SQL索引
- 檢視SQL PROFILE使用的HINTSQL
- 使用sqlmap進行sql注入SQL
- SQL PROFILE修改固定執行計劃SQL
- sql tuning task和sql profileSQL
- SQL最佳化案例-union代替or（九）SQL
- Oracle優化案例-coe_xfr_sql_profile固定執行計劃與刪除profile（二十五）Oracle優化SQL
- SQL PROFILE 測試SQL
- 控制執行計劃之-SQL Profile（一）SQL
- 用sql profile來固定執行計劃SQL
- Sql 巢狀迴圈最佳化案例SQL巢狀
- 自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)型別SQL
- OB案例、金融行業核心系統跑批SQL最佳化行業SQL
- MySQL Profile檢視SQL的資源使用MySql
- Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹SQL
- SQL Profile 實驗03SQL
- SQL Profile 實驗02SQL
- SQL Profile 實驗01SQL
- 使用exp進行SQL報錯注入SQL
- ORACLE 使用TRACE進行SQL效能分析OracleSQL
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- sql最佳化：使用儲存提綱穩定sql執行計劃SQL