Oracle ACS罗敏:过度使用视图的弊端

  1. 视图的基本作用
    作为数据库基本对象之一的视图(View),在数据库设计和应用软件开发中扮演着重要角色。首先,视图可以将某些复杂应用逻辑进行封装和屏蔽,为普通用户或开发人员提供给一种简洁的数据访问方式。其次,视图在数据库安全性,特别是访问控制方面可起到一定作用,例如可根据访问权限要求,设计只返回某些表、某些字段、某些记录的视图。第三,视图还可提高数据库设计和应用软件开发的模块化、层次化,从而有效提高整个系统设计、开发、运维的可管理性。
    正因为视图的上述种种益处,于是我们发现在很多大型应用软件中,视图得到了广泛运用。但是,凡事都有利弊,过度使用视图的弊端何在呢?这就是本文的主题了。
  2. 典型案例分析
    我们先分析一个某行业的视图使用典型案例。以下就是一个非常复杂、冗长的SQL语句,为便于大家阅读,只保留了该语句的关键结构部分:
    select AU.YEAR_MON "_yearMon",
    va.RPT_GRP_NAME "_project_name",
    P.HSSS "_hsss",
    … …
    round(P.ZHQYB, 2) "_zhqyb"
    from PC_RPT_SEQU_GEO_V_M VA,
    (select c.entity_id,
    … …
    from pc_production_monthly c
    where year_mon between '201408' and '201408'
    and driving_type_code = '000') p,
    (select a.entity_id,
    … …
    from pc_pro_method_mon A
    where year_mon between '201408' and '201408'
    group by a.entity_id,
    a.year_mon,
    a.driving_type_code,
    a.entity_type,
    A.PRODUCT_CLASS) n,
    (SELECT AU.YEAR_MON, AU.ORG_ID, ORG.ORG_NAME, ORG.REPORT_SEQU_NUM
    FROM PC_MONTH_DATA_AUDIT AU, PC_ORGANIZATION ORG
    WHERE AU.VERIFIED_STATUS IN (1, 0)
    AND LOWER(AU.TABLE_CODE) IN
    ('pc_production_monthly', 'pc_pro_method_mon')
    AND AU.ORG_ID = ORG.org_id
    AND ORG.org_level = 10
    GROUP BY AU.YEAR_MON, AU.ORG_ID, ORG.ORG_NAME, ORG.REPORT_SEQU_NUM
    HAVING COUNT(*) = 2
    ORDER BY ORG.REPORT_SEQU_NUM) AU
    where p.entity_id = n.entity_id(+)
    and p.ny = n.ny(+)
    and p.entity_type = n.entity_type(+)
    and p.driving_type_code = n.driving_type_code(+)
    AND P.PRODUCT_CLASS = N.PRODUCT_CLASS(+)
    and p.entity_type = n.entity_type(+)
    AND va.ORG_ID = AU.ORG_ID
    AND P.NY = AU.YEAR_MON
    and va.RPT_GRP_TYPE = p.entity_type
    and va.rpt_grp_id = p.entity_id
    and va.driving_type_code = p.driving_type_code
    and va.product_class = p.product_class
    and VA.RPT_CODE = 'CYCYTKFZHSJB1'
    AND VA.YEAR_MON = (SELECT MAX(V.YEAR_MON)
    FROM v_pc_rpt_sequ_geo V
    WHERE V.YEAR_MON <= '201408'
    AND V.RPT_CODE = 'CYCYTKFZHSJB1'
    and v.org_id = va.org_id)
    AND va.org_id in ('LHJZvpwfad')
    order by va.RPT_SEQU_NUM, AU.YEAR_MON

实际语句长达200多行,更为恐怖的是语句中的PC_RPT_SEQU_GEO_V_M是一个三层嵌套视图,每个层次都是长达数百行的复杂逻辑视图。语句的执行计划更是冗长、复杂无比,为不让大家感到枯燥,就不在此文展开了。
虽然看似整个语句的成本不高,也没有典型的全表扫描、全索引扫描等问题,但语句的执行效率的确不高。是啊,Oracle优化器仅仅是分析和解析该语句,就够忙乎一阵子了。
事实上,通过与应用开发人员和业务人员的共同分析,我们发现视图PC_RPT_SEQU_GEO_V_M的数据基本为静态数据,即一年更新一次。因此,可以将该视图直接转换为一个中间临时表或定期更新的物化视图,使得该语句避免进行复杂的逻辑计算,而是直接引用该中间临时表或物化视图的数据。经此优化之后,性能大幅度提升!
  1. 如何避免过度使用视图?
    上述语句就是典型的过度使用视图带来的典型问题,如何避免此类问题?欲回答此问题,我们先看看Oracle官方对视图使用的建议:
    Though views provide clean programming interfaces, they can cause suboptimal, resource-intensive queries when nested too deeply. The worst type of view use is creating joins on views that reference other views, which in turn reference other views. In many cases, developers can satisfy the query directly from the table without using a view. Because of their inherent properties, views usually make it difficult for the optimizer to generate the optimal execution plan.

如Oracle官方所言,尽管视图有这样、那样的好处,但过度使用视图,特别是采取视图套视图、多层嵌套策略设计视图的最大弊端就是可能导致Oracle优化器无法产生最优的执行计划,从而导致语句性能下降。解决此问题的一个简单策略就是:减少视图设计的层次性和复杂性,甚至直接改为对基表(Base Table)操作。
以本人的经验和体会,此类问题更深层次原因在于如下几方面:首先,某些设计开发人员其实并没有很强的性能管理和优化意识,若在设计、开发阶段就能加强性能管理和优化工作,这类问题都可在投入到生产系统之前就被发现,并加以有效解决的。其次,设计开发者考虑问题缺乏一种全局性和综合平衡能力,具体而言就是过于追求软件设计的模块化、层次性、通用性等,而缺乏量化管理和质量控制意识。本人还曾在某社保系统见识过客户大量使用自定义函数,大部分字段都通过这些自定义函数进行访问,导致相应字段的索引被抑制而无法使用,从而最终导致大面积性能问题。同样都是因为过于追求模块化、层次性、通用性,而忽略和牺牲了系统的访问效率。
回到视图话题,如何有效制定视图设计和运用策略呢?本人建议的基本原则就是在模块化、层次性、通用性,以及系统性能方面达到综合平衡,更具体的建议就是:
(1) 首先,可以先在应用逻辑层面开展视图设计,甚至包括嵌套的视图设计。
(2) 其次,应分析相应SQL语句的质量,特别是执行计划、资源开销等。
(3) 第三,一旦发现出现性能问题,特别是由于视图设计而引起的性能问题,尝试简化视图设计的策略,例如改为对基表操作;改为静态中间表、物化视图操作;减少视图嵌套层次等策略。
总之,从视图设计和运用这个具体技术角度出发,可以折射出设计、开发人员驾驭大型复杂IT系统的眼界、视野和综合平衡能力。
2014年10月9日
2014-11-30 19:56 添加评论 分享
已邀请:

要回复问题请先登录注册

退出全屏模式 全屏模式 回复