一个社保系统的自助查询系统查询个人医疗费用明细的查询语句要用一分多钟还没查询出来，语句如下：
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毫秒