SQL优化案例 - ROW LEVEL SECURITY 优化案例 - 《SQL优化入门》

在做报表开发的时候，有时我们会遇到这样的需求：不同权限的账户各自对应不同的权限，从而看到不同的数据，这时我们一般会采用 Row Level Security 实现这样的需求。2011 年，作者罗老师在惠普的时候，遇到过多起 Row Level Security 引发的 SQL 性能问题。

Obiee 报表开发人员发来邮件反映，使用权限较低的账户打开报表非常缓慢，报表运行了 15 分钟还没响应；而使用权限最高的账户，报表可以在 16 秒内执行完毕。执行缓慢的 SQL 代码如下。

select sum(nvl(T1796547.ACTL_GIV_AMT , 0)) as c1,
T1792779.ACCT_LONG_NAME as c2,
T1792779.NAME as c3,
T1796631.PRMTN_NAME as c4,
T1796631.PRMTN_ID as c5,
case  when case  when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end  is null then 'Private' else 
case  when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end  end  as c6,
T1796631.PRMTN_STTUS_CODE as c7,
T1796631.APPRV_BY_DESC as c8,
T1796631.APPRV_STTUS_CODE as c9,
T1796631.AUTO_UPDT_GTIN_IND as c10,
T1796631.CREAT_DATE as c11,
T1796631.PGM_START_DATE as c12,
T1796631.PGM_END_DATE as c13,
nvl(case  when T1796631.PRMTN_STTUS_CODE = 'Confirmed' then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as  
VARCHAR ( 10 ) ) end  , '') as c14,
T1796631.PRMTN_STOP_DATE as c15,
T1796631.SHPMT_START_DATE as c16,
T1796631.SHPMT_END_DATE as c17,
T1796631.CNBLN_WK_CNT as c18,
T1796631.ACTVY_DETL_POP as c19,
T1796631.CMMNT_DESC as c20,
T1796631.PRMTN_AVG_POP as c21,
T1792779.CHANL_TYPE_DESC as c22,
T1796631.PRMTN_SKID as c23
from 
OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
OPT_BUS_UNIT_FDIM T1796263,
OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
OPT_PRMTN_FDIM T1796631,
OPT_BASLN_FCT T1796547
where  ( T1792779.ACCT_SKID = T1796547.ACCT_SKID 
and T1792779.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID 
and T1796263.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID 
and T1796547.WK_SKID = T1796564.CAL_MASTR_SKID 
and T1796547.BUS_UNIT_SKID = T1796631.BUS_UNIT_SKID 
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326' 
and T1796263.BUS_UNIT_NAME = 'Japan' 
and T1796547.PRMTN_SKID = T1796631.PRMTN_SKID 
and T1796564.FISC_YR_ABBR_NAME = 'FY10/11' 
and T1792779.ACCT_LONG_NAME is not null 
-- add RLS
and T1796547.acct_skid IN (select  org.org_skid from (SELECT DISTINCT ap.org_skid
    FROM opt_acct_postn_lkp ap, opt_party_persn_lkp pp, opt_user_lkp u
    WHERE ap.postn_id = pp.party_id
      AND pp.persn_id = u.user_id
      AND u.login_name = 'BT0016'
    union select 0 as org_skid
    from sys.dual) org
   )
and T1792779.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796547.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796263.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796631.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
-- end RLS
and (case  when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' 
else T1796631.CORP_PRMTN_TYPE_CODE end  in ('Corporate', 'Planned', 'Private')) 
and T1796631.PRMTN_LONG_NAME in (select distinct T1796631.PRMTN_LONG_NAME as c1
from 
OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
OPT_BUS_UNIT_FDIM T1796263,
OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
OPT_PRMTN_FDIM T1796631,
OPT_PRMTN_PROD_FLTR_LKP T1796906
where  ( T1792779.ACCT_SKID = T1796906.ACCT_PRMTN_SKID 
and T1792779.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID 
and T1796263.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID 
and T1796564.CAL_MASTR_SKID = T1796906.DATE_SKID 
and T1796631.PRMTN_SKID = T1796906.PRMTN_SKID 
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326' 
and T1796263.BUS_UNIT_NAME = 'Japan' and T1796564.FISC_YR_ABBR_NAME = 'FY10/11' 
and T1796631.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID 
and (case  when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end  in ('Corporate', 'Planned', 
'Private')) and ROWNUM >= 1 ) ) ) 
group by T1792779.NAME, T1792779.CHANL_TYPE_DESC,
T1792779.ACCT_LONG_NAME, T1796631.PRMTN_SKID, 
T1796631.PRMTN_ID, T1796631.PRMTN_NAME, 
T1796631.SHPMT_END_DATE, T1796631.SHPMT_START_DATE, 
T1796631.PRMTN_STTUS_CODE, T1796631.APPRV_STTUS_CODE, 
T1796631.CMMNT_DESC, T1796631.PGM_START_DATE,
T1796631.PGM_END_DATE, T1796631.CREAT_DATE,
T1796631.APPRV_BY_DESC, T1796631.AUTO_UPDT_GTIN_IND, 
T1796631.PRMTN_STOP_DATE, T1796631.ACTVY_DETL_POP, 
T1796631.CNBLN_WK_CNT, T1796631.PRMTN_AVG_POP,
case  when case  
when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' 
then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end  is null 
then 'Private' else case  when 
T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' 
else T1796631.CORP_PRMTN_TYPE_CODE end  end , nvl(case  when 
T1796631.PRMTN_STTUS_CODE = 'Confirmed'
then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as  VARCHAR ( 10 ) )
end , '')
order by c23, c2;

