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