sql优化案例（视图问题）

业务反应某sql执行慢，影响部分业务。
通过v$session视图获取会话当前的sql_id,在通过v$sql_bind_capture或dba_hist_sqlbind等视图获取该sql绑定变量的值。

设置会话级别参数（执行计划详细信息）：
ALTER SESSION SET statistics_level=all;
执行sql:
SELECT /*test0601*/ *
FROM (SELECT row_.*, rownum rownum_
FROM (SELECT
c.cons_type,
c.cons_id,
c.cons_no,
c.cons_name,
c.elec_addr,
c.elec_type_code,
c.volt_code,
c.mr_sect_no,
c.org_no,
c.checker_no,
c.last_chk_date,
c.chk_cycle,
p.plan_no,
p.app_no,
pd.dtl_id,
pd.type_code,
pd.chk_month,
pd.plan_status_code,
pd.chk_date AS plan_chk_date,
pd.actual_chk_date AS actual_chk_date,
pd.content_code,
r.id,
r.checker_name,
r.otherperson_name,
r.chk_date,
c.contract_cap,
p.plan_desc
FROM s_chk_plan p,
s_chk_plan_det pd,
s_inspect_rslt r,
SGPM.v_cons c,
s_hr_important_cust shic
WHERE p.plan_no = pd.plan_no
AND pd.dtl_id = r.dtl_id(+)
AND pd.id = r.id(+)
AND pd.cons_id = c.cons_id(+)
AND p.chk_month NOT IN ('year', 'YEAR')
AND c.cons_no = shic.cust_no(+)
AND PD.PLAN_STATUS_CODE = '04'
AND PD.TYPE_CODE = '100'
AND PD.PLAN_NO = '11889920984'
and r.app_no is null
and (p.plan_desc not like 'SHFQD;%' or p.plan_desc is null)
and c.cons_type = '1'
AND c.org_no in
(select org_no
from o_org
start with org_no = '3340550'
connect by prior org_no = p_org_no)
and c.org_no in (SELECT inst_no
FROM dyx_inst_rela
WHERE inst_type = 'org'
AND busi_type = 'be'
AND top_inst_no = '0000607841'
AND top_inst_type = 'dept'
AND busi_inst_flag = '1')
AND PD.PLAN_YEAR = '2020'
AND PD.CHK_MONTH = '202006'
) row_
WHERE rownum <= 500)
WHERE rownum_ > 0

select sql_id,child_number,sql_text from v$sql where sql_text like '%test0601%'

select * from table(dbms_xplan.display_cursor('030c5894z15hy','0','allstats last'))


Plan hash value: 2673931418

 

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

从执行计划查看时间主要耗费在

 

 

该步骤对应的就是SGPM.v_cons c视图
查看视图：
CREATE OR REPLACE VIEW SGPM.V_CONS AS
SELECT '1' cons_type, cons_id, vat_id, cust_id, cons_no, cons_name, cust_query_no,
tmp_pay_rela_no, orgn_cons_no, volt_code, elec_addr, trade_code, elec_type_code,
contract_cap, run_cap, shift_no, lode_attr_code, ps_volt, hec_industry_code,
holiday, build_date, ps_date, cancel_date, due_date, notify_mode, settle_mode,
status_code, org_no, rrio_code, chk_cycle, last_chk_date, checker_no, poweroff_code,
transfer_code, mr_sect_no, note_type_code, tmp_flag, tmp_date, cons_sort_code,
NULL gc_sort_code, rural_cons_code, NULL abso_mode, NULL gc_type, NULL invest_mode,
NULL cen_gov_sub, NULL pro_gra, NULL city_gra, NULL county_gra, NULL taxer_type,
NULL switch_type, NULL inst_loc, NULL gc_ps_type,MARKET_PROP_SORT
FROM c_cons
UNION ALL
SELECT '2' cons_type, gc_id cons_id, NULL vat_id, cust_id, gc_no cons_no,
gc_name cons_name, NULL cust_query_no, NULL tmp_pay_rela_no,
orgn_gc_no orgn_cons_no, volt_code, gc_addr elec_addr, trade_code,
NULL elec_type_code, contract_cap, 0 run_cap, NULL shift_no, NULL lode_attr_code,
NULL ps_volt, NULL hec_industry_code, NULL holiday, build_date, gc_date ps_date,
cancel_date, NULL due_date, NULL notify_mode, NULL settle_mode, status_code, org_no,
NULL rrio_code, chk_cycle, last_chk_date, checker_no, NULL poweroff_code,
'0' transfer_code, mr_sect_no, NULL note_type_code, '03' tmp_flag, NULL tmp_date,
NULL cons_sort_code, gc_sort_code, NULL rural_cons_code, abso_mode, gc_type,
invest_mode, cen_gov_sub, pro_gra, city_gra, county_gra, taxer_type, switch_type,
inst_loc, gc_ps_type,null MARKET_PROP_SORT
FROM fc_gc;
发现是两表的union all操作，查询优化器也未使用视图合并去优化sql。
仔细观察过滤条件c.cons_type = '1'是写死的，未使用绑定变量，我们发现就是单独查询c_cons的业务逻辑，fc_gc表对应的cons_type='2'
依次我们可以直接将上述sql改写为
SELECT /*test0602*/ *
FROM (SELECT row_.*, rownum rownum_
FROM (SELECT
'1',
c.cons_id,
c.cons_no,
c.cons_name,
c.elec_addr,
c.elec_type_code,
c.volt_code,
c.mr_sect_no,
c.org_no,
c.checker_no,
c.last_chk_date,
c.chk_cycle,
p.plan_no,
p.app_no,
pd.dtl_id,
pd.type_code,
pd.chk_month,
pd.plan_status_code,
pd.chk_date AS plan_chk_date,
pd.actual_chk_date AS actual_chk_date,
pd.content_code,
r.id,
r.checker_name,
r.otherperson_name,
r.chk_date,
c.contract_cap,
p.plan_desc
FROM s_chk_plan p,
s_chk_plan_det pd,
s_inspect_rslt r,
SGPM.c_cons c,
s_hr_important_cust shic
WHERE p.plan_no = pd.plan_no
AND pd.dtl_id = r.dtl_id(+)
AND pd.id = r.id(+)
AND pd.cons_id = c.cons_id(+)
AND p.chk_month NOT IN ('year', 'YEAR')
AND c.cons_no = shic.cust_no(+)
AND PD.PLAN_STATUS_CODE = '04'
AND PD.TYPE_CODE = '100'
AND PD.PLAN_NO = '11889920984'
and r.app_no is null
and (p.plan_desc not like 'SHFQD;%' or p.plan_desc is null)
--and c.cons_type = '1'
AND c.org_no in
(select org_no
from o_org
start with org_no = '3340550'
connect by prior org_no = p_org_no)
and c.org_no in (SELECT inst_no
FROM dyx_inst_rela
WHERE inst_type = 'org'
AND busi_type = 'be'
AND top_inst_no = '0000607841'
AND top_inst_type = 'dept'
AND busi_inst_flag = '1')
AND PD.PLAN_YEAR = '2020'
AND PD.CHK_MONTH = '202006'
) row_
WHERE rownum <= 500)
WHERE rownum_ > 0
执行计划如下：

 

 

改写后sql查询为毫秒级别。

该案例主要问题还是在视图那边，应为视图比较复杂，导致查询优化器为能使用视图合并的查询转换。
同时我们发现sql逻辑存在问题，其实该sql无须查询这个视图，查询单表就能获取数据。
如业务逻辑需要两个表数据，则将view里的两个表拆开。