执行缓慢的 SQL 与执行较快的 SQL 相比，缓慢的 SQL 在 where 条件中多了以下部分代码。

-- add RLS
and T1796547.acct_skid IN (select  org.org_skid from (SELECT DISTINCT ap.org_skid
    FROM opt_acct_postn_lkp ap, opt_party_persn_lkp pp, opt_user_lkp u
    WHERE ap.postn_id = pp.party_id
      AND pp.persn_id = u.user_id
      AND u.login_name = 'BT0016'
    union select 0 as org_skid
    from sys.dual) org
   )
and T1792779.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796547.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796263.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796631.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
-- end RLS

这部分代码就是实现 Row Level Security 功能的代码，对于权限较低的账户过滤掉一部分数据，而对于权限最高的账号不做过滤。如果不加 RLS 代码，报表能在 16 秒内执行完毕，但是增加了 RLS 代码，报表执行了 15 分钟不出结果。通过以上信息，我们判断是由于增加了 RLS 代码，导致执行计划发生了变化，从而导致 SQL 性能问题。

RLS 代码中有一个 in 子查询，in 子查询中有 union 关键字。在第 7 章中讲到过子查询非嵌套，当 where 条件中有子查询，优化器会尝试将子查询展开，从而消除 Filter。in 子查询中有 union 是可以展开的（unnest），而 exists 子查询中有 union 是不可以展开的。如果 where 条件中的子查询不能展开（no_unnest），执行计划中会出现 Filter，Filter 一般是在 SQL 的最后阶段执行。如果 where 条件中的子查询展开了，子查询会与主表提前关联。

因为增加了 RLS 代码导致 SQL 产生了性能问题，RLS 代码中有 in 子查询，因为 in 子查询可以展开（unnest），所以我们推断是优化器的子查询非嵌套（Subquery Unnesting）导致产生的性能问题，让 Obiee 开发人员在 in 子查询中添加 HINT：NO_UNNEST，让子查询不展开。子查询不展开，执行计划中就会出现 Filter，但是 Filter 是在最后进行过滤，子查询不展开就不会干扰原始的（跑得快的）执行计划，只是在跑得快的执行计划的最后一步添加 Filter 过滤而已。添加完 HINT 之后，SQL 能在 12 秒内执行完毕。

因为子查询中有 union，这里也可以不添加 HINT：NO_UNNEST，将 in 改写为 exists，这时优化器会自动走 Filter，也能达到优化目的。需要提醒大家的是，千万不要因为我们将 in 改写为 exists、exists 执行快就说 exists 性能比 in 高。如果有谁遇到本案例，将 in 改写为 exists，然后发布博客说今天又用 exists 优化了 in 子查询，这只会让人贻笑大方。

罗老师的个人技术博客中还记录了另一个 RLS 引发的性能问题，大家如有兴趣也可以查看网页：ROW LEVEL SECURITY(RLS) 性能问题。